0

I have a dataframe which structure is like this:

Year   Spain   England   Malta
2001   23      24        98
2007   34      99        87

And I want it to become like this one:

Country   Rank   Year
Spain     23     2001
Spain     34     2007
England   24     2001
England   99     2007
Malta     98     2001
Malta     87     2007

Is there a simple/smarter way of doing this using R (with no additional libraries) without having to loop over columns and transpose the columns adding one more name (Countries) to it?

ludas
  • 57
  • 6

2 Answers2

2

You can do this in base R

setNames(cbind(stack(df[,2:4]), df[,1]), c("Rank","Country","Year"))
  Rank Country Year
1   23   Spain 2001
2   34   Spain 2007
3   24 England 2001
4   99 England 2007
5   98   Malta 2001
6   87   Malta 2007

Data

df <- structure(list(Year = c(2001L, 2007L), Spain = c(23L, 34L), England = c(24L, 
99L), Malta = c(98L, 87L)), class = "data.frame", row.names = c(NA, 
-2L))
Andre Wildberg
  • 12,344
  • 3
  • 12
  • 29
1

You can use reshape2::melt()

df <- data.frame(year = c(2001, 2007), Spain = c(23, 34), England = c(24, 99), Malta = c(98,87))

library(reshape2)
df <- data.frame(melt(df[,-1]), year = c(2001, 2007))

Result

> df
  variable value year
1    Spain    23 2001
2    Spain    34 2007
3  England    24 2001
4  England    99 2007
5    Malta    98 2001
6    Malta    87 2007
Dion Groothof
  • 1,406
  • 5
  • 15