CODEWITHSUNDEEP
python
Huan Ren
Huan Ren

Reputation: 15

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'   5

Upvotes: 1

Views: 9081

Answers (2)

gboffi
gboffi

Reputation: 25093

  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')])

Upvotes: 0

Marek
Marek

Reputation: 885

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.

Upvotes: 5

Related Questions