Extract data from pivot table to data frame (i.e. "reverse the pivot")

Question

I have the following data showing some numbers, broken down by year and month (the more recent happens to be first, but that's not a problem).

Is there (in R) a way to easily "reverse the pivot table" into a data frame with two columns, "data" and "value"? Of course, I could write a small program to do it (or, in this case, a few minutes of manual work would do it: remove the labels, put everything in one line, than in Excel paste the long line and fill in the timestamps with Fill Series), but I'm curious whether this can be done natively in R, since it will be useful in the future.

        Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

2002    315 45  397 750 380 343 794 110 777 323 746 735
2003    748 711 315 395 726 359 799 138 719 345 787 772
2004    109 776 764 738 384 368 396 700 384 398 340 781
2005    747 720 191 938 188 135 185 177 753 733 313 399
2006    726 316 713 135 103 794 751 165 348 701 798 128
2007    763 123 276 111 150 145 163 226 248 950 917 879
2008    167 873 775 130 184 780 444 115 107 325 781 701
2009    897 143 805 104 743 158 113 727 755 764 394 710
2010    349 743 180 762 359 744 102 775 109 178 369 799
2011    746 706 800 737 786 194 195 105 101 784 795 142
2012    124 794 141 126 284 207 877 803 832 175 140 763
2013    796 100 896 119 810 134 100 183 792 185 743 792

Answer 1

There is a number of ways to do this. In vanilla R this is one way:

First, lets read in your data into a data frame (you'd use read.csv, presumably, but I'm copypasting your table in, so I'm using this approach):

mytable=read.table(stdin(),header=TRUE,row.names=1)
0:         Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1: 2002    315 45  397 750 380 343 794 110 777 323 746 735
2: 2003    748 711 315 395 726 359 799 138 719 345 787 772
3: 2004    109 776 764 738 384 368 396 700 384 398 340 781
4: 2005    747 720 191 938 188 135 185 177 753 733 313 399
5: 2006    726 316 713 135 103 794 751 165 348 701 798 128
6: 2007    763 123 276 111 150 145 163 226 248 950 917 879
7: 2008    167 873 775 130 184 780 444 115 107 325 781 701
8: 2009    897 143 805 104 743 158 113 727 755 764 394 710
9: 2010    349 743 180 762 359 744 102 775 109 178 369 799
10: 2011    746 706 800 737 786 194 195 105 101 784 795 142
11: 2012    124 794 141 126 284 207 877 803 832 175 140 763
12: 2013    796 100 896 119 810 134 100 183 792 185 743 792
13: 

df1=stack(mytable)
mydf=data.frame(value=df1$values,month=df1$ind,
                year=factor(rownames(mytable))[row(mytable)])
head(mydf)
   value month year
1    315   Jan 2002
2    748   Jan 2003
3    109   Jan 2004
4    747   Jan 2005
5    726   Jan 2006
6    763   Jan 2007

There may well be more efficient approaches still, and there are numerous functions in packages that make this sort of task simple, but it's not so hard to just do something like this.

Answer 2

Using stats:::reshape you can come a long way (figure out how to get months to be named properly). Your question is similar to this one.

> reshape(x, direction = "long", varying = list(2:ncol(x)), idvar = "Year")
        Year time Jan
2002.1  2002    1 315
2003.1  2003    1 748
2004.1  2004    1 109
2005.1  2005    1 747
2006.1  2006    1 726
2007.1  2007    1 763

Another option would be to use package reshape2.

> library(reshape2)
> melt(x, id.vars = "Year")
    Year variable value
1   2002      Jan   315
2   2003      Jan   748
3   2004      Jan   109
4   2005      Jan   747
5   2006      Jan   726
6   2007      Jan   763
7   2008      Jan   167

Answer 3

I tried using the answer upvoted most, but it wasn't working for me, even though my data was in the same shape. I found this other method that's much easier:

Using package tidyr:

mydf1 <- data.frame(mydata %>%
                      pivot_longer(-year, names_to = "month", values_to = "value"))

for more info and more complex transformations see: https://tidyr.tidyverse.org/reference/pivot_longer.html