Tidying a dataframe using gather and ...?

Question

I have a dataframe with the below structure:

record <- c(seq_along(1:10))
store <- c(1, 2, 3, 4, 5, 1, 2, 3, 4, 5)
week <- c(1, 1, 1, 1, 1, 2, 2, 2, 2, 2)
sales_1 <- c(3, 3, 3, 3, 3, 2, 5, 1, 2, 10)
sales_2 <- c(1, 2, 4, 5, 6, 2, 3, 6, 1, 8)
price_1 <- runif(10, 2, 6)
price_2 <- runif(10, 2, 6)

df <- data_frame(record, store, week, sales_1, sales_2, price_1, price_2)

Suppose I want to gather this and transform it whereby the 'record', 'store' and 'week' columns are all preserved, but then I also create a new column called 'category', which represents the trailing numbers at the end of each 'sales_' and 'price_' column. Last, I would consolidate the values of the 'sales' and 'price' columns into two columns (simply 'sales' and 'price'). The result would look something like this:

record | store | week | category | sales | price
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  1        1      1        1         3      2.51
  1        1      1        2         1      5.50
  2        2      1        1         3      4.86

Original discussion came from here. Thanks to @markdly who predicted I would end up here...

Answer 1

You can gather sales and price columns, separate the key into the new header and category and then spread the header:

df %>% 
    gather(key, val, sales_1:price_2) %>% 
    separate(key, c('header', 'category'), sep='_') %>% 
    spread(header, val)

# A tibble: 20 x 6
#   record store  week category    price sales
# *  <int> <dbl> <dbl>    <chr>    <dbl> <dbl>
# 1      1     1     1        1 5.005186     3
# 2      1     1     1        2 4.184387     1
# 3      2     2     1        1 3.790764     3
# 4      2     2     1        2 4.668122     2
# ...