如何按州分类大学？在R中为无州信息的高校数据集添加state列

问题背景 #

当前处理的高校数据集行名为大学名称，但缺少所属州的信息，需要新增一列state来标记每所高校对应的州。

现有代码 #

college = read.csv("College.csv",
                   header = T, na.strings = "?" )
row.names(college) = college[,1]
college=college[,-1]
college=na.omit(college)
# 将定性变量转换为定量变量用于绘图
college$Private = as.numeric(as.factor(college$Private))
college

解决方案 #

下面提供两种可行的方法来添加州信息：

方法1：从大学名称中提取州信息（基于字符串匹配） #

很多美国高校名称会包含州名或州缩写，可借助stringr包的字符串匹配功能提取：

# 加载必要工具包
library(stringr)

# 构建常见州缩写与全称的映射表（可根据数据集补充更多条目）
state_mappings = c(
  "AL" = "Alabama", "AK" = "Alaska", "AZ" = "Arizona", "AR" = "Arkansas",
  "CA" = "California", "CO" = "Colorado", "CT" = "Connecticut", "DE" = "Delaware",
  "FL" = "Florida", "GA" = "Georgia", "HI" = "Hawaii", "ID" = "Idaho",
  "IL" = "Illinois", "IN" = "Indiana", "IA" = "Iowa", "KS" = "Kansas",
  "KY" = "Kentucky", "LA" = "Louisiana", "ME" = "Maine", "MD" = "Maryland",
  "MA" = "Massachusetts", "MI" = "Michigan", "MN" = "Minnesota", "MS" = "Mississippi",
  "MO" = "Missouri", "MT" = "Montana", "NE" = "Nebraska", "NV" = "Nevada",
  "NH" = "New Hampshire", "NJ" = "New Jersey", "NM" = "New Mexico", "NY" = "New York",
  "NC" = "North Carolina", "ND" = "North Dakota", "OH" = "Ohio", "OK" = "Oklahoma",
  "OR" = "Oregon", "PA" = "Pennsylvania", "RI" = "Rhode Island", "SC" = "South Carolina",
  "SD" = "South Dakota", "TN" = "Tennessee", "TX" = "Texas", "UT" = "Utah",
  "VT" = "Vermont", "VA" = "Virginia", "WA" = "Washington", "WV" = "West Virginia",
  "WI" = "Wisconsin", "WY" = "Wyoming"
)

# 从行名（大学名称）中匹配州缩写
college$state = str_extract(row.names(college), paste(names(state_mappings), collapse = "|"))
# 可选：将缩写转换为完整州名
college$state = state_mappings[college$state]

方法2：利用专业高校数据集匹配 #

借助collegeScorecard包获取官方的高校州信息，实现精准匹配：

# 安装并加载工具包
install.packages("collegeScorecard")
library(collegeScorecard)

# 获取包含州信息的高校官方数据
scorecard_data = load_scorecard()

# 根据大学名称合并州信息
college = merge(college, scorecard_data[, c("instnm", "stabbr")], 
                by.x = "row.names", by.y = "instnm", all.x = TRUE)
# 整理列名与行名
colnames(college)[colnames(college) == "stabbr"] = "state"
row.names(college) = college$Row.names
college$Row.names = NULL

注意事项 #

• 方法1的字符串匹配可能存在误差，对于名称中不含州缩写的高校，需要手动补充对应州信息。
• 方法2需要联网获取数据，若出现名称匹配失败的情况，可使用stringdist包的模糊匹配功能优化匹配精度。

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