Tidying "side-by-side" datasets

Question

I am working with a wide data set that looks like this:

library( tibble )
wide_data <- data_frame(month_1 = c("Jan", "Feb", "Mar", "Jun"),
                        score_1 = c(4, 5, 6, 4),
                        month_2 = c("Jan", "Mar", NA, NA),
                        score_2 = c(3, 2, NA, NA),
                        month_3 = c("Feb", "Mar", "Jun", NA),
                        score_3 = c(8, 7, 4, NA))

I would like to produce the following:

id month score
1  Jan   4
1  Feb   5
1  Mar   6
1  Jun   4
2  Jan   3
2  Mar   2
3  Feb   8  
3  Mar   7
3  Jun   4

Note that the months in the initial data set do not line up across observations. What's the best way to 'tidy' this up? Should I just read the underlying data into R two columns at a time and bind_rows? If so, what's the most elegant way to go about that?

`library(data.table) ; melt(setDT(wide_data), measure = patterns("^month", "^score"))` — David Arenburg, Feb 09 '17 at 07:31
Thanks! If my ID variables are more than just standard indices (e.g. date_S97 and date_S94 instead of date_1 and date_2) is there any way to recover those in my melted data frame? — joebruin, Feb 09 '17 at 08:06
See [this](http://stackoverflow.com/questions/41883573/convert-numeric-representation-of-variable-column-to-original-string-following/) perhaps — David Arenburg, Feb 09 '17 at 08:09

score -1 · Answer 1 · answered Feb 09 '17 at 07:13

You can bind several columns together by searching the column names for the relevant string. I'm using grep to achieve that here.

new <- data_frame(
    month = do.call( c, wide_data[ , grep( "^month_", names( wide_data ) ) ] ),
    score = do.call( c, wide_data[ , grep( "^score_", names( wide_data ) ) ] )
)

Which gives:

> new
# A tibble: 12 × 2
   month score
   <chr> <dbl>
1    Jan     4
2    Feb     5
3    Mar     6
4    Jun     4
5    Jan     3
6    Mar     2
7   <NA>    NA
8   <NA>    NA
9    Feb     8
10   Mar     7
11   Jun     4
12  <NA>    NA

Tidying "side-by-side" datasets

1 Answers1