Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
233 views
in Technique[技术] by (71.8m points)

python - Pandas Dataframe to Nested JSON

I am trying to convert a Pandas Dataframe to a JSON object. My Dataframe contains data in the following format:

         student      date    grade         course
0     Student_1    2017-06-25  93          ENGLISH
1     Student_2    2017-06-25  83          ENGLISH
2     Student_1    2017-06-25  93          MATH
3     Student_2    2017-06-25  83          MATH
4     Student_1    2017-06-26  90          MATH
5     Student_2    2017-06-26  85          MATH
6     Student_1    2017-06-26  96          ENGLISH
7     Student_2    2017-06-26  99          ENGLISH

I want to convert it to a JSON object in the following format:

[
    {'ENGLISH': [
      {
        'date' : '2017-06-25',
        'Student_1' : 93,
        'Student_2' : 83
      },

      {
        'date' : '2017-06-26',
        'Student_1' : 96,
        'Student_2' : 89
      }]
   },

    {'MATH': [
      {
        'date' : '2017-06-25',
        'Student_1' : 93,
        'Student_2' : 83
      },

      {
        'date' : '2017-06-26',
        'Student_1' : 90,
        'Student_2' : 85
      }]
    }
]

A simple .to_json() call did not do the trick for me. Is there anyway I can create the JSON object in the required format in Pandas?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You can first define a function to convert sub-groups to json, then apply this function to each group, and then merge sub-group jsons to a single json object.

def f(x):
    return (dict({'date':x.date.iloc[0]},**{k:v for k,v in zip(x.student,x.grade)}))

(
    df.groupby(['course','date'])
      .apply(f)
      .groupby(level=0)
      .apply(lambda x: x.tolist())
      .to_dict()
)
Out[1006]: 
{'ENGLISH': [{'Student_1': 93, 'Student_2': 83, 'date': '2017-06-25'},
  {'Student_1': 96, 'Student_2': 99, 'date': '2017-06-26'}],
 'MATH': [{'Student_1': 93, 'Student_2': 83, 'date': '2017-06-25'},
  {'Student_1': 90, 'Student_2': 85, 'date': '2017-06-26'}]}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...