[FIXED] Convert dataframe into specific JSON

Issue

I would like to convert my DataFrame into a specific JSON. I try to use to_dict() but for the moment I didn't find the correct parameters to replicate the output.

Do you have any idea?

My code :

import pandas as pd
data = {
    'alt' : ["BeattheBeachmark NEW", "BeattheBeachmark NEW"],
    'Mod' : ["GA", "GA"],
    'Pers' : ["Movment", "Movment"],
    'Vie' : ["Inprogress", "Inprogress"],
    'Actions' : ["Clear", "Add"]
}

df = pd.DataFrame(data)

My Ouput :

result = {
    "alt" : {
        "BeattheBeachmark NEW" : {
            "Mod" : {
                "GA" :  {
                    "Pers" : {
                        "Movment" : {
                            "Vie" : {
                                "Inprogress" : {
                                    'Actions' : ["Clear", "Add"]
                                }
                            }
                        }
                    }

                }
            }

        }
    }
}

Solution

You can group your dataframe by "alt", by "Mod"... and so on and create your dictionary along the way:

import pandas as pd
import json
data = {
    'alt' : ["BeattheBeachmark NEW", "BeattheBeachmark NEW"],
    'Mod' : ["GA", "GA"],
    'Pers' : ["Movment", "Movment"],
    'Vie' : ["Inprogress", "Inprogress"],
    'Actions' : ["Clear", "Add"]
}

df = pd.DataFrame(data)
output_dict = dict()
output_dict['alt'] = dict()

for alt in df.groupby("alt"):
    output_dict['alt'][alt[0]] = dict()
    output_dict['alt'][alt[0]]["Mod"] = dict()
    for mod in alt[1].groupby("Mod"):
        output_dict['alt'][alt[0]]["Mod"][mod[0]] = dict()
        output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"] = dict()
        for pers in mod[1].groupby("Pers"):
            output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]] = dict()
            output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]]["Vie"] = dict()
            for vie in pers[1].groupby("Vie"):
                output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]]["Vie"][vie[0]] = dict()
                output_dict['alt'][alt[0]]["Mod"][mod[0]]["Pers"][pers[0]]["Vie"][vie[0]]["Actions"] = list(vie[1].Actions)

print(json.dumps(output_dict, indent=4))

Output:

{
    "alt": {
        "BeattheBeachmark NEW": {
            "Mod": {
                "GA": {
                    "Pers": {
                        "Movment": {
                            "Vie": {
                                "Inprogress": {
                                    "Actions": [
                                        "Clear",
                                        "Add"
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

EDIT: for archive purpose, I add a recursive solution for this kind of problem, making it much more generic:

import pandas as pd
import json
data = {
    'alt' : ["BeattheBeachmark NEW", "BeattheBeachmark NEW"],
    'Mod' : ["GA", "GA"],
    'Pers' : ["Movment", "Movment"],
    'Vie' : ["Inprogress", "Inprogress"],
    'Actions' : ["Clear", "Add"]
}

df_in = pd.DataFrame(data)
output_dict = dict()

def extract_columns(df, col, output_dict):
    if col == len(df.columns)-1:
        output_dict[df.columns[col]] = list(df[df.columns[col]])
    else:
        output_dict[df.columns[col]] = dict()
        for first_col_grp in df.groupby(df.columns[col]):
            output_dict[df.columns[col]][first_col_grp[0]] = dict()
            extract_columns(first_col_grp[1], col+1, output_dict[df.columns[col]][first_col_grp[0]])


extract_columns(df_in, 0, output_dict)

print(json.dumps(output_dict, indent=4))

Answered By - Tranbi