What is the proper way to identify the data type of columns in a pandas dataframe?

user_11474852 0
Viewed 1446

I'm currently working on a project where I need to transform the data in a pandas dataframe to another representation that uses a different (non-python) type system. The transformation is not covered by one of pandas' serialization/io methods. In particular I need to map the pandas dataframe column data types to those of the other type system. For starters, let's assume the target type system to be pretty simple having only string, integer, float, boolean, and timestamp types.

So, I started by looking at the dataframe dtypes with a simple example:

import pandas as pd
from datetime import datetime

headers = ["string", "integer", "float", "boolean", "timestamp"]
data = [["a", 1, 1.0, True, datetime.now()]]

df = pd.DataFrame(data, columns=headers)
dts = df.dtypes

for col in dts.index:
  print("column: ", col, " - type: ", dts[col].name)

which gave me:

column:  string  - type:  object
column:  integer  - type:  int64
column:  float  - type:  float64
column:  boolean  - type:  bool
column:  timestamp  - type:  datetime64[ns]

Okay, getting object for string columns is not nice so I found the Dataframe.convert_dtypes() method which, when added to the dataframe creation line gave me:

column:  string  - type:  string
column:  integer  - type:  Int64
column:  float  - type:  Int64
column:  boolean  - type:  boolean
column:  timestamp  - type:  datetime64[ns]

Better for my string column, but now I'm getting Int64 (with a capital "I") for both my integer and float columns (!) and boolean instead of bool. (Okay, I'm back to float64 when I use a "real" float such as "0.1" in the example data but still...)

That made me wonder whether I'm on the right track with this approach. I then looked at the numpy dtype documentation and the numpy dtype charcodes. But there doesn't seem to be a charcode for every possible data type, esp. not for string types. Also, pandas' extension dtypes that I get after applying convert_dtypes() don't have the char attribute anymore.

So my question is, what is the canonical way to obtain data type identifiers for the columns in a pandas dataframe that I can use for mapping those data types to another type system?

pandas dataframe python types
1 Answers

df.dtypes is the canonical way to obtain data type identifiers. You can print for each dtype the associated underlying numpy dtype code with <dtype>.str. You can also get the kind (integer, float, ...) with <dtype>.kind:

import pandas as pd
from datetime import datetime

headers = ["string", "integer", "float", "boolean", "timestamp"]
data = [["a", 1, 1.0, True, datetime.now()]]

df = pd.DataFrame(data, columns=headers)

dts = df.dtypes
for index, value in dts.items():
    print("column %s dtype[class: %s; name: %s; code: %s; kind: %s]" % (index, type(value), value.name, value.str, value.kind))

yields:

column string dtype[class: <class 'numpy.dtype'>; name: object; code: |O; kind: O]
column integer dtype[class: <class 'numpy.dtype'>; name: int64; code: <i8; kind: i]
column float dtype[class: <class 'numpy.dtype'>; name: float64; code: <f8; kind: f]
column boolean dtype[class: <class 'numpy.dtype'>; name: bool; code: |b1; kind: b]
column timestamp dtype[class: <class 'numpy.dtype'>; name: datetime64[ns]; code: <M8[ns]; kind: M]

The issue is that some datatypes are defined specifically in pandas as you noted, but they are backed by numpy datatypes (they have numpy datatype codes). For example, numpy defines the datetime64[ns] that you can see above, but pandas defines a timezone-localized dtype on top of it. You can see it :

# localize with timezone
df['timestamp'] = pd.DatetimeIndex(df['timestamp']).tz_localize(tz='UTC')

# look at the dtype of timestamp: now a pandas dtype
index, value = 'timestamp', df.dtypes.timestamp
print("column %s dtype[class: %s; name: %s; code: %s; kind: %s]" % (index, type(value), value.name, value.str, value.kind))

yields

column timestamp dtype[class: <class 'pandas.core.dtypes.dtypes.DatetimeTZDtype'>; name: datetime64[ns, UTC]; code: |M8[ns]; kind: M]

Now the dtype class is a custom pandas class (DatetimeTZDtype), while the underlying dtype code is a numpy one. The same would go if you use string datatypes, that are not in numpy by default.

So to sum-up, to reach your original goal, you should first look at the type(<dtype>) and if it is not a custom pandas one, then to look at the numpy <dtype>.kind (It is preferrable to <dtype>.str as numpy allows you to define many kind of integers (big/little endian, nb of bits, etc.)).

Finally as you found out, Dataframe.convert_dtypes() is a converter, and it has parameters to select which auto-conversion feature to turn on/off.

user_72622470
Related