Problem
I was given a pretty big json file that looks like this minimal example:
json_file = """
{
"products":
[
{
"id":"0",
"name": "First",
"emptylist":[],
"properties" :
{
"id" : "",
"name" : ""
}
},
{
"id":"1",
"name": "Second",
"emptylist":[],
"properties":
{
"id" : "23",
"name" : "a useful product",
"features" :
[
{
"name":"Features",
"id":"18",
"features":
[
{
"id":"1001",
"name":"Colour",
"value":"Black"
},
{
"id":"2093",
"name":"Material",
"value":"Plastic"
}
]
},
{
"name":"Sizes",
"id":"34",
"features":
[
{
"id":"4736",
"name":"Length",
"value":"56"
},
{
"id":"8745",
"name":"Width",
"value":"76"
}
]
}
]
}
},
{
"id":"2",
"name": "Third",
"properties" :
{
"id" : "876",
"name" : "another one",
"features" :
[
{
"name":"Box",
"id":"937",
"features":
[
{
"id":"3758",
"name":"Amount",
"value":"1"
},
{
"id":"2222",
"name":"Packaging",
"value":"Blister"
}
]
},
{
"name":"Features",
"id":"8473",
"features":
[
{
"id":"9372",
"name":"Colour",
"value":"White"
},
{
"id":"9375",
"name":"Position",
"value":"A"
},
{
"id":"2654",
"name":"Amount",
"value":"6"
}
]
}
]
}
}
]
}
"""
And I want to make a flat table out of it. It should look like this one:
id name emptylist properties.id properties.name properties.features.name properties.features.id properties.features.features.id properties.features.features.name properties.features.features.value
0 First [] "" "" NaN NaN NaN NaN NaN
1 Second [] "23" "a useful product" Features 18 1001 Colour Black
1 Second [] "23" "a useful product" Features 18 2093 Material Plastic
1 Second [] "23" "a useful product" Sizes 34 4736 Length 56
1 Second [] "23" "a useful product" Sizes 34 8745 Width 76
2 Third "876" "another one" Box 937 3758 Amount 1
2 Third "876" "another one" Box 937 2222 Packaging Blister
2 Third "876" "another one" Features 8473 9372 Colour White
2 Third "876" "another one" Features 8473 9375 Position A
2 Third "876" "another one" Features 8473 2654 Amount 6
What I tried
I tried this:
import pandas as pd
import json
j = json.loads(json_file)
df = pd.json_normalize(j['products'])
df
id name emptylist properties.id properties.name properties.features
0 0 First [] NaN
1 1 Second [] 23 a useful product [{'name': 'Features', 'id': '18', 'features': ...
2 2 Third NaN 876 another one [{'name': 'Box', 'id': '937', 'features': [{'i...
And I tried to play a bit with the additional arguments, but I got nowhere. It seems like this is not the right way.
Can anyone help me?
Additional infos
I got a working solution with R, but I need to be able to do it with Python. If it helps, this would be the R code that I am trying to translate in Python.
library(tidyr)
jsonlite::fromJSON(json_file)$products %>%
jsonlite::flatten() %>%
unnest(properties.features , names_sep = ".", keep_empty = TRUE) %>%
unnest(properties.features.features, names_sep = ".", keep_empty = TRUE)
EDIT
With the help of @piterbarg and some research I got to this solution:
j = json.loads(json_file)
df = pd.json_normalize(j['products'])
df1 = df.explode('properties.features')
df2 = pd.concat([df1.reset_index(drop=True).drop('properties.features', axis = 1),
df1['properties.features'].apply(pd.Series).reset_index(drop=True).add_prefix("properties.features.").drop("properties.features.0", axis = 1)], axis = 1)
df2 = df2.explode('properties.features.features')
df3 = pd.concat([df2.reset_index(drop=True).drop('properties.features.features', axis = 1),
df2['properties.features.features'].apply(pd.Series).reset_index(drop=True).add_prefix("properties.features.features.").drop("properties.features.features.0", axis = 1)], axis = 1)
df3
With this I get exactly the solution I'm looking for but the code looks pretty messy and I'm not sure how efficient this solution may be. Any help?
It is similar to what you have in Edit, but perhaps slightly shorter syntax and more performant.
If you have NaN in the DataFrame, older version of Pandas could fail on
json_normalize.This solution should work with Pandas 1.3+.
Perf. with 1000 products.