How to spread a column in a Pandas data frame

I have the following pandas data frame:

import pandas as pd
import numpy as np
df = pd.DataFrame({
               'fc': [100,100,112,1.3,14,125],
               'sample_id': ['S1','S1','S1','S2','S2','S2'],
               'gene_symbol': ['a', 'b', 'c', 'a', 'b', 'c'],
               })

df = df[['gene_symbol', 'sample_id', 'fc']]
df

Which produces this:

Out[11]:
  gene_symbol sample_id     fc
0           a        S1  100.0
1           b        S1  100.0
2           c        S1  112.0
3           a        S2    1.3
4           b        S2   14.0
5           c        S2  125.0

How can I spread sample_id so that in the end I get this:

gene_symbol    S1   S2
a             100   1.3
b             100   14.0
c             112   125.0

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use pivot or unstack:

#df = df[['gene_symbol', 'sample_id', 'fc']]
df = df.pivot(index='gene_symbol',columns='sample_id',values='fc')
print (df)
sample_id       S1     S2
gene_symbol              
a            100.0    1.3
b            100.0   14.0
c            112.0  125.0

df = df.set_index(['gene_symbol','sample_id'])['fc'].unstack(fill_value=0)
print (df)
sample_id       S1     S2
gene_symbol              
a            100.0    1.3
b            100.0   14.0
c            112.0  125.0

But if duplicates, need pivot_table or aggregate with groupby or , mean can be changed to sum, median, …:

df = pd.DataFrame({
               'fc': [100,100,112,1.3,14,125, 100],
               'sample_id': ['S1','S1','S1','S2','S2','S2', 'S2'],
               'gene_symbol': ['a', 'b', 'c', 'a', 'b', 'c', 'c'],
               })
print (df)
      fc gene_symbol sample_id
0  100.0           a        S1
1  100.0           b        S1
2  112.0           c        S1
3    1.3           a        S2
4   14.0           b        S2
5  125.0           c        S2 <- same c, S2, different fc
6  100.0           c        S2 <- same c, S2, different fc

df = df.pivot(index='gene_symbol',columns='sample_id',values='fc')

ValueError: Index contains duplicate entries, cannot reshape

df = df.pivot_table(index='gene_symbol',columns='sample_id',values='fc', aggfunc='mean')
print (df)
sample_id       S1     S2
gene_symbol              
a            100.0    1.3
b            100.0   14.0
c            112.0  112.5

df = df.groupby(['gene_symbol','sample_id'])['fc'].mean().unstack(fill_value=0)
print (df)
sample_id       S1     S2
gene_symbol              
a            100.0    1.3
b            100.0   14.0
c            112.0  112.5

EDIT:

For cleaning set columns name to None and reset_index:

df.columns.name = None
df = df.reset_index()
print (df)
  gene_symbol     S1     S2
0           a  100.0    1.3
1           b  100.0   14.0
2           c  112.0  112.5

Method 2

you can also use pd.crosstab() method:

In [82]: pd.crosstab(index=df.gene_symbol, columns=df.sample_id, 
                     values=df.fc, aggfunc='mean') 
    ...:   .rename_axis(None,1) 
    ...:   .reset_index()
    ...:
Out[82]:
  gene_symbol     S1     S2
0           a  100.0    1.3
1           b  100.0   14.0
2           c  112.0  125.0

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0