I've looked through a lot of solutions on this topic, but I have been unable to adapt my case to a performant one. Suppose I have a list of dictionaries stored as:
db_data = [
{
"start_time": "2020-04-20T17:55:54.000-00:00",
"results": {
"key_1": ["a","b","c","d"],
"key_2": ["a","b","c","d"],
"key_3": ["a","b","c","d"]
}
},
{
"start_time": "2020-04-20T18:32:27.000-00:00",
"results": {
"key_1": ["a","b","c","d"],
"key_2": ["a","b","e","f"],
"key_3": ["a","e","f","g"]
}
},
{
"start_time": "2020-04-21T17:55:54.000-00:00",
"results": {
"key_1": ["a","b","c"],
"key_2": ["a"],
"key_3": ["a","b","c","d"]
}
},
{
"start_time": "2020-04-21T18:32:27.000-00:00",
"results": {
"key_1": ["a","b","c"],
"key_2": ["b"],
"key_3": ["a"]
}
}
]
I am trying to get a data aggregation from the list output as a dictionary, with the key values of the results object as the keys of the output, and the size of the set of unique values for each date for each key.
I am attempting to aggregate the data by date value, and outputting the count of unique values for each key for each day.
Expected output is something like:
{
"key_1": {
"2020-04-20": 4,
"2020-04-21": 3
},
"key_2": {
"2020-04-20": 6,
"2020-04-21": 2
},
"key_3": {
"2020-04-20": 7,
"2020-04-21": 4
}
}
What I have tried so far is using defaultdict
and loops to aggregate the data. This takes a very long time unfortunately:
from datetime import datetime
from collections import defaultdict
grouped_data = defaultdict(dict)
for item in db_data:
group = item['start_time'].strftime('%-b %-d, %Y')
for k, v in item['results'].items():
if group not in grouped_data[k].keys():
grouped_data[k][group] = []
grouped_data[k][group] = v + grouped_data[k][group]
for k, v in grouped_data.items():
grouped_data[k] = {x:len(set(y)) for x, y in v.items()}
print(grouped_data)
Any help or guidance is appreciated. I have read that pandas
might help here, but I am not quite sure how to adapt this use case.