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

dting · Accepted Answer · 2015-06-18 02:20:18Z

2

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:])

edited Jun 18, 2015 at 2:20

answered Jun 18, 2015 at 1:36

dting

39.4k10 gold badges98 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Captain4725 Over a year ago

Why do i get this error: Traceback (most recent call last): File "waterfall.py", line 13, in <module> m1 = months[int(row['Date1'].split('/')[0])] KeyError: 'Date1'

FirebladeDan · Accepted Answer · 2015-06-18 01:08:16Z

1

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

answered Jun 18, 2015 at 1:08

FirebladeDan

1,0696 silver badges14 bronze badges

Collectives™ on Stack Overflow

writing csv output python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related