I have json data that is structured like this, which I want to turn into a data frame:
{
"data": {
"1": {
"Conversion": {
"id": "1",
"datetime": "2024-03-26 08:30:00"
}
},
"50": {
"Conversion": {
"id": "50",
"datetime": "2024-03-27 09:00:00"
}
}
}
}
My usual approach would be to use json_normalize, like this:
df = pd.json_normalize(input['data'])
My goal is to have a table/dataframe with just the columns "id" and "datetime".
How do I skip the numbering level below data and go straight to Conversion? I would imagine something like this (which clearly doesn’t work):
df = pd.json_normalize(input['data'][*]['Conversion'])
What is the best way to achieve this? Any hints are greatly appreciated!
>Solution :
You have to manually change data in double list comprehension:
L = [b['Conversion'] for k, v in input['data'].items() for a, b in v.items()]
print (L)
[{'id': '1', 'datetime': '2024-03-26 08:30:00'},
{'id': '50', 'datetime': '2024-03-27 09:00:00'}]
out = pd.json_normalize(L)
print (out)
id datetime
0 1 2024-03-26 08:30:00
1 50 2024-03-27 09:00:00
Here is json_normalize not necessary, working DataFrame constructor:
out = pd.DataFrame(L)
print (out)
id datetime
0 1 2024-03-26 08:30:00
1 50 2024-03-27 09:00:00
Thank you chepner for another idea with .values:
out = pd.json_normalize((b['Conversion'] for v in input['data'].values()
for b in v.values()))
print (out)
id datetime
0 1 2024-03-26 08:30:00
1 50 2024-03-27 09:00:00
out = pd.DataFrame((b['Conversion'] for v in input['data'].values()
for b in v.values()))
print (out)
id datetime
0 1 2024-03-26 08:30:00
1 50 2024-03-27 09:00:00
In json_normalize is parameter max_level, but working different:
Max number of levels(depth of dict) to normalize. if None, normalizes all levels.
out = pd.json_normalize(input['data'], max_level=1)
print (out)
data.1 \
0 {'Conversion': {'id': '1', 'datetime': '2024-0...
data.50
0 {'Conversion': {'id': '50', 'datetime': '2024-...
out = pd.json_normalize(input['data'], max_level=2)
print (out)
data.1.Conversion \
0 {'id': '1', 'datetime': '2024-03-26 08:30:00'}
data.50.Conversion
0 {'id': '50', 'datetime': '2024-03-27 09:00:00'}
out = pd.json_normalize(input['data'], max_level=3)
print (out)
data.1.Conversion.id data.1.Conversion.datetime data.50.Conversion.id \
0 1 2024-03-26 08:30:00 50
data.50.Conversion.datetime
0 2024-03-27 09:00:00