I have a json file with this structure:
[
{
"name": "myName",
"type": {
"x": {
"id": [
"x1",
"x2"
]
},
"y": {
"id": "y1"
},
"z": {
"id": "z1"
}
}
}
]
Type
can only take values x, y, and z. Any of those can be missing, but there is always at least one x, y, or z. There is no limit to the number of id’s for each type, although there are never more than ten id-values in the data.
I would like to transform that into a Pandas dataframe, with structure:
Name | type | id |
---|---|---|
myName | x | x1 |
myName | x | x2 |
myName | y | y1 |
myName | z | z1 |
I’ve experimented with to_json{orient="..."}
and json_normalize()
, but I’m not able to flatten the lists in the json.
2
Answers
You should first create the rows, than convert those into DF:
Result:
Assuming
lst
the input (as python object) and using a nested list comprehension:Output:
Which can be simplified using
explode
:Output:
Used input: