Reshaping data

README.md

Reshaping data

My colleague had a set of temperature readings with one row per date. Another colleague needed to visualize the data in a tool that required one row per day of the month with columns for each year value. He was working with the data in a SQL database, but neither of us knew off the top of our heads the best way to do this. This gist is a number of examples of how to approach the problem.

pivot.R

#!/usr/bin/env Rscript

# Reshape data with one row per day of the year and a column for the
# temperature reading for each year in the dataset.
#
# Usage: cat summers.csv | ./pivot.R

library(reshape)

data = read.csv(file('stdin'))
data$day_of_year <- sprintf("%02d/%02d", data$month, data$day)
data <- melt(data, id=c('day_of_year', 'year'), measure=c('tmax'))
data <- cast(data, day_of_year ~ ...)
write.csv(data, stdout())

pivot.sql

-- Show tmax values by day, with one column for year
-- This works with PostgreSQL, though there are likely similar techniques that work with other RDBMs
-- This kind of sucks because you can't generate the output columns dynamically, though
-- you could write a custom function that generates text as described as
-- http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
-- You can leverage your editor like I did to quickly generate the tmax_YYYY column definitions.
SELECT * FROM crosstab(
    'SELECT (lpad(month::text, 2, ''0'') || ''/''  || lpad(day::text, 2, ''0'')) AS monthday, year, tmax FROM summers ORDER BY 1',
    'SELECT DISTINCT year FROM summers ORDER BY 1'
)
AS pivot(
    day text,
    tmax_1959 character varying(3),
    tmax_1960 character varying(3),
    tmax_1961 character varying(3),
    tmax_1962 character varying(3),
    tmax_1963 character varying(3),
    tmax_1964 character varying(3),
    tmax_1965 character varying(3),
    tmax_1966 character varying(3),
    tmax_1967 character varying(3),
    tmax_1968 character varying(3),
    tmax_1969 character varying(3),
    tmax_1970 character varying(3),
    tmax_1971 character varying(3),
    tmax_1972 character varying(3),
    tmax_1973 character varying(3),
    tmax_1974 character varying(3),
    tmax_1975 character varying(3),
    tmax_1976 character varying(3),
    tmax_1977 character varying(3),
    tmax_1978 character varying(3),
    tmax_1979 character varying(3),
    tmax_1980 character varying(3),
    tmax_1981 character varying(3),
    tmax_1982 character varying(3),
    tmax_1983 character varying(3),
    tmax_1984 character varying(3),
    tmax_1985 character varying(3),
    tmax_1986 character varying(3),
    tmax_1987 character varying(3),
    tmax_1988 character varying(3),
    tmax_1989 character varying(3),
    tmax_1990 character varying(3),
    tmax_1991 character varying(3),
    tmax_1992 character varying(3),
    tmax_1993 character varying(3),
    tmax_1994 character varying(3),
    tmax_1995 character varying(3),
    tmax_1996 character varying(3),
    tmax_1997 character varying(3),
    tmax_1998 character varying(3),
    tmax_1999 character varying(3),
    tmax_2000 character varying(3),
    tmax_2001 character varying(3),
    tmax_2002 character varying(3),
    tmax_2003 character varying(3),
    tmax_2004 character varying(3),
    tmax_2005 character varying(3),
    tmax_2006 character varying(3),
    tmax_2007 character varying(3),
    tmax_2008 character varying(3),
    tmax_2009 character varying(3),
    tmax_2010 character varying(3),
    tmax_2011 character varying(3),
    tmax_2012 character varying(3),
    tmax_2013 character varying(3),
    tmax_2014 character varying(3),
    tmax_2015 character varying(3)
);

pivot_pandas.py

#!/usr/bin/env python

"""
Reshape data with one row per day of the year and a column for the
temperature reading for each year in the dataset.

Usage: cat summers.csv | python pivot_pandas.py

"""

import sys
import pandas as pd

def add_day_of_year(month, day):
    return "{:02d}/{:02d}".format(month, day)

if __name__ == "__main__":
    # Read CSV from stdin
    data = pd.read_table(sys.stdin,
            sep=',')
    # Add a day of year column to use as the index for the pivot
    data['day_of_year'] = map(add_day_of_year, data['month'], data['day']) 

    pivoted = data.pivot(index='day_of_year', columns='year', values='tmax')
    pivoted.to_csv(sys.stdout)

summers.csv