基于R中的部分字符串匹配（在另一个数据帧中）替换数据帧中的NAs

Question

目标：基于另一个数据帧中的“键”更改一个数据帧中的NAs列（类似于VLookUp，但仅在R中除外）

这里给定df1（为了简单起见，我只有6行。我拥有的键是50个状态的50行）：

这里给出了df2（这只是一个示例。我正在处理的真实数据帧有更多的行）：

任务：创建一个R函数，该函数循环并读取每个df2$Article行中的状态；然后将其与df1$State\u Name交叉引用，根据df2$Article中的状态，用相应的df1$缩写键替换df2$State中的NAs。我知道这有点过分。我一直在思考如何开始和完成这个难题。硬编码不是一个选项，因为我的真实数据表中有数千行这样的内容，随着我们向文本摘要中添加更多文章，硬编码将不断更新。

输出应如下所示：

注意：带有DC的第五个条目旨在为NA。

非常感谢您提供指南链接和/或关于如何编写代码的任何建议。非常感谢。

Answer 1

没有上面那么简洁，而是基本R方法：

# Unlist handling 0 length vectors: list_2_vec => function()
list_2_vec <- function(lst){
  # Coerce 0 length vectors to na values of the appropriate type: 
  # .zero_to_nas => function()
  .zero_to_nas <- function(x){
    if(identical(x, character(0))){
      NA_character_ 
    }else if(identical(x, integer(0))){
      NA_integer_
    }else if(identical(x, numeric(0))){
      NA_real_
    }else if(identical(x, complex(0))){
      NA_complex_
    }else if(identical(x, logical(0))){
      NA
    }else{
      x
    }
  }
  # Unlist cleaned list: res => vector
  res <- unlist(lapply(lst, .zero_to_nas))
  # Explictly define return object: vector => GlobalEnv()
  return(res)
}

# Classify each article as belonging to the appropriate state: 
# clean_df => data.frame
clean_df <- transform(
  df2,
  State = df1$Abbreviation[
    match(
      list_2_vec(
        regmatches(
          Article, 
          gregexpr(
            paste0(df1$State_Name, collapse = "|"), Article
          )
        )
      ),
      df1$State_Name
    )
  ]
)

# Data: 
df1 <- structure(list(Index = 1:6, State_Name = c("California", "Maryland", 
"New York", "Texas", "Virginia", "Washington"), Abbreviation = c("CA", 
"MD", "NY", "TX", "VA", "WA")), class = "data.frame", row.names = c(NA, -6L))

df2 <- structure(list(Index = 1:6, State = c(NA, NA, NA, NA, NA, NA), 
Article = c("Texas governor, Abbott, signs new abortion bill", 
"Effort to recall California governor Newsome loses steam", 
"New York governor, Cuomo, accused of manipulating Covid-19 nursing home data", 
"Hogan (Maryland, R) announces plans to lift statewide Covid restrictions", 
"DC statehood unlikely as Manchin opposes", "Amazon HQ2 causing housing prices to soar in northern Virginia"
)), class = "data.frame", row.names = c(NA, -6L))

Answer 2

您可以从State\u Name创建正则表达式模式，并使用str\u extract从文章中提取。使用匹配从df1中获取相应的缩写名称。

library(stringr)

df2$State <- df1$Abbreviation[match(str_extract(df2$Article, 
               str_c(df1$State_Name, collapse = '|')), df1$State_Name)]
df2$State
#[1] "TX" "CA" "NY" "MD" NA   "VA"

您还可以使用内置的state.name和state.abb而不是df1来获取州名和缩写。

这里有一种在循环中执行此操作的方法-

for(i in seq(nrow(df1))) {
  inds <- grep(df1$State_Name[i], df2$Article)
  if(length(inds)) df2$State[inds] <- df1$Abbreviation[i]
}
df2

#  Index State                                                                      Article
#1     1    TX                              Texas governor, Abbott, signs new abortion bill
#2     2    CA                     Effort to recall California governor Newsome loses steam
#3     3    NY New York governor, Cuomo, accused of manipulating Covid-19 nursing home data
#4     4    MD     Hogan (Maryland, R) announces plans to lift statewide Covid restrictions
#5     5  <NA>                                     DC statehood unlikely as Manchin opposes
#6     6    VA               Amazon HQ2 causing housing prices to soar in northern Virginia