Writing dictionary keys to a csv file based on common key and column value

Question

I have two CSV files that I need to join. One file contains part of the information I want. I created a dictionary of the information I need from CSV 1 and need to write that Dictionary to CSV 2. I would like to write the keys of the dictionary to their appropriate ID in CSV 2.

How can I write the dictionary to the CSV2? My code populates each entry of the pet column with the same value.

Examples of the dictionary, CSV2 and output CSV are provided to provide interpretability of the code.

Code below

Dictionary
{'511': 'Dog', '611': 'Cat'}

CSV
ID,   Location,   Country
511,  Stack,      USA
611,  Overflow    Mexico 
711,  Stack       USA

Expected CSV
ID,   Location,    Country,    Pet
511,  Stack,       USA,        Dog
611,  Overflow,    Mexico,     Cat
711,  Stack        USA,  

Output Generated CSV
ID,   Location,    Country,    Pet
511,  Stack,       USA,        Dog
611,  Overflow,    Mexico,     Dog
711,  Stack        USA,        Dog

def readfile(filename):
    global map 
    with open(filename, 'rb') as file:
        map = {}
        reader = csv.reader(file)               
        for row in reader:
            #print row[0]
            key = row[0]
            if key in map:
                pass
            map[key] = row[1:]
    file.close()
    return True
            

#print map.keys()

def writefile(filename):
    Location = 'Location'
    tmpfile = filename + '_tmp.csv'
    
    with open(filename, 'rb') as input:  
        with open(tmpfile,'w+b') as output:
            dreader = csv.DictReader(input, delimiter=',')
            fieldnames = dreader.fieldnames
            fieldnames.append(Location)
            
            print('I am here 1')   
            csvwriter = csv.DictWriter(output,fieldnames, delimiter = ',')
            
            #csvwriter.writeheader()
            try:
                csvwriter.writeheader()

            except AttributeError:
                    headers = []
                    for h in fieldnames:
                        headers.append((h,h))
                        csvwriter.writerow(dict(headers))
            print('I am here 2')
                
            try:
                for row in dreader:
                    for col in row:
                        if col is None: continue
                    s = row[col]
                    if s is not None:
                        row[col] = s.replace('"', '\'').replace('\\','\\\\').strip()
                        if row[col] == "NULL": 
                            row[col] = ''
                        
                        for key,value in map.iteritems():
                            if len(value) == 1 and key == 'ID':
                               pass
                            else:
                                row[Location] = value[0]
                
       # w.writeheader()
                    csvwriter.writerow(row)
                
        #print('I am here')
            except Exception: 
                    print ('Could not write appropriate value')

Andrej Kesely · Accepted Answer · 2022-09-15 19:23:33Z

1

Try:

import csv

dic = {"511": "Dog", "611": "Cat"}

with open("in.csv", "r") as f_in, open("out.csv", "w") as f_out:
    reader = csv.reader(f_in)
    writer = csv.writer(f_out)
    headers = next(reader)
    writer.writerow(headers + ["Pet"])

    for row in reader:
        writer.writerow(row + [dic.get(row[0], "")])

This creates out.csv with content:

ID,Location,Country,Pet
511,Stack,USA,Dog
611,Overflow,Mexico,Cat
711,Stack,USA,

answered Sep 15, 2022 at 19:23

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

1 Comment

chitete Over a year ago

is this python 2 or 3? I'm using python 2. But when I try to incorporate your logic I get an empty file with header only.

Deepak Tripathi · Accepted Answer · 2022-09-15 19:40:35Z

0

Try this:

import csv
d = {'511': 'Dog', '611': 'Cat'}

with open("temp.csv", "r") as input_file, open("temp1.csv", "w+") as output_file:
    input_csv = csv.DictReader(input_file, delimiter=",")
    output_csv = csv.DictWriter(output_file, fieldnames=input_csv.fieldnames + ["Pet"])
    output_csv.writeheader() and output_csv.writerows({**row, **{"Pet": d.get(row.get("ID"))}} for row in input_csv)

answered Sep 15, 2022 at 19:40

Deepak Tripathi

3,2611 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

Writing dictionary keys to a csv file based on common key and column value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related