r using dplyr 'gather' function

Question

I have a dataframe that looks like the picture showned below in 'input'.

I try to get 1 date per row (see picture below in 'desired output'). In other word, I try to do a kind of 'transpose' for each row.

Let's stipulate that the combination 'LC' and 'Prod' is a unique key.

Input

Desired output:

Info:

In my real dataset, there is some missing values in the quantity field (the colored region area). Thus, I should still be able to compute with missing values.

My try that fails

I have tried the following but it fails...

library("dplyr")
outputTest <- tbl_df(inputTest) %>%
  gather(date, value, c(inputTest$LC, inputTest$Prod))

outputTest

Source:

inputTest <- structure(list(LC = structure(c(1L, 3L, 1L, 2L), .Label = c("berlin", 
                                                            "munchen", "stutgart"), class = "factor"), Prod = structure(c(1L, 
                                                                                                                          2L, 2L, 1L), .Label = c("(STORE1)400096", "STORE2_00154"), class = "factor"), 
               PROD_TYPE = structure(c(1L, 2L, 2L, 1L), .Label = c("STORE1", 
                                                                   "STORE2"), class = "factor"), X2015.6.29 = c(20.08, 8.91, 
                                                                                                                11.38, 15.42), X2015.7.6 = c(20.66, 8.49, 10.91, 15.57), 
               X2015.7.13 = c(19.02, 8.55, 10.89, 14.6), X2015.7.20 = c(18.6, 
                                                                        7.95, 10.58, 14.31)), .Names = c("LC", "Prod", "PROD_TYPE", 
                                                                                                         "2015.6.29", "2015.7.6", "2015.7.13", "2015.7.20"), class = "data.frame", row.names = c(NA, 
                                                                                                                                                                                                     -4L))

Answer 1

Using gather, you can specify the columns you do not want to gather with the negation operator '-' (minus sign). The key in your case is the date, the value is the value, and LC, Prod, and PROD_TYPE serve as identifiers.

output <- as.data.frame(inputTest) %>%
        tidyr::gather(key = Date, value = Value, -LC, -Prod, -PROD_TYPE)

This yields:

         LC           Prod PROD_TYPE      Date Value
1    berlin (STORE1)400096    STORE1 2015.6.29 20.08
2  stutgart   STORE2_00154    STORE2 2015.6.29  8.91
3    berlin   STORE2_00154    STORE2 2015.6.29 11.38
4   munchen (STORE1)400096    STORE1 2015.6.29 15.42
5    berlin (STORE1)400096    STORE1  2015.7.6 20.66
6  stutgart   STORE2_00154    STORE2  2015.7.6  8.49
7    berlin   STORE2_00154    STORE2  2015.7.6 10.91
8   munchen (STORE1)400096    STORE1  2015.7.6 15.57
9    berlin (STORE1)400096    STORE1 2015.7.13 19.02
10 stutgart   STORE2_00154    STORE2 2015.7.13  8.55
11   berlin   STORE2_00154    STORE2 2015.7.13 10.89
12  munchen (STORE1)400096    STORE1 2015.7.13 14.60
13   berlin (STORE1)400096    STORE1 2015.7.20 18.60
14 stutgart   STORE2_00154    STORE2 2015.7.20  7.95
15   berlin   STORE2_00154    STORE2 2015.7.20 10.58
16  munchen (STORE1)400096    STORE1 2015.7.20 14.31