如何使用pandas将嵌套dict（json）列表转换为自定义数据帧？

Question

我正在通过一个API合并调查结果，并希望将这些结果转换为如下所示的数据帧：

PersonId | Question | Answer | Department

要实现这一点，每一行必须是一个人的一对问答，包括第一个问题的部门。所以在这种情况下应该是这样的：

^{pr2}$

以下是从api检索数据并将其组合后的外观。我不需要'answers'和'id'键，因为'results'包含参与者给出的答案。答案总是在1到5之间。在

[
      {
        '0': {
          'title': 'What department do you work at?',
          'id': '2571050',
          'results': {
            '0': 'Department A',
            '1': '',
          },
          'answers': {
            '0': 'Department A',
            '1': 'Department B',
          }
        },
        '1': {
          'title': 'I can focus on clear targets',
          'id': '5275962',
          'results': {
            '0': '3'
          },
          'answers': {
            '0': 'Strongly disagree',
            '1': 'Strongly Agree'
          }
        },
        '2': {
          'title': 'I am satisfied with my working environment',
          'id': '5276045',
          'results': {
            '0': '4'
          },
          'answers': {
            '0': 'Strongly Disagree',
            '1': 'Strongly Agree'
          }
        },
      },
      {
        '0': {
          'title': 'What department do you work at?',
          'id': '2571050',
          'results': {
            '0': '',
            '1': 'Department B',
          },
          'answers': {
            '0': 'Department A',
            '1': 'Department B',
          }
        },
        '1': {
          'title': 'I can focus on clear targets',
          'id': '5275962',
          'results': {
            '0': '1'
          },
          'answers': {
            '0': 'Strongly disagree',
            '1': 'Strongly Agree'
          }
        },
        '2': {
          'title': 'I am satisfied with my working environment',
          'id': '5276048',
          'results': {
            '0': '3'
          },
          'answers': {
            '0': 'Strongly Disagree',
            '1': 'Strongly Agree'
          }
        }
      }
 ]

Answer 1

小心您的JSON文件包含一些错误。字典最后一个值的末尾不应该有逗号。您还应该对字典的键/值使用双引号，而不是单引号。我在答案的末尾链接了正确的JSON文件。在

回到您的问题，您可以使用json和pandas库来解析文件。下面是它的样子：

import json
import pandas as pd

df = pd.DataFrame({'PersonId' : [], 'Question' : [], 'Answer' : [], 'Department' : []})
i = 1
for people in data:
    # We assign an id to the answerer
    person_id = i
    i += 1
    #We retrieve the department of the answerer
    if people['0']['results']['0'] != '':
        department = people['0']['results']['0']
    else:
        department = people['0']['results']['1']
    for answer in people:
        #if we are not asking for the department :
        new_row = {'PersonId' : person_id, 'Department' : department}
        if answer != '0':
            # We collect the question and the answer
            new_row['Question'] = people[answer]['title']
            new_row['Answer'] = people[answer]['results']['0']
            df = df.append(new_row, ignore_index = True)

输出：

^{pr2}$

---

JSON文件：

[
      {
        "0": {
          "title": "What department do you work at?",
          "id": "2571050",
          "results": {
            "0": "Department A",
            "1": ""
          },
          "answers": {
            "0": "Department A",
            "1": "Department B"
          }
        },
        "1": {
          "title": "I can focus on clear targets",
          "id": "5275962",
          "results": {
            "0": "3"
          },
          "answers": {
            "0": "Strongly disagree",
            "1": "Strongly Agree"
          }
        },
        "2": {
          "title": "I am satisfied with my working environment",
          "id": "5276045",
          "results": {
            "0": "4"
          },
          "answers": {
            "0": "Strongly Disagree",
            "1": "Strongly Agree"
          }
        }
      },
      {
        "0": {
          "title": "What department do you work at?",
          "id": "2571050",
          "results": {
            "0": "",
            "1": "Department B"
          },
          "answers": {
            "0": "Department A",
            "1": "Department B"
          }
        },
        "1": {
          "title": "I can focus on clear targets",
          "id": "5275962",
          "results": {
            "0": "1"
          },
          "answers": {
            "0": "Strongly disagree",
            "1": "Strongly Agree"
          }
        },
        "2": {
          "title": "I am satisfied with my working environment",
          "id": "5276048",
          "results": {
            "0": "3"
          },
          "answers": {
            "0": "Strongly Disagree",
            "1": "Strongly Agree"
          }
        }
      }
 ]