This is the code for the table I am starting with.

data = [['tom', 'class a', 20], ['nick', 'class a', 14], 
['juli','class b', 14],['jane','class b', 155],['jone','class c', 80]]
  

df = pd.DataFrame(data, columns=['Name', 'class', 'grade'])

I tried to pivot it using:

 df = pd.pivot_table(
        data=df,
        index='class',
        columns = 'Name',
        values = 'grade' )
    
 df.reset_index(inplace=True) 
 df = df.rename(columns ={'class':'class'}) 
 s = pd.Series([1, 2, 3]) 
 df = df.set_index(s)

And then turning it into a json table using:

df = json.dumps(df.to_dict())

I am getting this in the end:

{
    "class": {"1": "class a", "2": "class b", "3": "class c"},
    "jane": {"1": NaN, "2": 155.0, "3": NaN},
    "jone": {"1": NaN, "2": NaN, "3": 80.0},
    "juli": {"1": NaN, "2": 14.0, "3": NaN},
    "tom": {"1": 20.0, "2": NaN, "3": NaN},
}

While the intended result would be something like this:

{
  "class": class a,
  "tom": 20,
  "nick": 14,
  "juli": 0,
  "jane": 0 or NaN doesnt matter,
  "jone": 0 or NaN
}, {
  "class": class b,
  "tom": 0 or NaN,
  "nick": 0 or NaN,
  "juli": 14,
  "jane": 155,
  "jone": 0 or NaN
}, {
  "class": class c,
  "tom": 0 or NaN,
  "nick": 0 or NaN,
  "juli": 0 or NaN,
  "jane": 0 or NaN,
  "jone": 80
}

CodePudding user response：

You can use df.to_dict("records"). Docs: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_dict.html

Output:

[{'class': 'class a', 'jane': nan, 'jone': nan, 'juli': nan, 'nick': 14.0, 'tom': 20.0}, {'class': 'class b', 'jane': 155.0, 'jone': nan, 'juli': 14.0, 'nick': nan, 'tom': nan}, {'class': 'class c', 'jane': nan, 'jone': 80.0, 'juli': nan, 'nick': nan, 'tom': nan}]

Additionally, renaming class to class is redundant