Cleaning data using pandas and excel

Question

I have a massive data frame which I exported as an excel file to fix up spelling by removing duplicates and creating a column with all words corrected. Now I want to reimport the corrected data and replace the old values with the new ones so in the data frame every instance of 'Ne York' would become 'New York'. Here Location is the value in the data frame and Final Location is the edited one in excel.

Location Final Location
New Yo  New York
Austin  Austin
Londn   London
Pais    Paris
Berlin  Berlin
Mosscow Moscow
Varsaw  Warsaw

Any help would be greatly appreciated.

Answer 1

You can load the new excel file as dataframe as follows.

import pandas as pd

df = pd.read_excel(r'Path/Filename.xlsx')
print(df)

if you want to replace the location column in the old dataframe with Final column, you can do: df_old['Location'] = df['Final']