r - 按范围调整 pivot_wider 的整洁方法(由两列中的值给出)？

Question

我正在尝试转动它

df <- structure(list(id = c(38320858L, 38408709L, 38314694L, 38285286L, 
38332258L), type = c("recreation", "business", "friends", "business", 
"recreation"), start_week = c(6, 8, 6, 6, 7), end_week = c(11, 
10, 11, 10, 11)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

# A tibble: 5 x 4
        id type       start_week end_week
     <int> <chr>           <dbl>    <dbl>
1 38320858 recreation          6       11
2 38408709 business            8       10
3 38314694 friends             6       11
4 38285286 business            6       10
5 38332258 recreation          7       11

进入这个:

result <- structure(list(type = c("recreation", "business", "friends", 
"recreation", "business", "friends", "recreation", "business", 
"friends", "recreation", "business", "friends", "recreation", 
"business", "friends", "recreation", "friends"), week = c(6L, 
6L, 6L, 7L, 7L, 7L, 8L, 8L, 8L, 9L, 9L, 9L, 10L, 10L, 10L, 11L, 
11L), n = c(1L, 1L, 1L, 2L, 1L, 1L, 2L, 2L, 1L, 2L, 2L, 1L, 2L, 
2L, 1L, 2L, 1L)), row.names = c(NA, -17L), class = "data.frame")

#          type week n
# 1  recreation    6 1
# 2    business    6 1
# 3     friends    6 1
# 4  recreation    7 2
# 5    business    7 1
# 6     friends    7 1
# 7  recreation    8 2
# 8    business    8 2
# 9     friends    8 1
# 10 recreation    9 2
# 11   business    9 2
# 12    friends    9 1
# 13 recreation   10 2
# 14   business   10 2
# 15    friends   10 1
# 16 recreation   11 2
# 17    friends   11 1

注意

• 周是开始周、结束周、和之间的所有周
• n 是一个计数(如果 n = 0，则可以省略，例如第 11 周的“业务”被省略，换句话说，a group_by( , by = c("type", "week") 很好，如果有帮助的话)

我尝试过的

问题的棘手部分是处理周的范围，我想不出一个简洁的方法来做到这一点(即没有循环的方法)。

在这次尝试中，我只处理单周列 - 这只是说明性的 - 它不处理周范围，例如

df %>% 
  select(-id, -end_week) %>% 
  mutate(n=1) %>% 
  pivot_wider(names_from = start_week, values_from = n, values_fill = list(n=0)) %>% 
  pivot_longer(`6`:`7`)

# A tibble: 9 x 3
  type       name  value
  <chr>      <chr> <dbl>
1 recreation 6         1
2 recreation 8         0
3 recreation 7         1
4 business   6         1
5 business   8         1
6 business   7         0
7 friends    6         1
8 friends    8         0
9 friends    7         0

请注意，我的尝试毫无用处，因为它根本没有处理周的范围

Answer 1

library(data.table)

setDT(df)

df[,.(type=type,week=seq(start_week,end_week)),by=seq_len(nrow(df))][,.(n=.N),by=.(type,week)][order(week)]
#>           type week n
#>  1: recreation    6 1
#>  2:    friends    6 1
#>  3:   business    6 1
#>  4: recreation    7 2
#>  5:    friends    7 1
#>  6:   business    7 1
#>  7: recreation    8 2
#>  8:   business    8 2
#>  9:    friends    8 1
#> 10: recreation    9 2
#> 11:   business    9 2
#> 12:    friends    9 1
#> 13: recreation   10 2
#> 14:   business   10 2
#> 15:    friends   10 1
#> 16: recreation   11 2
#> 17:    friends   11 1

^{由 reprex package 创建于 2020-05-02 (v0.3.0)}