Turning a Dataframe into a nested dictionary-CodePudding

I have a dataframe like below. How can I get it into a nested dictionary like

     Guest  GuestCode          ProductName  Quantity Invoice No
0    Maria        NaN       Pro Plus Cream         2  OBFL22511
1    Maria        NaN  Soothe Stress Cream         1  OBFL22511
2  Sanchez  OBFLG3108       Pro Plus Cream         1  OBFL22524
3    Karen  OBFLG1600  Soothe Stress Cream         1  OBFL22525
4    Karen  OBFLG1600       Pro Plus Cream         1  OBFL22525

I want the dataframe converted into the following dictionary format:

{"Guest": {"GuestCode": {"Invoice No": {"ProductName": Quantity}}}

For example:

{"Karen": {"OBFLG160": {"OBFL22525": {"Soothe Stress Cream": 1, "Pro Plus Cream": 1}}}

I tried this:

for index, row in df.iterrows():
    my_dict[row['Guest']] = {row['GuestCode']: {row['Invoice No']: {row['ProductName']}}}

But it does not list all the items if a guest has multiple products.

I also tried and played around with this, but don't really understand dictionary comprehension:

d = {k: v.groupby('GuestCode')['Invoice No','ProductName' , 'Quantity'].apply(list).to_dict() for k, v in df.groupby('Guest')}

CodePudding user response：

my_dict = {k[0]: {k[1]: {k[2]: {p: q for p, q in row[['ProductName', 'Quantity']].values}}} for k, row in df.fillna('<NA>').groupby(['Guest', 'GuestCode', 'Invoice No'])}

Output:

>>> my_dict
{'Karen': {'OBFLG1600': {'OBFL22525': {'Soothe Stress Cream': 1, 'Pro Plus Cream': 1}}},
 'Maria': {'<NA>': {'OBFL22511': {'Pro Plus Cream': 2, 'Soothe Stress Cream': 1}}},
 'Sanchez': {'OBFLG3108': {'OBFL22524': {'Pro Plus Cream': 1}}}}

>>> import json
>>> print(json.dumps(my_dict, indent=2))
{
  "Karen": {
    "OBFLG1600": {
      "OBFL22525": {
        "Soothe Stress Cream": 1,
        "Pro Plus Cream": 1
      }
    }
  },
  "Maria": {
    "<NA>": {
      "OBFL22511": {
        "Pro Plus Cream": 2,
        "Soothe Stress Cream": 1
      }
    }
  },
  "Sanchez": {
    "OBFLG3108": {
      "OBFL22524": {
        "Pro Plus Cream": 1
      }
    }
  }
}