Convert dataframe into specific JSON-CodePudding

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"]
                                }
                            }
                        }
                    }

                }
            }

        }
    }
}

CodePudding user response：

To get the same dictionary as in your example, you can iterate through your dataframe's columns and create the dictionary as such (using literal evaluation to help since df.to_json returns a string and you want a list):

import ast
your_dict = {}

for col in df.columns:
    your_dict[col] = df[col].to_json(orient='records')
    your_dict[col] = ast.literal_eval(your_dict[col])

print(your_dict)

Giving you:

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

CodePudding user response：

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"
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}