如何将这个JSON加载到数据框中？

Question

尝试在Python中读取以下JSON文件：

[[ {"fecha" : "2013-01-01","indicativo" : "3195","nombre" : "MADRID, RETIRO","provincia" : "MADRID","altitud" : "667","tmed" : "6,4","prec" :"0,0"},{"fecha" : "2013-01-02","indicativo" : "3196","nombre" : "MADRID,RETIRO","provincia" : "MADRID","altitud" : "667","tmed" : "6,5","prec" : "0,0"},{"fecha" : "2013-01-03","indicativo" : "3197","nombre": "MADRID, RETIRO","provincia" : "MADRID","altitud" : "667","tmed" : "6,7","prec" : "0,0"}],[ {"fecha" : "2013-02-01","indicativo" : "3195","nombre" : "MADRID, RETIRO","provincia" : "MADRID","altitud" : "667","tmed" : "6,4","prec" : "0,0"},{"fecha" : "2013-02-02","indicativo" :"3196","nombre" : "MADRID, RETIRO","provincia" : "MADRID","altitud" : "667","tmed" : "6,5","prec" : "0,0"}]]

我已尝试以下选项：

选项1：

df2 = pd.read_json('test.json',orient='split')

结果：

AttributeError: 'list' object has no attribute 'iteritems'

选项2：

df2 = pd.read_json('test.json',orient='columns')

结果：

我期望的结果是：

基本上是将数据框展平，列名等于属性，每个元素只有一行。

- Carlos

Answer 1

也许你可以尝试这段代码：

import pandas as pd
import json


file = './test.json'

with open(file, 'r') as f:
    data = json.load(f)


big_df = pd.DataFrame()

for i in range(len(data)):
    a = json.dumps(data[i])
    big_df = pd.concat([big_df, pd.read_json(a)], axis=0)

big_df.reset_index(drop=True)

注意：我已经在python3中测试了代码，但我认为它也应该在python2中工作。

- thanasissdr

Answer 2

pd.DataFrame构造函数可以接受一个字典列表作为输入，因此解决方案很简单：

import pandas as pd    
import json

file = 'test.json'
with open(file, 'r') as f:
    data = json.load(f)
rows = data[0]
df = pd.DataFrame(rows)

如果你想知道 pd.read_json() 输入的正确格式，可以看一下 df.to_json()。

- Viktoriya Malyasova

---

如果我执行：df6=pd.DataFrame(df2[0][0])，那么会出现错误：ValueError: If using all scalar values, you must pass an index。 - Carlos