writing "dictionary of dictionaries" to .csv file in a particular format

Question

I am generating a dictionary out of multiple .csv files and it looks like this (example):

dtDict = {'AV-IM-1-13991730': {'6/1/2014 0:10': '0.96',
                      '6/1/2014 0:15': '0.92',
                      '6/1/2014 0:20': '0.97'},
 'AV-IM-1-13991731': {'6/1/2014 0:10': '1.96',
                      '6/1/2014 0:15': '1.92',
                      '6/1/2014 0:20': '1.97'},
 'AV-IM-1-13991732': {'6/1/2014 0:10': '2.96',
                      '6/1/2014 0:15': '2.92',
                      '6/1/2014 0:20': '2.97'},
 'AV-IM-1-13991733': {'6/1/2014 0:10': '3.96',
                      '6/1/2014 0:15': '3.96',
                      '6/1/2014 0:20': '3.97'}}

I want to save it to a .csv file in the following format:

timestamp,AV-IM-1-13991730,AV-IM-1-13991731,AV-IM-1-13991732,AV-IM-1-13991733
6/1/2014 0:10,0.96,1.96,2.96,3.96
6/1/2014 0:15,0.92,1.92,2.92,3.96
6/1/2014 0:20,0.97,1.97,2.97,3.97

The piece of code I have as of now (related to this objective):

header = '''# file...... Recorder file
# date...... Thu Mar 12 14:35:32 2015
# user...... Sri
# host...... (null)
# group..... None
# property.. AVA Measurements
# limit..... 
# interval..''' 

testpower        = open("custpower.csv",'w')
testpower.writelines([header,'\n','# timestamp\n'])
...
for key, value in dtDict.iteritems():
    #Still trying to figure out how to write to custpower.csv

I tried doing something similar to this:

for key, value in dtDict.iteritems():
    testpower.writelines([key,',',','.join(value),'\n'])

but it didnot quite do what I was trying to do.

Answer 1

This is beyond simple if you can use pandas.

import pandas as pd

data = {'AV-IM-1-13991730': {'6/1/2014 0:10': '0.96',
                             '6/1/2014 0:15': '0.92',
                             '6/1/2014 0:20': '0.97'},
        'AV-IM-1-13991731': {'6/1/2014 0:10': '1.96',
                             '6/1/2014 0:15': '1.92',
                             '6/1/2014 0:20': '1.97'},
        'AV-IM-1-13991732': {'6/1/2014 0:10': '2.96',
                             '6/1/2014 0:15': '2.92',
                             '6/1/2014 0:20': '2.97'},
        'AV-IM-1-13991733': {'6/1/2014 0:10': '3.96',
                             '6/1/2014 0:15': '3.96',
                             '6/1/2014 0:20': '3.97'}}

df = pd.DataFrame(data)
df.to_csv(PATH_TO_OUTPUT_FILE)

df becomes a DataFrame that looks like

              AV-IM-1-13991730 AV-IM-1-13991731 AV-IM-1-13991732 AV-IM-1-13991733
6/1/2014 0:10             0.96             1.96             2.96             3.96
6/1/2014 0:15             0.92             1.92             2.92             3.96
6/1/2014 0:20             0.97             1.97             2.97             3.97

And your resulting csv looks like

,AV-IM-1-13991730,AV-IM-1-13991731,AV-IM-1-13991732,AV-IM-1-13991733
6/1/2014 0:10,0.96,1.96,2.96,3.96
6/1/2014 0:15,0.92,1.92,2.92,3.96
6/1/2014 0:20,0.97,1.97,2.97,3.97

Pandas is also nice because you can then do:

df.convert_objects(convert_numeric=True).plot()
# the converts change "0.97" -> 0.97 so it's plottable

To get:

Answer 2

You can re-organize your data into a new dictionary of lists structure like this. Keep in mind, you this will read in the entire file before printing (it has to get the last value for the first timestamp). So it may be slow if your input is huge. Also, dictionaries don't keep their keys in any particular order, so if order matters you may want to save the keys in a separate list.

ts = dtDict.keys()

print "timestamp," + ",".join(ts)   
reformatted = {}

for k in ts:
    sub_dict = dtDict[k]
    for timestamp in sub_dict.keys():
        value = sub_dict[timestamp]
        if not reformatted.has_key(timestamp):
            reformatted[timestamp] = []
        reformatted[timestamp].append(value)

for rec in reformatted.keys():
    print rec + " " + ",".join(reformatted[rec])

Of course, if the set of timestamps is always consistent, you can do something even simpler:

datasets = dtDict.keys()
timestamps = dtDict[datasets[0]].keys()


for ts in timestamps:
    values = []
    for ds in datasets:
        values.append(dtDict[ds][ts])
    print ts + " " + "".join(values)

Again, it will show up in some arbitrary order unless you set the order ahead of time. So instead of setting the timestamps from the dictionary itself you would just read them in ahead of time.