基于列的两数据帧求交

Question

假设我有以下两个数据帧：

df1:
    Composite   Beta_value  Chromosome  Start       End     Gene_Symbol
0   cg00000029  0.297449111 chr16       53434200    53434201    RBL2
1   cg00000108  0.660066803 chr3        37417715    37417716    C3orf35
2   cg00000109  0.660066803 chr3        172198247   172198248   FNDC3B
3   cg00000165  0.660066803 chr1        90729117    90729118    C3orf35
4   cg00000236  0.905679244 chr8        42405776    42405777    VDAC3



df2:     
    Composite   Beta_value  Chromosome  Start       End     Gene_Symbol
2   cg00000109  0.660066803 chr3        172198247   172198248   FNDC3B
3   cg00000165  0.660066803 chr1        90729117    90729118    C3orf35
4   cg00000236  0.905679244 chr8        42405776    42405777    VDAC3
46  cg00002116  0.017114732 chr17       81703380    81703381    MRPL12
47  cg00002145  0.780230816 chr2        237340893   237340894   COL6A3
48  cg00002190  0.781140134 chr8        19697522    19697523    CSGALNACT1
49  cg00002224  0.220786047 chr8        143038982   143038983   C8orf31

我想要的是根据“Start”和“Gene\u Symbol”列找到这两个数据帧的交集，如果它们的“Start”和“Gene\u Symbol”与df2中的行匹配，则只保留df1中的行。 例如，我希望结果如下所示：

    Composite   Beta_value  Chromosome  Start       End     Gene_Symbol
2   cg00000109  0.660066803 chr3        172198247   172198248   FNDC3B
3   cg00000165  0.660066803 chr1        90729117    90729118    C3orf35
4   cg00000236  0.905679244 chr8        42405776    42405777    VDAC3

我所说的交集并不意味着合并数据帧并最终得到12列，就像我使用：

intersection = pd.merge(df1, df2, how='inner', on=['Start','Gene_Symbol'])
s1.dropna(inplace=True)

合并了两个数据框中的列，例如：

intersection.columns
Index(['Composite Element REF_x', 'Beta_value_x', 'Chromosome_x', 'Start',
       'End_x', 'Gene_Symbol', 'Gene_Type_x', 'Transcript_ID_x',
       'Position_to_TSS_x', 'CGI_Coordinate_x', 'Feature_Type_x',
       'Composite Element REF_y', 'Beta_value_y', 'Chromosome_y', 'End_y',
       'Gene_Type_y', 'Transcript_ID_y', 'Position_to_TSS_y',
       'CGI_Coordinate_y', 'Feature_Type_y'],
      dtype='object')

Answer 1

仅使用df2中所需的列

pd.merge(df1, df2[['Start','Gene_Symbol']], on=['Start','Gene_Symbol'])

Answer 2

使用^{}时，请确保选择正确的列，这样df2中的所有列也不会合并：

keys = ['Start', 'Gene_Symbol']
intersection = df1.merge(df2[keys], on=keys)

    Composite  Beta_value Chromosome      Start        End Gene_Symbol
0  cg00000109    0.660067       chr3  172198247  172198248      FNDC3B
1  cg00000165    0.660067       chr1   90729117   90729118     C3orf35
2  cg00000236    0.905679       chr8   42405776   42405777       VDAC3