将JSON数据提取到关系选项卡中

Question

我有一个来自YouTube的iframeapi的JSON文件，需要进行预处理。我想把这个JSON数据放到一个dataframe中，其中每个JSON键都是一列，每个记录的“事件”都应该是一个新行。你知道吗

我可以使用read\ujson将数据作为数据帧加载，但是使用这个方法，每个事件的键都显示为一个数组。你知道吗

以下是我的JSON数据：

{  
   "events":[  
      {  
         "timemillis":1563467463580,
         "date":"18.7.2019",
         "time":"18:31:03,580",
         "name":"Player is loading",
         "data":""
      },
      {  
         "timemillis":1563467463668,
         "date":"18.7.2019",
         "time":"18:31:03,668",
         "name":"Player is loaded",
         "data":"5"
      }
]
}

我就是这样把它转换成数据帧的：

data=pd.read_json("file.json")
df=pd.DataFrame(data)
print(df)

输出如下所示：

0  {'timemillis': 1563469276604, 'date': '18.7.20...
1  {'timemillis': 1563469276694, 'date': '18.7.20...
...

如何将这个输出转换成一个表，在这个表中，这些键有单独的列，比如“timemmillis”、“date”、“name”等等？我以前从未和JSONs合作过，所以我有点困惑。你知道吗

Answer 1

import pandas as pd
import json

data = {  
   "events":[  
      {  
         "timemillis":1563467463580,
         "date":"18.7.2019",
         "time":"18:31:03,580",
         "name":"Player is loading",
         "data":""
      },
      {  
         "timemillis":1563467463668,
         "date":"18.7.2019",
         "time":"18:31:03,668",
         "name":"Player is loaded",
         "data":"5"
      }
]
}

# or read data from file
# rather than reading file directly to pandas dataframe read as json
# data=pd.read_json("file.json")


with open('file.json') as json_file:
    data = json.load(json_file)
    df=pd.DataFrame(data['events'])
    print(df)

结果

data       date               name          time     timemillis
0       18.7.2019  Player is loading  18:31:03,580  1563467463580
1    5  18.7.2019   Player is loaded  18:31:03,668  1563467463668

Answer 2

import pandas as pd

df=pd.read_json("file.json",orient='columns')
rows = []
for i,r in df.iterrows():
    rows.append({'eventid':i+1,'timemillis':r['events']['timemillis'],'name':r['events']['name']})
df = pd.DataFrame(rows)
print(df)

现在您可以将这个df插入数据库