How to handle .json fine in tabular form in python?-CodePudding

By using this code:

import pandas as pd
patients_df = pd.read_json('/content/students.json',lines=True)
patients_df.head()

the data are shown in tabular form look like this:

The main json file looks like this:

data = []
for line in open('/content/students.json', 'r'):
    data.append(json.loads(line))

How can I get the score column of the table in an organized manner like column name Exam, Quiz, and Homework

CodePudding user response：

Possible solution could be the following:

import pandas as pd
import json


def separate_column(row):
    for e in row["scores"]:
        row[e["type"]] = e["score"]
    return row


with open('/content/students.json', 'r') as file:
    data = [json.loads(line.rstrip()) for line in file]

df = pd.json_normalize(data)
df = df.apply(separate_column, axis=1)
df = df.drop(['scores'], axis=1)

print(df)