python - 使用返回 KeyError 的 Pandas 扁平化 JSON

Question

我有一个几乎采用正确 JSON 格式的文本文件，我正在尝试使用 Pandas 来压平它。我无法解析一个嵌套对象。这是我正在使用的最新版本:

    import pandas as pd
    import json
    import os

    def load_data():

        with open('Downloads/insert.json','r') as theFile:    
            data = theFile.readlines()
            data = map(lambda x: x.rstrip(), data)
            data_jsons = "[" + ','.join(data) + "]"
            jsons = json.loads(data_jsons)
            #json.dumps('Downloads/insert_df.json')
            df = pd.read_json(data_jsons)
            df.to_json('Downloads/insert_df.json')
            '''df = pd.concat(
            [
                pd.concat([pd.Series(m) for m in t[3]], axis=1) for t in df[0]
            ], keys=[t[3] for t in data[0]])'''
            df_table = pd.io.json.json_normalize(df)
            print('Done')

JSON 看起来像这样:

{"id":"011cb1f2-a54b-4f1c-8ded-30c57912d240","impressionId":"izef8qlp0100gs41000DAkdno0udt","publisher":"ed0a68ac761a862d73564f9f09c4977f8e997cc8","matches":[{"phrase":"microsoft word","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/85b953ca0e271f56adbc34d71cedc7085ec45fe5"}]}
{"id":"014f2a4f-67a0-4d69-bf24-cd70b5363663","impressionId":"izefao700100mm4z000MA14wn5xth84ti9","publisher":"3ef8b9758830155061b2c8fc9fbd703502f3cbd7","matches":[{"phrase":"peugeot 307","productUrl":"http://7acb261fef948b59ffde0b870799932090f6123f.com/4acd8769b7e61a40a8833b88a121ae028cc26bf4"},{"phrase":"peugeot 308","productUrl":"http://7acb261fef948b59ffde0b870799932090f6123f.com/35ba8a025c827b53a6314d536922ae50b1688c8a"}]}
{"id":"017f535a-a2d9-4a60-9998-a3a8e100ff57","impressionId":"izeg3g6e01000axn000DAcshm0rzl","publisher":"48eb83dc3dbab5a49427ed68fe416b9722b94c53","matches":[{"phrase":"mini cooper","productUrl":"http://04e8a39eadf614f5f705fa490c51db0053501fa0.com/d5ef197198cc9195a626b189f8317a790d706ffc"},{"phrase":"vin decoder","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/b6651645376fb3bbcff1e7716bdd0db4f3d4f919"},{"phrase":"automotive parts & accessories","productUrl":"https://7acb261fef948b59ffde0b870799932090f6123f.com/0056e723e5b4836de6d60651511dd3ad3ea8d322"},{"phrase":"macan","productUrl":"http://04e8a39eadf614f5f705fa490c51db0053501fa0.com/0cc2b4e74b225624643ca8087008646d2ea3c134"},{"phrase":"turbo & s","productUrl":"http://04e8a39eadf614f5f705fa490c51db0053501fa0.com/29e74a2f933533f6b6c1a220832c85772413962f"},{"phrase":"aston martin db7","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/5f7aa33fc7b5a07cc0c4c6e85f42b0590dcf18c0"},{"phrase":"996tt","productUrl":"http://1755111130359fbf943a9d396376adb44df537e7.com/9abf608bae5ec830cfa9e5fc34b6e4d1ef34254b"},{"phrase":"boxster","productUrl":"http://04e8a39eadf614f5f705fa490c51db0053501fa0.com/330df71b7a7cf69141c445ca4c23fabab658c36f"},{"phrase":"nissan gtr","productUrl":"http://1755111130359fbf943a9d396376adb44df537e7.com/75bcf958a01efaeec7af3efc8b322a802521ab0f"},{"phrase":"vantage v8","productUrl":"http://1755111130359fbf943a9d396376adb44df537e7.com/26e1c4928bcb789b0cd43173391e1ab09a81c390"}]}
{"id":"01b0b630-94f3-4cdc-a540-11e3b8ce01cd","impressionId":"izefxzlm01000d5p000DAdmgu2ggl","publisher":"f4c6b5a40762849ac5e90d417222a732e08a1f3d","matches":[{"phrase":"classic bike stuff","productUrl":"http://7acb261fef948b59ffde0b870799932090f6123f.com/9b2e17ff5be3aec0405097f8400b30fa588007ed"},{"phrase":"classic bikes","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/c374f39951871413cb1f57e5f1a8b302017d149a"}]}
{"id":"030957cf-3dba-41a7-a61d-92118a75fc8f","impressionId":"izefc4jp01001s0n000DLjvhikuvx","publisher":"3160678e5920effce2440b741e4a10ad46d82bd7","matches":[{"phrase":"onboard supply control unit","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/0e20204b482f0cb3609709024cba60d8238dce26"},{"phrase":"electric fuel pump","productUrl":"http://1755111130359fbf943a9d396376adb44df537e7.com/00e6d3e4441019cb26d94509bd51f8c0fb8f93ac"},{"phrase":"blower relay","productUrl":"http://1755111130359fbf943a9d396376adb44df537e7.com/ea69de9738b8fcb1031dcf136110f708294175b4"},{"phrase":"fuel pump relay","productUrl":"http://3d270cf415da0548cec2e8f86eb08fd3b9a48ca3.com/a2782f9de20f82bf68aca409d09a61ef2bafc287"},{"phrase":"electric fuel pump 2","productUrl":"http://04e8a39eadf614f5f705fa490c51db0053501fa0.com/068a946b4689e99308a4b2683fe559b4400c5912"},{"phrase":"pump relay 2","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/9f70a2a01320e9aa8fc26bd1385c0719f6173ae0"},{"phrase":"fuel pump 2","productUrl":"http://04e8a39eadf614f5f705fa490c51db0053501fa0.com/7fe7467d8a46c23d58687be34101620a7dc85cdd"},{"phrase":"fresh air blower","productUrl":"https://7acb261fef948b59ffde0b870799932090f6123f.com/ebdfd2915c9907b65896df31940e590bf8548a0a"},{"phrase":"auxiliary heater","productUrl":"http://1755111130359fbf943a9d396376adb44df537e7.com/e3c7faef1f03436056169c3d677cc2a95b11930f"},{"phrase":"equipment level","productUrl":"http://7acb261fef948b59ffde0b870799932090f6123f.com/be65bfac3bc8a6f12efb8e9ecbb2519d1847cdf7"},{"phrase":"single fuse","productUrl":"http://7acb261fef948b59ffde0b870799932090f6123f.com/ce090b8f3d9ab60662b52324aa71d42fac86f0a8"}]}
{"id":"0397b15a-ca3a-46cc-8eca-de8bcf0b30a6","impressionId":"izeglsbt0100s3dy000DAppubjs73","publisher":"56b045f6c605ecfca8fb61c4c018424c84ce37b1","matches":[{"phrase":"ceiling speakers","productUrl":"https://7acb261fef948b59ffde0b870799932090f6123f.com/2b9c4e26e766d5e95786bda8606f4f337b1b2147"},{"phrase":"invoice","productUrl":"https://7acb261fef948b59ffde0b870799932090f6123f.com/89ab6fd89ab7cfe74b7616558d9b9792ab210cef"}]}
{"id":"039ba1da-0fb2-4147-adaa-48c159b20d84","impressionId":"izefboc101000aza000DAleteduu9","publisher":"53fdb0f4b1fefa82dab5979f0c3f59dca23a2722","matches":[{"phrase":"away 6","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/4826958b8363c2740ed9fc89c036dab532bf2d86"},{"phrase":"nattie","productUrl":"http://19887088ffe99ed72a7c40067eba7b970c9fd225.com/cf17ea9b4a4a1d3f1d00e7cec14c71d8d5702d98"}]}

Answer 1

考虑内置方法ast.literal_eval()将每行文本作为字典读取，然后使用 pandas' json_normalize()将字典列表转换为数据框:

import ast
from pandas.io.json import json_normalize

data = []
with open('Downloads/insert.txt', 'r') as f:
    for line in f:
        data.append(ast.literal_eval(line.replace('\n','')))

df = json_normalize(data, 'matches', meta=['id', 'impressionId', 'publisher'])

输出