How to groupby a column and output multiple columns separated by a tab - Python

Question

My goal is to group by 'Patient' column and output each patient in a single row =, followed by multiple columns from my input file in sequence. Ideally, I would prefer to have a counter in the column heads. Here is my input file example:

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

Expected output where it groupby 'Patient' Column, then loops over columns "Test", "gene" and "alteration" to create the following:

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

Ideally, it would be nice to # the Tests/gene/alteration i.e Test_1 gene_1 alteration_1 etc. However, I realize that made things complicated.

here is what I tried and I am unable to get the output of interest

df = pd.read_table(args.md, sep="\t")
df=pd.DataFrame(df)  #I used an input file  
values=grouped['gene'].apply('\t'.join).reset_index()

the output for this function 1) did not let me combine more than 'gene' column so if I use ['gene', 'Test'] it wouldn't give the desired output and 2) the joining '\t' would literally be output as '\t' instead of a tab

So then I tried

grouped=df.groupby('Patient')
print grouped
values=grouped['gene'].apply('\t'.join).reset_index()
print values
id_df = grouped['Test'].apply(lambda x: pd.Series(x.values)).unstack()
id_df = id_df.rename(columns={i: 'Test{}'.format(i + 1) for i in range(id_df.shape[1])})
result = pd.concat([id_df, values], axis=1)
print(result)

The structure of this second attempt did not conform what with I needed but it did provide me with a counter

I was wondering if anyone can provide some insight to get the desired output. I used the above commands from looking on SO but was not able to troubleshoot.

This is a classic `.pivot` problem, with the added complication that you first need to create your `column` variable, as the result of a `.cumcount`. Please see https://stackoverflow.com/questions/22798934/pandas-long-to-wide-reshape-by-two-variables and https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe. — ALollz, Feb 22 '19 at 20:54

score 1 · Accepted Answer · answered Feb 22 '19 at 20:53

1

Below one of the possible solution. Maybe not super elegant, but works.

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)

answered Feb 22 '19 at 20:53

Marek

678
3
13

Thank you - I think this worked nicely. But, it took all columns. How can I select the columns to include in the output? My original file has 100 columns - the above example was just for demonstration. I assume the change would be in "for c in range(2, len(g.columns)):" . Instead of len(g.columns), I would like to specific for instance columns 2, 4, 5, and 10 to only be included in the output – BioProgram Feb 22 '19 at 21:23

FabienP · Answer 2 · 2019-02-22T23:22:33.690

An approach using melt, groupby and unstack:

Data

Original

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

Tidy data

pd.DataFrame.melt allows to tidy this table:

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

Reshape

Using goupby and 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

Using crosstab

This gives equivalent result:

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