Excel中如何按ID合并行并将不同日期的项置于最后列

原始数据 #

目标效果 #

方法一：Python Pandas 批量处理 #

适合数据量较大的场景，步骤如下：

1. 导入依赖并构造数据

import pandas as pd

data = {
    'id': [1,1,1,2,2,2],
    'date': ['03/02/2023', '03/01/2023', '03/01/2023', '03/01/2023', '03/01/2023', '02/14/2023'],
    'item': ['Apple', 'Orange', 'Banana', 'Kiwi', 'Apple', 'Orange']
}
df = pd.DataFrame(data)

2. 统计每个ID下各日期的出现频率

# 计算每个id-date组合的出现次数
date_counts = df.groupby(['id', 'date']).size().reset_index(name='count')
# 将频率合并回原数据
df = df.merge(date_counts, on=['id', 'date'])

3. 按规则排序数据

# 先按id升序，再按日期出现频率降序排序
df_sorted = df.sort_values(by=['id', 'count'], ascending=[True, False]).reset_index(drop=True)

4. 转换为目标宽表格式

# 给每个ID内的item添加序号
df_sorted['item_num'] = df_sorted.groupby('id').cumcount() + 1
# 转换为宽表并重命名列
result = df_sorted.pivot(index='id', columns='item_num', values='item').reset_index()
result.columns = ['id'] + [f'item{i}' for i in result.columns[1:]]

5. 输出结果

print(result)

运行后将得到与目标一致的表格：

id  item1  item2  item3
0   1  Orange Banana  Apple
1   2    Kiwi  Apple Orange

方法二：Excel手动处理（适合小数据量） #

1. 添加日期频率辅助列

   • 在D1单元格输入日期频率
   • D2单元格输入公式：=COUNTIFS($A:$A,A2,$B:$B,B2)，下拉填充至所有行，计算每个ID对应日期的出现次数

2. 排序数据

   • 选中所有数据（含表头），点击「数据」-「排序」
   • 设置主要关键字为id（升序），次要关键字为日期频率（降序），完成排序

3. 整理目标表格

   • 复制排序后的id列到新表格A列，用「数据」-「删除重复值」保留唯一ID
   • 将排序后的item列按ID分组，依次粘贴到对应ID的右侧列，最后修改表头为id、item1、item2、item3即可

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