时间戳

首页 > 解决方案 > 如何解析具有多个 Json 条目的文件并仅使用 Pandas 获取嵌套的 Json？

python - 如何解析具有多个 Json 条目的文件并仅使用 Pandas 获取嵌套的 Json？

问题描述

我有一个文件，每行包含一个 Json 条目：

{"_id":"5d42af1fb42842aa680cdba8","data_type":"8a6f03a1-4594-4133-9ba9-35e8eb83b62b","version":"1b1ec5d7-931a-4d60-b892-1db20ce2d98e","data":[{"id":"5d42af1f170e8d210fe935af","name":"Harriett Floyd"},{"id":"5d42af1fa92b30f9edbd4fb7","name":"Serrano Stein"},{"id":"5d42af1f2c1a804f5ac64491","name":"Denise Lopez"}]}
{"_id":"5d42af1fe2969c2e4064b522","data_type":"e627abb0-2b89-49af-8f26-2554dc655755","version":"4c625773-617b-460b-ac7c-8ddfb19058c8","data":[{"id":"5d42af1f0c91b1b5e484dc02","name":"Sears Gray"},{"id":"5d42af1f880d828b2e6d0c9f","name":"Carmen Britt"},{"id":"5d42af1fecdf9b333ce210a5","name":"Laura Haynes"}]}
{"_id":"5d42af1f932313d233121f52","data_type":"0189ecbd-ec19-4675-adab-0efaa4b3980e","version":"b0161b41-0f74-4040-94c7-2dc65916eb2a","data":[{"id":"5d42af1f07c1413d3cee996b","name":"Espinoza Miranda"},{"id":"5d42af1f4de7227a20790512","name":"Gallegos Everett"},{"id":"5d42af1fd65727bdeefebbc2","name":"Kristy Gates"}]}
{"_id":"5d42af1f41316fd69bb8eb65","data_type":"c69aa41d-bd7b-49b4-b147-a06a03ee14d1","version":"854427a3-1ad0-4f48-8682-197bec45c0fd","data":[{"id":"5d42af1f51417661828db0b6","name":"Morgan Osborne"},{"id":"5d42af1f8f346e78685f45d3","name":"Colleen Bray"},{"id":"5d42af1f80cd622be5c8491b","name":"Shana Henson"}]}
{"_id":"5d42af1f6f6ebc59ed4d3a04","data_type":"2d3de9f1-0a0f-41b0-8c7c-9dfb6e909a1c","version":"9ded1de4-6b01-4fbf-b150-559f7a638544","data":[{"id":"5d42af1f8c1eb70073dae767","name":"Maricela Austin"},{"id":"5d42af1f640fc89271413622","name":"Tabatha Silva"},{"id":"5d42af1f96c309104b2b8127","name":"Gail Mendez"}]}

一个更漂亮格式的条目：

{
    "_id": "5d42af1fb42842aa680cdba8",
    "data_type": "8a6f03a1-4594-4133-9ba9-35e8eb83b62b",
    "version": "1b1ec5d7-931a-4d60-b892-1db20ce2d98e",
    "data": [
      {
        "id": "5d42af1f170e8d210fe935af",
        "name": "Harriett Floyd"
      },
      {
        "id": "5d42af1fa92b30f9edbd4fb7",
        "name": "Serrano Stein"
      },
      {
        "id": "5d42af1f2c1a804f5ac64491",
        "name": "Denise Lopez"
      }
    ]
  }

因此 Json 条目包含多个属性，'data'其中一个包含嵌套的 Json。我想做的是仅使用 Pandas，将所有 Json 条目存储在 DataFrame 中，每个数据一行。

我试过这个：

df_json = pd.read_json(path_json_file, lines=True)

并获得了这个：

                         _id                               data_type    version      data
0   5d42af1fb42842aa680cdba8    8a6f03a1-4594-4133-9ba9-35e8eb83b62b    1b1ec5d7-931a-4d60-b892-1db20ce2d98e    [{'id': '5d42af1f170e8d210fe935af', 'name': 'H...
1   5d42af1fe2969c2e4064b522    e627abb0-2b89-49af-8f26-2554dc655755    4c625773-617b-460b-ac7c-8ddfb19058c8    [{'id': '5d42af1f0c91b1b5e484dc02', 'name': 'S...
2   5d42af1f932313d233121f52    0189ecbd-ec19-4675-adab-0efaa4b3980e    b0161b41-0f74-4040-94c7-2dc65916eb2a    [{'id': '5d42af1f07c1413d3cee996b', 'name': 'E...
3   5d42af1f41316fd69bb8eb65    c69aa41d-bd7b-49b4-b147-a06a03ee14d1    854427a3-1ad0-4f48-8682-197bec45c0fd    [{'id': '5d42af1f51417661828db0b6', 'name': 'M...
4   5d42af1f6f6ebc59ed4d3a04    2d3de9f1-0a0f-41b0-8c7c-9dfb6e909a1c    9ded1de4-6b01-4fbf-b150-559f7a638544    [{'id': '5d42af1f8c1eb70073dae767', 'name': 'M...

所以'data'列包含一个 Json 数组，但是，我想要的是数组中包含的每个数据的一行。

然后我了解了json_normalizePandas 的功能，做了以下工作：

1）我通过这样做将所有Jsons存储在一个数组中：

import pandas as pd
import os
import json

json_array = []
with open(path_json_file, 'r') as f:
    for line in f:
        json_array.append(json.loads(line))

2）我存储了 Json 的键，除了数据来保留列：

key_list = list(json_array[0].keys())
key_list.remove("data")

3）我使用的json_normalize功能：

pd.io.json.json_normalize(json_array, "data", key_list, errors="ignore", record_prefix="record_data_")

输出：

 record_data_id record_data_name    _id data_type   version
0   5d42af1f170e8d210fe935af    Harriett Floyd  5d42af1fb42842aa680cdba8    8a6f03a1-4594-4133-9ba9-35e8eb83b62b    1b1ec5d7-931a-4d60-b892-1db20ce2d98e
1   5d42af1fa92b30f9edbd4fb7    Serrano Stein   5d42af1fb42842aa680cdba8    8a6f03a1-4594-4133-9ba9-35e8eb83b62b    1b1ec5d7-931a-4d60-b892-1db20ce2d98e
2   5d42af1f2c1a804f5ac64491    Denise Lopez    5d42af1fb42842aa680cdba8    8a6f03a1-4594-4133-9ba9-35e8eb83b62b    1b1ec5d7-931a-4d60-b892-1db20ce2d98e
3   5d42af1f0c91b1b5e484dc02    Sears Gray  5d42af1fe2969c2e4064b522    e627abb0-2b89-49af-8f26-2554dc655755    4c625773-617b-460b-ac7c-8ddfb19058c8
4   5d42af1f880d828b2e6d0c9f    Carmen Britt    5d42af1fe2969c2e4064b522    e627abb0-2b89-49af-8f26-2554dc655755    4c625773-617b-460b-ac7c-8ddfb19058c8
5   5d42af1fecdf9b333ce210a5    Laura Haynes    5d42af1fe2969c2e4064b522    e627abb0-2b89-49af-8f26-2554dc655755    4c625773-617b-460b-ac7c-8ddfb19058c8
6   5d42af1f07c1413d3cee996b    Espinoza Miranda    5d42af1f932313d233121f52    0189ecbd-ec19-4675-adab-0efaa4b3980e    b0161b41-0f74-4040-94c7-2dc65916eb2a
...

这个输出正是我想要的，但是有没有一个技巧可以只使用 Pandas 来做到这一点而不必自己解析文件？

标签： pythonjsonpandas

解决方案

基于数据帧连接的短方法：

import pandas as pd

df_json = pd.read_json(path_json_file, lines=True)
dfs = df_json.apply(lambda r: pd.DataFrame(r['data']).assign(**r.drop('data')), axis=1)
res = pd.concat(dfs.tolist(), ignore_index=True)
print(res.to_string())

输出：

                          id              name                       _id                             data_type                               version
0   5d42af1f170e8d210fe935af    Harriett Floyd  5d42af1fb42842aa680cdba8  8a6f03a1-4594-4133-9ba9-35e8eb83b62b  1b1ec5d7-931a-4d60-b892-1db20ce2d98e
1   5d42af1fa92b30f9edbd4fb7     Serrano Stein  5d42af1fb42842aa680cdba8  8a6f03a1-4594-4133-9ba9-35e8eb83b62b  1b1ec5d7-931a-4d60-b892-1db20ce2d98e
2   5d42af1f2c1a804f5ac64491      Denise Lopez  5d42af1fb42842aa680cdba8  8a6f03a1-4594-4133-9ba9-35e8eb83b62b  1b1ec5d7-931a-4d60-b892-1db20ce2d98e
3   5d42af1f0c91b1b5e484dc02        Sears Gray  5d42af1fe2969c2e4064b522  e627abb0-2b89-49af-8f26-2554dc655755  4c625773-617b-460b-ac7c-8ddfb19058c8
4   5d42af1f880d828b2e6d0c9f      Carmen Britt  5d42af1fe2969c2e4064b522  e627abb0-2b89-49af-8f26-2554dc655755  4c625773-617b-460b-ac7c-8ddfb19058c8
5   5d42af1fecdf9b333ce210a5      Laura Haynes  5d42af1fe2969c2e4064b522  e627abb0-2b89-49af-8f26-2554dc655755  4c625773-617b-460b-ac7c-8ddfb19058c8
6   5d42af1f07c1413d3cee996b  Espinoza Miranda  5d42af1f932313d233121f52  0189ecbd-ec19-4675-adab-0efaa4b3980e  b0161b41-0f74-4040-94c7-2dc65916eb2a
7   5d42af1f4de7227a20790512  Gallegos Everett  5d42af1f932313d233121f52  0189ecbd-ec19-4675-adab-0efaa4b3980e  b0161b41-0f74-4040-94c7-2dc65916eb2a
8   5d42af1fd65727bdeefebbc2      Kristy Gates  5d42af1f932313d233121f52  0189ecbd-ec19-4675-adab-0efaa4b3980e  b0161b41-0f74-4040-94c7-2dc65916eb2a
9   5d42af1f51417661828db0b6    Morgan Osborne  5d42af1f41316fd69bb8eb65  c69aa41d-bd7b-49b4-b147-a06a03ee14d1  854427a3-1ad0-4f48-8682-197bec45c0fd
10  5d42af1f8f346e78685f45d3      Colleen Bray  5d42af1f41316fd69bb8eb65  c69aa41d-bd7b-49b4-b147-a06a03ee14d1  854427a3-1ad0-4f48-8682-197bec45c0fd
11  5d42af1f80cd622be5c8491b      Shana Henson  5d42af1f41316fd69bb8eb65  c69aa41d-bd7b-49b4-b147-a06a03ee14d1  854427a3-1ad0-4f48-8682-197bec45c0fd
12  5d42af1f8c1eb70073dae767   Maricela Austin  5d42af1f6f6ebc59ed4d3a04  2d3de9f1-0a0f-41b0-8c7c-9dfb6e909a1c  9ded1de4-6b01-4fbf-b150-559f7a638544
13  5d42af1f640fc89271413622     Tabatha Silva  5d42af1f6f6ebc59ed4d3a04  2d3de9f1-0a0f-41b0-8c7c-9dfb6e909a1c  9ded1de4-6b01-4fbf-b150-559f7a638544
14  5d42af1f96c309104b2b8127       Gail Mendez  5d42af1f6f6ebc59ed4d3a04  2d3de9f1-0a0f-41b0-8c7c-9dfb6e909a1c  9ded1de4-6b01-4fbf-b150-559f7a638544

推荐阅读