Multiple Variables (in columns), multiple Years(in columns) to reshape to flatfile in R

Question

I have data in the following format, with Variables, data by years and where A, B, C, D are the row id's.

        Variable 1     blank column       Variable 2
  2008 2009 2010 2011                2008 2009 2010 2011
A   1   5    9    13                   5   10   15   20
B   2   6    10   14                  25   30   35   40
C   3   7    11   15                  45   50   55   60
D   4   8    12   16                  65   70   75   80

I would like to get it in this format:

  Variable   Year  Data
A Variable1  2008  1
A Variable1  2009  5
.....
.....
D Variable2  2010  75
D Variable2  2011  80

I thought of using gather from library(tidyr) but I cant figure out how to do it. Sorry do not have a reproducible example.

structure(list(X1 = c(NA, "A", "B", "C", "D"), Variable1 = c(2008, 
1, 2, 3, 4), X3 = c(2009, 5, 6, 7, 8), X4 = c(2010, 9, 10, 11, 
12), X5 = c(2011, 13, 14, 15, 16), Variable1 = c(2008, 5, 25, 
45, 65), X7 = c(2009, 10, 30, 50, 70), X8 = c(2010, 15, 35, 55, 
75), X9 = c(2011, 20, 40, 60, 80)), .Names = c("X1", "Variable1", 
"X3", "X4", "X5", "Variable1", "X7", "X8", "X9"), row.names = c(NA, 
5L), class = "data.frame")

May be relevant, though not a dupe: https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format — , Apr 30 '18 at 09:59
Hello. I have added dput(). @dash2 sorry the example was not useful — Beginner, Apr 30 '18 at 11:47

score 1 · Accepted Answer · edited Nov 14 '21 at 17:47

library(tidyverse)

names(df) <- c("row_name", 
               paste(c(t(replicate(4, names(df)[1 + seq(1, length.out=floor(length(names(df))/4), by=4)]))),
                     df[1,-1], 
                     sep="_"))

df[-1,] %>%
  gather(Variable_Year, Data, -row_name) %>%
  separate(Variable_Year, into=c("Variable", "Year"), sep="_") %>%
  arrange(row_name, Variable, Year)

Note that you can't have non-unique values as "row names" of a dataframe so you may need to think of an alternative way to handle below row_name column.

Output is:

   row_name  Variable Year Data
1         A Variable1 2008    1
2         A Variable1 2009    5
...
31        D Variable2 2010   75
32        D Variable2 2011   80

Sample data:

df -> structure(list(row_name = c(NA, "A", "B", "C", "D"), Variable1_2008 = c(2008, 
1, 2, 3, 4), Variable1_2009 = c(2009, 5, 6, 7, 8), Variable1_2010 = c(2010, 
9, 10, 11, 12), Variable1_2011 = c(2011, 13, 14, 15, 16), Variable2_2008 = c(2008, 
5, 25, 45, 65), Variable2_2009 = c(2009, 10, 30, 50, 70), Variable2_2010 = c(2010, 
15, 35, 55, 75), Variable2_2011 = c(2011, 20, 40, 60, 80)), .Names = c("row_name", 
"Variable1_2008", "Variable1_2009", "Variable1_2010", "Variable1_2011", 
"Variable2_2008", "Variable2_2009", "Variable2_2010", "Variable2_2011"
), row.names = c(NA, 5L), class = "data.frame")

Multiple Variables (in columns), multiple Years(in columns) to reshape to flatfile in R

1 Answers1