Manipulating CSV - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Manipulating CSV (/thread-17824.html) |
Manipulating CSV - Prince_Bhatia - Apr-25-2019 Hi, i have a csv file whose data structure is like this City Preference Type Value ['Adilabad'] Builder sale 0 ['Adilabad'] Owner sale 5 ['Adilabad'] Broker sale 0 ['Adilabad'] Builder rent 0 ['Adilabad'] Owner rent 2 ['Adilabad'] Broker rent 0 ['Agartala'] Builder sale 0 ['Agartala'] Owner sale 10 ['Agartala'] Broker sale 0 ['Agartala'] Builder rent 0 ['Agartala'] Owner rent 5 ['Agartala'] Broker rent 0 ['Agra'] Builder sale 0 ['Agra'] Owner sale 122 ['Agra'] Broker sale 1 ['Agra'] Builder rent 0but i want it like this Owner Broker Builder Sale Rent Sale Rent Sale Rent sum(of sale) sum(of rent) it is like sum of all sale for owner , sum of all rent for owner and like wise for builder and for broke for each city which is the first column of csv This is code below: import csv INPUT_FILE = 'data.csv' OUTPUT_FILE = 'Desired_format.csv' with open(INPUT_FILE, 'r') as f_in, open(OUTPUT_FILE, 'w', newline="") as f_out: rdr = csv.DictReader(f_in) header = ["Builder","Owner","Broker"] wrtr = csv.DictWriter(f_out, fieldnames = header, extrasaction='ignore') wrtr.writeheader() for line in rdr: line[line['Preference']] = [line['Type'],line["Value"]] wrtr.writerow(line)But it is not giving the desired result. This is the output i am getting Builder Owner Broker ['sale', '0'] ['sale', '5'] ['sale', '0'] ['rent', '0'] ['rent', '2'] ['rent', '0'] ['sale', '0'] ['sale', '10'] ['sale', '0'] ['rent', '0'] ['rent', '5']any help? RE: Manipulating CSV - Gribouillis - Apr-25-2019 I have better results by passing delemiter=' ', skipinitialspace=True to the DictReader constructorimport csv import io f_in = io.StringIO("""\ City Preference Type Value ['Adilabad'] Builder sale 0 ['Adilabad'] Owner sale 5 ['Adilabad'] Broker sale 0 ['Adilabad'] Builder rent 0 ['Adilabad'] Owner rent 2 ['Adilabad'] Broker rent 0 ['Agartala'] Builder sale 0 ['Agartala'] Owner sale 10 ['Agartala'] Broker sale 0 ['Agartala'] Builder rent 0 ['Agartala'] Owner rent 5 ['Agartala'] Broker rent 0 ['Agra'] Builder sale 0 ['Agra'] Owner sale 122 ['Agra'] Broker sale 1 ['Agra'] Builder rent 0 """) f_out = io.StringIO() with f_in, f_out: rdr = csv.DictReader(f_in, delimiter=' ', skipinitialspace=True) header = ["Builder","Owner","Broker"] wrtr = csv.DictWriter(f_out, fieldnames = header, extrasaction='ignore') wrtr.writeheader() for line in rdr: print(line) line[line['Preference']] = [line['Type'],line["Value"]] wrtr.writerow(line) print(f_out.getvalue()) If this is not the desired output, can you be more specific on the expected output?
|