python - 将数据框内的字典转换为新的数据框并选择其中的行

Question

我有一个如下所示的数据框:

    code   size type  type_tops
0   123    5.11 A .   [dictionary]
1   345    5.5  B .   [dictionary]
2   543    6.2  B .   [dictionary]

那些字典看起来像这样:

{'size': {6640: 6.2, 10481: 6.4, 6585: 6.1}, 'Speed': {6640: 119685000.0, 10481: 145793000.0, 6585: 200021000.0}, 'type': {6640: '62.0', 10481: '62.0', 6585: '62.0'}, 'name': {6640: 'John', 10481: 'Mark', 6585: 'Weasley'}

我想创建一个名为“highest_guy”的新列和另一个名为“highest_guy_size”的列。每行都有不同的字典。假设代码 123 在上面有这个字典。第一行应如下所示:

    code   size type  type_tops       highest_guy    highest_guy_size
0   123    5.11 A .   [dictionary]    Mark           6.4

我想我需要将该字典转换为数据框并选择最高的人及其大小。我可以使用 for 循环来做到这一点，就像这样:

for i in df.index:
    tops=pd.DataFrame(df['type_tops][i].sort_values(['size'].reset_index())
    df[highest_guy]=tops['name'][0]
    df['highest_guy_size']=tops['size][0]

但是有没有办法不用 for 呢？

Answer 1

您可以使用 groupby 执行此操作并应用。

无需将每个字典转换为单独的数据框 - 您只需获取最高个人的 ID 并将其用作从嵌套的 name 和 size 中获取值的键 指令:

import pandas as pd

df = pd.DataFrame({'code': [123, 345, 543],
                   'size': [5.11, 5.5, 6.2],
                   'type': ['A', 'B', 'B'],
                   'type_tops': [
                            {'size': {6640: 6.2, 10481: 6.4, 6585: 6.1},
                             'Speed': {6640: 119685000.0, 10481: 145793000.0, 6585: 200021000.0},
                             'type': {6640: '62.0', 10481: '62.0', 6585: '62.0'},
                             'name': {6640: 'John', 10481: 'Mark', 6585: 'Weasley'}},
                            {'size': {6640: 6.2, 10481: 6.4, 6585: 6.9},
                             'Speed': {6640: 119685000.0, 10481: 145793000.0, 6585: 200021000.0},
                             'type': {6640: '62.0', 10481: '62.0', 6585: '62.0'},
                             'name': {6640: 'John', 10481: 'Mark', 6585: 'Weasley'}},
                            {'size': {6640: 6.7, 10481: 6.4, 6585: 6.1},
                             'Speed': {6640: 119685000.0, 10481: 145793000.0, 6585: 200021000.0},
                             'type': {6640: '62.0', 10481: '62.0', 6585: '62.0'},
                             'name': {6640: 'John', 10481: 'Mark', 6585: 'Weasley'}}
                           ]
                   })


def extract_vals(df):
    cur_df = df

    # get the dictionary
    cur_dict = dict(cur_df['type_tops'].item())

    # get the key/id of the tallest individual
    highest_id = max(cur_dict['size'], key=lambda k: cur_dict['size'][k])

    # use id to get their name and size
    highest_guy_size = cur_dict['size'][highest_id]
    highest_guy = cur_dict['name'][highest_id]

    cur_df['highest_guy'] = highest_guy
    cur_df['highest_guy_size'] = highest_guy_size

    return cur_df


new = df.groupby('code').apply(lambda x: extract_vals(x))

这将为您提供如下所示的输出数据框: