Using Pandas how to copy row value based on two Columns from different csv file-CodePudding

I am working with pandas to transform csv data and I am stuck here.
I have two CSV files Test1.csv and Test2.csv. Both files contain two columns ID and Name
Based on Test1.csv ID, I am trying to find an ID value in Test2.csv and find the name corresponding with that ID, and copy it into a new file output.csv.
FOR EXAMPLE:
I have a file Test1.csv

ID,Name
1,John
2,Doe
3,Adam
4,Casy
5,Jane
6,Elot
7,Doe
8,xavi

And another file Test2.csv

ID,Name
1,Casy
3,Adam
2,Tom 
4,Bruno
5,Johnop
6,John

Based on the ID of Test1.csv I am trying to look for that ID value in Test2.csv with the corresponding Name value and store it in a new file with oldName and newName column as mentioned in output.csv file.
Expected Output: output.csv

oldName,ID,newName
John,1,Casy
Doe,2,Tom
Adam,3,Adam 
Casy,4,Bruno
Jane,5,Johnop
Elot,6,John
Doe,7,
xavi,8,

CodePudding user response：

read Test1.csv and Test2.csv as pandas df

try output = Test1.merge(Test2,left_on='ID',right_on='ID',how='left')

and save the output df as output.csv

CodePudding user response：

Try this:

df1 = pd.read_csv('Test1.csv')
df2 = pd.read_csv('Test2.csv')

merged = pd.merge(df1, df2, on='ID', how='left').fillna('')
merged = merged.rename(columns={'Name_x': 'oldName', 'Name_y': 'newName'})
merged[['oldName', 'ID', 'newName']].to_csv('output.csv', index=False)