CODEWITHSUNDEEP
pandasdataframe
Jason Sun
Jason Sun

Reputation: 73

Pandas how to replace multiple columns of str% to float number?

          A        B         C       D        E      F
0    CEKAPE   23.60%   117.374   0.53%    0.11%    <2%
1    HYTFFZ   25.32%   116.250   1.88%    0.38%     5%

The df has several columns that contain percetage numbers as string type. This code only process one column at a time. df['D'] = df['D'].str.strip('%').astype(float) / 100 How to write the code while dozens of cloumns need to be processed?

          A        B         C        D        E      F
0    CEKAPE   0.2360   117.374   0.0053   0.0011    <2%
1    HYTFFZ   0.2532   116.250   0.0188   0.0038   0.05

Upvotes: 1

Views: 94

Answers (1)

ansev
ansev

Reputation: 30920

Use pd.to_numeric with errors = coerce and DataFrame.stack + DataFrame.unstack avoiding having to use apply:

new_df = (pd.to_numeric(df.replace('%','',regex = True).stack(),
                        errors = 'coerce')
            .div(100)
            .unstack()
            .fillna(df))
print(new_df)
        A       B        C       D       E     F
0  CEKAPE   0.236  1.17374  0.0053  0.0011   <2%
1  HYTFFZ  0.2532   1.1625  0.0188  0.0038  0.05

or dropna = False and Series.str.replace

new_df = (pd.to_numeric(df.stack(dropna = False).str.replace('%',''),
                        errors = 'coerce')
            .div(100)
            .unstack()
            .fillna(df)
         )

Upvotes: 1

Related Questions