python replace multiple values from all columns in dataframe pandas

user_15627896 0
Viewed 1837

Below is a sample of my table.

DataFrame

Below is the code for the table:

d = {'1978': ['10k', '20000'],
    '1979': ['30k', '2M'],
    '1980': ['60000', '20k'],
    '1981': ['10000', '1M'],
    '1982': ['15000', '70k'],
    '1983': ['12k', '8M']}
df = pd.DataFrame(data=d)

Actually, the one I am working has 60 columns and 200 rows. However, It's the same structure.

My goal is to replace "k" for "000" and "M" for "000000" for all the rows of the many columns.

So the output should be:

Expected DataFrame

If someone could share with me the code to get this desired output, I would really appreciate.

multiple-value pandas python replace
2 Answers

You can use pandas.DataFrame.replace with a dictionary as argument and regex=True:

new_df = df.replace({'k':'000', "M": "000000"}, regex=True)
user_104090930

Hi a quick solution is to convert the columns to string, do the character replacement and then convert back to int or float. This can be done columns by column:

import pandas as pd

d = {'1978': ['10k', '20000'], '1979': ['30k', '2M'], '1980': ['60000', '20k'], '1981': ['10000', '1M'], '1982': ['15000', '70k'], '1983': ['12k', '8M']}
df = pd.DataFrame(data=d)
for col in df.columns:
    df[col] = (
        df[col].astype(str)
        .str.replace("k", "000")
        .str.replace("M", "000000")
        .astype(int)
    )

or as a whole:

df = (
   df.astype(str)
   .str.replace("k", "000")
   .str.replace("M", "000000")
   .astype(int)
)
user_64258220
Related