Create a nested dictionary with multiple columns in pandas-CodePudding

I'm trying to group the data in this way - {10:{10:[Pole], 5:[Carl]}

Right now, I have grouped data based on age and data column. Now I'm trying to include rating in it as well. So {Age:{Rating:[Data], Rating:[Data]}

This is how I'm grouping now,

df.groupby("Age")["Data"].agg(list).to_dict()

[Table Link] https://i.stack.imgur.com/PC8Cw.png

CodePudding user response：

Grouping can be done by groupby, but building dictionaries can only be done by custom functions. create_dict is used to dynamically create nested dictionaries based on grouping keys, with the innermost element setting the value

import pandas as pd


def create_dict(key_lst, val):
    global res
    key_num = len(key_lst)
    tmp_dict = res
    for index, key in enumerate(key_lst):
        if index == key_num - 1:
            tmp_dict[key] = val
        else:
            if key not in tmp_dict:
                tmp_dict.setdefault(key, {})

        tmp_dict = tmp_dict[key]


res = {}
df = pd.DataFrame({"Age": [10, 10, 3], "Data": ["Pole", "Carl", "Jack"], "Rating": [10, 5, 5]})

group_data = df.groupby(["Age", "Rating"]).agg({"Data": list}).Data

for k, v in zip(group_data.index, group_data.values):
    create_dict(k, v)

print(res)

# {3: {5: ['Jack']}, 10: {5: ['Carl'], 10: ['Pole']}}

CodePudding user response：

try this:

df.set_index('Rating').groupby(["Age"]).agg(dict).apply(lambda x: x.to_dict())

output:

Data    {3: {5: 'Jack'}, 10: {10: 'Pole', 5: 'Carl'}}

for different input:

Age Data    Rating
10  Pole    10
10  Carl    10
3   Jack    5

result:

{3: {5: 'Jack'}, 10: {10: ['Pole', 'Carl']}}

I'm, not sure this is what you're looking for. might delete :)