How to loop over pivot table to create list of dictionaries taking the index and value from each row of each column

Question

I have this table here and i'm trying to take the value and device category from each row in each column so I can have data like below.

series: [{
        name: 'engaged_sessions',
        data: [{
            name: 'Desktop',
            y: 7765,
        }, {
            name: 'Mobile',
            y: 388
        },...
        name: 'event_count',
        data: [{
            name: 'Desktop',
            y: 51325,
        }, {
            name: 'Mobile',
            y: 4349
        },...

And basically go through each column taking the device category and value into a list of dictionaries Heres the pivot table,

                 engaged_sessions  event_count  new_users  total_revenue  total_users
device_category                                                                      
Desktop                      7765        51325       6593              9         8021
Mobile                        388         4349        795              0          412
Smart Tv                        2           38          1              250          9
Tablet                         87          111         37              0           97

I've tried using a for loop and put each iteration into a list but it wasn't quite right. The closest I've gotten is with to_dict() method and I think that has the best bet so far.

This question here (Pandas to_dict data structure, using column as dictionary index) is very similar but I'm trying group by each column and if I use groupby(df.cloumns) or groupby(['column'],['column']) it gives me objects with numbers in it but no reference to what they are

Answer 1

If df contains the pivoted dataframe from your question you can do:

out = []
for c in df:
    out.append(
        {"name": c, "data": [{"name": k, "y": v} for k, v in df[c].to_dict().items()]}
    )
print(out)

Prints:

[
    {
        "name": "engaged_sessions",
        "data": [
            {"name": "Desktop", "y": 7765},
            {"name": "Mobile", "y": 388},
            {"name": "Smart Tv", "y": 2},
            {"name": "Tablet", "y": 87},
        ],
    },
    {
        "name": "event_count",
        "data": [
            {"name": "Desktop", "y": 51325},
            {"name": "Mobile", "y": 4349},
            {"name": "Smart Tv", "y": 38},
            {"name": "Tablet", "y": 111},
        ],
    },
    {
        "name": "new_users",
        "data": [
            {"name": "Desktop", "y": 6593},
            {"name": "Mobile", "y": 795},
            {"name": "Smart Tv", "y": 1},
            {"name": "Tablet", "y": 37},
        ],
    },
    {
        "name": "total_revenue",
        "data": [
            {"name": "Desktop", "y": 9},
            {"name": "Mobile", "y": 0},
            {"name": "Smart Tv", "y": 250},
            {"name": "Tablet", "y": 0},
        ],
    },
    {
        "name": "total_users",
        "data": [
            {"name": "Desktop", "y": 8021},
            {"name": "Mobile", "y": 412},
            {"name": "Smart Tv", "y": 9},
            {"name": "Tablet", "y": 97},
        ],
    },
]