Pivoting one column while keeping the rest in R

Question

I am quite new to coding in R, and im working on cleaning and transforming some data.

I have looked at some different uses of reshape() and reshape2() for the cast function to help me, but i have not been able to succeed.

Basically what i would like to do is, to move one column up as column headers for the values.

This is my data:

#My data:
KEYFIGURE   LOCID    PRDID    KEYFIGUREDATE    KEYFIGUREVALUE
Sales       1001     A        2018-01-01       1
Promo       1001     A        2018-01-02       2
Disc        1001     A        2018-01-03       3
Sales       1001     B        2018-01-01       10
Promo       1001     B        2018-01-01       11
Disc        1002     B        2018-01-03       12

The result i would like to get:

LOCID    PRDID    KEYFIGUREDATE    Sales    Promo     Disc
1001        A        2018-01-01    1        2         
1001        A        2018-01-03    3 
1001        B        2018-01-01    10       11
1002        B        2018-01-03                       12

However, i am having quite some trouble figuring out how this is possibly in a smart way w. reshape package.

score 0 · Accepted Answer · answered Oct 10 '18 at 14:38

You can do this in one line with tidyr::spread:

library(tidyr)
df %>%
    spread(KEYFIGURE, KEYFIGUREVALUE)

  LOCID PRDID KEYFIGUREDATE Disc Promo Sales
1  1001     A    2018-01-01   NA    NA     1
2  1001     A    2018-01-02   NA     2    NA
3  1001     A    2018-01-03    3    NA    NA
4  1001     B    2018-01-01   NA    11    10
5  1002     B    2018-01-03   12    NA    NA

The way the function works is that you give it 2 variables in your dataset: the first is the variable to spread across multiple columns, while the second is the variable that sets the values to put in those cells.

Thanks! worked perfectly!! :) – Tue Herlevsen Oct 10 '18 at 16:08 — Tue Herlevsen, Oct 10 '18 at 16:08

Pivoting one column while keeping the rest in R

1 Answers1