通过OpenAI API补全DataFrame缺失字段遇异常返回求助

核心问题排查 #

导致重复请求、重复输入词的常见原因：

• 逐行循环处理时，向量与数据框的类型转换问题，导致赋值后字段内容异常
• API返回的原始文本未做清洗，包含冗余空格、换行或重复表述
• 未对相同的瑞典语单词做缓存，重复发起API请求
• 缺乏API调用错误处理，失败请求的异常结果混入数据

优化后的代码实现 #

1. 增强API调用函数（含错误处理与结果清洗） #

library(httr)
library(stringr)

generate_completion <- function(prompt) {
  # 发送API请求
  response <- httr::POST(
    url = "https://api.openai.com/v1/engines/davinci/completions",
    httr::add_headers(`Authorization` = paste("Bearer", api_key)),
    httr::content_type("application/json"),
    body = list(
      prompt = prompt,
      max_tokens = 60,
      temperature = 0.7,
      n = 1,
      stop = "\n"  # 提前终止，避免冗余内容
    ),
    encode = "json"
  )
  
  # 处理HTTP错误
  if (httr::http_error(response)) {
    warning(paste("API请求失败，状态码:", httr::status_code(response)))
    return(NA_character_)
  }
  
  # 提取并清洗结果
  response_content <- httr::content(response)
  result <- str_trim(response_content$choices[[1]]$text)
  
  # 移除重复的同义词
  if (str_detect(prompt, "synonyms")) {
    result <- str_split(result, ",|;")[[1]] %>% 
      str_trim() %>% 
      unique() %>% 
      paste(collapse = ", ")
  }
  
  # 移除翻译中重复的原词
  if (str_detect(prompt, "translation")) {
    swe_word <- str_extract(prompt, "(?<=for ).+?(?= \\?)|(?<=word ).+?(?= \\?)")
    result <- str_remove_all(result, regex(paste0("^", swe_word, "[:punct:]*\\s*|\\s*", swe_word, "[:punct:]*$"), ignore_case = TRUE)) %>% 
      str_trim()
  }
  
  return(result)
}

2. 批量缓存+向量化填充（避免重复请求） #

fill_missing_values <- function(df) {
  # 提取需要补全的唯一瑞典语单词
  missing_def_swe <- unique(df$Swe[is.na(df$Def)])
  missing_syn_swe <- unique(df$Swe[is.na(df$Syn)])
  missing_eng_swe <- unique(df$Swe[is.na(df$Eng)])
  
  # 批量获取定义
  if (length(missing_def_swe) > 0) {
    def_map <- sapply(missing_def_swe, function(word) {
      generate_completion(paste("What is the definition of", word, "?"))
    })
    df$Def[is.na(df$Def)] <- def_map[df$Swe[is.na(df$Def)]]
  }
  
  # 批量获取同义词
  if (length(missing_syn_swe) > 0) {
    syn_map <- sapply(missing_syn_swe, function(word) {
      generate_completion(paste("List two distinct synonyms for", word, "?"))
    })
    df$Syn[is.na(df$Syn)] <- syn_map[df$Swe[is.na(df$Syn)]]
  }
  
  # 批量获取英文翻译
  if (length(missing_eng_swe) > 0) {
    eng_map <- sapply(missing_eng_swe, function(word) {
      generate_completion(paste("What is the English translation of the Swedish word", word, "?"))
    })
    df$Eng[is.na(df$Eng)] <- eng_map[df$Swe[is.na(df$Eng)]]
  }
  
  return(df)
}

# 执行填充
tab_test1 <- fill_missing_values(tab_test)

关键优化点说明 #

• 错误处理：添加HTTP状态码检查，避免错误结果写入数据框
• 结果清洗：自动移除冗余空格、换行，同义词去重，翻译中剔除原词重复
• 批量缓存：对相同单词只发起一次API请求，彻底解决重复请求问题
• 向量化操作：替代逐行循环，提升效率同时避免类型转换异常

内容的提问来源于stack exchange，提问作者E.Andersson