How to replace specific words in specific column with another from excel

user_12263285 0
Viewed 1635
  • I've problem with replacing, here's what I wrote, I need to replace 1999 with 1900 as you can see. I started recently, so please excuse me. (I searched a lot and watched clips on YouTube, but the method didn't work.)
        import pandas as pd
        df = pd.read_excel('book1.xlsx')

        #replace
        df.replace("1999","1900")

        #I also tried this method, but it didn't work.
        #df.replace(to_replace = "1999", value = "1900")

        #writer
        writer = pd.ExcelWriter('book2.xlsx')
        df.to_excel(writer,'new_sheet')
        writer.save()
  • My second question, how can I replace data through a text file (or Excel), for example, replace 1999 (in column A, book1.xlsx) with the column b in mistakes.xlsx.
 A     B
 1999   1900

Thank you guys for the help.

pandas xlsx dataframe python-3.x python
2 Answers

You could define a function and apply it element-wise with Series.apply:

df = pandas.DataFrame.from_records([('Cryptonomicon', 1999), ('Snow Crash', 1992), ('Quicksilver', 2003)], columns=['Title', 'Year'])
# df is:
#             Title  Year
#  0  Cryptonomicon  1999 
#  1     Snow Crash  1992
#  2    Quicksilver  2003



# Imagine this dataframe came from an Excel spreadsheet...
df_replacements = pandas.DataFrame.from_records([(1999, 1900), (2003, 3003)], columns=['A', 'B'])

replacements = pandas.Series(df_replacements['B'].values, index=df_replacements['A'])

def replaced(value):
    return replacements.get(value, value)

df['Year'] = df['Year'].apply(replaced)

# df is:
#             Title  Year
#  0  Cryptonomicon  1900
#  1     Snow Crash  1992
#  2    Quicksilver  3003

If you have a very large dataframe, you could vectorize this using pandas.Series.map():

year = df['Year']
df['Year'] = year.where(~year.isin(replacements.keys()), 
                        year,
                        year.map(replacements))
user_17185750

This should work. It will work with either string or numbers but it will store the values as string. If you know that you only have 1999 as a number then just remove .astype(str) and take out the single quotes around the years.

df=pd.read_excel('book1.xlsx',sheetname='Sheet1')

for key, value in df.iteritems(): 
    df[key] = df[key].astype(str).replace(to_replace='1999', value='1900')

writer=ExcelWriter('book2.xlsx')
df.to_excel(writer,'new_sheet',index=False)
writer.save()
user_64068750
Related