I have the following output from an SqlAlchemy select operator.

| device_id |     event_name      | event_count |
| :--------:| :-----------------: |:-----------:|
| 123456    | speed more than 100 |      3      |
| 123456    | speed less than 12  |      0      |
| 334455    | out of NYC          |      5      |
| 111111    | in UCSD campus      |      1      |

Now I want to save this result into a nested dictionary with the following format, and I don’t know how to do it efficiently.

I need to have a dictionary in which its keys are the device_ids, and the values are dictionaries in which the keys are event_names and the values are event_counts.

{'123456' : {'speed more than 100' : 3,
             'speed less than 12': 0},
 '334455' : {'out of NYC' : 5},
 '111111' : {'in UCSD campus' : 1}
}

Here is my code.

 def count_per_event_json(self, count_per_event_query_result):
        result = {}
        print(count_per_event_query_result)
        for item in enumerate(count_per_event_query_result):
            if item[0] not in result.keys():
                # result[item[0]] = {I don't know how to fill this inner dict'}
    
        return result

>Solution :

You could do this with pandas:

import pandas as pd

# Use pandas.read_sql to read the query results into a dataframe
df = pd.read_sql(select_query, con=your_db_connection)

# Use the pandas groupby function to group the dataframe by device_id
grouped_df = df.groupby('device_id')

# Convert the grouped dataframe into a nested dictionary using the to_dict method
result_dict = grouped_df.apply(lambda x: x.set_index('event_name')['event_count'].to_dict()).to_dict()