基于顺序行值创建新数据帧

Question

我有一个由名字和年份组成的dataframe，并有一个虚拟变量来判断名字是否发生在一年中。

null

   Name x2017 x2018 x2019
1 Terry     1     1     0
2   Sam     0     0     1
3   Nic     0     1     1
4 Sarah     0     1     1

data.frame(
  Name = c("Terry", "Sam", "Nic", "Sarah"), 
  x2017 = c(1, 0, 0, 0), 
  x2018 = c(1, 0, 1, 1), 
  x2019 = c(0, 1, 1, 1)
  )

  Year Total New
1 2017     1   1
2 2018     3   2
3 2019     3   1

谢谢！

Answer 1

也许这就是你要找的：

1. 使用tidy::pivot_longe
将形状重新设置为长格式
2. 按名称分组，并使用DPLYR::LAG添加个人是否为新人的指示符
3. 按年份汇总

d <- data.frame(
  Name = c("Terry", "Sam", "Nic", "Sarah"), 
  x2017 = c(1, 0, 0, 0), 
  x2018 = c(1, 0, 1, 1), 
  x2019 = c(0, 1, 1, 1)
) 

library(dplyr)
library(tidyr)

d %>% 
  tidyr::pivot_longer(-Name, names_to = "year") %>% 
  mutate(year = gsub("^x", "", year)) %>%
  group_by(Name) %>% 
  mutate(new = as.numeric(value == 1 & lag(value) == 0),
         new = ifelse(is.na(new), value, new)) %>% 
  ungroup() %>% 
  group_by(year) %>% 
  summarise(total = sum(value), new = sum(new))
#> # A tibble: 3 x 3
#>   year  total   new
#>   <chr> <dbl> <dbl>
#> 1 2017      1     1
#> 2 2018      3     2
#> 3 2019      3     1