writing csv output python

Question

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 out to another csv file to make it look like this...

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

the spaces are suppose to be blank because nothing goes there and this is the code that I have so far

 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 am not really sure how to use the cvs module. I was thinking of making an array and just match the numbers to the months. Since price1 goes with date2 and price2 goes with date1. Am I just over thinking this? I was searching around and maybe use datetime also to connect the month number to the month name. Any help or guidance is appreciated!Thanks again!

Edit:

Would it be hard if I want to also add some numbers from a column like

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

Answer 1

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#'s span the entire year you can change:

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

to

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

Answer 2

From what it looks like you want to do a column shift. That is actually quite difficult with some libraries but the one below should be quite helpful.

Pandas Python