Python Update A Column Value Of A Csv File According To Another Csv File

For example consider the files a.csv 1 23 'better' 0 2 21 'bad' 0 3 34 'good' 0 and b.csv 23 2 34 5 We can get the result: 1 23 'better' 2 2 21 'bad' 0 3 34 'good'

Solution 1:

Thanks for making the question clearer. This code does not modify file A inplace and instead it uses output file fileC.

import csv #imports module csv

filea = "fileA.csv"
fileb = "fileB.csv"
output = "fileC.csv"

delim = ";" #set your own delimiter

source1 = csv.reader(open(filea,"r"),delimiter=delim)
source2 = csv.reader(open(fileb,"r"),delimiter=delim)
#open csv readers

source2_dict = {}

# prepare changes from file B
for row in source2:
    source2_dict[row[0]] = row[1]

# write new changed rows
with open(output, "w") as fout:
    csvwriter = csv.writer(fout, delimiter=delim)
    for row in source1:
        # needs to check whether there are any changes prepared
        if row[1] in source2_dict:
            # change the item
            row[3] = source2_dict[row[1]]
        csvwriter.writerow(row)

I hope I understood your intention well.

Just a short explanation of the steps:

• First you specify the paths to source files and an output file and you also specify the delimiter.
• Then you load CSV readers using csv module.
• You read all the changes from source file B and store it in a
  dictionary.
• And then you iterate through file A, modify the row when necessary and then you save it to output file.

Solution 2:

1. read a.csv

   a_content = [l.split() for l in open('a.csv')]
   

2. read b.csv

   b_dict = {k:v for k,v in [l.split() for l in open('b.csv')]}
   

3. output the modified data

   for rec in a_content:
       if rec[1] in b_dict:
          rec[3] = b_dict[rec[1]]
       outfile.write(" ".join(rec)+'\n')
   

---

Addendum

Thinking about it, a single print statement will do

print "\n".join(" ".join(i[:-1]+([b[i[1]]]if i[1]in b else[i[-1]]))for b in[{k:v for k,v in[l.split()for l in open('b.csv')]}]for i in[l.split()for l in open('a.csv')])