In this post I’ll look at replicating Hadley Wickham‘s gather() tool from his tidyr package using the pandas melt() function. Why would anyone want to do this? Well, Dr. Wickham’s work is beautiful, and the pandas.melt() function is not as elegant as the tidyr::gather() function. You may read Dr. Wickham’s pre-print paper here.

tidyr

The tidyr package contains functions to tidy data, which is to say, transform data sets so that each column represents a feature or variable, and each row represents a unique record, or observation. The RStudio Blog has a great introduction to the tidyr functions gather(), separate(), and spread(). The gather() function is used to convert wide data to long data. I’ll use an example from the RStudio Blog post to describe this operation. Suppose we have the following table describing heart rates for two different treatments:

We’d like to have treatments in one column, and heart rates in another column, like this:

The tidyr::gather() function achieves this deftly. We pass the name of the key column, treatment, and the name of the value column, heartrate, and then an expression describing the columns to be gathered which may take several forms. The lines 10-12 are all equivalent. The colon in line ten means “all columns from a to b”, and the minus in line twelve means, “not the name column”. If we cannot use a colon or a minus sign, then we may list the columns or variables of interest as individual trailing arguments, as in line eleven.

The %>% is a pipe that, in this example, passes messy through the gather() function. This is available to us through (wait for it) the magrittr package. (Get it? Magritte? Pipe?) The pipe operator allows you to chain a bunch of functions together instead of nesting them.

library(magrittr)
library(tidyr)

messy <- data.frame(
  name = c("Wilbur", "Petunia", "Gregory"),
  a = c(67, 80, 64),
  b = c(56, 90, 50)
)

tidy <- messy %>% gather( "treatment", "heartrate", a:b )
tidy <- messy %>% gather( "treatment", "heartrate", a, b )
tidy <- messy %>% gather( "treatment", "heartrate", -name )

pandas

We can emulate this in pandas using the melt() function, which is similar to the melt() function in the other Wickham package, reshape2. In Python, we can recreate our toy data set:

import pandas

names = [ 'Wilbur', 'Petunia', 'Gregory' ]
a = [ 67, 80, 64 ]
b = [ 56, 90, 50 ]
df = pandas.DataFrame({'names':names,'a':a,'b':b})

Then we can define a function that uses the pandas.melt() function to simplistically emulate the tidyr::gather() function.

def gather( df, key, value, cols ):
    id_vars = [ col for col in df.columns if col not in cols ]
    id_values = cols
    var_name = key
    value_name = value
    return pandas.melt( df, id_vars, id_values, var_name, value_name )

Then we can call our function as:

gather( df, 'drug', 'heartrate', ['a','b'] )

This gives us the following output:

So, our hacked together gather() function is still kind of clunky, and only a little bit less clunky than the pandas.melt() function, but it was fun learning about this stuff.