如何在R语言中将Excel主分类（技能流/家庭流）转换为关联列？

需求说明 #

需要将Excel中以「技能流」「家庭流」为主分类的结构化数据，转换为主分类单独成列、子分类与主分类一一对应的规范格式，同时移除技能流中不需要的「November onshore」子分类。

实现步骤与代码 #

1. 安装并加载依赖包

需要用到readxl读取Excel，tidyverse完成数据清洗：

# 首次运行需安装包
install.packages(c("readxl", "tidyverse"))

# 加载包
library(readxl)
library(tidyverse)

2. 读取Excel数据

目标数据来自「迁移趋势统计包2021-22」，根据实际工作表名调整sheet参数：

# 跳过无关表头行（根据实际数据结构调整skip数值）
df <- read_excel("migration_trends_statistical_package_2021_22.xlsx", 
                 sheet = "visa_grants", 
                 skip = 5)

3. 处理主分类列（填充合并单元格空值）

原Excel主分类为合并单元格，读取后子分类行的主分类值为空，需向下填充关联：

# 重命名分类列（假设第一列为分类列，可根据实际调整）
colnames(df)[1] <- "category"

# 识别并标记主分类
df <- df %>%
  mutate(main_category = case_when(
    str_detect(category, "技能流|Skill stream") ~ "技能流",
    str_detect(category, "家庭流|Family stream") ~ "家庭流",
    TRUE ~ NA_character_
  )) %>%
  # 向下填充主分类，让子分类关联到对应主分类
  fill(main_category, .direction = "down")

4. 过滤无效子分类并整理数据

筛选有效子分类，移除不需要的项，保留规范结构：

clean_df <- df %>%
  # 过滤主分类行本身（只保留子分类数据）
  filter(category != main_category) %>%
  # 移除技能流中的November onshore（兼容中英文匹配）
  filter(!(main_category == "技能流" & str_detect(category, "November onshore|境内十一月"))) %>%
  # 可选：将子分类统一翻译为中文
  mutate(sub_category = case_when(
    str_detect(category, "Employer sponsored") ~ "雇主担保类",
    str_detect(category, "State Territory Nominated") ~ "州/领地提名类",
    str_detect(category, "Regional") ~ "偏远地区类",
    str_detect(category, "Partner") ~ "配偶类",
    str_detect(category, "Parent") ~ "父母类",
    str_detect(category, "Child") ~ "子女类",
    TRUE ~ category # 保留未匹配的子分类原名
  )) %>%
  # 整理输出列顺序
  select(main_category, sub_category, everything())

5. 查看最终结果

print(clean_df)

注意事项 #

• skip参数需根据Excel实际表头行数调整，确保读取到正确的数据起始行
• 分类匹配的字符串需根据数据实际的中英文格式微调
• 原数据中的数值列会被自动保留，无需额外修改

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