Transforming Pandas pivot-table to multiple arrays of objects

Question

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

Answer 1

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

---

EDIT: With new output:

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

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

Prints:

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