Pandas Create dict from Dataframe

Question

How Can I convert this DF from pandas

Into this dictionary:

{"114811":{
  "password-manager-active":[
   {
     "ref_date":"2021-10-04",
     "value":"0",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"3",
     "60_months":"3"
  },
  {
     "ref_date":"2021-10-04",
     "value":"1",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"0",
     "60_months":"0"
    }
  ],
  "password-manager-installed":[
  {
     "ref_date":"2021-10-04",
     "value":"0",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"0",
     "60_months":"0"
  },
  {
     "ref_date":"2021-10-04",
     "value":"1",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"1",
     "60_months":"1"
   }
  ],
   "password-manager-last-used":[
  {
     "ref_date":"2021-10-04",
     "value":"1",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"0",
     "60_months":"0"
     }
   ],
   "password-manager-multifactor":[
  {
     "ref_date":"2021-10-04",
     "value":"0",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"0",
     "60_months":"0"
  },
  {
     "ref_date":"2021-10-04",
     "value":"1",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"0",
     "60_months":"0"
    }
  ],
   "password-manager-reset-required":[
   {
     "ref_date":"2021-10-04",
     "value":"0",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"0",
     "60_months":"0"
   },
   {
     "ref_date":"2021-10-04",
     "value":"1",
     "1_months":"0",
     "2_months":"0",
     "6_months":"0",
     "12_months":"0",
     "24_months":"0",
     "60_months":"0"
        }
      ]
    }}

I mean, I need to group by person_nid and behavior_type and then create a list of dicts for the records to be grouped where person_nid is a key with another dictionary inside. In the nested dictionary, the keys are the behavior_type and then the value for every behavior_type is a list of dicts describing each record.

check out this answer: https://stackoverflow.com/a/39074579/6770704 it seems like you would like to set this dataframe with a multi-index `person_id` and `behavior_type` and follow the steps in the link — Ian, Oct 06 '21 at 12:46

trolloldem · Answer 1 · 2021-10-06T15:21:07.207

In order to obtain what you are asking, you can filter your DataFrame for each unique user id, and on the filtered Dataframe obtain every row for each behavior_type. You can achieve this with the following code:

   import pandas as pd

   # read your dataframe inside the df variable

   final_dict = dict()
   not_nested= ["person_nid", "behaviour_type"]
   final_cols= list()

   # obtain the inner columns
   for col in  df.columns:
        if col not in not_nested:
                final_cols.append(col)

   # iterate over all the person_nid
   for nid in df["person_nid"].unique():

        # dataframe containing only the rows with the current person_nid
        nid_df = df[df["person_nid"]==nid]
        # create nested dict for current person_nid
        final_dict[nid] = dict()

        # get all the behaviour_type for the current nid
        for btype in nid_df["behaviour_type"].unique():

                # create list of dictionaries for the current behaviour_type
                final_dict[nid][btype]=list()
                # dataframe with the rows for the inner dictionaries
                b_df = nid_df[nid_df["behaviour_type"]==btype][final_cols]
                # create all the dictionaries inside the list
                for index, row in b_df.iterrows():
                        new_dict = dict()
                        for col in b_df.columns:
                             new_dict[col]=row[col]
                        final_dict[nid][btype].append(new_dict)

If you don't want to create the final dictionaries, you could store a list of the rows returned by b_df.iterrows() since they can be accessed in the same way of a dictionary with the instruction rows[colname]. If you want to avoid iterations, you can use groupby and apply like in the following code to produce the same result:

not_nested= ["person_nid", "behaviour_type"]
final_cols= list()
for col in  df.columns:
   if col not in not_nested:
      final_cols.append(col)

def external_apply(df):
    df= df.groupby("behaviour_type").apply(inner_apply)
    return df.to_dict()
def inner_apply(df):
    return df[final_cols].to_dict(orient="records")

final_dict =df.groupby("person_nid").apply(external_apply).to_dict()

Thanks For your answer, Even though your answer is valid, I want to avoid iterations. That's because I want to be more memory efficient. — Steven Arias, Oct 06 '21 at 13:28
@StevenArias I've edited my answer to show an option which avoids the use of loops — trolloldem, Oct 06 '21 at 15:21

score 0 · Answer 2 · answered Oct 06 '21 at 17:36

Something like multiple groups then using to_dict('records') could work.

result = {}
for g,g_hold in df.groupby('person_nid'):
    for g2,g2_hold in g_hold[[h for h in g_hold if h != 'person_nid']].groupby('behaviour_type'):
        if g in result:
            result[g][g2] = g2_hold[[h for h in g2_hold if h != 'behaviour_type']].to_dict('records')
        else:
            result[g] = {g2:g2_hold[[h for h in g2_hold if h != 'type']].to_dict('records')}

Pandas Create dict from Dataframe

2 Answers2