Cumulative multiplication after `group

Here is my R data.frame:

df <- data.frame(User=c('User 1', 'User 1', 'User 1', 'User 2', 'User 2', 'User 2'), Scores=c(3, 9, 2, 7, 3, 4))
print(df)

    User Scores
1 User 1      3
2 User 1      9
3 User 1      2
4 User 2      7
5 User 2      3
6 User 2      4

My goal is to group this data.frame by the User column and perform cumulative multiplication horizontally to the values from Scores.

What I have tried so far is:

df %>% group_by(User) %>% mutate(val=cumprod(Scores))

Which does cumulatively multiply the values from each group, but it doesn't do it horizontally.

The output I get (incorrect):

  User   Scores   val
  <chr>   <dbl> <dbl>
1 User 1      3     3
2 User 1      9    27
3 User 1      2    54
4 User 2      7     7
5 User 2      3    21
6 User 2      4    84

My desired output (correct):

  User   Scores_1   Scores_2   Scores_3
1 User 1        3         27         54
2 User 2        7         21         84

Any help appreciated.

CodePudding user response：

Use pivot_wider:

library(dplyr)
library(tidyr)

df %>% 
  group_by(User) %>% 
  mutate(val = cumprod(Scores),
         id = 1:n()) %>% 
  pivot_wider(-Scores, names_from = id, values_from = val, names_prefix = "Scores_")

#   User   Scores_1 Scores_2 Scores_3
# 1 User 1        3       27       54
# 2 User 2        7       21       84