How to convert pandas dataframe to uniquely structured nested json

Question

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         35.497082
2   desktop         app1    IP  html        18.294468
3   desktop         app1    IP  xhr         30.422464
4   desktop         app2    CI  html        11.028240
5   desktop         app2    CI  json        33.548279
6   mobile          app1    IP  html        12.808367
7   mobile          app1    IP  image       14.410633

I need to output it to a json of the following structure:

{ "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.028240
                   "json" : 33.548279
                        }
                  }
              },
  "mobile": {
          app1: [  {
              "key": "IP",
              "threshold: 1,
              "window": 60,
              "factors: {
                   "html" : 12.808367
                   "xhr" : 14.410633
                        } 
                 ]
             }
 } 

The structure is admittedly convoluted.

I've considered the following previous answers and tried to mimic their logic to no avail:

Convert Pandas Dataframe to Custom Nested JSON

convert dataframe to nested json

Pandas Dataframe to Nested JSON

Any help is appreciated. Please don't just post a solution, but also explain your logic.

Answer 1

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]: for t, 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}}]}}

Answer 2

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:

def create_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]))                                                                                   

Answer 3

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 = {}
for traffic_group in sorted_df.traffic_group.unique():
    json_output[traffic_group] = {}
    for app_id in sorted_df[sorted_df.traffic_group == traffic_group].app_id.unique():
        json_output[traffic_group][app_id] = []
        for key in 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" : {}}
            for category in 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)