How to compare two csv files?

Question

I have csv file1 that looks like

FLAGSTAFF AZ 50244.67 5.02 KA1_Podium_Garage_S FLAGSTAFF AZ 33752.13 3.38 KA1_Podium_Garage_S FLAGSTAFF AZ 11965.5 1.2 KA1_Podium_Garage_S FLAGSTAFF AZ 3966.48 0.4 KA1_Podium_Garage_S SEATTLE WA 12646.9 1.26 KA1_Podium_Garage_S SEATTLE WA 225053.92 22.51 KA1_Podium_Garage_S SEATTLE WA 23974.3 2.4 KA1_Podium_Garage_S SEATTLE WA 7036.4 0.7 KA1_Podium_Garage_S SEATTLE WA 3021.93 0.3 KA1_Podium_Garage_S 

I have a csv 2 file that looks like

 Alabama AL 1 Alaska AK 2 Arizona AZ 4 Arkansas AR 5 California CA 6 Colorado CO 8 Connecticut CT 9 SEATTLE WA 53 

Now I need to add the third column value from csv file2 to csv1 file, by comparing the second column

For example, it should look like this:

Code AZ - 4 Code WA - 53 where AZ, WA is in my csv1 file, the code should be added to the column

My output should look like this:

 FLAGSTAFF AZ 50244.67 5.02 KA1_Podium_Garage_S 4 FLAGSTAFF AZ 33752.13 3.38 KA1_Podium_Garage_S 4 FLAGSTAFF AZ 11965.5 1.2 KA1_Podium_Garage_S 4 FLAGSTAFF AZ 3966.48 0.4 KA1_Podium_Garage_S 4 SEATTLE WA 12646.9 1.26 KA1_Podium_Garage_S 53 SEATTLE WA 225053.92 22.51 KA1_Podium_Garage_S 53 SEATTLE WA 23974.3 2.4 KA1_Podium_Garage_S 53 SEATTLE WA 7036.4 0.7 KA1_Podium_Garage_S 53 SEATTLE WA 3021.93 0.3 KA1_Podium_Garage_S 53 

Here is the code I tried,

 with open("/home/sumit/Desktop/CSV_FILE1.csv", "r") as f: first = {rows[1]: rows[0:] for rows in list(csv.reader(f))} # compare second csv and append Code with open("CSVFILE2.csv", "r") as f1: for row in csv.reader(f1): if row[1] in first: first[row[1]].append(row[2]) # convert dict back to list merged = [(k,) + tuple(v) for k, v in first.items()] # write list to output csv with open("output.csv", "w") as f1: csv.writer(f1).writerows(merged) 

The output am gets like,

 AZ FLAGSTAFF AZ 44230.4 4.42 KA1_Podium_Garage_S 4 WA SEATTLE WA 45329.3 4.53 KA1_Podium_Garage_S 53 

Answer 1

This line creates a state abbreviated dictionary from your first file. This does not look right, since the dictionary can only have one key at a time, and you have several lines with "AZ" for example.

 first = { rows[1]: rows[0:] for rows in list(csv.reader(f)) } 

Instead, you should make your dictionary based on the second file:

 with open("CSVFILE2.csv", "r") as f1: code = { row[1]: row[2] for row in csv.reader(f1) } 

Then collapse your first file and add the appropriate code to each line.

 with open("/home/sumit/Desktop/CSV_FILE1.csv", "r") as f: merged = [ row + [code[row[1]]] for row in csv.reader(f) ] 

And write the combined data into a new file.

---

Explanation row + [code[row[1]]] :

If the string ["FLAGSTAFF", "AZ", 50244.67, 5.02, "KA1_Podium_Garage_S"] , then

• row[1] - "AZ" ,
• code[row[1]] code["AZ"] , which 4 ,
• [code[row[1]]] is a list of [4]
• and row + [code[row[1]] - a list combining that creates the desired new row value: ["FLAGSTAFF", "AZ", 50244.67, 5.02, "KA1_Podium_Garage_S", 4]