Pandas astype throwing invalid literal for int() with base 10 error

user_5181947 0
Viewed 1521

I have a pandas dataframe df whose column name and dtypes are specified in another file (read as data_dict). So to get the data properly I am using the below code:

col_list = data_dict['name'].tolist()
dtype_list = data_dict['type'].tolist()
dtype_dict = {col_list[i]: dtype_list[i] for i in range(len(col_list))}
df.columns = col_list
df = df.fillna(0)
df = df.astype(dtype_dict)

But it is throwing this error:

invalid literal for int() with base 10: '2.230'

Most of the answers I searched online recommended using pd.to_numeric() or something like df[col1].astype(float).astype(int). The issue here is that df contains 50+ columns out of which around 30 should be converted to integer type. Therefore I don't want to convert the data types one column at a time.

So how can I easily fix this error?

pandas python
3 Answers

Try via boolean masking:

mask=df.apply(lambda x:x.str.isalpha(),1).fillna(False)

Finally:

df[~mask]=df[~mask].astype(float).astype(int)

Or

cols=df[~mask].dropna(axis=1).columns
df[cols]=df[cols].astype(float).astype(int)
user_142898920

df[col_list] = pd.to_numeric(df[col_list])

user_31303050

You can set the data type of the whole dataframe like this:

import pandas as pd
df = pd.DataFrame({'A': map(str, np.random.rand(10)), 'B': np.random.rand(10)})
df.apply(pd.to_numeric)

          A         B
0  0.493771  0.389934
1  0.991265  0.387819
2  0.398947  0.128031
3  0.869156  0.007609
4  0.129748  0.532235
5  0.993632  0.882933
6  0.244311  0.213737
7  0.773192  0.229257
8  0.392530  0.339418
9  0.732609  0.685258

and for just some columns like this:

df[['A', 'B']] = df[['A', 'B']].apply(pd.to_numeric)

In case you want to have a way to convert types to float for whole dataframe where you do not know which column has numbers, you can use this:

import pandas as pd
df = pd.DataFrame({'A': map(str, np.random.rand(10)), 'B': np.random.rand(10), 'C': [x for x in 'ABCDEFGHIJ']})

def to_num(df):
    for col in df:
        try:
            df[col] = pd.to_numeric(df[col])
        except:
            continue
    return df

df.pipe(to_num)

          A         B  C
0  0.762027  0.095877  A
1  0.647066  0.931435  B
2  0.016939  0.806675  C
3  0.260255  0.346676  D
4  0.561694  0.551960  E
5  0.561363  0.675580  F
6  0.312432  0.498806  G
7  0.353007  0.203697  H
8  0.418549  0.128924  I
9  0.728632  0.600307  J
user_119717850
Related