将熊猫数据框转换为嵌套的 json

Question

我有一个如下所示的数据框，其中一列包含已经嵌套的字典列表：import pandas as pddata = {'First':  ['First value', 'Second value'],    'Second': ['First value', 'Second value'],    'third': ['First value', 'Second value'],    'forth': ['[{"values": "","entity": "datetime","","Turn":  [{"expression": "","tid": "","type": "", "value": "","mod": "","anchor": "","beginPoint": "","endPoint": ""}]}]','[{"values": "","entity": "datetime","Turn": [{"expression": "","tid": "","type": "", "value": "","mod": "","anchor": "","beginPoint": "","endPoint": ""}]}]'],    }df = pd.DataFrame (data, columns = ['First','second','third','forth'])我想将其转换为以下 json 格式并保存为：[  {    "first": "",    "second": "",    "third": "",    "forth": [        {          "values": "",          "entity": "",          "TIMEX3": [            {              "expression": "",              "tid": "",              "type": "",              "value": "",              "mod": "",              "anchorTimeID": "",              "beginPoint": "",              "endPoint": ""                    }                  ]                }              ]            },...我试过以下，但输出太乱，看起来不像我想保存的输出  my_json = (df.groupby(['text','intent','domain'], as_index=False)               .apply(lambda x: x[['entities']].to_dict('r'))               .reset_index()               .to_json(orient='records',indent= 2))

Answer 1

我相信，您离想要的格式不远了。唯一的问题是列forth包含字典作为字符串。一种可能的方法是将所有内容转换回字典，使用 eval 将字符串转换回字典，并使用 json 解析器很好地打印它：

import pandas as pd

import json

data = {'First':  ['First value', 'Second value'],

    'Second': ['First value', 'Second value'],

    'third': ['First value', 'Second value'],

    'forth': ['[{"values": "","entity": "datetime","Turn":  [{"expression": "","tid": "","type": "", "value": "","mod": "","anchor": "","beginPoint": "","endPoint": ""}]}]','[{"values": "","entity": "datetime","Turn": [{"expression": "","tid": "","type": "", "value": "","mod": "","anchor": "","beginPoint": "","endPoint": ""}]}]'],

    }

df = pd.DataFrame (data, columns = ['First','Second','third','forth'])

my_dict = df.to_dict(orient='records')

for row in my_dict:

    row['forth'] = eval(row['forth'])

my_json = json.dumps(my_dict, indent=2)

print(my_json)

有两个小的更正，密钥大写Second和无效条目：, "", 在您的forth密钥中。

这是我的输出的副本：

[

  {

    "First": "First value",

    "Second": "First value",

    "third": "First value",

    "forth": [

      {

        "values": "",

        "entity": "datetime",

        "Turn": [

          {

            "expression": "",

            "tid": "",

            "type": "",

            "value": "",

            "mod": "",

            "anchor": "",

            "beginPoint": "",

            "endPoint": ""

          }

        ]

      }

    ]

  },  ...

如果列forth已经是数据框中的字典，您可以to_json直接调用，格式将是您想要的。例如，您可以尝试将更正后的数据转换回my_dict数据帧：

test_df = pd.DataFrame(my_dict)

print(test_df.to_json(orient='records', indent=2))