Pivoting tables : from long to wide

Pivoting tables : from long to wide

As bioinformaticians, we often have to work with data that are not formatted the way we would need them to be. One case we might encounter is receiving data in a “long” format instead of receiving them in a more familiar “wide” format. For those of you familiar with the ggplot R package, you know this format very well. It’s the format required by ggplot to produce its nice graphs.


  genes   samples expression
1   BAD     S01    7.525395
2   BAD     S02    9.020534
3   BAD     S03   14.099557
4  TP53     S01    5.175252
5  TP53     S02    4.437941
6  TP53     S03    3.827847
7   A2M     S01    1.055729
8   A2M     S02   17.104853
9   A2M     S03   14.456857

  genes      S01       S02       S03
1   A2M 1.055730 17.104854 14.456857
2   BAD 7.525395  9.020534 14.099557
3  TP53 5.175253  4.437941  3.827848

Table1. Small dataset arranged in long (left) and wide (right) formats.

Starting from the long format, how can we get a wide matrix (where the genes are found as rows and the samples are found as columns) ?

There are several ways to do the conversion, but first let’s generate some data so you can try pivoting tables on your own.

Generating data

We can generate a small toy dataset containing 3 genes and 3 samples in R using the rep and rnorm functions.

# repeat each item in the vector n times
genes <- rep(c('BAD', 'TP53', 'A2M'),each=3)  
#[1] "BAD"  "BAD"  "BAD"  "TP53" "TP53" "TP53" "A2M"  "A2M"  "A2M"

# repeat the vector n times
samples <- rep(c('S01', 'S02', 'S03'),times=3)  
#[1] "S01" "S02" "S03" "S01" "S02" "S03" "S01" "S02" "S03"

# choose numbers from normal distribution randomly
expression <- abs(rnorm(9, 1, 10))
#[1] 17.623573 13.709192  4.529254  3.056479  2.407857 15.619984  2.309779
#[8]  8.497227 14.941006

df <- as.data.frame(cbind(genes, samples, expression))
df$expression <- as.numeric(as.character(df$expression))

This will generate the long format matrix presented in Table1. We can use the same idea to generate a big dataset of 10,000 genes and 1,000 samples*.

ngenes <- 10000
nsamples <- 1000
genes <- rep(paste('G',seq(ngenes), sep=''),each=nsamples)
samples <- rep(paste('S',seq(nsamples), sep=''),times=ngenes)
expression <- abs(rnorm(ngenes*nsamples, 1, 10))
df <- as.data.frame(cbind(genes, samples, expression))
df$expression <- as.numeric(as.character(df$expression))
write.table(df, file='longbig.txt', sep='\t', quote=F)

Pivoting in R

In R, the most used packages to pivot tables are : tidyr and reshape2. The functions dcast from reshape2 and spread from tidyr are quite easy to use. We just need to specify the data to pivot, how to organize the rows and columns and what values should be used to fill the matrix.

The dcast function expects a formula stating which variables should be used as row identifiers and which variable to put in columns. The var.value parameters indicates which value should fill the matrix. If there is more than one variable to identify each row, the formula will look like : varRow1 + varRow2 ~ varCol . For example, genes + organism ~ samples would put each sample as a column and have the gene and organism as row identifier.

With spread the only variables to specify are the ones to distribute as columns and the one to use to fill the matrix.

tb <- dcast(df, genes ~ samples, value.var="expression")

tb <- spread(df, samples, expression)

For the big dataset of 10,000 genes and 1,000 samples, the long format matrix contains 10,000,000 rows and 3 columns. It takes about 60-90 seconds to read this 331 MB file in R and 10 seconds to pivot the table.

Pivoting in Python

It is just as easy to go from long to wide in python using the pandas package. This library has a lot of useful functions for data analysis. Actually, since the addition nice R-like visualization functions, I've found myself using it more and more instead of using R.

Here is how I would pivot with pandas in python :

import pandas
df = pandas.read_csv('longbig.txt', sep='\t')
tb = pandas.pivot_table(df, values="expression", index=["genes"], columns="samples")

Reading the file is much faster but the pivoting step takes longer. In the end, pivoting the big dataset takes pretty much the same time as it took in R.


Python's pivot_table and R's dcast functions can also be used to aggregate data i.e. to reduce data dimensions using functions such as sum or mean. By default, dcast will use the length (or count) and pivot_table will use the mean as aggregation function, but this is up to you. If there is more than one row with the same identifier, the aggregation function will be applied automatically. For example, in the following code, ignoring the 'samples' in the pivoting step (ie: asking for only one column for 'expression') results in having more than one expression value per gene. Hence, a mean is computed.

tab = pandas.pivot_table(df, values="expression", index=["genes"])

## Mean of gene in samples
#G1        7.959172
#G10       8.017683
#G100      8.199654
#G1000     8.059022

df.ix[df.genes=='G1',:].mean() # Checking that the output is the mean
#expression    7.959172
#dtype: float64

To use aggregation on non-numeric values, don't forget to define your own aggregation function !

* If this step takes to much time in R, switch to python (pandas is required here):

import random 
import pandas 
ngenes = 10000
nsamples =  1000
genes = ['G%i' %i for i in range(ngenes)]*nsamples
samples = ['S%i' %i for i in range(nsamples)]*ngenes
expression =  [abs(random.gauss(1,10)) for i in range(nsamples*ngenes)]
df = pandas.DataFrame(zip(genes, samples), columns=['genes', 'samples'])
df['expression'] = expression
df.to_csv('longbig.txt', sep='\t')
By | 2016-11-14T15:33:58+00:00 November 14, 2016|Categories: Python, R|0 Comments

About the Author:

I’ve started in biochemistry but it is as a bioinformatician that I’ve been having fun for several years now : whether doing data analysis and visualization in R, building interactive web interfaces in javascript or exploring machine learning in python.

Leave A Comment