write values of dictionary in separate csv columns and create headers

Question

I have created a dictionary that uses date as the key and added multiple values to each date. The dictionary is populated by reading an original csv so that I can create totals per date.

My Code:

import csv

##Opens the csv file to be read
tradedata=open("test.csv","r")

##Reads the csv file
tradedatareader = csv.reader(tradedata,delimiter=',',quotechar='"')

##create dictionary
my_dict = {}
for row in tradedatareader:
 Date = row[1][0:8]
 volume = int(row[4])
 price = float(row[5])
 Transtype=row[6]
 ##Find SEC_Fee
 if Transtype !="BUY":
    ttype =1
 else:
    ttype=0
 secfee=(ttype*volume*price*.0000221)

##Finds Notional Value
 notional_val = (volume*price)

##Finds Clearing Fees
 cl_fee = (volume*.0005)

 if cl_fee < .05:
     clearing_fee = 0.05
 else:
     clearing_fee = (volume*.0005)
##Finds Totals per Date
 my_dict[Date] = my_dict.setdefault(Date, [0,0,0,0,0]) 
 my_dict[Date][0] = my_dict[Date][0] + volume
 my_dict[Date][1] = my_dict[Date][1] + notional_val
 my_dict[Date][2] = my_dict[Date][2] + secfee
 my_dict[Date][3] = my_dict[Date][3] + clearing_fee
 my_dict[Date][4] = my_dict[Date][4] + secfee + clearing_fee

## Writes totals to CSV
with open('mycsvfile.csv','w') as f:
    w = csv.writer(f, delimiter = ',')
    w.writerows(my_dict.items())

This currently writes the key in column A and the values in column B and skips a line between each row.

I would like each value to be written in its own column and would like each column to have a header like this:

DATE      Volume    Notional Value       SEC FEES     Clearing Fees      Total Fees   
20140612   2751       157750.56       3.4132565999     1.4500000          4.8632566
20140612   5148       270200.02       5.831338266      2.692499999        8.523838265999998

I'm not sure why your post was down voted, but I will up-vote it because I think it is valuable. — ericmjl
– ericmjl, Commented Oct 30, 2014 at 13:25
On another note, I would suggest naming your variables as lower-cased with underscores between them. Example: Date should become date. That will make your code readable w.r.t. Pythonic conventions. — ericmjl
– ericmjl, Commented Oct 30, 2014 at 13:27
I'd guess the downvote is because there's much more code here than necessary to solve the question asked. Agreed, a useful question though. — Jamie Bull
– Jamie Bull, Commented Oct 30, 2014 at 13:28
I am brand new to Python just started looking at it on Monday for a project I was assigned. I thank you very much for your words of encouragement. — Tuccs
– Tuccs, Commented Oct 30, 2014 at 13:51

ericmjl · Accepted Answer · 2014-10-30 13:24:48Z

3

I would suggest using Pandas.

If you set up your data as a list of dictionaries, where each dictionary represents a row, and the keys of the dictionary are the columns with the values being the row values, then when you do:

import pandas as pd
pd.DataFrame(list_of_dictionaries).to_csv('put_your_filename_here.csv')

you should have the data formatted correctly.

answered Oct 30, 2014 at 13:24

ericmjl

14.9k13 gold badges57 silver badges83 bronze badges

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

2 Comments

Jamie Bull Over a year ago

You can also read in the data using pandas.read_csv('test.csv') which might make the whole process easier and avoid going via dictionaries at all.

ericmjl Over a year ago

Ah yes! Totally did not spot that point in the OP's post - that the data came from a CSV file itself.

Hai Vu · Accepted Answer · 2014-10-30 13:36:45Z

0

The items() returns a list of key, values, which is not what you want to write to the file. This code works:

with open('mycsvfile.csv', 'w') as f:
    w = csv.writer(f)
    w.writerow(['DATE', 'Volume', 'Notional Value', 'SEC FEES', 'Clearing Fees', 'Total Fees'])
    for date in sorted(my_dict):
        w.writerow([date] + my_dict[date])

If you don't want the output sorted, just remove the sorted() function.

answered Oct 30, 2014 at 13:36

Hai Vu

41.4k16 gold badges75 silver badges106 bronze badges

2 Comments

Tuccs Over a year ago

This actually worked perfectly; however I still have empty rows in between each populated row. Is there a way to remove those empty rows?

Hai Vu Over a year ago

Since I don't know what your data looks like, I have no idea how to deal with it. You can help by updating your post with input data.

Collectives™ on Stack Overflow

write values of dictionary in separate csv columns and create headers

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related