pymapd
pymapd copied to clipboard
Column type detection issues
I tried to upload a pandas dataframe to Immerse however I got the following errors
Input:
user_str = 'API Key Name'
password_str = 'API Key Secret'
host_str = 'use2-api.mapd.cloud'
dbname_str = 'mapd'
connection = connect(user=user_str, password=password_str, host=host_str, dbname=dbname_str, port=443, protocol='https')
table_name = 'DivvyData'
print(DivvyUse.dtypes)
connection.load_table(table_name, DivvyUse)
TRIP ID object
START TIME object
STOP TIME object
BIKE ID float64
TRIP DURATION object
FROM STATION ID float64
FROM STATION NAME object
TO STATION ID float64
TO STATION NAME object
USER TYPE object
GENDER object
BIRTH YEAR float64
FROM LATITUDE float64
FROM LONGITUDE float64
FROM LOCATION object
TO LATITUDE float64
TO LONGITUDE float64
TO LOCATION object
Boundaries - ZIP Codes float64
Zip Codes object
Community Areas float64
Wards float64
FROM ZIPCODE int64
FROM REGION object
FROM TRANSIT TYPE object
TO ZIPCODE int64
TO REGION object
TO TRANSIT TYPE object
GENERATION object
Age float64
dtype: object
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
~/miniconda3/lib/python3.7/site-packages/pandas/core/nanops.py in f(values, axis, skipna, **kwds)
126 else:
--> 127 result = alt(values, axis=axis, skipna=skipna, **kwds)
128 except Exception:
~/miniconda3/lib/python3.7/site-packages/pandas/core/nanops.py in reduction(values, axis, skipna, mask)
741 else:
--> 742 result = getattr(values, meth)(axis)
743
~/miniconda3/lib/python3.7/site-packages/numpy/core/_methods.py in _amax(a, axis, out, keepdims, initial)
27 initial=_NoValue):
---> 28 return umr_maximum(a, axis, None, out, keepdims, initial)
29
TypeError: '>=' not supported between instances of 'int' and 'str'
During handling of the above exception, another exception occurred:
TypeError Traceback (most recent call last)
<ipython-input-41-38b62b814b8d> in <module>
10 table_name = 'DivvyData'
11 print(DivvyUse.dtypes)
---> 12 connection.load_table(table_name, DivvyUse)
~/miniconda3/lib/python3.7/site-packages/pymapd/connection.py in load_table(self, table_name, data, method, preserve_index, create)
499
500 if create:
--> 501 self.create_table(table_name, data)
502
503 if method == 'infer':
~/miniconda3/lib/python3.7/site-packages/pymapd/connection.py in create_table(self, table_name, data, preserve_index)
445 """
446
--> 447 row_desc = build_row_desc(data, preserve_index=preserve_index)
448 self._client.create_table(self._session, table_name, row_desc,
449 TFileType.DELIMITED, TCreateParams(False))
~/miniconda3/lib/python3.7/site-packages/pymapd/_pandas_loaders.py in build_row_desc(data, preserve_index)
201 if preserve_index:
202 data = data.reset_index()
--> 203 dtypes = [(col, get_mapd_dtype(data[col])) for col in data.columns]
204 # row_desc :: List<TColumnType>
205 row_desc = [
~/miniconda3/lib/python3.7/site-packages/pymapd/_pandas_loaders.py in <listcomp>(.0)
201 if preserve_index:
202 data = data.reset_index()
--> 203 dtypes = [(col, get_mapd_dtype(data[col])) for col in data.columns]
204 # row_desc :: List<TColumnType>
205 row_desc = [
~/miniconda3/lib/python3.7/site-packages/pymapd/_pandas_loaders.py in get_mapd_dtype(data)
28 "Get the OmniSci type"
29 if is_object_dtype(data):
---> 30 return get_mapd_type_from_object(data)
31 else:
32 return get_mapd_type_from_known(data.dtype)
~/miniconda3/lib/python3.7/site-packages/pymapd/_pandas_loaders.py in get_mapd_type_from_object(data)
74 return 'BOOL'
75 elif isinstance(val, int):
---> 76 if data.max() >= 2147483648 or data.min() <= -2147483648:
77 return 'BIGINT'
78 return 'INT'
~/miniconda3/lib/python3.7/site-packages/pandas/core/generic.py in stat_func(self, axis, skipna, level, numeric_only, **kwargs)
10954 skipna=skipna)
10955 return self._reduce(f, name, axis=axis, skipna=skipna,
> 10956 numeric_only=numeric_only)
10957
10958 return set_function_name(stat_func, name, cls)
~/miniconda3/lib/python3.7/site-packages/pandas/core/series.py in _reduce(self, op, name, axis, skipna, numeric_only, filter_type, **kwds)
3628 'numeric_only.'.format(name))
3629 with np.errstate(all='ignore'):
-> 3630 return op(delegate, skipna=skipna, **kwds)
3631
3632 # TODO(EA) dispatch to Index
~/miniconda3/lib/python3.7/site-packages/pandas/core/nanops.py in f(values, axis, skipna, **kwds)
128 except Exception:
129 try:
--> 130 result = alt(values, axis=axis, skipna=skipna, **kwds)
131 except ValueError as e:
132 # we want to transform an object array
~/miniconda3/lib/python3.7/site-packages/pandas/core/nanops.py in reduction(values, axis, skipna, mask)
740 result = np.nan
741 else:
--> 742 result = getattr(values, meth)(axis)
743
744 result = _wrap_results(result, dtype, fill_value)
~/miniconda3/lib/python3.7/site-packages/numpy/core/_methods.py in _amax(a, axis, out, keepdims, initial)
26 def _amax(a, axis=None, out=None, keepdims=False,
27 initial=_NoValue):
---> 28 return umr_maximum(a, axis, None, out, keepdims, initial)
29
30 def _amin(a, axis=None, out=None, keepdims=False,
TypeError: '>=' not supported between instances of 'int' and 'str'
@sagoyal2 it looks like this is the source of the problem:
~/miniconda3/lib/python3.7/site-packages/pymapd/_pandas_loaders.py in get_mapd_type_from_object(data)
74 return 'BOOL'
75 elif isinstance(val, int):
---> 76 if data.max() >= 2147483648 or data.min() <= -2147483648:
77 return 'BIGINT'
78 return 'INT'
We'll have to look into why this would be happening, it's doing a test on the column to determine the data type and perhaps seeing the column as an int
when it contains str
types.
It only does this when it's trying to infer the data type, so as a workaround you can create your table first in OmniSci which will then read the types from the schema instead of trying to infer them. For reference, it's one (or more) of the object
data types in your Pandas dataframe that it's tripping up on.
Actually after looking at the code block it's debatable that it's a bug at all. If the type of the Pandas dataframe is object
then it takes the first value of the column and gets a type from it. In this case the first value in the column is an int
, so it assumes all the other values are. In this case there must be other rows in this column of str
type and hence the error.
We need to decide if this is "just the way it works" or if we do something more complicated with the auto detection, like check the types of the first 1000 rows instead, with additional rules for which data type wins when they are of different types.
If we decide we should do something, perhaps the most logical alternative would be to keep the detection the same as the OmniSci detection itself - ie. using the same logic as the detect_column_types
endpoint. An additional OmniSciDB endpoint could use the same detection logic but accept data instead of a filename. That way we don't have to replicate all the functionality of column detection in pymapd, and we could make detection consistent across other connectors also.