Skip to main content
 首页 » 编程设计

python之按键连接数据框之重复数据作为新列

2024年12月31日8三少

我面临下一个情况。我有两个数据帧可以说 df1 和 df2,我需要通过一个键( ID_ed , ID )加入它们第二个数据帧可能不止一次出现该键,我需要的是加入两个数据帧,并添加作为新列重复出现的键(如下图所示)

我尝试使用 merge = df2.join( df1 , lsuffix='_ZID', rsuffix='_IID' , how = "left") 和 concat 操作,但到目前为止没有成功。似乎它只保留最后一次出现(好像它正在覆盖数据)

非常感谢您在此方面的任何帮助,并提前致谢。

请您参考如下方法:

另一种方法是create a serial counter for the ID_ed column , set_index 和 unstack 在调用 pivot_table 之前。 pivot_table 聚合将是第一个。这种方法与 this SO answer 非常相似

生成数据

import pandas as pd 
import numpy as np 
 
a = [['ID_ed','color'],[1,5],[2,8],[3,7]] 
b = [['ID','code'],[1,1],[1,5], 
    [2,np.nan],[2,20],[2,74], 
    [3,10],[3,98],[3,85], 
    [3,21],[3,45]] 
 
df1 = pd.DataFrame(a[1:], columns=a[0]) 
df2 = pd.DataFrame(b[1:], columns=b[0]) 
print(df1) 
   ID_ed  color 
0      1      5 
1      2      8 
2      3      7 
 
print(df2) 
   ID  code 
0   1   1.0 
1   1   5.0 
2   2   NaN 
3   2  20.0 
4   2  74.0 
5   3  10.0 
6   3  98.0 
7   3  85.0 
8   3  21.0 
9   3  45.0 

首先是合并和拆栈

# Merge and add a serial counter column 
df = df1.merge(df2, how='inner', left_on='ID_ed', right_on='ID') 
df['counter'] = df.groupby('ID_ed').cumcount()+1 
print(df) 
   ID_ed  color  ID  code  counter 
0      1      5   1   1.0        1 
1      1      5   1   5.0        2 
2      2      8   2   NaN        1 
3      2      8   2  20.0        2 
4      2      8   2  74.0        3 
5      3      7   3  10.0        1 
6      3      7   3  98.0        2 
7      3      7   3  85.0        3 
8      3      7   3  21.0        4 
9      3      7   3  45.0        5 
 
# Set index and unstack 
df.set_index(['ID_ed','color','counter']).\ 
   unstack().\ 
   swaplevel(1,0,axis=1).\ 
   sort_index(level=0,axis=1).add_prefix('counter_') 
print(df) 
 
counter      counter_1               counter_2             \ 
            counter_ID counter_code counter_ID counter_code\ 
ID_ed color                                                \ 
1     5            1.0          1.0        1.0          5.0\ 
2     8            2.0          NaN        2.0         20.0\ 
3     7            3.0         10.0        3.0         98.0 \ 
 
 counter_3               counter_4               counter_5              
counter_ID counter_code counter_ID counter_code counter_ID counter_code 
       NaN          NaN        NaN          NaN        NaN          NaN 
       2.0         74.0        NaN          NaN        NaN          NaN 
       3.0         85.0        3.0         21.0        3.0         45.0 

接下来生成数据透视表

# Pivot table with 'first' aggregation 
dfp = pd.pivot_table(df, index=['ID_ed','color'], 
                        columns=['counter'], 
                        values=['ID', 'code'], 
                        aggfunc='first') 
print(dfp) 
              ID                      code                         
counter        1    2    3    4    5     1     2     3     4     5 
ID_ed color                                                        
1     5      1.0  1.0  NaN  NaN  NaN   1.0   5.0   NaN   NaN   NaN 
2     8      2.0  2.0  2.0  NaN  NaN   NaN  20.0  74.0   NaN   NaN 
3     7      3.0  3.0  3.0  3.0  3.0  10.0  98.0  85.0  21.0  45.0 

最后按部分列名重命名列和切片

# Rename columns 
level_1_names = list(dfp.columns.get_level_values(1)) 
level_0_names = list(dfp.columns.get_level_values(0)) 
new_cnames = [b+'_'+str(f) for f, b in zip(level_1_names, level_0_names)] 
dfp.columns = new_cnames 
 
# Slice by new column names 
print(dfp.loc[:, dfp.columns.str.contains('code')].reset_index(drop=False)) 
   ID_ed  color  code_1  code_2  code_3  code_4  code_5 
0      1      5     1.0     5.0     NaN     NaN     NaN 
1      2      8     NaN    20.0    74.0     NaN     NaN 
2      3      7    10.0    98.0    85.0    21.0    45.0