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

Does this answer your question https://stackoverflow.com/questions/39067831/dataframe-with-multiindex-to-dict? — Nikolay Zakirov, Oct 27 '22 at 08:52
@NikolayZakirov Only partially. Because their solution is transforming the pivot into a dict of dictionaries, while my desired output is a dict of lists of objects. — newuser1628, Oct 27 '22 at 08:57
I actually think that your additional list is redundant. Although certainly not hard to do — Nikolay Zakirov, Oct 27 '22 at 08:59
@arasemco I did try it but it did not do the trick for me as I could not structure the data the way I want. — newuser1628, Oct 27 '22 at 09:06
@NikolayZakirov Taking a second look at the structure, you are right, it seems this could be better structured. any suggestions? — newuser1628, Oct 27 '22 at 09:07
I think that dict of dict is a more natural way to structure it. But depends on your needs of course — Nikolay Zakirov, Oct 27 '22 at 09:14
@NikolayZakirov I agree, but I'm just worried about the complications it might cause when using Javascript since for me slicing arrays in Java is much simpler than filtering objects. — newuser1628, Oct 27 '22 at 09:16

Andrej Kesely · Accepted Answer · 2022-10-27T10:53:20.280

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

Amazing! works great! Would it be possible to adjust your answer to the new output format, please? :) — newuser1628, Oct 27 '22 at 09:45

score 0 · Answer 2 · answered Oct 27 '22 at 09:17

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'])

Transforming Pandas pivot-table to multiple arrays of objects

2 Answers2