如何获取DataFrame中value_counts最大值对应的日期及Sales列数据

你有如下DataFrame：

import pandas as pd
dfsupport = pd.DataFrame({'Date': ['8/12/2020','8/12/2020','13/1/2020','24/5/2020','31/10/2020','11/7/2020','11/7/2020','4/4/2020','1/2/2020'],
                            'Category': ['Table','Chair','Cushion','Table','Chair','Mats','Mats','Large','Large'],
                            'Sales': ['1 table','3chairs','8 cushions','3Tables','12 Chairs','12Mats','4Mats','13 Chairs and 2 Tables', '3 mats, 2 cushions 4@chairs'],
                            'Paid': ['Yes','Yes','Yes','Yes','No','Yes','Yes','No','Yes'],
                            'Amount': ['93.78','$51.99','44.99','38.24','£29.99','29 21 only','18','312.8','63.77' ]
                            })

执行dfsupport['Date'].value_counts().max()得到最大销量次数为2，现在需要找出对应的日期及这些日期下的Sales记录，可按以下步骤操作：

解决方案 #

步骤1：计算日期出现次数并筛选高频日期 #

# 计算每个日期的出现次数
date_counts = dfsupport['Date'].value_counts()
# 获取最大出现次数
max_count = date_counts.max()
# 筛选出所有出现次数等于max_count的日期
top_dates = date_counts[date_counts == max_count].index.tolist()

步骤2：筛选原DataFrame中对应日期的Sales记录 #

# 筛选出目标日期的记录，只保留Date和Sales列
result = dfsupport[dfsupport['Date'].isin(top_dates)][['Date', 'Sales']]
# 输出结果
print(result)

执行后会得到如下输出：

Date      Sales
0   8/12/2020    1 table
1   8/12/2020    3chairs
5   11/7/2020     12Mats
6   11/7/2020      4Mats

简化写法 #

如果想一步完成，可合并代码：

result = dfsupport[
    dfsupport['Date'].isin(
        dfsupport['Date'].value_counts()[
            dfsupport['Date'].value_counts() == dfsupport['Date'].value_counts().max()
        ].index
    )
][['Date', 'Sales']]
print(result)

内容的提问来源于stack exchange，提问作者Dan