将多个JSON记录读取到Pandas数据框中 [英] Reading multiple JSON records into a Pandas dataframe

问题描述

我想知道是否存在一种内存有效的方式来将多记录JSON文件(每行是一个JSON dict)读入pandas数据帧.以下是带有工作解决方案的2行示例，我可能需要非常多的记录.使用示例将处理Hadoop Pig JSonStorage函数的输出.

I'd like to know if there is a memory efficient way of reading multi record JSON file ( each line is a JSON dict) into a pandas dataframe. Below is a 2 line example with working solution, I need it for potentially very large number of records. Example use would be to process output from Hadoop Pig JSonStorage function.

import json
import pandas as pd

test='''{"a":1,"b":2}
{"a":3,"b":4}'''
#df=pd.read_json(test,orient='records') doesn't work, expects []

l=[ json.loads(l) for l in test.splitlines()]
df=pd.DataFrame(l)

推荐答案

注意:

Note: Line separated json is now supported in read_json (since 0.19.0):

In [31]: pd.read_json('{"a":1,"b":2}\n{"a":3,"b":4}', lines=True)
Out[31]:
   a  b
0  1  2
1  3  4

或使用文件/文件路径而不是json字符串:

or with a file/filepath rather than a json string:

pd.read_json(json_file, lines=True)

---

这取决于您DataFrames的大小，该大小会更快，但是另一种选择是使用str.join将多行"JSON"(注意:它不是有效的json)粉碎成有效的json并使用read_json:

---

It's going to depend on the size of you DataFrames which is faster, but another option is to use str.join to smash your multi line "JSON" (Note: it's not valid json), into valid json and use read_json:

In [11]: '[%s]' % ','.join(test.splitlines())
Out[11]: '[{"a":1,"b":2},{"a":3,"b":4}]'

对于这个小例子，它的运行速度较慢，如果大约为100，则该速度类似；如果更大，则显着增加...

For this tiny example this is slower, if around 100 it's the similar, signicant gains if it's larger...

In [21]: %timeit pd.read_json('[%s]' % ','.join(test.splitlines()))
1000 loops, best of 3: 977 µs per loop

In [22]: %timeit l=[ json.loads(l) for l in test.splitlines()]; df = pd.DataFrame(l)
1000 loops, best of 3: 282 µs per loop

In [23]: test_100 = '\n'.join([test] * 100)

In [24]: %timeit pd.read_json('[%s]' % ','.join(test_100.splitlines()))
1000 loops, best of 3: 1.25 ms per loop

In [25]: %timeit l = [json.loads(l) for l in test_100.splitlines()]; df = pd.DataFrame(l)
1000 loops, best of 3: 1.25 ms per loop

In [26]: test_1000 = '\n'.join([test] * 1000)

In [27]: %timeit l = [json.loads(l) for l in test_1000.splitlines()]; df = pd.DataFrame(l)
100 loops, best of 3: 9.78 ms per loop

In [28]: %timeit pd.read_json('[%s]' % ','.join(test_1000.splitlines()))
100 loops, best of 3: 3.36 ms per loop

注意:那时联接速度非常快.

这篇关于将多个JSON记录读取到Pandas数据框中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！