搜索json文件返回dataframe列中键的一部分

Question

我有一个json文件。 看起来是这样的：

example = {"evaluation user1":{
      "results test1": {
        "chapter": "Chapter1",
        "Chapter_type": "Chemistry1",
        "Exercice 1 code:Chem01": "Wright",
        "Exercice 2 code:Chem02": "Wrong",
        "Exercice 3 code:Chem03": "Wrong",
        "userEmail": "user1@mail.com",
        },
      "results test2": {
        "chapter": "Chapter1",
        "Chapter_type": "Chemistry2",
        "Exercice 1 code:Chem01": "Wrong",
        "Exercice 2 code:Chem02": "Wright",
        "Exercice 3 code:Chem03": "Wrong",
        "userEmail": "user1@mail.com",
        }}
    }

我需要提取键/值并将它们添加到数据帧中。 我是这样做的：

for key, value in example.items():
  if 'evaluation' in key:
    for key1, value1 in value.items():
      computedValues = {}
      computedValues['Email'] = value['userEmail']
      computedValues['Chapter'] = value['chapter']

它适用于示例中的“chapter”：“Chapter1”等键/值

我的问题是我需要提取“exercies”键的内容：

Code     Answer
Chem01

Email              Test     Code         Answer
user1@mail.com     test1    Chem01       Wright
user1@mail.com     test1    Chem02       Wrong
user1@mail.com     test1    Chem03       Wrong
user1@mail.com     test2    Chem01       Wrong
user1@mail.com     test2    Chem02       Wright
user1@mail.com     test2    Chem03       Wrong

基本上，我需要访问列/行中的键/值（如电子邮件），但我需要访问行中exercies的部分键/值，因为我可以有许多exercies代码，我不想有数百列。 有人能帮我吗

Answer 1

修改示例代码以生成列表列表：

computedValues = []

for key, value in example.items():
  if 'evaluation' in key:
    for key1, value1 in value.items():
      test0, test = key1.split(" ")
      email = value1.pop('userEmail')
      chapter = value1.pop('chapter')
      chapter_type = value1.pop('Chapter_type')
      for key2, value2 in value1.items():
        code0, code = key2.split(':')
        computedValues.append([email, test, chapter, code, value2])

输出将是：

[['user1@mail.com', 'test1', 'Chapter1', 'Chem01', 'Wright'],
 ['user1@mail.com', 'test1', 'Chapter1', 'Chem02', 'Wrong'],
 ['user1@mail.com', 'test1', 'Chapter1', 'Chem03', 'Wrong'],
 ['user1@mail.com', 'test2', 'Chapter1', 'Chem01', 'Wrong'],
 ['user1@mail.com', 'test2', 'Chapter1', 'Chem02', 'Wright'],
 ['user1@mail.com', 'test2', 'Chapter1', 'Chem03', 'Wrong']]

我不明白你为什么要把你处理过的数据再次放入dict。 如果你能澄清最终结果，我可以更新答案