CODEWITHSUNDEEP
pythonjsonpandaspandas-groupbydata-analysis
Vaidik Trivedi
Vaidik Trivedi

Reputation: 1

How to convert pandas Data frame into Json?

I am using pandas and I have appplied groupby on dataframe. Now I need to convert this dataframe into Json file.

Here is an image of DataFrame

Dataframe (name = grouped):

student_id stuent_name course_id subject_name teacher weight_score total_average
1 A 1 Biology Mr.D 90.1 72.0333
2 History Mrs.P 51.8 72.0333
3 Math Mrs.C 74.2 72.0333
2 B 1 Biology Mr.D 50.1 62.1500
3 Math Mrs.C 74.2 62.1500
3 C 1 Biology Mr.D 90.1 72.0333
2 History Mrs.P 51.8 72.0333
3 Math Mrs.C 74.2 72.0333

Type of DataFrame:

type(grouped)
pandas.core.frame.DataFrame

When I try to retrieve columns, It's only allow me to get two columns. I want all values with column names.

grouped.columns
Index(['weighted_score', 'total_average'], dtype='object')

I need output like this:

[{
  "students": [
    {
      "id": 1,
      "name": "A",
      "totalAverage": 72.03,
      "courses": [
        {
          "id": 1,
          "name": "Biology",
          "teacher": "Mr. D",
          "courseAverage": 90.1
        },
        {
          "id": 3,
          "name": "Math",
          "teacher": "Mrs. C",
          "courseAverage": 74.2
        },
        {
          "id": 2,
          "name": "History",
          "teacher": "Mrs. P",
          "courseAverage": 51.8
        }
      ]
    },
    {
      "id": 2,
      "name": "B",
      "totalAverage": 62.15,
      "courses": [
        {
          "id": 1,
          "name": "Biology",
          "teacher": "Mr. D",
          "courseAverage": 50.1
        },
        {
          "id": 3,
          "name": "Math",
          "teacher": "Mrs. C",
          "courseAverage": 74.2
        }
      ]
    },
    {
      "id": 3,
      "name": "C",
      "totalAverage": 72.03,
      "courses": [
        {
          "id": 1,
          "name": "Biology",
          "teacher": "Mr. D",
          "courseAverage": 90.1
        },
        {
          "id": 2,
          "name": "History",
          "teacher": "Mrs. P",
          "courseAverage": 51.8
        },
        {
          "id": 3,
          "name": "Math",
          "teacher": "Mrs. C",
          "courseAverage": 74.2
        }
      ]
    }
  ]
}]

Upvotes: 0

Views: 83

Answers (1)

Nk03
Nk03

Reputation: 14949

One way:

df1 = df.reset_index()
new_dict = (
    df1.set_index(['student_id', 'stuent_name', 'total_average'])
    .apply(dict, 1)
    .groupby(level=[0, 1, 2])
    .agg(list)
    .reset_index(name='courses')
    .to_dict('records')
)

OUTPUT:

[{'student_id': 1,
  'stuent_name': 'A',
  'total_average': 72.0333,
  'courses': [{'course_id': '1',
    'subject_name': 'Biology',
    'teacher': 'Mr.D',
    'weight_score': 90.1},
   {'course_id': '1',
    'subject_name': 'History',
    'teacher': 'Mrs.P',
    'weight_score': 51.8},
   {'course_id': '1',
    'subject_name': 'Math',
    'teacher': 'Mrs.C',
    'weight_score': 74.2}]},
 {'student_id': 2,
  'stuent_name': 'B',
  'total_average': 62.15,
  'courses': [{'course_id': '1',
    'subject_name': 'Biology',
    'teacher': 'Mr.D',
    'weight_score': 50.1},
   {'course_id': '1',
    'subject_name': 'Math',
    'teacher': 'Mrs.C',
    'weight_score': 74.2}]},
 {'student_id': 3,
  'stuent_name': 'C',
  'total_average': 72.0333,
  'courses': [{'course_id': '1',
    'subject_name': 'Biology',
    'teacher': 'Mr.D',
    'weight_score': 90.1},
   {'course_id': '1',
    'subject_name': 'History',
    'teacher': 'Mrs.P',
    'weight_score': 51.8},
   {'course_id': '1',
    'subject_name': 'Math',
    'teacher': 'Mrs.C',
    'weight_score': 74.2}]}]

Upvotes: 1

Related Questions