如何在Python中提取最低级别的嵌套JSON到DataFrame ?

Question

我希望只提取Python中JSON的最低级别对象。例如，来自API的前几条记录看起来像这样:

{
"season": 2021,
"charts": {
"ARI": {
"TE": [
{
"team": "ARI",
"position": "TE",
"depth": "1",
"playerId": "1443",
"name": "Dan Arnold"
},
{
"team": "ARI",
"position": "TE",
"depth": "2",
"playerId": "599",
"name": "Maxx Williams"
}
],
"K": [
{
"team": "ARI",
"position": "K",
"depth": "1",
"playerId": "1121",
"name": "Zane Gonzalez"
},
{

我最终希望将所有这些结果放入具有以下结构的DataFrame中:

| team | position | depth | playerId | name |
|:---- |:-------- |:----- |:-------- |:---- |

我尝试了以下代码的变体，但没有成功:

import requests as rq
import pandas as pd
# Retrieve Depth Charts
json_depthCharts = rq.get(f"https://api.fantasynerds.com/v1/nfl/depth?apikey={API_KEY}").json()
df_depthCharts = pd.json_normalize(json_depthCharts, 'charts', ['charts', 'team'])
print(df_depthCharts)

任何见解都是赞赏的!

Answer 1

尝试json_normalize()+melt()+explode()+Dataframe():

df=pd.DataFrame(pd.json_normalize(json_depthCharts).melt('season')['value'].explode().tolist())

或

通过stack()+drop()组合代替melt()，其余所有方法保持不变:

df=pd.DataFrame(pd.json_normalize(json_depthCharts).drop(columns='season').stack().explode().tolist())

df

输出:

team    position    depth   playerId    name
0       ARI     TE          1       1443        Dan Arnold
1       ARI     TE          2       599         Maxx Williams
2       ARI     K           1       1121        Zane Gonzalez
3       ARI     K           2       1454        Brett Maher
4       ARI     LWR         1       338         DeAndre Hopkins
...     ...     ...        ...      ...         ...
932     WAS     LDE         2       179         Ryan Kerrigan
933     WAS     RCB         1       647         Ronald Darby
934     WAS     RB          1       1957        Antonio Gibson
935     WAS     RB          2       1542        Bryce Love
936     WAS     NB          1       1733        Jimmy Moreland
937 rows × 5 columns