Pandas and JSON Format-CodePudding

I would like to export a pandas dataframe to JSON but with a specific structure. This is the structure that I would like to have in the output JSON:

{
    "Var_1":"A1",
    "Var_2": [
        {"Var_3": "Value_3",
        "Var_4":  "Value_4",
        },
        {"Var_3": "Value_5",
        "Var_4":  "Value_6",
        }
    ]
    "Var_1":"A2",
    "Var_2": [
        {"Var_3": "Value_7",
        "Var_4":  "Value_8",
    ]
}

This my dataframe: pd.DataFrame({"Var_1": ['A1', 'A1', 'A2'], "Var_3": ["Value_3", "Value_5","Value7"], "Var_4": ["Value_4", "Value_6", "Value_8"]})

Var_2 can be computed with Var_3 and Var_4.

I tried with https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_json.html with records, split, etc... But impossible to get the right structure.

CodePudding user response：

IIUC, try:

output = (df.groupby("Var_1")
            .apply(lambda x: x.drop("Var_1",axis=1).to_dict("records"))
            .rename("Var_2")
            .reset_index()
            .to_json(orient="records")
          )

>>> output

[
  {
    "Var_1": "A1",
    "Var_2": [
      {
        "Var_3": "Value_3",
        "Var_4": "Value_4"
      },
      {
        "Var_3": "Value_5",
        "Var_4": "Value_6"
      }
    ]
  },
  {
    "Var_1": "A2",
    "Var_2": [
      {
        "Var_3": "Value7",
        "Var_4": "Value_8"
      }
    ]
  }
]