如何在tidyverse中拆分含年月及咬伤类型的列名生成新列？

我的数据集列名包含多类变量信息，例如列名Shrawan 2071 -Dog Bite中，Shrawan是月份、2071是年份、Dog Bite是咬伤类型；另一列Bhadra 2071 -Other Rabies Susceptible Animal Bite同理，Bhadra为月份、2071为年份、Other Rabies Susceptible Animal Bite为咬伤类型。该数据集包含12种月份、2种咬伤类型、10年的数据，请问如何在tidyverse中将这些列名中的分类信息拆分到单独的列中？

以下是数据集的前10行前30列结构：

structure(list(Province = c("1 Koshi Province", "1 Koshi Province", 
"1 Koshi Province", "1 Koshi Province", "1 Koshi Province", "1 Koshi Province", 
"1 Koshi Province", "1 Koshi Province", "1 Koshi Province", "1 Koshi Province"
), District = c("101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG", 
"101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG", 
"101 TAPLEJUNG", "101 TAPLEJUNG", "101 TAPLEJUNG"), Municipaltiy = c("10108 Sirijanga Rural Municipality", 
"10109 Sidingba Rural Municipality", "10107 Yangwarak Rural Municipality", 
"10105 Aatharai Tribeni Rural Municipality", "10105 Aatharai Tribeni Rural Municipality", 
"10105 Aatharai Tribeni Rural Municipality", "10106 Phungling Municipality", 
"10106 Phungling Municipality", "10104 Maiwakhola Rural Municipality", 
"10106 Phungling Municipality"), Ward = c("10108 Sirijanga 02", 
"10109 Sidingba 03", "10107 Yangwarak 05", "10105 Aatharai Tribeni 05", 
"10105 Aatharai Tribeni 04", "10105 Aatharai Tribeni 02", "10106 Phungling 01", 
"10106 Phungling 02", "10104 Maiwakhola 02", "10106 Phungling 08"
), `Health Facility` = c("AMBEGUDIN HP TAPLEJUNG", "ANGKHOP HP TAPLEJUNG", 
"CHAKSIBOTE HP TAPLEJUNG", "CHANGE BHSC TAPLEJUNG", "CHANGE HP TAPLEJUNG", 
"CHOKPUR HP TAPLEJUNG", "DANDAGAUN BHSC PHUNGLING 01 TAPLEJUNG", 
"DEULINGE BHSC TAPLEJUNG", "DHUNGESAGHU PHC TAPLEJUNG", "DOKHU HP TAPLEJUNG"
), `Shrawan 2071 -Dog Bite` = c(NA, NA, NA, NA, 2, NA, NA, NA, 
NA, 2), `Shrawan 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Bhadra 2071 -Dog Bite` = c(NA, NA, NA, 
NA, 2, NA, NA, NA, 1, NA), `Bhadra 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Ashwin 2071 -Dog Bite` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), `Ashwin 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Kartik 2071 -Dog Bite` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), `Kartik 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Mangsir 2071 -Dog Bite` = c(NA, NA, NA, 
NA, 1, NA, NA, NA, NA, NA), `Mangsir 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Poush 2071 -Dog Bite` = c(NA, NA, NA, NA, 
2, NA, NA, NA, NA, 1), `Poush 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Magh 2071 -Dog Bite` = c(NA, NA, NA, NA, 
1, NA, NA, NA, NA, NA), `Magh 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Falgun 2071 -Dog Bite` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), `Falgun 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Chaitra 2071 -Dog Bite` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), `Chaitra 2071 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Baishak 2072 -Dog Bite` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), `Baishak 2072 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Jestha 2072 -Dog Bite` = c(NA, NA, NA, 
NA, NA, NA, NA, NA, NA, 1), `Jestha 2072 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Asar 2072 -Dog Bite` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), `Asar 2072 -Other Rabies Susceptible Animal Bite` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Shrawan 2072 -Dog Bite` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", 
"data.frame"))

解决方案 #

可以用tidyverse工具链中的tidyr::pivot_longer和tidyr::separate组合完成需求，步骤如下：

1. 加载依赖包 #

library(tidyverse)

2. 数据转换与拆分 #

bites_hmis_clean <- bites_hmis %>%
  # 将宽格式转为长格式，保留地理信息列，其余列转为"类别-计数"对
  pivot_longer(
    cols = -c(Province, District, Municipaltiy, Ward, `Health Facility`),
    names_to = "category",
    values_to = "count"
  ) %>%
  # 按" - "拆分类别列，分离出日期信息和咬伤类型
  separate(category, into = c("date_info", "bite_type"), sep = " - ") %>%
  # 按空格拆分日期信息列，分离出月份和年份
  separate(date_info, into = c("month", "year"), sep = " ") %>%
  # 可选：将年份转为数值型，便于后续分析
  mutate(year = as.numeric(year))

代码说明 #

• pivot_longer：把存储计数的宽列转为长表结构，cols参数指定排除不需要转换的地理信息列，names_to存储原列名，values_to存储对应计数值。
• 第一次separate：用" - "作为分隔符，将原列名拆分为日期组合（月份+年份）和咬伤类型两部分。
• 第二次separate：用空格作为分隔符，把日期组合拆分为独立的月份和年份列。
• mutate：可选操作，将年份列转为数值类型，方便后续的时间序列分析或筛选。

处理后的数据会新增month、year、bite_type三列，每条记录对应一个地理区域+时间+咬伤类型的计数，符合整洁数据标准。

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