32

I have a data frame where all the columns are supposed to be numbers. While reading it, some of them were read with commas. I know a single column can be fixed by

df['x']=df['x'].str.replace(',','')

However, this works only for series objects and not for entire data frame. Is there an elegant way to apply it to entire data frame since every single entry in the data frame should be a number.

P.S: To ensure I can str.replace, I have first converted the data frame to str by using

df.astype('str')

So I understand, I will have to convert them all to numeric once the comma is removed.

bad_coder
  • 11,289
  • 20
  • 44
  • 72
PagMax
  • 8,088
  • 8
  • 25
  • 40

3 Answers3

62

Numeric columns have no ,, so converting to strings is not necessary, only use DataFrame.replace with regex=True for substrings replacement:

df = df.replace(',','', regex=True)

Or:

df.replace(',','', regex=True, inplace=True)

And last convert strings columns to numeric, thank you @anki_91:

c = df.select_dtypes(object).columns
df[c] = df[c].apply(pd.to_numeric,errors='coerce')
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    is `regex=True` needed here? I think I'm missing something – Adam.Er8 Jul 09 '19 at 07:30
  • 3
    ok, got it: `to_replace: [...], str: string **exactly** matching to_replace will be replaced with value` – Adam.Er8 Jul 09 '19 at 07:30
  • 1
    Thanks @Jezrael. This works great. I would have never figured out the last part which converts strings columns to numeric, on my own! – PagMax Jul 09 '19 at 09:58
  • when I tried the same for replacing dot , this line is replacing all cells with empty values and am getting an empty dataframe. df.replace('.' , '' , regex=True, inplace=True). Why is '.' matching with every entry? – SuperUser Jun 01 '21 at 18:18
  • @SuperUser - You can check [this](https://stackoverflow.com/a/13989661/2901002) – jezrael Jun 02 '21 at 05:32
4

Well, you can simplely do:

df = df.apply(lambda x: x.str.replace(',', ''))

Hope it helps!

Azuuu
  • 853
  • 8
  • 17
1

In case you want to manipulate just one column:

df.column_name = df.column_name.apply(lambda x : x.replace(',',''))
janw
  • 8,758
  • 11
  • 40
  • 62
Rakesh Babu
  • 79
  • 1
  • 2
    OP explicitly asked about manipulating the entire DataFrame, after showing a way to manipulate just one column. So this does not help. – Karl Knechtel Apr 26 '21 at 13:18