提取作为字符串嵌入 Pandas 数据帧中的嵌套 JSON

我有一个 CSV，其中一个字段是嵌套的 JSON 对象，存储为字符串。我想将 CSV 加载到数据框中，并将 JSON 解析为附加到原始数据框的一组字段；换句话说，提取 JSON 的内容并使它们成为数据帧的一部分。

My CSV:

id|dist|json_request
1|67|{"loc":{"lat":45.7, "lon":38.9},"arrival": "Monday", "characteristics":{"body":{"color":"red", "make":"sedan"}, "manuf_year":2014}}
2|34|{"loc":{"lat":46.89, "lon":36.7},"arrival": "Tuesday", "characteristics":{"body":{"color":"blue", "make":"sedan"}, "manuf_year":2014}}
3|98|{"loc":{"lat":45.70, "lon":31.0}, "characteristics":{"body":{"color":"yellow"}, "manuf_year":2010}}

请注意，并非所有行的所有键都相同。 我希望它生成一个与此等效的数据框：

data = {'id'     : [1, 2, 3],
        'dist'  : [67, 34, 98],
        'loc_lat': [45.7, 46.89, 45.70],
        'loc_lon': [38.9, 36.7, 31.0],
        'arrival': ["Monday", "Tuesday", "NA"],
        'characteristics_body_color':["red", "blue", "yellow"],
        'characteristics_body_make':["sedan", "sedan", "NA"],
        'characteristics_manuf_year':[2014, 2014, 2010]}
df = pd.DataFrame(data)

（我真的很抱歉，我无法让表格本身看起来很合理！请不要生我的气，我是一个菜鸟:(）

我尝试过的

经过一番折腾后，我想出了以下解决方案：

#Import data
df_raw = pd.read_csv("sample.csv", delimiter="|")

#Parsing function
def parse_request(s):
    sj = json.loads(s)
    norm = json_normalize(sj)
    return norm

#Create an empty dataframe to store results
parsed = pd.DataFrame(columns=['id'])

#Loop through and parse JSON in each row
for i in df_raw.json_request:
    parsed = parsed.append(parse_request(i))

#Merge results back onto original dataframe
df_parsed = df_raw.join(parsed)

这显然不优雅而且效率很低（我必须解析 300K 行需要几个小时）。有没有更好的办法？

我看过的地方

我已经解决了以下相关问题：将 CSV 读入 pandas，其中一列是 json 字符串 https://stackoverflow.com/questions/20680272/reading-a-csv-into-pandas-where-one-column-is-a-json-string（这似乎只适用于简单的非嵌套 JSON）

JSON 到 pandas DataFrame https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe（我从中借用了部分解决方案，但我无法弄清楚如何在数据帧中应用此解决方案而不循环遍历行）

我正在使用 Python 3.3 和 Pandas 0.17。

这是一种可以将速度提高 10 到 100 倍的方法，并且可以让您在一分钟内读取大文件，而不是一个多小时。这个想法是只有在读取完所有数据后才构造一个数据帧，从而减少需要分配内存的次数，并且只调用json_normalize对整个数据块而不是每行执行一次：

import csv
import json

import pandas as pd
from pandas.io.json import json_normalize

with open('sample.csv') as fh:
    rows = csv.reader(fh, delimiter='|')
    header = next(rows)

    # "transpose" the data. `data` is now a tuple of strings
    # containing JSON, one for each row
    idents, dists, data = zip(*rows)

data = [json.loads(row) for row in data]
df = json_normalize(data)
df['ids'] = idents
df['dists'] = dists

So that:

>>> print(df)

   arrival characteristics.body.color characteristics.body.make  \
0   Monday                        red                     sedan   
1  Tuesday                       blue                     sedan   
2      NaN                     yellow                       NaN   

   characteristics.manuf_year  loc.lat  loc.lon ids  
0                        2014    45.70     38.9   1  
1                        2014    46.89     36.7   2  
2                        2010    45.70     31.0   3

此外，我还研究了哪些pandas's json_normalize正在执行，并且它正在执行一些深度复制，如果您只是从 CSV 创建数据帧，则不需要这些深度复制。我们可以自己实现flatten函数接受字典并“展平”键，类似于json_normalize做。然后我们可以制作一个生成器，它一次输出数据帧的一行作为记录。这种方法甚至更快：

def flatten(dct, separator='_'):
    """A fast way to flatten a dictionary,"""
    res = {}
    queue = [('', dct)]

    while queue:
        prefix, d = queue.pop()
        for k, v in d.items():
            key = prefix + k
            if not isinstance(v, dict):
                res[key] = v
            else:
                queue.append((key + separator, v))

    return res

def records_from_json(fh):
    """Yields the records from a file object."""
    rows = csv.reader(fh, delimiter='|')
    header = next(rows)
    for ident, dist, data in rows:
        rec = flatten(json.loads(data))
        rec['id'] = ident
        rec['dist'] = dist
        yield rec

def from_records(path):
    with open(path) as fh:
        return pd.DataFrame.from_records(records_from_json(fh))

这是计时实验的结果，我通过重复行人为地增加了样本数据的大小。行数表示为n_rows:

        method 1 (s)  method 2 (s)  original time (s)
n_rows                                               
96          0.008217      0.002971           0.362257
192         0.014484      0.004720           0.678590
384         0.027308      0.008720           1.373918
768         0.055644      0.016175           2.791400
1536        0.105730      0.030914           5.727828
3072        0.209049      0.060105          11.877403

线性推断，第一种方法应该在大约 20 秒内读取 300k 行，而第二种方法应该需要大约 6 秒。