How to Convert dataframe into json with list of list as a value?-CodePudding

I have a dataframe like this:

How can i convert it into a json object like this :

[
    {
        "name": "ABC",
        "data": [
            [
                1506709800000,
                null
            ],
            [
                1514658600000,
                756.98
            ],
            [
                1522434600000,
                null
            ],
            [
                1530297000000,
                null
            ],
            
        ]
    },
    {
        "name": "EFG",
        "data": [
            [
                1506779800000,
                789
            ],
            [
                152368600000,
                412.98
            ],
            [
                16245834600000,
                512
            ],
            [
                19874567000000,
                null
            ],
            
        ]
    }
]

This is what i tried ,but i am getting column name with each records and not a list of list:

(df.groupby('Name')[['ts','value']]
       .apply(lambda x: x.to_dict('records'))
       .to_json(orient="index"))

CodePudding user response：

My solution is ugly, but it works:

import pandas
import json

Object = list(df.groupby('Name').apply(lambda x: {'name': x.name, 'data': list(zip(x['ts'], x['value']))}))
JsonObject = json.dumps(Object, indent=4)

You can use it before a better answer is found.

CodePudding user response：

If you pre-merge your ts and value columns into a data column, then you can simply aggregate the data values into a list before conversion to JSON:

df['data'] = df[['ts','value']].values.tolist()
df2 = df.groupby('Name')['data'].aggregate(lambda x:x.tolist()).reset_index()
jstr = json.dumps(df2.to_dict('records'), indent=4)
print(jstr)

Output:

[
    {
        "Name": "ABC",
        "data": [
            [
                1506709800000.0,
                NaN
            ],
            [
                1514658600000.0,
                756.98
            ],
            [
                1522434600000.0,
                NaN
            ],
            [
                1530297000000.0,
                NaN
            ]
        ]
    },
    {
        "Name": "EFG",
        "data": [
            [
                1506779800000.0,
                789.0
            ],
            [
                152368600000.0,
                412.98
            ],
            [
                16245834600000.0,
                512.0
            ],
            [
                19874567000000.0,
                NaN
            ]
        ]
    }
]