在列值等于序列中索引的数据帧上连接具有重复索引的序列

Question

假设我有一个系列和数据帧，如：

import pandas as pd
s = pd.Series([10,20,11,12,30,34],
    index=["red","red","blue","blue","green","green"])
s.index.name="numbers"
df = pd.DataFrame({
    "color":["red","green","blue","blue","red","green"],
    "id":[1,2,3,4,5,6]})

我想将s中的值添加到df中的列中，顺序与它们出现的顺序相同，其中s的索引等于df["color"]，即

pd.some_function(df,s,left_on="color",right_index=True)
color   id    numbers
red      1      10
green    2      30
blue     3      11
blue     4      12
red      5      20
green    6      34

我尝试过pd.merge、pd.join等，但我根本无法使其工作(如果不在df上循环，由color过滤，添加来自s的数据，然后在最后将其连接(

Answer 1

您可以使用groupby.cumcount为merge:设置唯一密钥

idx1 = s.groupby(level=0).cumcount()
# [0, 1, 0, 1, 0, 1]
idx2 = df.groupby('color').cumcount()
# [0, 0, 0, 1, 1, 1]
s.index.name="color"
out = (df
   .merge(s.reset_index(name='number'),
          left_on=['color', idx2], right_on=['color', idx1])
   .drop(columns='key_1')
)

变体：

s.index.name="color"
out = (df
   .assign(idx=df.groupby('color').cumcount())
   .merge(s.reset_index(name='number')
           .assign(idx=s.groupby(level=0).cumcount().values),
          left_on=['color', 'idx'], right_on=['color', 'idx'])
    .drop(columns='idx')
)

输出：

   color  id  number
0    red   1      10
1  green   2      30
2   blue   3      11
3   blue   4      12
4    red   5      20
5  green   6      34