Pandas数据帧按多列分组并删除重复行

Question

我正在尝试使用以下形式的数据帧执行任务（在生物信息学中，TCGA数据）：

df = pd.DataFrame({'ID':['TCGA-AB-0001','TCGA-AB-0001','TCGA-AB-0001','TCGA-AB-0001','TCGA-AB-0002','TCGA-AB-0002','TCGA-AB-0002','TCGA-AB-0002','TCGA-AB-0003','TCGA-AB-0002'],
              'Reference':['HG19','HG18','HG19','GRCh37','HG18','HG19','GRCh37','HG19','GRCh37','GRCh37'],
             'SampleType':['Tumor','Tumor','Normal','Normal','Tumor','Normal','Normal','Tumor','Tumor','Tumor']
               })

看起来像：

^{pr2}$

如果行对具有相同的“Reference”和不同的“SampleType”，我将尝试对它们进行匹配。结果将生成以下形式的新数据帧：

             TUMOR                                     NORMAL
index        ID Reference SampleType      index        ID Reference SampleType
0  TCGA-AB-0001      HG19      Tumor      2  TCGA-AB-0001      HG19     Normal
7  TCGA-AB-0002      HG19      Tumor      5  TCGA-AB-0002      HG19      Tumor
9  TCGA-AB-0002    GRCh37      Tumor      6  TCGA-AB-0002    GRCh37     Normal

现在，我想删除重复的ID，但这样做的优先级是根据列表[GRCh37，HG19，HG18]。例如，如果HG19和HG18都存在相同的ID，我将保留HG19。结果如下：

             TUMOR                                     NORMAL
index        ID Reference SampleType      index        ID Reference SampleType
0  TCGA-AB-0001      HG19      Tumor      2  TCGA-AB-0001      HG19     Normal
9  TCGA-AB-0002    GRCh37      Tumor      6  TCGA-AB-0002    GRCh37     Normal

有没有一种方法可以通过groupby或其他pandas函数来实现呢？在

谢谢！在

Answer 1

对我来说，想要的输出是什么仍然不是百分之百清楚的。但根据我的理解，这似乎起到了作用。在

import numpy as np
import pandas as pd


df = pd.DataFrame({'ID':['TCGA-AB-0001','TCGA-AB-0001','TCGA-AB-0001','TCGA-AB-0001','TCGA-AB-0002','TCGA-AB-0002','TCGA-AB-0002','TCGA-AB-0002','TCGA-AB-0003','TCGA-AB-0002', 'TCGA-AB-0001', 'TCGA-AB-0001'],
              'Reference':['HG19','HG18','HG19','GRCh37','HG18','HG19','GRCh37','HG19','GRCh37','GRCh37', 'GRCh37', 'GRCh37'],
             'SampleType':['Tumor','Tumor','Normal','Normal','Tumor','Normal','Normal','Tumor','Tumor','Tumor', 'Normal', 'Tumor']
               })

这比原始示例和具有冗余候选行的测试稍长。在

^{pr2}$

现在我们创建一个可能有“冗余”行的临时数据框。在

##
# Create the df with sort and first level filtering
##
df_2 = df.groupby(['ID','Reference']).filter(lambda x:set(x.SampleType)=={'Tumor','Normal'}).drop_duplicates(['ID', 'Reference', 'SampleType']).sort(['ID','Reference', 'SampleType'])
# By dropping dups and sorting, the SampleType column must alternate: Normal, Tumor, Normal...

# Break into two pieces for horizontal concat
left = df_2.iloc[np.arange(0,df_2.shape[0], 2)]
right = df_2.iloc[np.arange(1, df_2.shape[0], 2)]

# Reindex by ID so that pd.concat can properly match rows
left['old_index'] = left.index.values
left.index = left['ID']
right['old_index'] = right.index.values
right.index = right['ID']
right.columns = [c + '_2' for c in right.columns]  # Rename right side columns so we can groupby(['ID'])

# Horizontal concat
temp = pd.concat([left, right], axis=1)  # with possible duplicates for each unique (ID, Reference) tuple
temp.index = np.arange(temp.shape[0])  
temp

             ID Reference SampleType  old_index          ID_2 Reference_2  \
0  TCGA-AB-0001    GRCh37     Normal          3  TCGA-AB-0001      GRCh37
1  TCGA-AB-0001      HG19     Normal          2  TCGA-AB-0001        HG19
2  TCGA-AB-0002    GRCh37     Normal          6  TCGA-AB-0002      GRCh37
3  TCGA-AB-0002      HG19     Normal          5  TCGA-AB-0002        HG19

  SampleType_2  old_index_2
0        Tumor           11
1        Tumor            0
2        Tumor            9
3        Tumor            7

如果我理解正确，我们只想为每个ID保留一行，按照priority = ['GRCh37', 'HG19', 'HG18']的顺序选择它们

##
# Second level of filtering using priority list
##
priority = ['GRCh37', 'HG19', 'HG18']
g = temp.groupby(['ID'])

def filter_2(grp, priority = ['GRCh37', 'HG19', 'HG18']):
    pos = np.argsort(grp['Reference'], priority).iloc[0]
    idx = grp.index[pos]
    return grp.loc[idx, :]

final = temp.groupby(['ID']).apply(filter_2)
final.index = np.arange(final.shape[0])

这就产生了我对最终期望输出的理解。注意：这与原始示例不同，因为我在输入df中展开了。在

final

             ID Reference SampleType  old_index          ID_2 Reference_2  \
0  TCGA-AB-0001    GRCh37     Normal          3  TCGA-AB-0001      GRCh37
1  TCGA-AB-0002    GRCh37     Normal          6  TCGA-AB-0002      GRCh37

  SampleType_2  old_index_2
0        Tumor           11
1        Tumor            9

Answer 2

为了创建新的数据帧，您可以使用pandas条件切片：（在您的问题中，您在索引5行的数据框NORMAL上犯了一个错误，SampleType应该是Normal而不是{}）

NORMAL = df[df['SampleType']=='Normal'].copy()
TUMOR = df[df['SampleType']=='Tumor'].copy()

或者，如果你有机会拥有'normal'和'tumor'之外的任何东西，你不想得到除了'normal'之外的所有东西：

^{pr2}$

然后，为了删除重复项并保留特定值，可以创建另一列，该列保留相同的信息，但由整数组成（比字符串列表更容易排序）：

NORMAL['Whatever'] = 0
TUMOR['Whatever'] = 0

当然，可以在拆分数据帧df之前执行此操作（然后只对一个数据帧执行此操作，而不是对两个数据帧执行此操作）。填写此栏：

NORMAL.ix[NORMAL['Reference'] == 'HG19','Whatever'] = 1
TUMOR.ix[TUMOR['Reference'] == 'HG19','Whatever'] = 1
NORMAL.ix[NORMAL['Reference'] == 'HG18','Whatever'] = 2
TUMOR.ix[TUMOR['Reference'] == 'HG18','Whatever'] = 2

然后按此新列排序，删除重复项，只保留第一行：

NORMAL.sort_values(by = 'Whatever', inplace = True)
NORMAL.drop_duplicates(subset = 'ID',inplace = True)
TUMOR.sort_values(by = 'Whatever', inplace = True)
TUMOR.drop_duplicates(subset = 'ID',inplace = True)

为了得到预期的输出，删除临时列，然后按索引进行处理：

NORMAL.drop('Whatever',1,inplace = True)
NORMAL.sort_index(inplace = True)
TUMOR.drop('Whatever',1,inplace = True)
TUMOR.sort_index(inplace = True)

输出：

Out[3]: NORMAL
    ID              Reference   SampleType
3   TCGA-AB-0001    GRCh37      Normal
6   TCGA-AB-0002    GRCh37      Normal


Out[32]: TUMOR
    ID              Reference   SampleType
0   TCGA-AB-0001    HG19        Tumor
8   TCGA-AB-0003    GRCh37      Tumor
9   TCGA-AB-0002    GRCh37      Tumor