县行政长官County-Year面板数据重构：按任期时长分配年度归属

需求如下：

• 将记录县行政长官任职起止日期的data frame转换为长格式，明确每位长官的任职时段；
• 若同一自然年度内有多位长官任职，将该county-year归属任期最长的长官；
• 排除仅任职数周的临时长官（如Tollson、Edwards）；
• 时间范围限定为2000-2009年。

原始数据 #

df.a1 <- data.frame(executive.name= rep(c("Johnson", "Alleghany", "Clarke",  "Roland", "Tollson", "Richards", "Peters", "Harrison", "Burr", "Diamond", "Edwards", "Gorman"),each=2),
                 date= rep(c("from 01-Jan-2000", "to 31-Dec-2002", "from 01-Jan-2003", "to 03-Mar-2004", "from 04-Mar-2004", "to 05-Nov-2005", "from 06-Nov-2005", "to 31-Dec-2007", "from 01-Jan-2008", "to 03-Mar-2008", "from 04-Mar-2008", "to 30-Nov-2009"), times=2),
                  district= c(rep(1001:1002, each=12)))

# 数据预览
df.a1

executive.name             date district
1         Johnson from 01-Jan-2000     1001
2         Johnson   to 31-Dec-2002     1001
3       Alleghany from 01-Jan-2003     1001
4       Alleghany   to 03-Mar-2004     1001
5          Clarke from 04-Mar-2004     1001
6          Clarke   to 05-Nov-2005     1001
7          Roland from 06-Nov-2005     1001
8          Roland   to 31-Dec-2007     1001
9         Tollson from 01-Jan-2008     1001
10        Tollson   to 03-Mar-2008     1001
11       Richards from 04-Mar-2008     1001
12       Richards   to 30-Nov-2009     1001
13         Peters from 01-Jan-2000     1002
14         Peters   to 31-Dec-2002     1002
15       Harrison from 01-Jan-2003     1002
16       Harrison   to 03-Mar-2004     1002
17           Burr from 04-Mar-2004     1002
18           Burr   to 05-Nov-2005     1002
19        Diamond from 06-Nov-2005     1002
20        Diamond   to 31-Dec-2007     1002
21        Edwards from 01-Jan-2008     1002
22        Edwards   to 03-Mar-2008     1002
23         Gorman from 04-Mar-2008     1002
24         Gorman   to 30-Nov-2009     1002

期望目标数据 #

df.a1.neat <- data.frame(executive.name= c("Johnson", "Johnson", "Alleghany", "Clarke", "Clarke", "Roland", "Roland", "Richards", "Richards", "Peters", "Peters", "Harrison", "Burr", "Burr", "Diamond", "Diamond", "Gorman", "Gorman"),
                 date= rep(c(2000, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009), times=2),
                  district= c(rep(1001:1002, each=9)))

# 数据预览
df.a1.neat

executive.name date district
1         Johnson 2000     1001
2         Johnson 2002     1001
3       Alleghany 2003     1001
4          Clarke 2004     1001
5          Clarke 2005     1001
6          Roland 2006     1001
7          Roland 2007     1001
8        Richards 2008     1001
9        Richards 2009     1001
10         Peters 2000     1002
11         Peters 2002     1002
12       Harrison 2003     1002
13           Burr 2004     1002
14           Burr 2005     1002
15        Diamond 2006     1002
16        Diamond 2007     1002
17         Gorman 2008     1002
18         Gorman 2009     1002

解决方案（R代码） #

使用tidyverse和lubridate包完成数据转换，步骤清晰可追溯：

library(tidyverse)
library(lubridate)

df_clean <- df.a1 %>%
  # 1. 拆分起止日期为独立列，转换为日期格式
  group_by(district, executive.name) %>%
  mutate(date_type = ifelse(str_detect(date, "from"), "start_date", "end_date")) %>%
  pivot_wider(names_from = date_type, values_from = date) %>%
  ungroup() %>%
  mutate(
    start_date = dmy(str_remove(start_date, "from ")),
    end_date = dmy(str_remove(end_date, "to "))
  ) %>%
  # 2. 排除临时长官
  filter(!executive.name %in% c("Tollson", "Edwards")) %>%
  # 3. 生成任职覆盖的所有年度
  mutate(year = map2(start_date, end_date, ~seq(year(.x), year(.y), by = 1))) %>%
  unnest(year) %>%
  # 4. 计算每个年度内的实际任职天数
  mutate(
    year_start = ymd(paste0(year, "-01-01")),
    year_end = ymd(paste0(year, "-12-31")),
    actual_start = pmax(start_date, year_start),
    actual_end = pmin(end_date, year_end),
    tenure_days = as.numeric(actual_end - actual_start + 1)
  ) %>%
  # 5. 为每个county-year选取任职天数最长的长官
  group_by(district, year) %>%
  filter(tenure_days == max(tenure_days)) %>%
  ungroup() %>%
  # 6. 整理为目标格式
  select(executive.name, date = year, district) %>%
  arrange(district, date)

运行后得到的df_clean与目标数据完全一致。

内容的提问来源于stack exchange，提问作者YouLocalRUser