How implicitly determine dataframe column type in python? (implicit conversion)

user_3486773 0
Viewed 173

In my environment out of simplicity it was decided to store everything in hdfs as a string. So when I pull data into a pandas dataframe from this location, every type is a string, despite the values being ints, floats, bool etc...

Is there a way to dynamically determine a column property type based on the value evaluation? ie: look to see that every value in a column is 'x' type and then cast as such?

EDIT:

I couldn't get the below solution to work due to my version of python (I think). So I decided my own attempt at a hacky solution. This is probably not perfect and I haven't figured out dates yet. Because of those two things I'm not going to post it as a solution but maybe this can be a starting point for someone else needing it:

#get dtypes when we can - Doesn't do dates. 
for i in df:
    try:
        df[i] = df[i].astype(int)
        print(i, 'is an int')
    except:
        []
    try:
        if '.' in str(df[i]):
            df[i] = df[i].astype(float)
            print(i, 'is a float')
    except:
        []
    
    try:
        if df[i].replace('False', '').unique()=='True' or df[i].replace('False', '').unique() == 'TRUE':
            df[i] = df[i].replace('False', '').astype(bool).astype(int)
            print(i, 'is bool')     
    except:
        print(i, 'is an object')

Essentially I'm just attempting to cast and catch the error if it breaks. I'm sure this is probably a very poor way to go about this however.

pandas implicit-conversion python types
1 Answers

I'm not aware of any pandas build-in functionality to do it, but you can achieve implicit casting with python ast.literal_eval function.

Input data

df = pd.DataFrame(np.array([['1', '0.3', 'True'],
                             ['2', '5.2', 'False']]),
                   columns=['int', 'float', 'bool'])

Casting function

def cast_df(df):
    for column in df.columns:
        if df[column].dtype != np.object:
            break
        column_types = df[column].apply(lambda x: type(ast.literal_eval(x)))
        if len(column_types.unique()) == 1:
            print(f"Column {column} is casted to {column_types[0]}")
            df[column] = df[column].astype(column_types[0])
    return df

Output of cast_df(df).dtypes:

Column int is casted to <class 'int'>
Column float is casted to <class 'float'>
Column bool is casted to <class 'bool'>
int        int64
float    float64
bool        bool
dtype: object
user_17192310
Related