R function which transforms data adds new column depending on counted grouped rows-CodePudding

I am new in R, so maybe anyone could help me. I have dataset like this

ID	Date	Revenue	Sales
1	2022.01.01	10	20
1	2022.02.01	11	21
1	2022.03.01	12	22
2	2022.01.01	13	33
2	2022.02.01	14	41
2	2022.03.01	15	51
2	2022.04.01	16	61

I need to transform this dataset, with group_by(ID). Also is important how much rows there is by group.
My transformed data must look like that:

ID	Revenue4	Revenue3	Revenue2	Revenue1	Sales4	Sales3	Sales2	Sales1
1	-	12	11	10	-	22	21	20
2	16	15	14	13	61	51	41	33

I need to do this by some function, because I have a lot of rows with different ID's and about 40 columns.

Thank You!

CodePudding user response：

One approach is to add a column containing the row number within each group, and then use pivot_wider using the row number in the new column names, combined with sales and revenue.

library(tidyverse)

df %>%
  group_by(ID) %>%
  mutate(rn = row_number()) %>%
  pivot_wider(id_cols = ID,
              names_from = rn,
              values_from = c("Revenue", "Sales"))

Output

     ID Revenue_1 Revenue_2 Revenue_3 Revenue_4 Sales_1 Sales_2 Sales_3 Sales_4
  <int>     <int>     <int>     <int>     <int>   <int>   <int>   <int>   <int>
1     1        10        11        12        NA      20      21      22      NA
2     2        13        14        15        16      33      41      51      61

CodePudding user response：

Same idea as the other answer, but with data.table:

##
#   In future provide this using dput(...)
#
df <- read.table(text="ID   Date    Revenue     Sales
1   2022.01.01  10  20
1   2022.02.01  11  21
1   2022.03.01  12  22
2   2022.01.01  13  33
2   2022.02.01  14  41
2   2022.03.01  15  51
2   2022.04.01  16  61", header=TRUE)
##
#   you start here
#
library(data.table)
setDT(df)[, N:=seq(.N), by=.(ID)] |> dcast(ID~N, value.var = c('Revenue', 'Sales'), sep="")
##    ID Revenue1 Revenue2 Revenue3 Revenue4 Sales1 Sales2 Sales3 Sales4
## 1:  1       10       11       12       NA     20     21     22     NA
## 2:  2       13       14       15       16     33     41     51     61