Create columns for the values of the first n weeks for each product code

user_18852209 0
Viewed 26

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!

dataframe r
1 Answers

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
user_134606020
Related