how to convert comma seperated values to integer in pandas

Question

I have following dataframe in pandas.

  order_id    no
  1           1,234,450,445.00 
  2           1,234,450,446.00
  3           1,234,450,447.00

I want to convert the no column to integer. Following is my desired dataframe.

  order_id    no
  1           1234450445 
  2           1234450446
  3           1234450447

When I do dtypes, it shows as float64

I tried following

df['no'] = (pd.to_numeric(df['no'].str.replace(',',''), errors='coerce'))

How can I convert this to integer in pandas?

Try df.no.str.replace(",", "").str[:-3].astype(int). If you are reading in the file as a csv, pandas has a thousands parameter — sammywemmy
– sammywemmy, Commented Jul 5, 2020 at 4:56
@sammywemmy It gives me following error. TypeError: only integer scalar arrays can be converted to a scalar index — Neil
– Neil, Commented Jul 5, 2020 at 4:58
what do you get when you use just df.no.str.replace(",", "").str[:-3]? — sammywemmy
– sammywemmy, Commented Jul 5, 2020 at 4:59
@Neil try this, df.no.str.replace(",", "").astype(float).astype(int) — sushanth
– sushanth, Commented Jul 5, 2020 at 5:03

Scott Boston · Accepted Answer · 2020-07-05 05:07:20Z

3

Here is a way, go through float type first:

df['no'].str.replace(',','').astype(float).astype(int)

Output:

0    1234450445
1    1234450446
2    1234450447
Name: no, dtype: int64

Or slice '.00' off then end of all rows:

df['no'].str.strip('.00').str.replace(',','').astype(int)

edited Jul 5, 2020 at 5:07

answered Jul 5, 2020 at 5:04

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

3 Comments

Neil Over a year ago

it is still giving me TypeError: only integer scalar arrays can be converted to a scalar index this error. Moreover no column is already in float64 format

Neil Over a year ago

When I try to convert it to string df['no'] = df['no'].astype(str) it gives same error TypeError: only integer scalar arrays can be converted to a scalar index

Scott Boston Over a year ago

@Neil Can you do this on the first five rows is it successful? You need to find the errant data. If so, try the next few rows until you find a rows that is causing the problem. Post that to this question if you are unsure how to fix.

wa007 · Accepted Answer · 2020-07-05 05:00:18Z

0

Hope can help you.

import pandas as pd
import numpy as np
df['no'] = df['no'].astype(str).apply(lambda x: "".join(x.split(","))).astype(np.float64).astype(np.int64)

or

df['new_no'] = df['no'].astype(str).apply(lambda x: int(float("".join(x.split(",")))))

answered Jul 5, 2020 at 5:00

wa007

1359 bronze badges

Collectives™ on Stack Overflow

how to convert comma seperated values to integer in pandas

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related