Generating Custom JSON format output from pandas dataframe

Question

I have a pandas Dataframe like this.

The dataframe data

    date            aid     x_axis  y_axis  z_axis
0   2018-12-01  9.0     -0.71   8.75    5.23
1   2018-12-03  3.0     -0.55   10.13   1.15
2   2018-12-03  4.0     -1.84   4.56    5.74
3   2018-12-03  5.0     -1.49   4.11    7.03
4   2018-12-03  7.0     -0.40   0.83    -8.29
5   2018-12-04  4.0     -9.04   -0.04   0.55
6   2018-12-05  1.0     -3.03   6.20    7.16
7   2018-12-05  4.0     -3.93   7.69    3.82
8   2018-12-05  5.0     -4.00   7.75    1.01
9   2018-12-06  5.0     -4.47   6.79    4.12

---

I need to convert this to a User Defined JSON. in the format below:

   {
        "date": "2018-12-01",
        "9": {
            "mean": {
                "x_axis": -0.71,
                "y_axis": 8.75,
                "z_axis": 5.23
            }
        },
    },
    {   
       "date": "2018-12-03",
        "3": {
            "mean": {
                "x_axis": -0.55,
                "y_axis": 10.13,
                "z_axis": 1.15
            }
        },
        "4": {
            "mean": {
                "x_axis": -0.55,
                "y_axis": 10.13,
                "z_axis": 1.15
             }
         },  -----------------------------------------
----------------------------------------------------

    d = (data3.groupby(['date','aid'])['x_axis','y_axis','z_axis']
         .apply(lambda x: x.to_dict('index'))
         .reset_index(name='data')
         .groupby('date')['aid','data']
         .apply(lambda x: x.set_index('aid')['data'].to_dict())
         .to_json(indent = 2,  double_precision = 2))
     print(d)

I am Getting this kind of output.

{
  "2018-12-01":{
    "9.0":{
      "0":{
        "x_axis":-0.71,
        "y_axis":8.75,
        "z_axis":5.23
      }
    }
  },
  "2018-12-03":{
    "3.0":{
      "1":{
        "x_axis":-0.55,
        "y_axis":10.13,
        "z_axis":1.15
      }
    },
    "4.0":{
      "2":{
        "x_axis":-1.84,
        "y_axis":4.56,
        "z_axis":5.74
      }
    },
    "5.0":{
      "3":{
        "x_axis":-1.49,
        "y_axis":4.11,
        "z_axis":7.03
      }
    },
    "7.0":{
      "4":{
        "x_axis":-0.4,
        "y_axis":0.83,
        "z_axis":-8.29
      }
    }
  },

I am expecting about the solution of these type user defined conversion from Pandas Dataframe to User defined JSON.

Answer 1

Try:

out = []
for d, g in df.groupby("date"):
    out.append(
        {
            "date": d,
        }
    )
    for _, row in g.iterrows():
        out[-1][str(int(row["aid"]))] = {
            "mean": {
                "x_axis": row["x_axis"],
                "y_axis": row["y_axis"],
                "z_axis": row["z_axis"],
            }
        }

print(out)

Prints:

[
    {
        "date": "2018-12-01",
        "9": {"mean": {"x_axis": -0.71, "y_axis": 8.75, "z_axis": 5.23}},
    },
    {
        "date": "2018-12-03",
        "3": {"mean": {"x_axis": -0.55, "y_axis": 10.13, "z_axis": 1.15}},
        "4": {"mean": {"x_axis": -1.84, "y_axis": 4.56, "z_axis": 5.74}},
        "5": {"mean": {"x_axis": -1.49, "y_axis": 4.11, "z_axis": 7.03}},
        "7": {"mean": {"x_axis": -0.4, "y_axis": 0.83, "z_axis": -8.29}},
    },
    {
        "date": "2018-12-04",
        "4": {"mean": {"x_axis": -9.04, "y_axis": -0.04, "z_axis": 0.55}},
    },
    {
        "date": "2018-12-05",
        "1": {"mean": {"x_axis": -3.03, "y_axis": 6.2, "z_axis": 7.16}},
        "4": {"mean": {"x_axis": -3.93, "y_axis": 7.69, "z_axis": 3.82}},
        "5": {"mean": {"x_axis": -4.0, "y_axis": 7.75, "z_axis": 1.01}},
    },
    {
        "date": "2018-12-06",
        "5": {"mean": {"x_axis": -4.47, "y_axis": 6.79, "z_axis": 4.12}},
    },
]