I have a dataframe that I need to convert to a dict and send via API as Json.

This is my df:

I want to generate dict something like this:

{Cat1: {
        topc1:{
               step1:
                     [
                       {Stud1:10},
                       {Stud2:15},
                       {Stud3:30}
                     ]
               }
        }
        topc2:{
               step2:
                     [
                       {Stud1:10},
                       {Stud2:15},
                       {Stud3:30}
                     ]
               }
        }
}

I know I can achieve this with for loop and iterrows() but that would be slow result.

I have tried using to_dict() function from pandas with group_by() on Category, Topic, Step but that doesn't give required output. I have tried many ways like stack() and I even merging the rows of same name but it yields no result. Can anyone tell me best efficient way to achieve this.

CodePudding user response：

I'm not completely sure I understand the question fully. But you could try this:

result = {}
for (key1, key2, key3), sdf in df.groupby(["Category", "Topic", "Steps"]):
    inner = result.setdefault(key1, {}).setdefault(key2, {})
    inner[key3] = [
        {key: value}
        for record in sdf[["Stud1", "Stud2", "Stud3"]].to_dict(orient="records")
        for key, value in record.items()
    ]

Result for your example df:

{'Cat1': {'topc1': {'step1': [{'Stud1': 10}, {'Stud2': 15}, {'Stud3': 30}]},
          'topc2': {'step2': [{'Stud1': 16}, {'Stud2': 26}, {'Stud3': 42}]}},
 'Cat3': {'topc3': {'step3': [{'Stud1': 5}, {'Stud2': 62}, {'Stud3': 50}]}}}