Python Forum

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	0

but 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?

I have better results by passing delemiter=' ', skipinitialspace=True to the DictReader constructor

import 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())

Output:
{'Type': 'sale', 'Preference': 'Builder', 'City': "['Adilabad']", 'Value': '0'}
{'Type': 'sale', 'Preference': 'Owner', 'City': "['Adilabad']", 'Value': '5'}
{'Type': 'sale', 'Preference': 'Broker', 'City': "['Adilabad']", 'Value': '0'}
{'Type': 'rent', 'Preference': 'Builder', 'City': "['Adilabad']", 'Value': '0'}
{'Type': 'rent', 'Preference': 'Owner', 'City': "['Adilabad']", 'Value': '2'}
{'Type': 'rent', 'Preference': 'Broker', 'City': "['Adilabad']", 'Value': '0'}
{'Type': 'sale', 'Preference': 'Builder', 'City': "['Agartala']", 'Value': '0'}
{'Type': 'sale', 'Preference': 'Owner', 'City': "['Agartala']", 'Value': '10'}
{'Type': 'sale', 'Preference': 'Broker', 'City': "['Agartala']", 'Value': '0'}
{'Type': 'rent', 'Preference': 'Builder', 'City': "['Agartala']", 'Value': '0'}
{'Type': 'rent', 'Preference': 'Owner', 'City': "['Agartala']", 'Value': '5'}
{'Type': 'rent', 'Preference': 'Broker', 'City': "['Agartala']", 'Value': '0'}
{'Type': 'sale', 'Preference': 'Builder', 'City': "['Agra']", 'Value': '0'}
{'Type': 'sale', 'Preference': 'Owner', 'City': "['Agra']", 'Value': '122'}
{'Type': 'sale', 'Preference': 'Broker', 'City': "['Agra']", 'Value': '1'}
{'Type': 'rent', 'Preference': 'Builder', 'City': "['Agra']", 'Value': '0'}
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']",
,,"['rent', '0']"
"['sale', '0']",,
,"['sale', '122']",
,,"['sale', '1']"
"['rent', '0']",,

If this is not the desired output, can you be more specific on the expected output?