Data manipulation in R - creating a new data frame from an existing one-CodePudding

I have the below data frame in R

id <- c(112, 112,112)
case <- c("up","down","worse")
c1 <- c(0.12,0.24,0.09)
c2 <- c(0.11,0.14,0.06)
c3 <- c(0.15,0.34,0.04)
c4 <- c(0.16,0.44,0.03)
c5 <- c(0.17,0.94,0.01)
df3 <- data.frame(id,case,c1,c2,c3,c4,c5)

I am trying to create a new data frame with column names as id, case, value_in_period, and period For each id, the period will have values from 0-9. The value_in_period column will take values of c1,c2,c3,c4, and c5 from periods 0-5 and the rest of the values will be 0. A sample of desired output is attached below

I tried using inner join and transpose, but it doesn't seem to work. Any help will be appreciated.

CodePudding user response：

We reshape to 'long' format and then use complete to expand the data

library(dplyr)
library(tidyr)
out <- df3 %>% 
   pivot_longer(cols = c1:c5, names_to = NULL, 
      values_to = 'value_in_period') %>% 
   group_by(id, case = factor(case, levels = unique(case))) %>% 
  mutate(period = row_number()-1) %>%
  complete(period = 0:9, fill = list(value_in_period = 0)) %>% 
  ungroup %>%
  relocate(period, .after = 'value_in_period')

-output

> as.data.frame(out)
    id  case value_in_period period
1  112    up            0.12      0
2  112    up            0.11      1
3  112    up            0.15      2
4  112    up            0.16      3
5  112    up            0.17      4
6  112    up            0.00      5
7  112    up            0.00      6
8  112    up            0.00      7
9  112    up            0.00      8
10 112    up            0.00      9
11 112  down            0.24      0
12 112  down            0.14      1
13 112  down            0.34      2
14 112  down            0.44      3
15 112  down            0.94      4
16 112  down            0.00      5
17 112  down            0.00      6
18 112  down            0.00      7
19 112  down            0.00      8
20 112  down            0.00      9
21 112 worse            0.09      0
22 112 worse            0.06      1
23 112 worse            0.04      2
24 112 worse            0.03      3
25 112 worse            0.01      4
26 112 worse            0.00      5
27 112 worse            0.00      6
28 112 worse            0.00      7
29 112 worse            0.00      8
30 112 worse            0.00      9