Python Json to dataframe is not importing all columns

user_10652213 0
Viewed 31

I'm trying to put a json file into a dataframe using this script

import pandas as pd

json = 'sof.json'

df = pd.read_json(json)

print(df)

but it's loading to the dataframe only 3 columns I think maybe because the information in the column 'config' is a nested json.

  utcTime                                             config  sourceID
0  1661397791  {'toothSettings': [{'length': 0.52312962962962...       253
1  1661958325  {'toothSettings': [{'length': 0.52312962962962...       275
2  1661952893  {'toothSettings': [{'length': 0.50300925925925...       276
3  1661956026  {'toothSettings': [{'length': 0.50300925925925...       254

This is the JSON file

[
    {
        "utcTime": 1661397791,
        "config": {
            "toothSettings": [
                {
                    "length": 0.5231296296296296,
                    "lengthAlgo": 0.5231296296296296,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 1,
                    "lowConfidence": 1
                },
                {
                    "length": 0.512824074074074,
                    "lengthAlgo": 0.512824074074074,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 2,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5231296296296296,
                    "lengthAlgo": 0.5231296296296296,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 3,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5129222222222222,
                    "lengthAlgo": 0.5129222222222222,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 4,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5256814814814815,
                    "lengthAlgo": 0.5256814814814815,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 5,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5205777777777777,
                    "lengthAlgo": 0.5205777777777777,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 6,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5180259259259259,
                    "lengthAlgo": 0.5180259259259259,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 7,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5205777777777777,
                    "lengthAlgo": 0.5205777777777777,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 8,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5205777777777777,
                    "lengthAlgo": 0.5205777777777777,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 9,
                    "lowConfidence": 1
                }
            ]
        },
        "sourceID": 253
    },
    {
        "utcTime": 1661958325,
        "config": {
            "toothSettings": [
                {
                    "length": 0.5231296296296296,
                    "lengthAlgo": 0.5231296296296296,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 1,
                    "lowConfidence": 1
                },
                {
                    "length": 0.512824074074074,
                    "lengthAlgo": 0.512824074074074,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 2,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5030092592592592,
                    "lengthAlgo": 0.5030092592592592,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 3,
                    "lowConfidence": 1
                },
                {
                    "length": 0.4931944444444444,
                    "lengthAlgo": 0.4931944444444444,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 4,
                    "lowConfidence": 1
                },
                {
                    "length": 0.4852444444444445,
                    "lengthAlgo": 0.4852444444444445,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 5,
                    "lowConfidence": 1
                },
                {
                    "length": 0.48053333333333326,
                    "lengthAlgo": 0.48053333333333326,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 6,
                    "lowConfidence": 1
                },
                {
                    "length": 0.4781777777777777,
                    "lengthAlgo": 0.4781777777777777,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 7,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5005555555555555,
                    "lengthAlgo": 0.5005555555555555,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 8,
                    "lowConfidence": 1
                },
                {
                    "length": 0.48053333333333326,
                    "lengthAlgo": 0.48053333333333326,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 9,
                    "lowConfidence": 1
                }
            ]
        },
        "sourceID": 275
    },
    {
        "utcTime": 1661952893,
        "config": {
            "toothSettings": [
                {
                    "length": 0.5030092592592592,
                    "lengthAlgo": 0.5030092592592592,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 1,
                    "lowConfidence": 1
                },
                {
                    "length": 0.49231111111111103,
                    "lengthAlgo": 0.49231111111111103,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 2,
                    "lowConfidence": 1
                },
                {
                    "length": 0.46276851851851847,
                    "lengthAlgo": 0.46276851851851847,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 3,
                    "lowConfidence": 1
                },
                {
                    "length": 0.47346666666666665,
                    "lengthAlgo": 0.47346666666666665,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 4,
                    "lowConfidence": 1
                },
                {
                    "length": 0.4852444444444445,
                    "lengthAlgo": 0.4852444444444445,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 5,
                    "lowConfidence": 1
                },
                {
                    "length": 0.48053333333333326,
                    "lengthAlgo": 0.48053333333333326,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 6,
                    "lowConfidence": 1
                },
                {
                    "length": 0.49810185185185174,
                    "lengthAlgo": 0.49810185185185174,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 7,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5005555555555555,
                    "lengthAlgo": 0.5005555555555555,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 8,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5005555555555555,
                    "lengthAlgo": 0.5005555555555555,
                    "maxLength": 0.52,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 9,
                    "lowConfidence": 1
                }
            ]
        },
        "sourceID": 276
    },
    {
        "utcTime": 1661956026,
        "config": {
            "toothSettings": [
                {
                    "length": 0.5030092592592592,
                    "lengthAlgo": 0.5030092592592592,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 1,
                    "lowConfidence": 1
                },
                {
                    "length": 0.49231111111111103,
                    "lengthAlgo": 0.49231111111111103,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 2,
                    "lowConfidence": 1
                },
                {
                    "length": 0.48288888888888887,
                    "lengthAlgo": 0.48288888888888887,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 3,
                    "lowConfidence": 1
                },
                {
                    "length": 0.47346666666666665,
                    "lengthAlgo": 0.47346666666666665,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 4,
                    "lowConfidence": 1
                },
                {
                    "length": 0.4852444444444445,
                    "lengthAlgo": 0.4852444444444445,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 5,
                    "lowConfidence": 1
                },
                {
                    "length": 0.48053333333333326,
                    "lengthAlgo": 0.48053333333333326,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 6,
                    "lowConfidence": 1
                },
                {
                    "length": 0.4781777777777777,
                    "lengthAlgo": 0.4781777777777777,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 7,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5005555555555555,
                    "lengthAlgo": 0.5005555555555555,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 8,
                    "lowConfidence": 1
                },
                {
                    "length": 0.5205777777777777,
                    "lengthAlgo": 0.5205777777777777,
                    "maxLength": 0.615,
                    "minLength": 0.4,
                    "offset": 0,
                    "status": 0,
                    "toothNum": 9,
                    "lowConfidence": 1
                }
            ]
        },
        "sourceID": 254
    }
]

Do you know how can I have all the information contained in the column 'config' in separate columns?

Thank you.

dataframe python json
1 Answers

Try:

df = pd.read_json("data.json")
df = pd.concat([df, df.pop("config").apply(pd.Series)], axis=1)
df = df.explode("toothSettings")
df = pd.concat([df, df.pop("toothSettings").apply(pd.Series)], axis=1)
df["utcTime"] = pd.to_datetime(df["utcTime"], unit="s")
print(df.head(10).to_markdown())

Prints:

utcTime sourceID length lengthAlgo maxLength minLength offset status toothNum lowConfidence
0 2022-08-25 03:23:11 253 0.52313 0.52313 0.52 0.4 0 0 1 1
0 2022-08-25 03:23:11 253 0.512824 0.512824 0.52 0.4 0 0 2 1
0 2022-08-25 03:23:11 253 0.52313 0.52313 0.52 0.4 0 0 3 1
0 2022-08-25 03:23:11 253 0.512922 0.512922 0.52 0.4 0 0 4 1
0 2022-08-25 03:23:11 253 0.525681 0.525681 0.52 0.4 0 0 5 1
0 2022-08-25 03:23:11 253 0.520578 0.520578 0.52 0.4 0 0 6 1
0 2022-08-25 03:23:11 253 0.518026 0.518026 0.52 0.4 0 0 7 1
0 2022-08-25 03:23:11 253 0.520578 0.520578 0.52 0.4 0 0 8 1
0 2022-08-25 03:23:11 253 0.520578 0.520578 0.52 0.4 0 0 9 1
1 2022-08-31 15:05:25 275 0.52313 0.52313 0.52 0.4 0 0 1 1
user_100359850
Related