I have a data frame like this:
> df1 <- data.frame(product_code=c(201,201,201,201,201,201,301,301,301,301,301),
week=c(3,4,5,6,7,8,5,6,7,8,9),
first_week=c(3,3,3,3,3,3,5,5,5,5,5),
sales=c(12,14,13,15,17,18,34,41,38,43,47))
> df1
product_code week first_week sales
1 201 3 3 12
2 201 4 3 14
3 201 5 3 13
4 201 6 3 15
5 201 7 3 17
6 201 8 3 18
7 301 5 5 34
8 301 6 5 41
9 301 7 5 38
10 301 8 5 43
11 301 9 5 47
I would like to transform the data frame so that there is a column with sales for the first 4 weeks of each product in the data frame. The first week of each product is given in the data frame.
The output should look like this:
> df2 <- data.frame(product_code=c(201,301),
first_week=c(12,34),
second_week=c(14,41),
third_week=c(13,38),
fourth_week=c(15,43))
> df2
product_code first_week second_week third_week fourth_week
1 201 12 14 13 15
2 301 34 41 38 43
In total I would like to do that for the first 52 weeks of each product. Thanks in advance!
CodePudding user response:
Use tidyr::pivot_wider
:
library(tidyverse)
df1 %>%
group_by(product_code) %>%
mutate(n = row_number()) %>%
pivot_wider(product_code, names_from = n, names_prefix = "week", values_from = sales)
# A tibble: 2 x 7
# Groups: product_code [2]
product_code week1 week2 week3 week4 week5 week6
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 201 12 14 13 15 17 18
2 301 34 41 38 43 47 NA
To exactly match your expected output, you can use english::ordinal
:
df1 %>%
group_by(product_code) %>%
mutate(n = english::ordinal(row_number())) %>%
pivot_wider(product_code, names_from = n, names_glue = "{n}_week", values_from = sales)
product_code first_week second_week third_week fourth_week fifth_week sixth_week
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 201 12 14 13 15 17 18
2 301 34 41 38 43 47 NA