How to manipulate csv

[Prince_Bhatia]

hi,

i have a csv file (attached the file with the name python_dictionary.csv)
in the attached format has three columns Key, value1 and value2.. how can i manipulate csv using python so that i can make value1 as there headers.

this is desired format(for more details please refer desiredformat.csv)

key would be different in each case.

i am not able to go beyond because i dont know how to make permanent headers

import csv

filename = "python_dictionary.csv"
prj = {}
#fdata = dict()
#f = open(filename, "r")
data = f.read()
lst = data.split("\n")
for i in range(1, len(lst)):#because 0 is the header
    val = lst[i].split(",")

can anyone please guide?

[buran]

Are you sure you want multiple rows for a given key in the output file?

[Prince_Bhatia]

Yes sir, since this is how i can increment data into the backend. Every key is an ID and via that i can add data into backend. There could be Two architect to single ID or no Architect. If there are multiple rows system can identify where to enter the architect/contractor/structural engineer for a given key.

[buran]

Actually, the input format is more suitable as it reassembles a table/view in a relational database - i.e. if you have projects, identified by their ID (Key) and 'Specialist', the input table is cross of projects and specialists with their role in that project. All that said, the below code will produce your desired result
 

import csv
INPUT_FILE = 'python_dictionary.csv'
OUTPUT_FILE = 'Desired_format.csv'

with open(INPUT_FILE, 'r') as f_in, open(OUTPUT_FILE, 'w') as f_out:
    rdr = csv.DictReader(f_in)
    header = ['Key', ' Contractor', ' Architect', ' Structural Engineer']
    wrtr = csv.DictWriter(f_out, fieldnames = header, extrasaction='ignore')
    wrtr.writeheader()
    for line in rdr:
        line[line['Value1']] = line['Value2']
        wrtr.writerow(line)

I would remove extra space after commas in your input file and fix line#7 in the above code

[Prince_Bhatia]

Hi, you are amazing, it's working as desired. Thank you so much sir.