Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Manipulating CSV
#1
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?
Reply
#2
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?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Manipulating data from a CSV EvanS1 5 2,675 Jun-12-2020, 05:59 PM
Last Post: perfringo
  manipulating two lists rancans 8 3,112 Apr-16-2020, 06:00 PM
Last Post: deanhystad
  Manipulating index value, what is wrong with this code? Emun 1 1,726 Feb-05-2020, 07:18 AM
Last Post: perfringo
  Manipulating the filename of an output script mckinneycm 4 11,825 Jan-15-2020, 07:29 PM
Last Post: mckinneycm
  Manipulating Excel with Python. Spacely 2 3,586 Jun-25-2019, 01:57 AM
Last Post: Dequanharrison
  Reading and manipulating csv Prince_Bhatia 11 4,967 Mar-14-2019, 11:40 AM
Last Post: Larz60+
  Manipulating an Excel Workbook Stanimal 4 2,949 Jan-18-2019, 11:03 PM
Last Post: Stanimal
  Manipulating Binary Data arsenal88 10 8,494 Apr-25-2017, 02:30 PM
Last Post: snippsat
  Manipulating files Python 2.7 hugobaur 6 8,346 Nov-01-2016, 12:28 PM
Last Post: hugobaur

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020