代码之家 › 专栏 › 技术社区 › lubenthrust

如何通过对pandas DataFrame的每一行进行分组来有效地选择顶部列?

group-by pandas python

lubenthrust · 技术社区 · 1 年前

这是我之前在 How to efficiently select the top N columns by grouping for each row of a pandas DataFrame? 。随着我使用数据集,我的需求也在不断发展——再次感谢到目前为止帮助过我的每个人。以下是目前的问题:

假设我有一个pandas DataFrame,代表按日期组织的假设比赛中每个“参赛者”的分数。请注意,可能会散布零星的NaN值:

import numpy as np
import pandas as pd

rng = np.random.default_rng()
dates = pd.date_range('2024-08-01', '2024-08-07')
contestants = ['Alligator', 'Beryl', 'Chupacabra', 'Dandelion', 'Eggplant', 'Feldspar']
scores = rng.random(len(dates) * len(contestants))
scores[rng.integers(len(scores), size=10)] = np.nan
scores = scores.reshape((len(dates), len(contestants)))
scores = pd.DataFrame(scores, dates, contestants)
scores.index.name = 'DATE'
scores.columns.name = 'CONTESTANT'

CONTESTANT  Alligator     Beryl  Chupacabra  Dandelion  Eggplant  Feldspar
DATE
2024-08-01   0.425859  0.869790    0.025546   0.249784  0.164426  0.292931
2024-08-02   0.545743  0.245658    0.384288   0.148041  0.759137       NaN
2024-08-03   0.558930  0.773545    0.215342   0.644964  0.204309       NaN
2024-08-04   0.448075       NaN         NaN   0.795700  0.744143  0.807003
2024-08-05   0.858097  0.349170    0.339740   0.445206       NaN  0.118371
2024-08-06        NaN  0.847647    0.086368   0.806557       NaN       NaN
2024-08-07   0.167334  0.063111    0.152129   0.823477  0.613271  0.709280

此外,每个参赛者都被映射到一个特定的类别:

category_mapping = {
    'Alligator': 'Animal',
    'Beryl': 'Mineral',
    'Chupacabra': 'Animal',
    'Dandelion': 'Vegetable',
    'Eggplant': 'Vegetable',
    'Feldspar': 'Mineral'
}

考虑到这种设置,我如何在每一行中保留每个类别的最佳分数,将任何未通过筛选的分数清零或设置为NaN?例如,结果应该看起来像这样:

CONTESTANT  Alligator     Beryl  Chupacabra  Dandelion  Eggplant  Feldspar
DATE
2024-08-01   0.425859  0.869790    0.000000   0.249784  0.000000  0.000000
2024-08-02   0.545743  0.245658    0.000000   0.000000  0.759137       NaN
2024-08-03   0.558930  0.773545    0.000000   0.644964  0.000000       NaN
2024-08-04   0.448075       NaN         NaN   0.795700  0.000000  0.807003
2024-08-05   0.858097  0.349170    0.000000   0.445206       NaN  0.000000
2024-08-06        NaN  0.847647    0.086368   0.806557       NaN       NaN
2024-08-07   0.167334  0.000000    0.000000   0.823477  0.000000  0.709280

此外,我该如何着手制作这个 快速的 ? 我的实际应用是在大约250行、15000列和175个类别的DataFrame上进行蒙特卡洛模拟,因此效率是这里的关键。我主要是通过结合@mozway和@rezan21(转置groupby idxmax转置)的答案来解决这个问题的,但我怀疑我的方法不是最优的,可能会好得多。谢谢你的帮助!

1 回复 | 直到 1 年前

ouroboros1 1 年前

与 accepted answer 通过 @mozway 链接到您的帖子:

将列标签映射到类别( cat ).
使用 df.T 并申请 df.groupby 具有 groupby.transform + max ,并再次转置( max_transform ).
使用 df.where 并检查以下各项是否相等 scores 和 max_transform .
添加替代条件 scores.isna() 如果你想保存 NaN 价值观。

cat = scores.columns.map(category_mapping)

max_transform = scores.T.groupby(cat).transform('max').T

out = scores.where(scores == max_transform, 0)

输出:

CONTESTANT  Alligator     Beryl  Chupacabra  Dandelion  Eggplant  Feldspar
DATE                                                                      
2024-08-01   0.425859  0.869790    0.000000   0.249784  0.000000  0.000000
2024-08-02   0.545743  0.245658    0.000000   0.000000  0.759137  0.000000
2024-08-03   0.558930  0.773545    0.000000   0.644964  0.000000  0.000000
2024-08-04   0.448075  0.000000    0.000000   0.795700  0.000000  0.807003
2024-08-05   0.858097  0.349170    0.000000   0.445206  0.000000  0.000000
2024-08-06   0.000000  0.847647    0.086368   0.806557  0.000000  0.000000
2024-08-07   0.167334  0.000000    0.000000   0.823477  0.000000  0.709280

# preserve `NaN` values
scores.where((scores == max_transform) | (scores.isna()), 0)

CONTESTANT  Alligator     Beryl  Chupacabra  Dandelion  Eggplant  Feldspar
DATE                                                                      
2024-08-01   0.425859  0.869790    0.000000   0.249784  0.000000  0.000000
2024-08-02   0.545743  0.245658    0.000000   0.000000  0.759137       NaN
2024-08-03   0.558930  0.773545    0.000000   0.644964  0.000000       NaN
2024-08-04   0.448075       NaN         NaN   0.795700  0.000000  0.807003
2024-08-05   0.858097  0.349170    0.000000   0.445206       NaN  0.000000
2024-08-06        NaN  0.847647    0.086368   0.806557       NaN       NaN
2024-08-07   0.167334  0.000000    0.000000   0.823477  0.000000  0.709280

数据样本

import pandas as pd
import numpy as np

data = {'index': ['2024-08-01', '2024-08-02', '2024-08-03', '2024-08-04', 
                  '2024-08-05', '2024-08-06', '2024-08-07'], 
        'columns': ['Alligator', 'Beryl', 'Chupacabra', 'Dandelion', 
                    'Eggplant', 'Feldspar'], 
        'data': [[0.425859, 0.86979, 0.025546, 0.249784, 0.164426, 0.292931], 
                 [0.545743, 0.245658, 0.384288, 0.148041, 0.759137, np.nan], 
                 [0.55893, 0.773545, 0.215342, 0.644964, 0.204309, np.nan], 
                 [0.448075, np.nan, np.nan, 0.7957, 0.744143, 0.807003], 
                 [0.858097, 0.34917, 0.33974, 0.445206, np.nan, 0.118371], 
                 [np.nan, 0.847647, 0.086368, 0.806557, np.nan, np.nan], 
                 [0.167334, 0.063111, 0.152129, 0.823477, 0.613271, 0.70928]], 
        'index_names': ['DATE'], 
        'column_names': ['CONTESTANT']
        }

scores = pd.DataFrame.from_dict(data, orient='tight')