Manipulate/tidy a data frame with gather / spread

[anon73295571]

I have a data frame that looks like this...

# A tibble: 22,536 x 6
   Article           RRP  Retailer          `03.05.2020` `02.05.2020` `01.05.2020`
   <chr>             <dbl> <chr>                   <dbl>        <dbl>        <dbl>
 1 Article X         67.5 Retailer A                55           51           58
 2 Article X         67.5 Retailer B                48           48           NA
 3 Article X         67.5 Retailer C                49           NA           67
 4 Article X         67.5 Retailer D                NA           39           57
 5 Article Y         75.5 Retailer A                NA           NA           NA
 6 Article Y         75.5 Retailer B                70           70           70
 7 Article Y         75.5 Retailer C                58           58           58
 8 Article Y         75.5 Retailer D                74           74           74
 9 Article Z         27.5 Retailer A                29           29           29
10 Article Z         27.5 Retailer B                27           27           27
# … with 22,526 more rows

... and I want it to look like this... where the date is in one own column

# A tibble
   Article           RRP   Retailer       Price    Date 
   <chr>             <dbl> <chr>          <dbl>    <date>        
 1 Article X         67.5   Retailer A     55      03.05.2020
 2 Article X         67.5   Retailer A     51      02.05.2020
 3 Article X         67.5   Retailer A     58      01.05.2020
 4 Article X         67.5   Retailer B     48      03.05.2020
 5 Article X         67.5   Retailer B     48      02.05.2020
 6 Article X         67.5   Retailer B     NA      01.05.2020
 7 Article X         67.5   Retailer C     49      03.05.2020

etc..

Any suggestions? Thank You very much

[FJCC]

This can be done with either the gather() or the newer pivot_longer() function. The column names in my example got modified by the read.csv function. If you data are already in a data frame, you will not have that problem.

library(tidyr)
DF <- read.csv("c:/users/fjcc/Documents/R/Play/Dummy.csv")
DF
#>      Article  RRP   Retailer X03.05.2020 X02.05.2020 X01.05.2020
#> 1  Article X 67.5 Retailer A          55          51          58
#> 2  Article X 67.5 Retailer B          48          48          NA
#> 3  Article X 67.5 Retailer C          49          NA          67
#> 4  Article X 67.5 Retailer D          NA          39          57
#> 5  Article Y 75.5 Retailer A          NA          NA          NA
#> 6  Article Y 75.5 Retailer B          70          70          70
#> 7  Article Y 75.5 Retailer C          58          58          58
#> 8  Article Y 75.5 Retailer D          74          74          74
#> 9  Article Z 27.5 Retailer A          29          29          29
#> 10 Article Z 27.5 Retailer B          27          27          27
DFtall <- gather(DF, key = Date, value = "Value", -Article, -RRP, -Retailer)
head(DFtall)
#>     Article  RRP   Retailer        Date Value
#> 1 Article X 67.5 Retailer A X03.05.2020    55
#> 2 Article X 67.5 Retailer B X03.05.2020    48
#> 3 Article X 67.5 Retailer C X03.05.2020    49
#> 4 Article X 67.5 Retailer D X03.05.2020    NA
#> 5 Article Y 75.5 Retailer A X03.05.2020    NA
#> 6 Article Y 75.5 Retailer B X03.05.2020    70

DFtall2 <- pivot_longer(DF, -c("Article", "RRP", "Retailer"))
head(DFtall2)
#> # A tibble: 6 x 5
#>   Article     RRP Retailer   name        value
#>   <fct>     <dbl> <fct>      <chr>       <int>
#> 1 Article X  67.5 Retailer A X03.05.2020    55
#> 2 Article X  67.5 Retailer A X02.05.2020    51
#> 3 Article X  67.5 Retailer A X01.05.2020    58
#> 4 Article X  67.5 Retailer B X03.05.2020    48
#> 5 Article X  67.5 Retailer B X02.05.2020    48
#> 6 Article X  67.5 Retailer B X01.05.2020    NA

^{Created on 2020-05-04 by the reprex package (v0.3.0)}