CODEWITHSUNDEEP
rreshape2
Slash
Slash

Reputation: 509

How do I cast data into non-equi columns?

I have a dataset of events, grouped by let like so:

set.seed(3)
events <- data.frame(
  let = rep(LETTERS[1:2], each=3),
  age = c(0,sample(1:20, size=2),
          0,sample(1:20, size=2)),
  value = sample(1:100, size=6))

  let age value
1   A   0    61
2   A   4    60
3   A  16    13
4   B   0    29
5   B   8    56
6   B   7    99

How can I cast the data frame so that age is multiple columns grouped into weeks? So for each column, take the value of the largest age that is less than or equal to 0, 7, 14, 21 days.

events.cast <- data.frame(
  let = LETTERS[1:2],
  T0_value = c(61,29),
  T1_value = c(60,99),
  T2_value = c(60,56),
  T3_value = c(13,56))

 let T0_value T1_value T2_value T3_value
1   A       61       60       60       13
2   B       29       99       56       56

Upvotes: 2

Views: 44

Answers (1)

akrun
akrun

Reputation: 887118

One option is to cut the 'age' into buckets, get the max row by that group and 'let', then reshape into 'wide' format

library(dplyr)
library(tidyr)
library(stringr)
events %>%
  group_by(grp = cut(age, breaks = c(-Inf,0, 7, 14, 21),
        labels = str_c("T", 0:3, "_value")), let) %>% 
  slice(which.max(value)) %>% 
  ungroup %>%
  select(-age) %>% 
  group_by(let) %>%
  complete(grp = unique(.$grp)) %>%
  fill(value) %>% 
  pivot_wider(names_from = grp, values_from = value)
# A tibble: 2 x 5
# Groups:   let [2]
#  let   T0_value T1_value T2_value T3_value
#  <chr>    <int>    <int>    <int>    <int>
#1 A           61       60       60       13
#2 B           29       99       56       56

data

events <- structure(list(let = c("A", "A", "A", "B", "B", "B"), age = c(0L, 
4L, 16L, 0L, 8L, 7L), value = c(61L, 60L, 13L, 29L, 56L, 99L)),
class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))

Upvotes: 2

Related Questions