Transforming Pandas pivot-table to multiple arrays of objects-CodePudding

I have a Pandas pivot table; The goal is to pass this to a Django rest framework in the form of multiple arrays which I can easily filter in React JavaScript.

pivot:

                                x            y        z
Magazine      date
M1            2018-01           173          68       10
              2018-02           184          55       11

M2            2018-01           175          68       10
              2018-02           189          52       9

I need the output to be:

{
    "M1": [
           {
            "date": "2018-01",
            "x": 173,
            "y": 68,
            "z": 10},
           {
            "date": "2018-02",
            "x": 184,
            "y": 55,
            "z": 11}
          ],

    "M2": [
           {
            "date": "2018-01",
            "x": 175,
            "y": 68,
            "z": 10},
           {
            "date": "2018-02",
            "x": 189,
            "y": 52,
            "z": 9}
          ]
}

CodePudding user response：

Try:

out = {}
for (m, d), row in df.iterrows():
    out.setdefault(m, {}).setdefault(d, {})
    out[m][d] = dict(row)

out = {k: [{kk: vv} for kk, vv in v.items()] for k, v in out.items()}
print(out)

Prints:

{
    "M1": [
        {"2018-01": {"x": 173, "y": 68, "z": 10}},
        {"2018-02": {"x": 184, "y": 55, "z": 11}},
    ],
    "M2": [
        {"2018-01": {"x": 175, "y": 68, "z": 10}},
        {"2018-02": {"x": 189, "y": 52, "z": 9}},
    ],
}

CodePudding user response：

df.to_dict()

data = {
    "M1": [
        {"2018-01": {
            "x": 173,
            "y": 68,
            "z": 10}},
        {"2018-02": {
            "x": 184,
            "y": 55,
            "z": 11}}
    ],

    "M2": [
        {"2018-01": {
            "x": 175,
            "y": 68,
            "z": 10}},
        {"2018-02": {
            "x": 189,
            "y": 52,
            "z": 9}}
    ]
}

df = pd.DataFrame(data)
data1 = df.to_dict()

print(data['M1'][0]['2018-01']['z'])
print(data1['M1'][0]['2018-01']['z'])