How to normalize a json file when using streamlit file_uploader?

I am trying to load a json file in my streamlit app and parse it into a pandas dataframe.
This is the json file:

{
"school_name": "local primary school",
"class": "Year 1",
"info": {
  "president": "John Kasich",
  "address": "ABC road, London, UK",
  "contacts": {
    "email": "admin@e.com",
    "tel": "123456789"
  }
},
"students": [
{
    "id": "A001",
    "name": "Tom",
    "math": 60,
    "physics": 66,
    "chemistry": 61
},
{
    "id": "A002",
    "name": "James",
    "math": 89,
    "physics": 76,
    "chemistry": 51
},
{
    "id": "A003",
    "name": "Jenny",
    "math": 79,
    "physics": 90,
    "chemistry": 78
}]
}

I am using json_normalize but I get the following error “TypeError: string indices must be integers”

def show_students(file):
    json=pd.read_json(file)
    df=pd.json_normalize(
        json,
        record_path =['students'], 
        meta=[
            'class',
            ['info', 'president'], 
            ['info', 'contacts', 'tel']
        ]
    )
    st.write(df)

uploaded_file = st.file_uploader("Choose a file", type=['json'])
    if uploaded_file is not None:
    show_students(uploaded_file)

Any idea how I could solve this? Thanks!

Hi @CDV welcome to Streamlit!

I tried it in my local system but I was greeted with:

Traceback (most recent call last):
  File "/home/jovial/normalize.py", line 6, in <module>
    json = pd.read_json(jf)

...
raise ValueError(
ValueError: Mixing dicts with non-Series may lead to ambiguous ordering.

So Instead of pd.read_json() I used json.load() and it works.

import json

with open("x.json", "r") as jf:
    json_file = json.load(jf)
    # json = pd.read_json(jf) # errors!
    df = pd.json_normalize(json_file, meta=[
        'class',
        ['info', 'president'],
        ['info', 'contacts', 'tel']
    ])
    print(df)


""" Output
          school_name   class                                           students  ...          info.address info.contacts.email info.contacts.tel
0  local primary school  Year 1  [{'id': 'A001', 'name': 'Tom', 'math': 60, 'ph...  ...  ABC road, London, UK         admin@e.com         123456789

[1 rows x 7 columns]

"""

I’m sorry I’m not sure whey pandas throws an error, but it’s not related to Streamlit :grinning:

Happy Coding!

2 Likes

Hey @CDV,

Again, welcome to the streamlit community! :star2: :partying_face: :tada:

if this solution worked for you please let us know and mark it as the solution for @joe733 and anyone who visits your question in the future! :rainbow:

Happy Streamlit-ing!
Marisa

2 Likes

This works perfectly, thank you very much for this solution!

1 Like