如何访问嵌套字典，该字典是数据框中的一行 [英] How to access a nested dictionary which is a row in a Dataframe

问题描述

我有一个看起来像这样的json文件

I have a json file which looks like this

{
    "file": "name",
    "main": [{
                "question_no": "Q.1",
                "question": "what is ?",
                "answer": [{
                        "user": "John",
                        "comment": "It is defined as",
                        "value": {
                            "numbers": 2,
                            "submitted_value": [{
                                    "time": "4:06",
                                    "my_value": {
                                        "value1": 5,
                                        "value2": 10
                                    }
                                },
                                {
                                    "time": "13:47",
                                    "my_value": {
                                        "value1": 24,
                                        "value2": 30
                                    }
                                }
                            ]
                        }

                    },
                    {
                        "user": "Sam",
                        "comment": "as John said above it simply means",
                        "value": {
                            "numbers": 2,
                            "submitted_value": [{
                                    "time": "6:08",
                                    "my_value": {
                                        "value1": 9,
                                        "value2": 10
                                    }
                                },
                                {
                                    "time": "15:24",
                                    "my_value": {
                                        "value1": 54,
                                        "value2": 19
                                    }
                                }
                            ]
                        },
                        "closed": "no"
                    }
                ]

            }]
            }

当我做data = pd.json_normalize(file["main"], record_path='answer', meta='question_no')时 我得到的结果是

when I do data = pd.json_normalize(file["main"], record_path='answer', meta='question_no') the result I get is

   user                             comment    question_no           value                     
0  John                    It is defined as       Q.1       [{'my_value': 5, 'value_2': 10}, {'my_value': 24, 'value_2': 30}]
1   Sam  as John said above it simply means       Q.1        [{'my_value': 9, 'value_2': 10}, {'my_value': 54, 'value_2': 19}]

我需要访问列表value和字典submitted_value中的值，以将my_value and value_2的和作为新列.实际文件有点大，所以请也考虑处理总和所需的时间.

I need to access the values inside the list value and dictionary submitted_value to take the sum of my_value and value_2 as a new column. The actual file is a bit big so please consider the time taken to process the sum too.

所需结果:

value1_sum      value2_sum     question_no              user      comment  
 29                40                Q.1                    john    It is defined as
 63                29                Q.1                     Sam     as John said above it simply means

列的位置不是问题.

推荐答案

您也可以执行以下操作:

You can also do this:

with open('1.json', 'r+') as f:
    data = json.load(f)

df = pd.json_normalize(data['main'],
                       record_path=['answer', 'value', 'submitted_value'],
                       meta=[['question_no'], ['answer', 'user'], ['answer', 'comment']])
df = df.groupby(by=['answer.user', 'question_no', 'answer.comment'], as_index=False).sum()
print(df)

  answer.user question_no                      answer.comment  my_value.value1  my_value.value2
0        John         Q.1                    It is defined as               29               40
1         Sam         Q.1  as John said above it simply means               63               29

这篇关于如何访问嵌套字典，该字典是数据框中的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！