Reading Nested JSON into Pandas DataFrame

Question

How can I read a nested JSON file into a Pandas DataFrame? Consider for example, the following file:

{
    "quiz": {
        "sport": {
            "q1": {
                "question": "Which one is correct team name in NBA?",
                "options": [
                    "New York Bulls",
                    "Los Angeles Kings",
                    "Golden State Warriros",
                    "Huston Rocket"
                ],
                "answer": "Huston Rocket"
            }
        },
        "maths": {
            "q1": {
                "question": "5 + 7 = ?",
                "options": [
                    "10",
                    "11",
                    "12",
                    "13"
                ],
                "answer": "12"
            },
            "q2": {
                "question": "12 - 8 = ?",
                "options": [
                    "1",
                    "2",
                    "3",
                    "4"
                ],
                "answer": "4"
            }
        }
    }
}

I have tried for example:

data = json.load(open(json_file))

df = json_normalize(data['quiz'], 'sport', 'maths')

However, that returns:

Is there a more elegant solution?

Answer 1

Check out this thread (The accepted answer).

'Expected String or Unicode' when reading JSON with Pandas

I think the their code is pretty clean.

Us in each tuple mean that you have Unicode. Generally, a str is text representation in bytes, while a unicode is text representation in characters.

The accepted answer maps all data to their real types and not to their unicodes.

Hope it Helps