How to remove commas from ALL the column in pandas at once

python string pandas replace comma
PagMax picture PagMax · Jul 9, 2019 · Viewed 15.7k times · Source

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.

Answer

jezrael picture jezrael · Jul 9, 2019

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')

Related questions

Select by partial string from a pandas DataFrame

I have a DataFrame with 4 columns of which 2 contain string values. I was wondering if there was a way to select rows based on a partial string match against a particular column? In other words, a function or lambda function …

Read More
Convert columns to string in Pandas

I have the following DataFrame from a SQL query: (Pdb) pp total_rows ColumnID RespondentCount 0 -1 2 1 3030096843 1 2 3030096845 1 and I want to pivot it like this: total_data = total_rows.pivot_table(cols=['ColumnID']) (Pdb) pp total_data ColumnID -1 3030096843 3030096845 RespondentCount 2 1 1 [1 rows …

Read More
How to change the datetime format in pandas

My dataframe has a DOB column (example format 1/1/2016) which by default gets converted to pandas dtype 'object': DOB object Converting this to date format with df['DOB'] = pd.to_datetime(df['DOB']), the date gets converted to: 2016-01-26 and …

Read More