r - 计算一个值减去其组中第一个值之间的差值

Question

我有一个数据框 df，其中包含按城市、性别、年份和年龄分组的人口数据:

df <- data.frame(City=c("New York", "New York", "New York", "New York", "New York", 
             "Boston","Boston", "Boston", "Boston"),
             Gender=c("m","m","m", "f","f","m","m","f","f"),
             Year=c("2020","2021", "2022", "2020", "2021","2020","2021", "2020", "2021"),
             Age=c("1","1","1", "2","2","1","1","2","2"),
             Population=c("100", "105","110", "105", "110", "200","201", "220", "222"))

我需要为每一行计算与其组的第一个值(即 2020 年)的差异，以得出以下结果:

df2 <- data.frame(City=c("New York", "New York", "New York", "New York", "New York", "Boston","Boston", "Boston", "Boston"),
              Gender=c("m","m","m", "f","f","m","m","f","f"),
              Year=c("2020","2021", "2022", "2020", "2021","2020","2021", "2020", "2021"),
              Age=c("1","1","1", "2","2","1","1","2","2"),
              Population=c("100", "105","110", "105", "110", "200","201", "220", "222"),
              PopulationGrowth=c("0", "5","10", "0","5","0","1","0","2"))

谢谢!

Answer 1

df %>%
  group_by(City, Gender) %>%
  arrange(Year, .by_group = T) %>%
  mutate(Population = as.numeric(as.character(Population)),
         PopulationGrowth = Population - first(Population))

# # A tibble: 9 x 6
# # Groups:   City, Gender [4]
#   City     Gender Year  Age   Population PopulationGrowth
#   <fct>    <fct>  <fct> <fct>      <dbl>            <dbl>
# 1 Boston   f      2020  2            220                0
# 2 Boston   f      2021  2            222                2
# 3 Boston   m      2020  1            200                0
# 4 Boston   m      2021  1            201                1
# 5 New York f      2020  2            105                0
# 6 New York f      2021  2            110                5
# 7 New York m      2020  1            100                0
# 8 New York m      2021  1            105                5
# 9 New York m      2022  1            110               10

arrange 更改行的顺序。如果你想保留原来的顺序，试试这个:

df %>%
  group_by(City, Gender) %>%
  mutate(Population = as.numeric(as.character(Population)),
         PopulationGrowth = Population - first(Population, order_by = order(Year)))

# # A tibble: 9 x 6
# # Groups:   City, Gender [4]
#   City     Gender Year  Age   Population PopulationGrowth
#   <fct>    <fct>  <fct> <fct>      <dbl>            <dbl>
# 1 New York m      2020  1            100                0
# 2 New York m      2021  1            105                5
# 3 New York m      2022  1            110               10
# 4 New York f      2020  2            105                0
# 5 New York f      2021  2            110                5
# 6 Boston   m      2020  1            200                0
# 7 Boston   m      2021  1            201                1
# 8 Boston   f      2020  2            220                0
# 9 Boston   f      2021  2            222                2