R语言QC脚本自动化改造：实现年份参数化输入功能

核心实现步骤 #

• 使用readline()弹出输入提示，获取目标年份
• 将硬编码的年份值替换为变量，确保所有需年份的位置动态引用
• 增加简单输入验证，避免无效年份输入

修改后的完整脚本 #

# QC Check Function Building Blocks ---------------------------------------

# 弹出输入提示获取目标年份，并验证格式
target_year <- readline(prompt = "请输入需要进行QC的年份（如2021、2019）：")
# 验证输入是否为4位数字，不符合则终止脚本
if (!grepl("^\\d{4}$", target_year)) {
  stop("请输入有效的4位年份数字！")
}

#Bring in QC Results, QC Samples, and Results Tables and Filter Out Unneeded Columns
fn.importData(MDBPATH="C:/Users/h2edhmrs/Desktop/DASLER_TEST_COPY.mdb",
              TABLES=c("Analytes"))

"QC-Samples" <- `QC Samples` %>% select(LOC_ID, QC_SAMPLE, SAMPLE_DEPTH, QC_TYPE, ASSOC_SAMP)
"QC-Results" <- `QC Results` %>%  select(Loc_ID, QC_Sample, Units, Value, Text_Value, QC_Type, Storet_Num)
'Results_' <- `Results` %>% select(Loc_ID, Sample_Num, Units, Value, Storet_Num, Text_Value)

# 仅保留目标年份的QC样本记录
'QC-Samples' <- `QC-Samples` %>% filter(substr(QC_SAMPLE,1,4) == target_year)

#Rename QC-Results QC_Sample column to match name in QC-Samples table
colnames(`QC-Results`)[2] <- 'QC_SAMPLE'

#Merge Results and Samples table to get full target year QC records
QC_Results <- merge(`QC-Results`, `QC-Samples`[ ,c("QC_SAMPLE", "ASSOC_SAMP")], by = "QC_SAMPLE")

#Now must get associated samples into table
#To do this, I will rename "Sample_Num" column in Results_ table to ASSOC_SAMP and then merge the two
colnames(Results_)[2] <- "ASSOC_SAMP"
QCandResults <- merge(QC_Results, Results_[,c("ASSOC_SAMP","Storet_Num", "Units", "Value", "Text_Value")], by = c("ASSOC_SAMP", "Storet_Num"))

#rename columns of QCandResults for clarity
colnames(QCandResults)[c(1,5,6,7,9,10,11)] <- c("Sample_Num", "Units_QC", "Value_QC", "Text_Value_QC", "Units_Results", "Value_Results", "Text_Value_Results")

#matching Storet_num to display the parameter name in the QCandResults table
colnames(Analytes)[1] <- "Storet_Num"
QCandResults <- (merge(Analytes[,c("Storet_Num", "anl_short")], QCandResults, by = "Storet_Num"))
QCandResults <- QCandResults[-1]

#Making only dups and splits in the table
QCandResults <- subset(QCandResults, QC_Type ==  c("DUP", "SPL"))


# Relative Percent Difference Function ------------------------------------

#Developing Relative Percent Difference Function
RPD = \(x1, x2) {
  x1[is.na(x1)] = 0L; x2[is.na(x2)] = 0L
  abs((x1 - x2) / ((x1 + x2) * 0.5)) * 100
}

QCandResults <- transform(QCandResults, RPD = RPD(Value_Results, Value_QC))

#Creating pass column and then creating stat for how many QC failed
QCandResults <- transform(QCandResults, Pass = if_else(RPD > 20, "N", ""))

(sum(QCandResults$Pass == "N", na.rm=T) / nrow(QCandResults))

# 导出文件时使用目标年份命名
write.xlsx(QCandResults, paste0("QC", target_year, ".xlsx"))

关键说明 #

1. 年份输入与验证

   • readline()会在脚本运行时触发控制台输入，提示用户输入年份
   • 正则验证确保输入为4位数字，避免因无效输入导致后续逻辑出错

2. 动态年份替换

   • 过滤QC样本时，将原硬编码的"2023"替换为target_year变量
   • 导出Excel文件时，用paste0()动态生成带年份的文件名，无需手动修改

3. 兼容性

   • 保留原脚本所有核心逻辑，仅替换硬编码年份，新手无需改动其他部分
   • 后续人员只需输入目标年份即可一键运行，无需理解复杂代码逻辑

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