How to convert multiple columns from string to integer in pandas dataframe?

Question

I have a dataframe which has multiple year columns with data.

df_all = pd.read_csv('../filename.csv', header=2, skiprows= range(38,120), 
                     encoding = "ISO-8859-1")

    Code    Persons    1981    1982        1983      1984        1985 ....
    S002    Angus      5,180   46,650      5,568     265,708     344,500

I want to convert the data for the year columns (1981 to 2020) from 'object' to 'int64'.

I have tried

df_all['1981'] = df_all['1981'].apply(np.int64)

df_all['1981'] = df_all['1981'].astype('int64')

which led to ValueError: invalid literal for int() with base 10: '5,180'

and

int(df_all['1981'])
TypeError: cannot convert the series to <class 'int'>

float(int(df_all['1981'])
TypeError: cannot convert the series to <class 'float'>

so I dont know why it does not work even for a single column?

Also, is there there a way to convert them all at once using cols=df_all.loc[:, '1981':'2019']?

FYI if you load your dataframe using the "thousands" parameter, you won't have to convert those columns at all (they'll be correctly detected as int64 in the first place): pd.read_csv('filename.csv', thousands=',') — tdy
– tdy, Commented Apr 9, 2024 at 14:58

Anurag Dabas · Accepted Answer · 2021-05-11 14:49:02Z

6

Try with replace():

df_all['1981'] = df_all['1981'].replace(',','',regex=True)

Now try with astype() method:

df_all['1981'] = df_all['1981'].astype('int64')

If you want to convert multiple columns then:

df[df.columns[2:]]=df[df.columns[2:]].replace(',','',regex=True).astype('int64')

edited May 11, 2021 at 14:49

answered May 11, 2021 at 14:43

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bluetail Over a year ago

I wonder if somebody advises if I can set it via skiprows= ?

Anurag Dabas Over a year ago

If you will use skip rows parameter of read_csv() method then you lose your data and it isn't necessary that the error arises in particular top n rows and bottom n rows

Collectives™ on Stack Overflow

How to convert multiple columns from string to integer in pandas dataframe?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related