Convert a three column dataframe to JSON for a multi line plot-CodePudding

I have a data frame as attached. And I'm trying to get to convert it to a JSON.

Table:

id	date	count
A	01-Nov	20
A	02-Nov	30
A	03-Nov	50
B	01-Nov	10
B	02-Nov	45
B	03-Nov	23
C	01-Nov	12
C	02-Nov	34
C	03-Nov	45

df = pd.DataFrame({'id': ['A']*3 ['B']*3 ['C']*3
                   , 'date': ['01-Nov','02-Nov','03-Nov']*3
                   , 'count': [20,30,50,10,45,23,12,34,45]})

Expecting the output as:

{
    "date": [
        "01 Nov",
        "02 Nov",
        "03 Nov",
    ], "values": [
        {
            "id": "A",
            "count": [20,30,50]
        },
        {
            "id": "B",
            "count": [10,45,23]
        },
        {
            "id": "C",
            "count": [12,34,45]
        }
    ]
}

Any pointers would be helpful… I was able to use to_json but it isn't giving me the format as above.

CodePudding user response：

try this:

dfx=df.groupby('id').agg(list).reset_index() #groupby id and keep values in a list.
'''
  id                      date         count
0  A  [01-Nov, 02-Nov, 03-Nov]  [20, 30, 50]
1  B  [01-Nov, 02-Nov, 03-Nov]  [10, 45, 23]
2  C  [01-Nov, 02-Nov, 03-Nov]  [12, 34, 45]
'''
out= {'date':dfx['date'][0],'values':dfx[['id','count']].to_dict('records')}

'''
{
    "date": [
        "01-Nov",
        "02-Nov",
        "03-Nov",
    ], "values": [
        {
            "id": "A",
            "count": [20,30,50]
        },
        {
            "id": "B",
            "count": [10,45,23]
        },
        {
            "id": "C",
            "count": [12,34,45]
        }
    ]
}
'''