r - 基于第二列增加列

Question

考虑这样的数据框:

dat <- data.frame(name = c("John", "John", "John", "John", "Sam", "Sam", "Sam", "Sam", "Emily", "Emily", "Emily", "Emily", "Alex", "Alex", "Alex", "Alex"),
                  grade = c(9, NA, NA, 12, 10, NA, NA, NA, 9, 10, 10, 11, NA, 11, 11, NA),
                  year = c(2007, 2008, 2009, 2010, rep(c(2018, 2019, 2019, 2020), 3)))

    name grade year
1   John     9 2007
2   John    NA 2008
3   John    NA 2009
4   John    12 2010
5    Sam    10 2018
6    Sam    NA 2019
7    Sam    NA 2019
8    Sam    NA 2020
9  Emily     9 2018
10 Emily    10 2019
11 Emily    10 2019
12 Emily    11 2020
13  Alex    NA 2018
14  Alex    11 2019
15  Alex    11 2019
16  Alex    NA 2020

我想根据年份填写年级中缺失的NA。例如，Sam 2018 年是 10 年级，2019 年他将是 11 年级:

dat_desired <- data.frame(name = c("John", "John", "John", "John", "Sam", "Sam", "Sam", "Sam", "Emily", "Emily", "Emily", "Emily", "Alex", "Alex", "Alex", "Alex"),
                  grade = c(9, 10, 11, 12, 10, 11, 11, 12, 9, 10, 10, 11, 10, 11, 11, 12),
                  year = c(2007, 2008, 2009, 2010, rep(c(2018, 2019, 2019, 2020), 3)))

    name grade year
1   John     9 2007
2   John    10 2008
3   John    11 2009
4   John    12 2010
5    Sam    10 2018
6    Sam    11 2019
7    Sam    11 2019
8    Sam    12 2020
9  Emily     9 2018
10 Emily    10 2019
11 Emily    10 2019
12 Emily    11 2020
13  Alex    10 2018
14  Alex    11 2019
15  Alex    11 2019
16  Alex    12 2020

我该怎么做？我试过:

dat %>% 
  group_by(name) %>% 
  fill(grade, .direction = "updown")

这不起作用，因为它没有考虑年份。添加 group_by(year) 也没有帮助，因为这样就不会替换 NA。

Answer 1

可以尝试:

dat %>%
  group_by(name) %>%
  mutate(
    grade = coalesce(grade, first(grade[!is.na(grade)]) - (first(year[!is.na(grade)]) - year))
  )

输出:

# A tibble: 16 x 3
# Groups:   name [4]
   name  grade  year
   <fct> <dbl> <dbl>
 1 John      9  2007
 2 John     10  2008
 3 John     11  2009
 4 John     12  2010
 5 Sam      10  2018
 6 Sam      11  2019
 7 Sam      11  2019
 8 Sam      12  2020
 9 Emily     9  2007
10 Emily    10  2008
11 Emily    10  2009
12 Emily    11  2010
13 Alex     10  2018
14 Alex     11  2019
15 Alex     11  2019
16 Alex     12  2020