How to Replace a column in a CSV file in Python?

Question

I have 2 csv files. I need to replace a column in one file with a column from the other file but they have to stay sorted according to an ID column.

Here's an example:

file1:

ID, transect, 90mdist                                      
1, a, 10,                                                  
2, b, 20,                                                
3, c, 30,     

file2:

ID, transect, 90mdist                                
1, a, 50                                                   
2, b, 70                                                     
3, c, 90          

basically I created a new file with the correct 90mdist and I need to insert it into the old file but it has to line up with the same ID #.

It's my understanding that Python treats csv files as a string. so I can either use a dictionary or convert the data into a list and then change it? which way is best?

Any help would be greatly appreciated!!

Answer 1

The CSV Module in the Python Library is what you need here.

It allows you to read and write CSV files, treating lines a tuples or lists of items.

Just read in the file with the corrected values, store the in a dictionary keyed with the line's ID.

Then read in the second file, replacing the relevant column with the data from the dict and write out to a third file.

Done.