pythonjsonpandasdataframe

Can I turn a Pandas Dataframe column containing a jJSON string into additional columns and rows?


I've run into a strange issue with an API I am calling. I'm getting my data as a JSON with a string containing a JSON inside of it. Instead of being nested, for some reason it comes across as a string field inside the JSON. I've gotten to a point where I can change this original JSON into a Pandas Dataframe that can be seen below, but I need to eventually drop this into a table in a SQL database, but the JSON I am working with is significantly longer than the sample I am working with here, and as a result, the JSON is being cut off due to the maximum size of a varchar I can work with in SQL.

Basically, is there any way for me to work with this dataframe in Python to translate the JSON into additional columns and rows on the dataframe?

This is the Dataframe I am currently working with:

Department Value json
1 A [{"employeeID":123,"name":"Jenny"}, {"employeeID":456,"name":"Mike"}, {"employeeID":789,"name":"Ricky"}]
2 B [{"employeeID":735,"name":"Todd", "badgeNo":84639}, {"employeeID":223,"name":"Greg", "badgeNo":93649}]
3 C []
4 D [{"employeeID":947,"name":"Cardi"}, {"employeeID":284,"name":"Tom"}]

I am trying to get my Dataframe to look like this

Department Value employeeID name badgeNo
1 A 123 Jenny
1 A 456 Mike
1 A 789 Ricky
2 B 735 Todd 84639
2 B 223 Greg 93649
3 C
4 D 947 Cardi
4 D 284 Tom

Any and all help is appreciated


Solution

  • If the values in column 'json' are strings, first convert them to JSON with json.loads.

    Then you can explode the values into new columns for each JSON object, and use json_normalize to convert them into dataframes.

    Finally, concat (on axis=1) the original dataframe minus column 'json' + the newly created dataframe.

    df["json"] = df["json"].apply(json.loads)
    df = df.explode("json").reset_index(drop=True)
    out = pd.concat([df.drop(columns="json"), pd.json_normalize(df["json"])], axis=1)
    
       Department Value  employeeID   name  badgeNo
    0           1     A       123.0  Jenny      NaN
    1           1     A       456.0   Mike      NaN
    2           1     A       789.0  Ricky      NaN
    3           2     B       735.0   Todd  84639.0
    4           2     B       223.0   Greg  93649.0
    5           3     C         NaN    NaN      NaN
    6           4     D       947.0  Cardi      NaN
    7           4     D       284.0    Tom      NaN