Update one column of one csv based on column from another in Python

Question

I have two csv s like below :-

Master.csv

Column1	Column2	Column3	Column4
First	1001	Test1	1003
Second	1002	Test2	1004

Reference.csv

Code	Value
1001	Val1
1002	Val2
1003	Val3
1004	Val4

As can be seen the Column2 and Column4 of Master.csv values are present in the same column Code of Reference.csv and I want to join the two csv to populate like below :-

Column1	Column2	Column3	Column4
First	Val1	Test1	Val3
Second	Val2	Test2	Val4

Please let me know it can be achieved through Python ..

I tried to join to csvs but unable to get the expected outputs.

The column of the first CSV is basically populated based on the columns from the second csv by a existing product process ,so there is no possibility of having no match.... — Utso Das
– Utso Das, Commented Aug 15, 2024 at 7:47

jezrael · Accepted Answer · 2024-08-15 07:38:58Z

3

Create DataFrames first by read_csv and use DataFrame.replace:

df1 = pd.read_csv('Master.csv')
df2 = pd.read_csv('Reference.csv', index_col=['Code'])

out = df1.replace(df2['Value'])
print (out)
  Column1 Column2 Column3 Column4
0   First    Val1   Test1    Val3
1  Second    Val2   Test2    Val4

If need write to csv:

out.to_csv('output.csv', index=False)

If need replace only columns from list:

df1 = pd.read_csv('Master.csv')
df2 = pd.read_csv('Reference.csv', index_col=['Code'])

cols = ['Column2', 'Column4']
df1[cols] = df1[cols].replace(df2['Value'])

df1.to_csv('output.csv', index=False)

edited Aug 15, 2024 at 7:38

answered Aug 15, 2024 at 7:18

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Utso Das Over a year ago

Thanks for your prompt answer . I tried the approach and it is creating the output csv but basically it is pasting the same content of Master csv to output csv and the update is not happening .

jezrael Over a year ago

@UtsoDas - it means data are different data - strings vs integers

Utso Das Over a year ago

Thanks ,I changed the types of all cells in String while reading the csv and currently it is working ...Will test on more in this ..

Collectives™ on Stack Overflow

Update one column of one csv based on column from another in Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related