Add a missing key in a nested dictionary?

Question

I have a dictionary used to build a chart from data coming from the database. I have the following working code:

datasets = []

for row in data:
    # add serie if not exists already
    if not any(d['label'] == row['sql_id'] for d in datasets):
        serie = {'label':row['sql_id'],'backgroundColor':GetRandomHexColor(),'data':[]}
        datasets.append(serie)

    serie = next(item for item in datasets if item['label'] == row['sql_id'])
    serie['data'].append({'x': row['sample_time'],'y':row['resources_consumed']})

This build a dictionnary of this form:

{
"datasets": [{
    "data": [{
        "y": 3,
        "x": "2017-12-22 16:01"
    }, {
        "y": 23,
        "x": "2017-12-22 16:02"
    }, {
        "y": 33,
        "x": "2017-12-22 16:03"
    }, {
        "y": 12,
        "x": "2017-12-22 16:04"
    }, {
        "y": 5,
        "x": "2017-12-22 16:05"
    }, {
        "y": 13,
        "x": "2017-12-22 16:06"
    }, {
        "y": 17,
        "x": "2017-12-22 16:11"
    }, {
        "y": 24,
        "x": "2017-12-22 16:12"
    }, {
        "y": 12,
        "x": "2017-12-22 16:13"
    }, {
        "y": 9,
        "x": "2017-12-22 16:14"
    }, {
        "y": 10,
        "x": "2017-12-22 16:15"
    }, {
        "y": 24,
        "x": "2017-12-22 16:16"
    }, {
        "y": 28,
        "x": "2017-12-22 16:17"
    }, {
        "y": 4,
        "x": "2017-12-22 16:18"
    }, {
        "y": 18,
        "x": "2017-12-22 16:19"
    }, {
        "y": 25,
        "x": "2017-12-22 16:20"
    }, {
        "y": 25,
        "x": "2017-12-22 16:21"
    }, {
        "y": 14,
        "x": "2017-12-22 16:22"
    }, {
        "y": 10,
        "x": "2017-12-22 16:23"
    }, {
        "y": 9,
        "x": "2017-12-22 16:24"
    }],
    "backgroundColor": "#01F79A",
    "label": "3qkhfbf2kyvhk"
}, {
    "data": [{
        "y": 3,
        "x": "2017-12-22 16:01"
    }, {
        "y": 14,
        "x": "2017-12-22 16:02"
    }, {
        "y": 12,
        "x": "2017-12-22 16:03"
    }, {
        "y": 9,
        "x": "2017-12-22 16:04"
    }, {
        "y": 7,
        "x": "2017-12-22 16:05"
    }, {
        "y": 20,
        "x": "2017-12-22 16:06"
    }, {
        "y": 2,
        "x": "2017-12-22 16:10"
    }, {
        "y": 16,
        "x": "2017-12-22 16:11"
    }, {
        "y": 10,
        "x": "2017-12-22 16:12"
    }, {
        "y": 11,
        "x": "2017-12-22 16:13"
    }, {
        "y": 9,
        "x": "2017-12-22 16:14"
    }, {
        "y": 15,
        "x": "2017-12-22 16:15"
    }, {
        "y": 13,
        "x": "2017-12-22 16:16"
    }, {
        "y": 8,
        "x": "2017-12-22 16:17"
    }, {
        "y": 8,
        "x": "2017-12-22 16:18"
    }, {
        "y": 12,
        "x": "2017-12-22 16:19"
    }, {
        "y": 14,
        "x": "2017-12-22 16:20"
    }, {
        "y": 13,
        "x": "2017-12-22 16:21"
    }, {
        "y": 12,
        "x": "2017-12-22 16:22"
    }, {
        "y": 9,
        "x": "2017-12-22 16:23"
    }, {
        "y": 8,
        "x": "2017-12-22 16:24"
    }],
    "backgroundColor": "#743967",
    "label": "8u125dk9nfc0q"
}, {
    "data": [{
        "y": 1,
        "x": "2017-12-22 16:02"
    }, {
        "y": 1,
        "x": "2017-12-22 16:03"
    }, {
        "y": 1,
        "x": "2017-12-22 16:04"
    }, {
        "y": 2,
        "x": "2017-12-22 16:11"
    }, {
        "y": 1,
        "x": "2017-12-22 16:12"
    }, {
        "y": 2,
        "x": "2017-12-22 16:15"
    }, {
        "y": 2,
        "x": "2017-12-22 16:16"
    }, {
        "y": 1,
        "x": "2017-12-22 16:17"
    }, {
        "y": 2,
        "x": "2017-12-22 16:19"
    }, {
        "y": 1,
        "x": "2017-12-22 16:20"
    }, {
        "y": 1,
        "x": "2017-12-22 16:22"
    }, {
        "y": 1,
        "x": "2017-12-22 16:24"
    }],
    "backgroundColor": "#CA3582",
    "label": "b9nbhsbx8tqz5"
}, {
    "data": [{
        "y": 1,
        "x": "2017-12-22 16:02"
    }, {
        "y": 1,
        "x": "2017-12-22 16:04"
    }, {
        "y": 2,
        "x": "2017-12-22 16:11"
    }, {
        "y": 1,
        "x": "2017-12-22 16:12"
    }, {
        "y": 1,
        "x": "2017-12-22 16:14"
    }, {
        "y": 2,
        "x": "2017-12-22 16:15"
    }, {
        "y": 1,
        "x": "2017-12-22 16:19"
    }, {
        "y": 2,
        "x": "2017-12-22 16:20"
    }, {
        "y": 1,
        "x": "2017-12-22 16:22"
    }, {
        "y": 1,
        "x": "2017-12-22 16:24"
    }],
    "backgroundColor": "#8697A2",
    "label": "dp0vgyb1hsfjb"
}, {
    "data": [{
        "y": 5,
        "x": "2017-12-22 16:04"
    }, {
        "y": 4,
        "x": "2017-12-22 16:05"
    }, {
        "y": 8,
        "x": "2017-12-22 16:13"
    }, {
        "y": 1,
        "x": "2017-12-22 16:14"
    }, {
        "y": 9,
        "x": "2017-12-22 16:18"
    }, {
        "y": 8,
        "x": "2017-12-22 16:22"
    }, {
        "y": 1,
        "x": "2017-12-22 16:23"
    }],
    "backgroundColor": "#034D27",
    "label": "7726bj0dhtnmt"
}, {
    "data": [{
        "y": 12,
        "x": "2017-12-22 16:04"
    }, {
        "y": 12,
        "x": "2017-12-22 16:13"
    }, {
        "y": 12,
        "x": "2017-12-22 16:18"
    }, {
        "y": 10,
        "x": "2017-12-22 16:22"
    }],
    "backgroundColor": "#B3FDF5",
    "label": "cmx7t67z8wa74"
}, {
    "data": [{
        "y": 2,
        "x": "2017-12-22 16:04"
    }, {
        "y": 1,
        "x": "2017-12-22 16:05"
    }, {
        "y": 4,
        "x": "2017-12-22 16:12"
    }, {
        "y": 1,
        "x": "2017-12-22 16:15"
    }, {
        "y": 1,
        "x": "2017-12-22 16:17"
    }, {
        "y": 1,
        "x": "2017-12-22 16:22"
    }, {
        "y": 2,
        "x": "2017-12-22 16:24"
    }],
    "backgroundColor": "#3A74FB",
    "label": "ft7wcqu3hzvca"
}, {
    "data": [{
        "y": 7,
        "x": "2017-12-22 16:05"
    }, {
        "y": 6,
        "x": "2017-12-22 16:14"
    }, {
        "y": 6,
        "x": "2017-12-22 16:18"
    }, {
        "y": 6,
        "x": "2017-12-22 16:23"
    }],
    "backgroundColor": "#9733FC",
    "label": "7mwz4m103nn1k"
}, {
    "data": [{
        "y": 8,
        "x": "2017-12-22 16:05"
    }, {
        "y": 9,
        "x": "2017-12-22 16:14"
    }, {
        "y": 8,
        "x": "2017-12-22 16:18"
    }, {
        "y": 12,
        "x": "2017-12-22 16:23"
    }],
    "backgroundColor": "#383B19",
    "label": "9nrjf616y6g22"
}]
}

The problem is I need to add missing timeseries in case they don't exists in each data list. If serie 1 is a date "2017-12-22 16:23", each other series need the date with or without a value. If the value doesn't exists in an array then I need to add it with a 0 values for y.

I don't know how to efficiently do that without doing a lot of nested for loops.

EDIT:

Currently I have something like that:

{
"datasets": [{
    "data": [{
        "y": 3,
        "x": "2017-12-22 16:01"
    }, {
        "y": 23,
        "x": "2017-12-22 16:02"
    }, {
        "y": 33,
        "x": "2017-12-22 16:03"
    }, {
        "y": 12,
        "x": "2017-12-22 16:04"
    }, {
        "y": 5,
        "x": "2017-12-22 16:05"
    }],
    "backgroundColor": "#01F79A",
    "label": "3qkhfbf2kyvhk"
}, {
    "data": [{
        "y": 9,
        "x": "2017-12-22 16:04"
    }, {
        "y": 7,
        "x": "2017-12-22 16:05"
    }, {
        "y": 20,
        "x": "2017-12-22 16:06"
    }, {
        "y": 2,
        "x": "2017-12-22 16:10"
    }, {
        "y": 16,
        "x": "2017-12-22 16:11"
    }],
    "backgroundColor": "#743967",
    "label": "8u125dk9nfc0q"
}]
}

What I want is that:

{
"datasets": [{
    "data": [{
        "y": 3,
        "x": "2017-12-22 16:01"
    }, {
        "y": 23,
        "x": "2017-12-22 16:02"
    }, {
        "y": 33,
        "x": "2017-12-22 16:03"
    }, {
        "y": 12,
        "x": "2017-12-22 16:04"
    }, {
        "y": 5,
        "x": "2017-12-22 16:05"
    },{
        "y": 0,
        "x": "2017-12-22 16:06"
    }, {
        "y": 0,
        "x": "2017-12-22 16:10"
    }, {
        "y": 0,
        "x": "2017-12-22 16:11"
    }],
    "backgroundColor": "#01F79A",
    "label": "3qkhfbf2kyvhk"
}, {
    "data": [{
        "y": 0,
        "x": "2017-12-22 16:01"
    }, {
        "y": 0,
        "x": "2017-12-22 16:02"
    }, {
        "y": 0,
        "x": "2017-12-22 16:03"
    },{
        "y": 9,
        "x": "2017-12-22 16:04"
    }, {
        "y": 7,
        "x": "2017-12-22 16:05"
    }, {
        "y": 20,
        "x": "2017-12-22 16:06"
    }, {
        "y": 2,
        "x": "2017-12-22 16:10"
    }, {
        "y": 16,
        "x": "2017-12-22 16:11"
    }],
    "backgroundColor": "#743967",
    "label": "8u125dk9nfc0q"
}]
}

Each different timestamp must exists for each serie. If a timestamp does not exists in a serie I must add it with a 0 y value.

Here is the initial data I get from the database:

Query:

cursor.execute('SELECT strftime(\'%%Y-%%m-%%d %%H:%%M\',s.sample_time) as sample_time,\n'
                       '    id,\n'
                       '    dbid,\n'
                       '    sql_id,\n'
                       '    sql_plan_hash_value,\n'
                       '    sid,\n'
                       '    serial,\n'
                       '    count(sql_id) as resources_consumed\n'
                       'FROM sash s\n'
                       'where sql_id in (select sql_id from (\n'
                       '                                  select\n'
                       '                                      id,\n'
                       '                                      dbid,\n'
                       '                                      SQL_ID ,\n'
                       '                                      sql_plan_hash_value,\n'
                       '                                      sid,\n'
                       '                                      serial,\n'
                       '                                      count(*) as resources_consumed\n'
                       '                                 from sash\n'
                       '                                 where sid = %s \n'
                       '                                 and   serial = %s \n'
                       '                                 and   dbid = %s \n'
                       '                                 group by sql_id,sql_plan_hash_value,sid,serial,dbid\n'
                       '                         order by resources_consumed desc LIMIT 10)\n'
                       '             )\n'
                       'and sample_time between datetime(\'now\',\'localtime\',\'-60 minutes\') and datetime(\'now\',\'localtime\')        \n'
                       'and sid= %s \n'
                       'and serial= %s \n'
                       'group by strftime(\'%%Y-%%m-%%d %%H:%%M\',s.sample_time),sql_id,sql_plan_hash_value,sid,serial,dbid\n'
                       'order by strftime(\'%%Y-%%m-%%d %%H:%%M\',s.sample_time)', [sid, serial, dbid, sid, serial])

Output:

    [{
    'sql_id': u'3qkhfbf2kyvhk',
    'sql_plan_hash_value': 2234478098L,
    'sample_time': u'2017-12-23 10:41',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59389,
    'resources_consumed': 1
}, {
    'sql_id': u'ft7wcqu3hzvca',
    'sql_plan_hash_value': 2265968010L,
    'sample_time': u'2017-12-23 10:41',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59395,
    'resources_consumed': 2
}, {
    'sql_id': u'0m9b1dywgrdqj',
    'sql_plan_hash_value': 3103504081L,
    'sample_time': u'2017-12-23 10:42',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59469,
    'resources_consumed': 1
}, {
    'sql_id': u'3qkhfbf2kyvhk',
    'sql_plan_hash_value': 2234478098L,
    'sample_time': u'2017-12-23 10:42',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59511,
    'resources_consumed': 17
}, {
    'sql_id': u'50kcsz2gh1w84',
    'sql_plan_hash_value': 2667639044L,
    'sample_time': u'2017-12-23 10:42',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59421,
    'resources_consumed': 1
}, {
    'sql_id': u'8u125dk9nfc0q',
    'sql_plan_hash_value': 2470916118L,
    'sample_time': u'2017-12-23 10:42',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59503,
    'resources_consumed': 8
}, {
    'sql_id': u'dp0vgyb1hsfjb',
    'sql_plan_hash_value': 3272358443L,
    'sample_time': u'2017-12-23 10:42',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59454,
    'resources_consumed': 1
}, {
    'sql_id': u'ft7wcqu3hzvca',
    'sql_plan_hash_value': 2265968010L,
    'sample_time': u'2017-12-23 10:42',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59510,
    'resources_consumed': 8
}, {
    'sql_id': u'1xc91cuvu7j11',
    'sql_plan_hash_value': 3080963105L,
    'sample_time': u'2017-12-23 10:43',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59555,
    'resources_consumed': 3
}, {
    'sql_id': u'3qkhfbf2kyvhk',
    'sql_plan_hash_value': 2234478098L,
    'sample_time': u'2017-12-23 10:43',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59577,
    'resources_consumed': 26
}, {
    'sql_id': u'50kcsz2gh1w84',
    'sql_plan_hash_value': 2667639044L,
    'sample_time': u'2017-12-23 10:43',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59532,
    'resources_consumed': 1
}, {
    'sql_id': u'8u125dk9nfc0q',
    'sql_plan_hash_value': 2470916118L,
    'sample_time': u'2017-12-23 10:43',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59578,
    'resources_consumed': 8
}, {
    'sql_id': u'ft7wcqu3hzvca',
    'sql_plan_hash_value': 2265968010L,
    'sample_time': u'2017-12-23 10:43',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59565,
    'resources_consumed': 3
}, {
    'sql_id': u'3qkhfbf2kyvhk',
    'sql_plan_hash_value': 2234478098L,
    'sample_time': u'2017-12-23 10:44',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59644,
    'resources_consumed': 17
}, {
    'sql_id': u'50kcsz2gh1w84',
    'sql_plan_hash_value': 2667639044L,
    'sample_time': u'2017-12-23 10:44',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59623,
    'resources_consumed': 1
}, {
    'sql_id': u'8u125dk9nfc0q',
    'sql_plan_hash_value': 2470916118L,
    'sample_time': u'2017-12-23 10:44',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59642,
    'resources_consumed': 11
}, {
    'sql_id': u'dp0vgyb1hsfjb',
    'sql_plan_hash_value': 3272358443L,
    'sample_time': u'2017-12-23 10:44',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59614,
    'resources_consumed': 2
}, {
    'sql_id': u'ft7wcqu3hzvca',
    'sql_plan_hash_value': 2265968010L,
    'sample_time': u'2017-12-23 10:44',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59648,
    'resources_consumed': 3
}, {
    'sql_id': u'3qkhfbf2kyvhk',
    'sql_plan_hash_value': 2234478098L,
    'sample_time': u'2017-12-23 10:45',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59665,
    'resources_consumed': 3
}, {
    'sql_id': u'50kcsz2gh1w84',
    'sql_plan_hash_value': 2667639044L,
    'sample_time': u'2017-12-23 10:45',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59649,
    'resources_consumed': 1
}, {
    'sql_id': u'7726bj0dhtnmt',
    'sql_plan_hash_value': 453825145,
    'sample_time': u'2017-12-23 10:45',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59718,
    'resources_consumed': 11
}, {
    'sql_id': u'8u125dk9nfc0q',
    'sql_plan_hash_value': 2470916118L,
    'sample_time': u'2017-12-23 10:45',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59707,
    'resources_consumed': 13
}, {
    'sql_id': u'cmx7t67z8wa74',
    'sql_plan_hash_value': 4270729444L,
    'sample_time': u'2017-12-23 10:45',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59705,
    'resources_consumed': 19
}, {
    'sql_id': u'0m9b1dywgrdqj',
    'sql_plan_hash_value': 3103504081L,
    'sample_time': u'2017-12-23 10:47',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59765,
    'resources_consumed': 2
}, {
    'sql_id': u'3qkhfbf2kyvhk',
    'sql_plan_hash_value': 2234478098L,
    'sample_time': u'2017-12-23 10:47',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59768,
    'resources_consumed': 10
}, {
    'sql_id': u'50kcsz2gh1w84',
    'sql_plan_hash_value': 2667639044L,
    'sample_time': u'2017-12-23 10:47',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59770,
    'resources_consumed': 1
}, {
    'sql_id': u'8u125dk9nfc0q',
    'sql_plan_hash_value': 2470916118L,
    'sample_time': u'2017-12-23 10:47',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59764,
    'resources_consumed': 11
}, {
    'sql_id': u'dp0vgyb1hsfjb',
    'sql_plan_hash_value': 3272358443L,
    'sample_time': u'2017-12-23 10:47',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59754,
    'resources_consumed': 1
}, {
    'sql_id': u'3qkhfbf2kyvhk',
    'sql_plan_hash_value': 2234478098L,
    'sample_time': u'2017-12-23 10:48',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59779,
    'resources_consumed': 4
}, {
    'sql_id': u'8u125dk9nfc0q',
    'sql_plan_hash_value': 2470916118L,
    'sample_time': u'2017-12-23 10:48',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59788,
    'resources_consumed': 5
}, {
    'sql_id': u'ft7wcqu3hzvca',
    'sql_plan_hash_value': 2265968010L,
    'sample_time': u'2017-12-23 10:48',
    'dbid': 312109145,
    'sid': 64,
    'serial': 16655,
    'id': 59789,
    'resources_consumed': 1
}]

I think you can google "pandas" on your own just fine. Pandas is a very popular library, but I don't know the link off the top of my head. — Mad Physicist, Dec 22 '17 at 16:07
Yeah I found the website already, just wanted to know if there is specific functionnality of pandas to handle this type of problem. Thanks anyways I will look at the documentation. — Cyrille MODIANO, Dec 22 '17 at 16:10
The documentation is pretty comprehensive but sometimes takes a bit of reading to find what you want. They do offer some tutorials at http://pandas.pydata.org/pandas-docs/stable/tutorials.html — paisanco, Dec 22 '17 at 16:12
This issue seems to be the exact same proble, I'm trying to solve: https://stackoverflow.com/questions/19324453/add-missing-dates-to-pandas-dataframe — Cyrille MODIANO, Dec 22 '17 at 16:17
Do you want to copy the last date (so multiple items have the same date/time stamp) or copy-and-add-one, or provide a default date, or what? — aghast, Dec 22 '17 at 16:31
If you want a solution, please [edit] your question and add example input and show at least the desired output (even better to show also show current results). Make these examples as small as possible to just illustrate the problem. — martineau, Dec 22 '17 at 17:01
@Cyrille: That's a huge improvement, but please also show the contents of the sample `data` that was as the source of the results your currently getting. This will allow folks to test their solutions before posting them. — martineau, Dec 22 '17 at 23:29
Is the following statement correct? You want to make all the series in dataset have exactly the same timestamps by adding them if necessary to each one (with y values of 0). — martineau, Dec 22 '17 at 23:35
@martineau Added the query and the output. The output is transformed to a list of dictionnaries to be readable. — Cyrille MODIANO, Dec 23 '17 at 10:01
Cyrille: Adding the output from the query has been helpful and has allowed me to make a lot of progress. However that are a couple of remaining issues. The data in the output shown isn't quite valid Python syntax: i.e. `u '3qkhfbf2kyvhk'` needs to be `u'3qkhfbf2kyvhk'`, another is the there's no `L` suffix on integers in Python 3, so `2265968010 L` is bad syntax. Lastly, there are duplicate entries for some series: i.e. the series with a `sql_id` of `'3qkhfbf2kyvhk'` has two different `'sample_time': u'2017-12-23 10:42'` entries each with different `'resources_consumed'` values (`17` and `5`). — martineau, Dec 23 '17 at 20:43
@martineau Thanks for your feedback. The u and L are just because of the JSONLint formatter I used, the original data is correct. About duplicates you are right, there were an issue with my query, I fixed it there will be no more duplicates for a single serie and a single timestamp. I edited my post. Thanks — Cyrille MODIANO, Dec 24 '17 at 10:58
Cyrille: Again that's an improvement, ***but*** having an `L` suffix on numbers isn't valid Python 3.x syntax. What version of Python are you using? If you're using Python 2.x, you should have (also) tagged your question with the appropriate language tag (one with a version in it, like "python-2.x". — martineau, Dec 24 '17 at 15:26
Cyrille: Good, sometimes small details like that are fairly important. Turns out though that converting what I already done to Python 2 was fairly easy—because I always consciously try to write portable code—see the answer I've posted. — martineau, Dec 25 '17 at 18:16
Excellent, the datasets building was quite informative for me, I’m not very good with generators yet, your exemple made me understand how this problem should be resolved. Thanks a lot — Cyrille MODIANO, Dec 25 '17 at 21:37

martineau · Accepted Answer · 2017-12-26T00:19:51.757

Here's something for Python 2 that does what I think you want. I left some print() calls it in which display the contents of a couple of important data structures that are built and used internally. Seeing what's in them should make it easier to understand how it works.

from __future__ import print_function
from collections import defaultdict
from pprint import pformat
from random import randint
from series import data

def indent(text, amount, char=' '):
    """ Indent each line of text by indicated number of characters. """
    padding = amount * char
    return ''.join(padding+line for line in text.splitlines(True))

def GetRandomHexColor():
    return '#{:06X}'.format(randint(0, 0xffffff))


# Extract the needed information from data grouped so that the sample times
# are grouped together by sql_id.
timestamps = defaultdict(dict)
for row in data:
    timestamps[row['sql_id']][row['sample_time']] = row['resources_consumed']
print('timestamps:')
print(indent(pformat(dict(timestamps)), 4))

# Create a sorted list of all the unique timestamps that exist.
master_series = sorted(set(stamp for stamps in timestamps.values()
                            for stamp in stamps))
print()
print('master_series:')
print(indent(pformat(master_series), 4))

# Create list of entries where each has a sublist that consists of values for
# every timestamp in the master_series.
datasets = [{'label': sql_id,
             'backgroundColor': GetRandomHexColor(),
             'data': [{'y': timestamps[sql_id][stamp]
                                if stamp in timestamps[sql_id] else 0,
                       'x': stamp} for stamp in master_series]
            } for sql_id in sorted(timestamps)]
print()
print('datasets:')
print(indent(pformat(datasets), 4))

Output:

timestamps:
    {u'0m9b1dywgrdqj': {u'2017-12-23 10:42': 1, u'2017-12-23 10:47': 2},
     u'1xc91cuvu7j11': {u'2017-12-23 10:43': 3},
     u'3qkhfbf2kyvhk': {u'2017-12-23 10:41': 1,
                        u'2017-12-23 10:42': 17,
                        u'2017-12-23 10:43': 26,
                        u'2017-12-23 10:44': 17,
                        u'2017-12-23 10:45': 3,
                        u'2017-12-23 10:47': 10,
                        u'2017-12-23 10:48': 4},
     u'50kcsz2gh1w84': {u'2017-12-23 10:42': 1,
                        u'2017-12-23 10:43': 1,
                        u'2017-12-23 10:44': 1,
                        u'2017-12-23 10:45': 1,
                        u'2017-12-23 10:47': 1},
     u'7726bj0dhtnmt': {u'2017-12-23 10:45': 11},
     u'8u125dk9nfc0q': {u'2017-12-23 10:42': 8,
                        u'2017-12-23 10:43': 8,
                        u'2017-12-23 10:44': 11,
                        u'2017-12-23 10:45': 13,
                        u'2017-12-23 10:47': 11,
                        u'2017-12-23 10:48': 5},
     u'cmx7t67z8wa74': {u'2017-12-23 10:45': 19},
     u'dp0vgyb1hsfjb': {u'2017-12-23 10:42': 1,
                        u'2017-12-23 10:44': 2,
                        u'2017-12-23 10:47': 1},
     u'ft7wcqu3hzvca': {u'2017-12-23 10:41': 2,
                        u'2017-12-23 10:42': 8,
                        u'2017-12-23 10:43': 3,
                        u'2017-12-23 10:44': 3,
                        u'2017-12-23 10:48': 1}}

master_series:
    [u'2017-12-23 10:41',
     u'2017-12-23 10:42',
     u'2017-12-23 10:43',
     u'2017-12-23 10:44',
     u'2017-12-23 10:45',
     u'2017-12-23 10:47',
     u'2017-12-23 10:48']

datasets:
    [{'backgroundColor': '#BBF2C0',
      'data': [{'x': u'2017-12-23 10:41', 'y': 0},
               {'x': u'2017-12-23 10:42', 'y': 1},
               {'x': u'2017-12-23 10:43', 'y': 0},
               {'x': u'2017-12-23 10:44', 'y': 0},
               {'x': u'2017-12-23 10:45', 'y': 0},
               {'x': u'2017-12-23 10:47', 'y': 2},
               {'x': u'2017-12-23 10:48', 'y': 0}],
      'label': u'0m9b1dywgrdqj'},
     {'backgroundColor': '#09BC4F',
      'data': [{'x': u'2017-12-23 10:41', 'y': 0},
               {'x': u'2017-12-23 10:42', 'y': 0},
               {'x': u'2017-12-23 10:43', 'y': 3},
               {'x': u'2017-12-23 10:44', 'y': 0},
               {'x': u'2017-12-23 10:45', 'y': 0},
               {'x': u'2017-12-23 10:47', 'y': 0},
               {'x': u'2017-12-23 10:48', 'y': 0}],
      'label': u'1xc91cuvu7j11'},
     {'backgroundColor': '#19F805',
      'data': [{'x': u'2017-12-23 10:41', 'y': 1},
               {'x': u'2017-12-23 10:42', 'y': 17},
               {'x': u'2017-12-23 10:43', 'y': 26},
               {'x': u'2017-12-23 10:44', 'y': 17},
               {'x': u'2017-12-23 10:45', 'y': 3},
               {'x': u'2017-12-23 10:47', 'y': 10},
               {'x': u'2017-12-23 10:48', 'y': 4}],
      'label': u'3qkhfbf2kyvhk'},
     {'backgroundColor': '#A85778',
      'data': [{'x': u'2017-12-23 10:41', 'y': 0},
               {'x': u'2017-12-23 10:42', 'y': 1},
               {'x': u'2017-12-23 10:43', 'y': 1},
               {'x': u'2017-12-23 10:44', 'y': 1},
               {'x': u'2017-12-23 10:45', 'y': 1},
               {'x': u'2017-12-23 10:47', 'y': 1},
               {'x': u'2017-12-23 10:48', 'y': 0}],
      'label': u'50kcsz2gh1w84'},
     {'backgroundColor': '#9FEC4A',
      'data': [{'x': u'2017-12-23 10:41', 'y': 0},
               {'x': u'2017-12-23 10:42', 'y': 0},
               {'x': u'2017-12-23 10:43', 'y': 0},
               {'x': u'2017-12-23 10:44', 'y': 0},
               {'x': u'2017-12-23 10:45', 'y': 11},
               {'x': u'2017-12-23 10:47', 'y': 0},
               {'x': u'2017-12-23 10:48', 'y': 0}],
      'label': u'7726bj0dhtnmt'},
     {'backgroundColor': '#4FBF10',
      'data': [{'x': u'2017-12-23 10:41', 'y': 0},
               {'x': u'2017-12-23 10:42', 'y': 8},
               {'x': u'2017-12-23 10:43', 'y': 8},
               {'x': u'2017-12-23 10:44', 'y': 11},
               {'x': u'2017-12-23 10:45', 'y': 13},
               {'x': u'2017-12-23 10:47', 'y': 11},
               {'x': u'2017-12-23 10:48', 'y': 5}],
      'label': u'8u125dk9nfc0q'},
     {'backgroundColor': '#ED6D56',
      'data': [{'x': u'2017-12-23 10:41', 'y': 0},
               {'x': u'2017-12-23 10:42', 'y': 0},
               {'x': u'2017-12-23 10:43', 'y': 0},
               {'x': u'2017-12-23 10:44', 'y': 0},
               {'x': u'2017-12-23 10:45', 'y': 19},
               {'x': u'2017-12-23 10:47', 'y': 0},
               {'x': u'2017-12-23 10:48', 'y': 0}],
      'label': u'cmx7t67z8wa74'},
     {'backgroundColor': '#8E3D97',
      'data': [{'x': u'2017-12-23 10:41', 'y': 0},
               {'x': u'2017-12-23 10:42', 'y': 1},
               {'x': u'2017-12-23 10:43', 'y': 0},
               {'x': u'2017-12-23 10:44', 'y': 2},
               {'x': u'2017-12-23 10:45', 'y': 0},
               {'x': u'2017-12-23 10:47', 'y': 1},
               {'x': u'2017-12-23 10:48', 'y': 0}],
      'label': u'dp0vgyb1hsfjb'},
     {'backgroundColor': '#1FC7DC',
      'data': [{'x': u'2017-12-23 10:41', 'y': 2},
               {'x': u'2017-12-23 10:42', 'y': 8},
               {'x': u'2017-12-23 10:43', 'y': 3},
               {'x': u'2017-12-23 10:44', 'y': 3},
               {'x': u'2017-12-23 10:45', 'y': 0},
               {'x': u'2017-12-23 10:47', 'y': 0},
               {'x': u'2017-12-23 10:48', 'y': 1}],
      'label': u'ft7wcqu3hzvca'}]

Here's the series.py module that's imported. All it does is define the list of sample data you now have in your question:

""" Define data from database. """

data = [
    {
        'sql_id': u'3qkhfbf2kyvhk',
        'sql_plan_hash_value': 2234478098L,
        'sample_time': u'2017-12-23 10:41',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59389,
        'resources_consumed': 1
    }, {
        'sql_id': u'ft7wcqu3hzvca',
        'sql_plan_hash_value': 2265968010L,
        'sample_time': u'2017-12-23 10:41',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59395,
        'resources_consumed': 2
    }, {
        'sql_id': u'0m9b1dywgrdqj',
        'sql_plan_hash_value': 3103504081L,
        'sample_time': u'2017-12-23 10:42',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59469,
        'resources_consumed': 1
    }, {
        'sql_id': u'3qkhfbf2kyvhk',
        'sql_plan_hash_value': 2234478098L,
        'sample_time': u'2017-12-23 10:42',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59511,
        'resources_consumed': 17
    }, {
        'sql_id': u'50kcsz2gh1w84',
        'sql_plan_hash_value': 2667639044L,
        'sample_time': u'2017-12-23 10:42',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59421,
        'resources_consumed': 1
    }, {
        'sql_id': u'8u125dk9nfc0q',
        'sql_plan_hash_value': 2470916118L,
        'sample_time': u'2017-12-23 10:42',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59503,
        'resources_consumed': 8
    }, {
        'sql_id': u'dp0vgyb1hsfjb',
        'sql_plan_hash_value': 3272358443L,
        'sample_time': u'2017-12-23 10:42',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59454,
        'resources_consumed': 1
    }, {
        'sql_id': u'ft7wcqu3hzvca',
        'sql_plan_hash_value': 2265968010L,
        'sample_time': u'2017-12-23 10:42',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59510,
        'resources_consumed': 8
    }, {
        'sql_id': u'1xc91cuvu7j11',
        'sql_plan_hash_value': 3080963105L,
        'sample_time': u'2017-12-23 10:43',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59555,
        'resources_consumed': 3
    }, {
        'sql_id': u'3qkhfbf2kyvhk',
        'sql_plan_hash_value': 2234478098L,
        'sample_time': u'2017-12-23 10:43',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59577,
        'resources_consumed': 26
    }, {
        'sql_id': u'50kcsz2gh1w84',
        'sql_plan_hash_value': 2667639044L,
        'sample_time': u'2017-12-23 10:43',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59532,
        'resources_consumed': 1
    }, {
        'sql_id': u'8u125dk9nfc0q',
        'sql_plan_hash_value': 2470916118L,
        'sample_time': u'2017-12-23 10:43',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59578,
        'resources_consumed': 8
    }, {
        'sql_id': u'ft7wcqu3hzvca',
        'sql_plan_hash_value': 2265968010L,
        'sample_time': u'2017-12-23 10:43',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59565,
        'resources_consumed': 3
    }, {
        'sql_id': u'3qkhfbf2kyvhk',
        'sql_plan_hash_value': 2234478098L,
        'sample_time': u'2017-12-23 10:44',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59644,
        'resources_consumed': 17
    }, {
        'sql_id': u'50kcsz2gh1w84',
        'sql_plan_hash_value': 2667639044L,
        'sample_time': u'2017-12-23 10:44',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59623,
        'resources_consumed': 1
    }, {
        'sql_id': u'8u125dk9nfc0q',
        'sql_plan_hash_value': 2470916118L,
        'sample_time': u'2017-12-23 10:44',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59642,
        'resources_consumed': 11
    }, {
        'sql_id': u'dp0vgyb1hsfjb',
        'sql_plan_hash_value': 3272358443L,
        'sample_time': u'2017-12-23 10:44',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59614,
        'resources_consumed': 2
    }, {
        'sql_id': u'ft7wcqu3hzvca',
        'sql_plan_hash_value': 2265968010L,
        'sample_time': u'2017-12-23 10:44',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59648,
        'resources_consumed': 3
    }, {
        'sql_id': u'3qkhfbf2kyvhk',
        'sql_plan_hash_value': 2234478098L,
        'sample_time': u'2017-12-23 10:45',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59665,
        'resources_consumed': 3
    }, {
        'sql_id': u'50kcsz2gh1w84',
        'sql_plan_hash_value': 2667639044L,
        'sample_time': u'2017-12-23 10:45',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59649,
        'resources_consumed': 1
    }, {
        'sql_id': u'7726bj0dhtnmt',
        'sql_plan_hash_value': 453825145,
        'sample_time': u'2017-12-23 10:45',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59718,
        'resources_consumed': 11
    }, {
        'sql_id': u'8u125dk9nfc0q',
        'sql_plan_hash_value': 2470916118L,
        'sample_time': u'2017-12-23 10:45',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59707,
        'resources_consumed': 13
    }, {
        'sql_id': u'cmx7t67z8wa74',
        'sql_plan_hash_value': 4270729444L,
        'sample_time': u'2017-12-23 10:45',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59705,
        'resources_consumed': 19
    }, {
        'sql_id': u'0m9b1dywgrdqj',
        'sql_plan_hash_value': 3103504081L,
        'sample_time': u'2017-12-23 10:47',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59765,
        'resources_consumed': 2
    }, {
        'sql_id': u'3qkhfbf2kyvhk',
        'sql_plan_hash_value': 2234478098L,
        'sample_time': u'2017-12-23 10:47',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59768,
        'resources_consumed': 10
    }, {
        'sql_id': u'50kcsz2gh1w84',
        'sql_plan_hash_value': 2667639044L,
        'sample_time': u'2017-12-23 10:47',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59770,
        'resources_consumed': 1
    }, {
        'sql_id': u'8u125dk9nfc0q',
        'sql_plan_hash_value': 2470916118L,
        'sample_time': u'2017-12-23 10:47',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59764,
        'resources_consumed': 11
    }, {
        'sql_id': u'dp0vgyb1hsfjb',
        'sql_plan_hash_value': 3272358443L,
        'sample_time': u'2017-12-23 10:47',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59754,
        'resources_consumed': 1
    }, {
        'sql_id': u'3qkhfbf2kyvhk',
        'sql_plan_hash_value': 2234478098L,
        'sample_time': u'2017-12-23 10:48',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59779,
        'resources_consumed': 4
    }, {
        'sql_id': u'8u125dk9nfc0q',
        'sql_plan_hash_value': 2470916118L,
        'sample_time': u'2017-12-23 10:48',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59788,
        'resources_consumed': 5
    }, {
        'sql_id': u'ft7wcqu3hzvca',
        'sql_plan_hash_value': 2265968010L,
        'sample_time': u'2017-12-23 10:48',
        'dbid': 312109145,
        'sid': 64,
        'serial': 16655,
        'id': 59789,
        'resources_consumed': 1
    }
]

Happy holidays!

Add a missing key in a nested dictionary?

1 Answers1