Trying to replace old "names" with new "names" in Python

I have two Excel files. One of the Excel files has the new and old names in it in a random order, something like this:

Colum A        Colum B
apple-er3      horse-er45
alk-er43       erk-bf43

and it continues for around 400 rows.

The other is filled with the old names in a specific order:

colum A         colum B
*old name*       *old name*

I want to be able to translate all the old names in it with the new.

I was thinking about making a dictionary but I can't figure out a good way of writing it when there are so many names to translate. I would like to go into the Excel with the old names, copy one colum at the time and then paste it into the Python program for it to translate it all to the new names.

Given a CSV file that looks something like:

apple-er3,horse-er45
alk-er43,erk-bf43

you can build a dictionary that translates between the two very easily:

import csv

with open('names.csv') as f:
    name_trans = dict(csv.reader(f))

dict() will take an iterable of pairs (which is what a csv.reader of a 2-column CSV file will give you) and turn it into a dictionary where the first item is the key and the second item is the value, so the above two lines of code is all you need to build the name translation dictionary you want.

If the CSV has a header you need to skip, or if it has other columns you need to ignore, those are modifications that only represent another line or two of code; the basic strategy is the same.

Once you have name_trans you can do:

new_name = name_trans[old_name]

to translate any old_name to its corresponding new_name.

I find pandas quite handy for loading data from [and saving data to] spreadsheets. You could use read_excel to read the old and new names from the fist file and convert them into a dictionary (tRef_dict) as below.

_{[NOTE: If your file is a CSV, use read_csv instead of read_excel.]}

import pandas as pd

tRef_fp = 'map_old_new.xlsx' ## path to file with new&old names
tRef_dict = pd.read_excel(tRef_fp).to_dict('split')
tRef_dict = {k:v for v,k,*_ in tRef_dict['data']} # colA->new, colB->old
# tRef_dict = dict(tRef_dict['data']) # only 2 cols, colA->old, colB->new

• Use pd.read_excel(tRef_fp, header=None).to_dict('split') if the sheet doesn't have a header row (otherwise, the first row would not be included in tRef_dict['data']).
• With your example data, dict(tRef_dict['data']) would return

  {'apple-er3': 'horse-er45', 'alk-er43': 'erk-bf43'}
  # 'apple-er3' will get translated to  'horse-er45'
  

• and {k:v for v,k,*_ in tRef_dict['data']} would return

  {'horse-er45': 'apple-er3', 'erk-bf43': 'alk-er43'}
  # 'horse-er45' will get translated to  'apple-er3'
  

Then, you can load the file with only old names, use .applymap and tRef_dict to update the names, and then save the updated names with .to_excel ^{(or .to_csv to save to a CSV file)} :

old_fp = 'old.xlsx' ## path to file with old names
new_fp = 'new.xlsx' ## [can be the same as old_fp]

names_df = pd.read_excel(old_fp) #, header=None) ## if there is no header row, specify
names_df = names_df.applymap(lambda x: tRef_dict.get(x,x)) ## [update names]
names_df.to_excel(new_fp, index=False) #, header=False) ## [save updated]