How to define a list in a nested dictionary from a CSV

0
votes

I have a csv that is formated this way, notice there are multiple records at the same time and within that timeframe there are multiple records with the same data4 value:

Time,data1,data2,data3,data4
8/12/2017 8:37:11.719,4435441.97983871,321106.049167927,1260.354,64
8/12/2017 8:37:11.719,4435451.97715054,321346.085476551,1260.354,60
8/12/2017 8:37:11.719,4435461.97446237,321096.047655068,1260.354,64
8/12/2017 8:37:11.719,4435461.97446237,321106.049167927,1260.354,64
8/12/2017 8:37:26.919,4436121.79704301,324496.562027231,1260.354,96
8/12/2017 8:37:26.919,4436121.79704301,324506.563540091,1260.354,96
8/12/2017 8:37:26.919,4436121.79704301,324546.569591528,1260.354,56
8/12/2017 8:37:26.919,4436121.79704301,324646.584720121,1260.354,64

I'm trying to write a function to read this csv into a nested dictionary that uses the Time column and the data4 column to as nested keys. What I have so far is this:

def build_dict(source_file):
    new_dict = defaultdict(dict)

    headers = ['Time','data1','data2','data3','data4']
    with open(source_file, 'rb') as fp:
        reader = csv.DictReader(fp, fieldnames=headers, dialect='excel',
                                skipinitialspace=True)
        for rowdict in reader:
            if None in rowdict:
                del rowdict[None]
            Time = rowdict.pop("Time")
            data4 = int(rowdict.pop("data4"))
            dict[Time][data4] = rowdict
    return dict(new_dict)

Which returns:

new_dict = {
    '8/12/2017 8:37:11.719' : {
        64: {'data3': '1260.354', 'data1': '4435441.97983871', 'data2': '321106.049167927'},
        60: {'data3': '1260.354', 'data1': '4435451.97715054', 'data2': '321346.085476551'}
    }
}

It's almost doing what I need but it overwrites the previous row data with Time and data4 are the same. I'm thinking I need to store data1, data2 and data3 in a list but not sure how to do it.

This is what I would like my dictionary to look like so that per time period I can group data by data4 values:

new_dict = {
    '8/12/2017 8:37:11.719' : {
        60 : [
            {'data1': '4435451.97715054', 'data2': '321346.085476551', 'data3': '1260.354'}
            ],
        64 : [
            {'data1': '4435441.97983871', 'data2': '321106.049167927', 'data3': '1260.354'},
            {'data1': '4435461.97446237', 'data2': '321096.047655068', 'data3': '1260.354'},
            {'data1': '4435461.97446237', 'data2': '321106.049167927', 'data3': '1260.354'}
            ]
        }
    }
2
pythonpython-2.7csvdictionary

2 Answers

0
votes

Well, it is classic use case: grouping

So, the more simple is to use itertools.groupby to group your dict by "Time".

reader = csv.DictReader(fp, dialect='excel', skipinitialspace=True)
headers = next(reader)
new_dict = {}
for group, records in itertools.groupby(reader, key=operator.itemgetter('Time')):
    new_dict[group] = list(records)

You get:

{'8/12/2017 8:37:11.719': [{'Time': '8/12/2017 8:37:11.719',
                            'data1': '4435451.97715054',
                            'data2': '321346.085476551',
                            'data3': '1260.354',
                            'data4': '60'},
                           {'Time': '8/12/2017 8:37:11.719',
                            'data1': '4435461.97446237',
                            'data2': '321096.047655068',
                            'data3': '1260.354',
                            'data4': '64'},
                           {'Time': '8/12/2017 8:37:11.719',
                            'data1': '4435461.97446237',
                            'data2': '321106.049167927',
                            'data3': '1260.354',
                            'data4': '64'}],
 '8/12/2017 8:37:26.919': [{'Time': '8/12/2017 8:37:26.919',
                            'data1': '4436121.79704301',
                            'data2': '324496.562027231',
                            'data3': '1260.354',
                            'data4': '96'},
                           {'Time': '8/12/2017 8:37:26.919',
                            'data1': '4436121.79704301',
                            'data2': '324506.563540091',
                            'data3': '1260.354',
                            'data4': '96'},
                           {'Time': '8/12/2017 8:37:26.919',
                            'data1': '4436121.79704301',
                            'data2': '324546.569591528',
                            'data3': '1260.354',
                            'data4': '56'},
                           {'Time': '8/12/2017 8:37:26.919',
                            'data1': '4436121.79704301',
                            'data2': '324646.584720121',
                            'data3': '1260.354',
                            'data4': '64'}]}

You can also use a comprehension dictionary:

new_dict = {group: list(records)
            for group, records in itertools.groupby(reader, key=operator.itemgetter('Time'))}

If you need to group with "time" and "data4", you need to change the grouping key:

for group, records in itertools.groupby(reader, key=lambda v: (v["Time"], int(v["data4"]))):
    new_dict[group] = list(records)

The result is:

{('8/12/2017 8:37:11.719', 60): [{'Time': '8/12/2017 8:37:11.719',
                                  'data1': '4435451.97715054',
                                  'data2': '321346.085476551',
                                  'data3': '1260.354',
                                  'data4': '60'}],
 ('8/12/2017 8:37:11.719', 64): [{'Time': '8/12/2017 8:37:11.719',
                                  'data1': '4435461.97446237',
                                  'data2': '321096.047655068',
                                  'data3': '1260.354',
                                  'data4': '64'},
                                 {'Time': '8/12/2017 8:37:11.719',
                                  'data1': '4435461.97446237',
                                  'data2': '321106.049167927',
                                  'data3': '1260.354',
                                  'data4': '64'}],
 ('8/12/2017 8:37:26.919', 56): [{'Time': '8/12/2017 8:37:26.919',
                                  'data1': '4436121.79704301',
                                  'data2': '324546.569591528',
                                  'data3': '1260.354',
                                  'data4': '56'}],
 ('8/12/2017 8:37:26.919', 64): [{'Time': '8/12/2017 8:37:26.919',
                                  'data1': '4436121.79704301',
                                  'data2': '324646.584720121',
                                  'data3': '1260.354',
                                  'data4': '64'}],
 ('8/12/2017 8:37:26.919', 96): [{'Time': '8/12/2017 8:37:26.919',
                                  'data1': '4436121.79704301',
                                  'data2': '324496.562027231',
                                  'data3': '1260.354',
                                  'data4': '96'},
                                 {'Time': '8/12/2017 8:37:26.919',
                                  'data1': '4436121.79704301',
                                  'data2': '324506.563540091',
                                  'data3': '1260.354',
                                  'data4': '96'}]}

If you need 2 levels of grouping: first "Time", then "data4", you need 2 loops:

new_dict = {}
for group1, records1 in itertools.groupby(reader, key=operator.itemgetter("Time")):
    new_dict[group1] = {}
    for group2, records2 in itertools.groupby(records1, key=lambda v: int(v["data4"])):
        new_dict[group1][group2] = list(records2)

The result:

{'8/12/2017 8:37:11.719': {60: [{'Time': '8/12/2017 8:37:11.719',
                                 'data1': '4435451.97715054',
                                 'data2': '321346.085476551',
                                 'data3': '1260.354',
                                 'data4': '60'}],
                           64: [{'Time': '8/12/2017 8:37:11.719',
                                 'data1': '4435461.97446237',
                                 'data2': '321096.047655068',
                                 'data3': '1260.354',
                                 'data4': '64'},
                                {'Time': '8/12/2017 8:37:11.719',
                                 'data1': '4435461.97446237',
                                 'data2': '321106.049167927',
                                 'data3': '1260.354',
                                 'data4': '64'}]},
 '8/12/2017 8:37:26.919': {56: [{'Time': '8/12/2017 8:37:26.919',
                                 'data1': '4436121.79704301',
                                 'data2': '324546.569591528',
                                 'data3': '1260.354',
                                 'data4': '56'}],
                           64: [{'Time': '8/12/2017 8:37:26.919',
                                 'data1': '4436121.79704301',
                                 'data2': '324646.584720121',
                                 'data3': '1260.354',
                                 'data4': '64'}],
                           96: [{'Time': '8/12/2017 8:37:26.919',
                                 'data1': '4436121.79704301',
                                 'data2': '324496.562027231',
                                 'data3': '1260.354',
                                 'data4': '96'},
                                {'Time': '8/12/2017 8:37:26.919',
                                 'data1': '4436121.79704301',
                                 'data2': '324506.563540091',
                                 'data3': '1260.354',
                                 'data4': '96'}]}}
0
votes

I suggest using the Pandas library since it provides nice ways to read and group CSV files via the Pandas Dataframe.

import pandas as pd

# read the CSV file
df = pd.read_csv("test.csv")

# group by the desired columns
gb = df.groupby(['Time', 'data4'])

This returns a GroupBy object whereas the key is a tuple of the timestamp and date4 and the value for each group is a new Dataframe containing the matches/values. Now you got three options:

# option 1
list(gb)

Which gives you:

[(('8/12/2017 8:37:11.719', 60),
                      Time         data1          data2     data3  data4
  1  8/12/2017 8:37:11.719  4.435452e+06  321346.085477  1260.354     60),
 (('8/12/2017 8:37:11.719', 64),
                      Time         data1          data2     data3  data4
  0  8/12/2017 8:37:11.719  4.435442e+06  321106.049168  1260.354     64
  2  8/12/2017 8:37:11.719  4.435462e+06  321096.047655  1260.354     64
  3  8/12/2017 8:37:11.719  4.435462e+06  321106.049168  1260.354     64),
 (('8/12/2017 8:37:26.919', 56),
                      Time         data1          data2     data3  data4
  6  8/12/2017 8:37:26.919  4.436122e+06  324546.569592  1260.354     56),
 (('8/12/2017 8:37:26.919', 64),
                      Time         data1         data2     data3  data4
  7  8/12/2017 8:37:26.919  4.436122e+06  324646.58472  1260.354     64),
 (('8/12/2017 8:37:26.919', 96),
                      Time         data1          data2     data3  data4
  4  8/12/2017 8:37:26.919  4.436122e+06  324496.562027  1260.354     96
  5  8/12/2017 8:37:26.919  4.436122e+06  324506.563540  1260.354     96)]

You can also use a dictionary that produces a comparable result:

# option 2
dict(list(gb))

Or you iterate over the groups and do whatever you want to do with the rows of each group

# option 3
result = {}
for name, df_group in gb:
    timestamp, date4 = name
    outer_dict = result.get(timestamp, {})
    inner_dict = df_group.T.to_dict()
    #inner_dict = df_group.to_dict(orient="index")
    #inner_dict = df_group.values.tolist()

    outer_dict[date4] = inner_dict
    result[timestamp] = outer_dict

print(result)

Which gives you the following. You can play around with dropping some of the columns like the index, the timestamp and date4. 

{'8/12/2017 8:37:11.719': {60: {1: {'Time': '8/12/2017 8:37:11.719',
    'data1': 4435451.97715054,
    'data2': 321346.08547655103,
    'data3': 1260.354,
    'data4': 60}},
  64: {0: {'Time': '8/12/2017 8:37:11.719',
    'data1': 4435441.97983871,
    'data2': 321106.049167927,
    'data3': 1260.354,
    'data4': 64},
   2: {'Time': '8/12/2017 8:37:11.719',
    'data1': 4435461.97446237,
    'data2': 321096.047655068,
    'data3': 1260.354,
    'data4': 64},
   3: {'Time': '8/12/2017 8:37:11.719',
    'data1': 4435461.97446237,
    'data2': 321106.049167927,
    'data3': 1260.354,
    'data4': 64}}},
 '8/12/2017 8:37:26.919': {56: {6: {'Time': '8/12/2017 8:37:26.919',
    'data1': 4436121.79704301,
    'data2': 324546.569591528,
    'data3': 1260.354,
    'data4': 56}},
  64: {7: {'Time': '8/12/2017 8:37:26.919',
    'data1': 4436121.79704301,
    'data2': 324646.584720121,
    'data3': 1260.354,
    'data4': 64}},
  96: {4: {'Time': '8/12/2017 8:37:26.919',
    'data1': 4436121.79704301,
    'data2': 324496.56202723103,
    'data3': 1260.354,
    'data4': 96},
   5: {'Time': '8/12/2017 8:37:26.919',
    'data1': 4436121.79704301,
    'data2': 324506.56354009104,
    'data3': 1260.354,
    'data4': 96}}}}

Hope you got the idea.