Python Pandas inferring column datatypes

Question

I am reading JSON files into dataframes. The dataframe might have some String (object) type columns, some Numeric (int64 and/or float64), and some datetime type columns. When the data is read in, the datatype is often incorrect (ie datetime, int and float will often be stored as "object" type). I want to report on this possibility. (ie a column is in the dataframe as "object" (String), but it is actually a "datetime").

The problem i have is that when i use pd.to_numeric and pd.to_datetime they will both evaluate and try to convert the column, and many times it ends up depending on which of the two I call last... (I was going to use convert_objects() which works but that is depreciated, so wanted a better option).

The code I am using to evaluate the dataframe column is (i realize a lot of the below is redundant, but I have written it this way for readability):

try:
   inferred_type = pd.to_datetime(df[Field_Name]).dtype
   if inferred_type == "datetime64[ns]":
      inferred_type = "DateTime"
except:
   pass
try:
   inferred_type = pd.to_numeric(df[Field_Name]).dtype
   if inferred_type == int:
      inferred_type = "Integer"
   if inferred_type == float:
      inferred_type = "Float"
except:
   pass

Answer 1

I came across the same problem of having to figure out column types for incoming data where the type is not known beforehand (from a database read in my case). I couldn't find a good answer here on SO, or by reviewing the Pandas source code. I solved it using this function:

def _get_col_dtype(col):
        """
        Infer datatype of a pandas column, process only if the column dtype is object. 
        input:   col: a pandas Series representing a df column. 
        """

        if col.dtype == "object":
            # try numeric
            try:
                col_new = pd.to_datetime(col.dropna().unique())
                return col_new.dtype
            except:
                try:
                    col_new = pd.to_numeric(col.dropna().unique())
                    return col_new.dtype
                except:
                    try:
                        col_new = pd.to_timedelta(col.dropna().unique())
                        return col_new.dtype
                    except:
                        return "object"
        else:
            return col.dtype