如何按列分组并输出由选项卡分隔的多个列

Question

我的目标是按“Patient”列分组，并在一行中输出每个患者=，然后按顺序从输入文件中输出多个列。理想情况下，我更希望在列标题中有一个计数器。下面是我的输入文件示例：

Patient Test panel gene alteration 1 A 54 APC E1345* 1 B 54 TP53 Y205H 1 C 54 APC V2278V 2 A 54 KRAS G12D 2 B 54 PTEN L25L 3 A 54 KRAS G13D 3 C 54 TP53 C141W 3 C 54 APC R876* 3 A 54 ERBB2 L663P

Answer 1

使用melt、groupby和unstack的方法：

数据

原始

In []: df
Out[]:
   Patient Test  panel   gene alteration
0        1    A     54    APC     E1345*
1        1    B     54   TP53      Y205H
2        1    C     54    APC     V2278V
3        2    A     54   KRAS       G12D
4        2    B     54   PTEN       L25L
5        3    A     54   KRAS       G13D
6        3    C     54   TP53      C141W
7        3    C     54    APC     R876*
8        3    A     54  ERBB2      L663P

整理数据

pd.DataFrame.melt允许整理此表：

In []: tidy = df.melt(id_vars=['Patient', 'Test'], value_vars=['panel', 'gene', 'alteration'])

In []: tidy
Out[]:
    Patient Test    variable   value
0         1    A       panel      54
1         1    B       panel      54
2         1    C       panel      54
3         2    A       panel      54
4         2    B       panel      54
5         3    A       panel      54
6         3    C       panel      54
7         3    C       panel      54
8         3    A       panel      54
9         1    A        gene     APC
10        1    B        gene    TP53
11        1    C        gene     APC
12        2    A        gene    KRAS
13        2    B        gene    PTEN
14        3    A        gene    KRAS
15        3    C        gene    TP53
16        3    C        gene     APC
17        3    A        gene   ERBB2
18        1    A  alteration  E1345*
19        1    B  alteration   Y205H
20        1    C  alteration  V2278V
21        2    A  alteration    G12D
22        2    B  alteration    L25L
23        3    A  alteration    G13D
24        3    C  alteration   C141W
25        3    C  alteration  R876*
26        3    A  alteration   L663P

重塑

使用goupby和unstack

In []: (tidy.groupby(['Patient', 'Test', 'variable'])  # group by three levels of interest
     ...:   .first()                                   # access values as a dataframe
     ...:   .unstack(level=[1,2]))                     # pivot on levels [1, 2] of multiindex
Out[]:
              value
Test              A                      B                      C
variable alteration  gene panel alteration  gene panel alteration  gene panel
Patient
1            E1345*   APC    54      Y205H  TP53    54     V2278V   APC    54
2              G12D  KRAS    54       L25L  PTEN    54        NaN   NaN   NaN
3              G13D  KRAS    54        NaN   NaN   NaN      C141W  TP53    54

使用交叉表

这就得到了相同的结果：

In []: pd.crosstab(tidy.Patient,                # index
                   [tidy.Test, tidy.variable],  # columns
                   values=tidy.value,
                   aggfunc='first')             # get first value
Out[]:
Test              A                      B                      C
variable alteration  gene panel alteration  gene panel alteration  gene panel
Patient
1            E1345*   APC    54      Y205H  TP53    54     V2278V   APC    54
2              G12D  KRAS    54       L25L  PTEN    54        NaN   NaN   NaN
3              G13D  KRAS    54        NaN   NaN   NaN      C141W  TP53    54

Answer 2

下面是一个可能的解决方案。也许不是很优雅，但很管用。你知道吗

grouped = df.groupby('Patient')

col = ['Patient']
data = []
for p, g in grouped:
    d = {'Patient': p}
    g.reset_index(inplace=True)
    for i, row in g.iterrows():
        for c in range(2, len(g.columns)):
            col_name = g.columns[c] + '_' + str(i + 1)
            d[col_name] = row[g.columns[c]]
            if col_name not in col:
                col.append(col_name)
    data.append(d)

df = pd.DataFrame(data, columns=col)