Reshape data set from wide to long format grouped by variable suffix

Question

Similar yet different to this post:Reshaping data.frame from wide to long format

I have a wide dataset with a unique ID variable and all other variables with a 4 digit year suffix:

ID MI1995 FRAC1995 MI1996 FRAC1996
1     2         3       2       4
7     3         10      12      1
10    1         2       1       1

I would like a long dataset grouped by the 4 digit variable suffix. So each ID should have 1 row per year of 4 digit suffix:

ID YEAR   MI  FRAC
1   1995   2   3
1   1996   2   4
7   1995   3   10
7   1996   12  1
10  1995   1   2
10  1996   1   1

Base/generic solutions are preferred.

The main questions here are, how do I establish automatic cutpoints for the "varying" parameter in reshape, and how do I supply the "timevar" parameter from the variable suffix?

This related post is a good start stackoverflow.com/questions/2185252/… — zx8754
@zx8754 thanks! have reviewed this post! Main differences is I have repeated grouping variables! — Mark

jay.sf jay.sf · Accepted Answer · 2019-04-25T07:00:35

Using reshape we can set the cutpoints with sep="".

reshape(d, idvar="ID", varying=2:5, timevar="YEAR", sep="", direction="long")
#         ID YEAR MI FRAC
# 1.1995   1 1995  2    3
# 7.1995   7 1995  3   10
# 10.1995 10 1995  1    2
# 1.1996   1 1996  2    4
# 7.1996   7 1996 12    1
# 10.1996 10 1996  1    1

Data

d <- structure(list(ID = c(1L, 7L, 10L), MI_1995 = c(2L, 3L, 1L),
                    FRAC_1995 = c(3L, 10L, 2L), MI_1996 = c(2L, 12L, 1L),
                    FRAC_1996 = c(4L, 1L, 1L)), row.names = c(NA, -3L),
               class = "data.frame")

Reshape data set from wide to long format grouped by variable suffix

1 Answers