在两个Pandas DataFrame间分配值：按类别关联生成item列

实现思路 #

先给df2每个类别下的Item标记顺序序号，再结合df1每行的数量确定需要提取的Item范围，最后将对应Item拼接成字符串合并到df1中。

具体步骤与代码 #

1. 为df2的类别内Item添加顺序索引
   按category分组后，给每个组内的Item分配从1开始的序号，方便后续通过序号范围匹配数量需求：

import pandas as pd

df1 = pd.DataFrame({"category": ["foo", "foo", "bar", "bar", "bar"], "quantity": [1,2,1,2,3]})
df2 = pd.DataFrame({
    "category": ["foo", "foo", "foo", "foo", "bar", "bar", "bar", "bar", "bar", "bar"], 
    "item": ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"]
})

# 给每个category组内的item添加顺序编号
df2['seq'] = df2.groupby('category').cumcount() + 1

2. 计算df1类别内的累计数量区间
   按category分组后，计算每行对应的Item序号起始和结束值，明确当前行需要提取的Item范围：

# 计算每个category组内的累计数量，确定每个行的item序号范围
df1['start'] = df1.groupby('category')['quantity'].cumsum() - df1['quantity'] + 1
df1['end'] = df1.groupby('category')['quantity'].cumsum()

3. 关联表并筛选匹配的Item
   通过category关联两个表，筛选出序号在df1每行区间内的Item，再按df1的行分组拼接Item：

# 关联df1和df2，筛选符合序号范围的item
merged = pd.merge(df1, df2, on='category', how='left')
merged = merged[(merged['seq'] >= merged['start']) & (merged['seq'] <= merged['end'])]

# 按df1的行索引分组，拼接item为逗号分隔的字符串
df3 = merged.groupby(merged.index).agg({
    'category': 'first',
    'quantity': 'first',
    'item': ','.join
}).reset_index(drop=True)

4. 查看结果
   运行后df3的输出与需求完全一致：

print(df3)
# 输出：
#   category  quantity     item
# 0      foo         1        A
# 1      foo         2      B,C
# 2      bar         1        E
# 3      bar         2      F,G
# 4      bar         3    H,I,J

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