'Using Pandas how to copy row value based on two Columns from different csv file
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,
Solution 1:[1]
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
Solution 2:[2]
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)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | micro5 |
| Solution 2 | Prabhakar Kalaiselvan |