how to pivot/unpivot (cast/melt) data frame?

r pivot-table reshape reshape2
pedrosaurio picture pedrosaurio · Nov 2, 2011 · Viewed 33.2k times · Source

How can I 'unpivot' a table? What is the proper technical term for this?

UPDATE: The term is called melt

I have a data frame for countries and data for each year

Country     2001    2002    2003
Nigeria     1       2       3
UK          2       NA       1

And I want to have something like 

Country    Year    Value
Nigeria    2001    1
Nigeria    2002    2
Nigeria    2003    3
UK         2001    2
UK         2002    NA
UK         2003    1

Answer

Roman Luštrik picture Roman Luštrik · Nov 2, 2011

I still can't believe I beat Andrie with an answer. :)

> library(reshape)
> my.df <- read.table(text = "Country     2001    2002    2003
   + Nigeria     1       2       3
   + UK          2       NA       1", header = TRUE)
> my.result <- melt(my.df, id = c("Country"))
> my.result[order(my.result$Country),]
     Country variable value
   1 Nigeria    X2001     1
   3 Nigeria    X2002     2
   5 Nigeria    X2003     3
   2      UK    X2001     2
   4      UK    X2002    NA
   6      UK    X2003     1

Related questions

Pivot Table-like Output in R?

I am writing a report that requires the generation of a number of pivot tables in Excel. I would like to think there is a way to do this in R so that I can avoid Excel. I would like …

Read More
How can I collapse a dataframe by some variables, taking mean across others

I need to summarize a data frame by some variables, ignoring the others. This is sometimes referred to as collapsing. E.g. if I have a dataframe like this: Widget Type Energy egg 1 20 egg 2 30 jap 3 50 jap 1 60 Then collapsing by Widget, …

Read More
melt / reshape in excel using VBA?

I'm currently adjusting to a new job where most of the work I share with colleagues is via MS Excel. I am using pivot tables frequently, and therefore need "stacked" data, precisely the output of the melt() function in the …

Read More