Geek Answers Handbook

Python csv output record

I need to take a csv file that looks like this:

Name,Price1,Price2,Date1,Date2 ABC,1000,7500,5/1,6/1 DEF,3000,500,5/1,7/1 GHI,5000,3500,6/1,8/1

and write it to another csv file so that it looks like this:

 Name,May,June,July,August ABC,7500,1000, , DEF,500, ,3000 GHI, ,3500, ,5000

spaces should be empty because nothing happens and this is the code that I still have

  import csv with open('test-data.csv','rb') as f: csv_file=csv.reader(f) data=[row for row in csv_file] with open('csv-out.csv', 'wb') as out: writer=csv.writer(out) writer.writerow(['Name','May','June','July','August']) for row in data: writer.writerow(row[0])

I am new to python and I'm not sure how to use the cvs module. I was thinking of creating an array and just matching the numbers with the months. Since price1 goes with date2, and price2 goes with date1. Did I just think about that? I searched and possibly used the date and time to associate the month number with the name of the month. Any help or guidance is appreciated! Thanks again!

Edit:

It would be difficult if I wanted to add some numbers from a column, for example

 Name,May,June,July,August ABC,7500,1000, , DEF,500, ,3000 GHI, ,3500, ,5000 Total,8000,4500,3000,5000
+5
source share
2 answers

Something like this should work for your question:

 import csv import calendar from collections import defaultdict months = [calendar.month_name[i] for i in range(0, 13)] totals = defaultdict(int) with open("data.csv", "r") as inf, open("data-out.csv", "w") as ouf: reader = csv.DictReader(inf) writer = csv.DictWriter(ouf, ['Name'] + months[5:9]) writer.writeheader() for row in reader: m1 = months[int(row['Date1'].split('/')[0])] p2 = int(row['Price2']) totals[m1] += p2 m2 = months[int(row['Date2'].split('/')[0])] p1 = int(row['Price1']) totals[m2] += p1 writer.writerow({'Name': row['Name'], m1: p2, m2: p1}) totals['Name'] = 'Total' writer.writerow(totals) 
 with open("data-out.csv", "r") as f: print(f.read()) Name,May,June,July,August ABC,7500,1000,, DEF,500,,3000, GHI,,3500,,5000 Total,8000,4500,3000,5000

If your Date# covers all year, you can change:

 writer = csv.DictWriter(ouf, ['Name'] + months[5:9])

to

 writer = csv.DictWriter(ouf, ['Name'] + months[1:])
+2
source

From how you want to do a column shift. This is quite complicated with some libraries, but one of them should be very useful.

Pandas python

+1
source

More articles:


All Articles