How To Convert Pandas Dataframe To Uniquely Structured Nested Json

I have a DF with structure as follows: traffic_group app_id key category factors 0 desktop app1 CI html 16.618628 1 desktop app1 CI xhr

Solution 1:

I don't see any "threshold" and "window" keys of the nested dictionaries in the input. Let's assume they have fixed values. Based on your output, it seems like for every triplet (traffic_group, app_id, key) you would like to create (in general) a different nested dictionary. Therefore, we need an initial groupby operation using these three keys. For each group we create the nested dictionary:

defcreate_nested_dicts(df): 
    return {'key': df['key'].unique()[0], 'threshold': 1, 'window': 60, 'factors': dict(zip(df['category'], df['factors']))}

df = df.groupby(['traffic_group', 'app_id', 'key']).apply(create_nested_dicts)

The next step is to combine rows into lists for each (traffic_group, app_id) doublet and return them as a dict:

df = df.groupby(['traffic_group', 'app_id']).apply(lambda df: df.tolist())

The final step is to convert the df into your output. There various ways of doing it. A simple one is the following:

df = df.reset_index().groupby('traffic_group').apply(lambda df: df.values)
output = dict(zip(df.index, [{app_id: val for _, app_id, val in vals} for vals in df.values]))                                                                                   

Solution 2:

Well, I've solved it the "old-fashioned" way. Posting my solution for anyone who may need it in the future. Nevertheless, if someone is able to do it using pandas I'd love to see it.

json_output = {}
fortraffic_groupin sorted_df.traffic_group.unique():
    json_output[traffic_group] = {}
    forapp_idin sorted_df[sorted_df.traffic_group == traffic_group].app_id.unique():
        json_output[traffic_group][app_id] = []
        forkeyin sorted_df[(sorted_df.traffic_group == traffic_group) &
                             (sorted_df.app_id == app_id)].key.unique():
            inner_dict = {"key" : key, "threshold" : 1, "window" : 60, "factors" : {}}
            forcategoryin sorted_df[(sorted_df.traffic_group == traffic_group) & 
                                      (sorted_df.app_id == app_id) & 
                                      (sorted_df.key == key)].category.unique():
                value = sorted_df[(sorted_df.traffic_group == traffic_group) & 
                                  (sorted_df.app_id == app_id) & 
                                  (sorted_df.key == key) & 
                                  (sorted_df.category == category)].factors  
                inner_dict["factors"][category] = value.iloc[0]
            json_output[traffic_group][app_id].append(inner_dict)

Solution 3:

Use the following approach:

In [208]: d = {}                                                                                                   

In [209]: grouped = df.groupby(['traffic_group', 'app_id', 'key']).agg(pd.Series.to_dict).to_dict(orient='index')  

In [210]: fort, v in grouped.items(): 
     ...:     traff_gr, app_id, key = t 
     ...:     inner_d = {"key": key, "threshold": 1, "window": 60, 'factors': dict(zip(v['category'].values(), v['f
     ...: actors'].values()))} 
     ...:     d.setdefault(traff_gr, {}).setdefault(app_id, []).append(inner_d) 
     ...:                                                                                                          

In [211]: d                                                                                                        
Out[211]: 
{'desktop': {'app1': [{'key': 'CI',
    'threshold': 1,
    'window': 60,
    'factors': {'html': 16.618628, 'xhr': 35.497082}},
   {'key': 'IP',
    'threshold': 1,
    'window': 60,
    'factors': {'html': 18.294468, 'xhr': 30.422464}}],
  'app2': [{'key': 'CI',
    'threshold': 1,
    'window': 60,
    'factors': {'html': 11.02824, 'json': 33.548279}}]},
 'mobile': {'app1': [{'key': 'IP',
    'threshold': 1,
    'window': 60,
    'factors': {'html': 12.808367, 'image': 14.410632999999999}}]}}