Merging two dataframes while changing the order of the second dataframe each time-CodePudding

df is like so:

Week    Name  
 1      TOM
 1      BEN
 1      CARL
 2      TOM
 2      BEN
 2      CARL
 3      TOM
 3      BEN
 3      CARL

and df1 is like so:

ID     Letter
1        A
2        B
3        C

I want to merge the two dataframes so that each name is assigned a different letter each time. So the result should be like this:

Week    Name    Letter
 1      TOM       A
 1      BEN       B
 1      CARL      C
 2      TOM       B
 2      BEN       C
 2      CARL      A
 3      TOM       C
 3      BEN       A
 3      CARL      B

Any help would be greatly appreciated. Thanks in advance.

CodePudding user response：

df1['Letter'] = df1.groupby('Week').cumcount().add(df1['Week'].sub(1)).mod(df1.groupby('Week').transform('count')['Name']).map(df2['Letter'])

Output:

>>> df1
   Week  Name Letter
0     1   TOM      A
1     1   BEN      B
2     1  CARL      C
3     2   TOM      B
4     2   BEN      C
5     2  CARL      A
6     3   TOM      C
7     3   BEN      A
8     3  CARL      B