如何在一个循环中运行多个查询，并将数据保存到dataframe中？

Question

我试图通过R运行几个SQL查询，每个查询由一个搜索参数(Zip代码)不同，对每个结果运行几个简单的计算，然后将发现保存到一个新的数据框架中。

我使用for循环来执行SQL查询，在执行计算时没有问题，但是在代码的末尾，我所收到的只是一个数据帧，其中一行重复了整个数据帧的长度。

我认为问题在于我如何将我的最后计算(dataframe:Togetic)传递到我的最终结果(dateframe: Togekiss)，但我没有能够正确地解决这个问题。

这是我的代码：

ALLZIP是我正在使用的所有邮政编码的列表。为了简洁起见，我删除了我的计算。

#Sample ALLZIP
ALLZIP <- c(10453,10457,10460,11238,11004)

ALLZIP<-as.data.frame(ALLZIP)

#Storage
Togekiss <-as.data.frame(matrix(NA,nrow = 15, ncol=length(ALLZIP$ALLZIP)))
names(Togekiss)<-ALLZIP$ALLZIP

j <- 1

for(i in (ALLZIP$ALLZIP)){

  print(i) 
  Togepi = sqlQuery(dbhandle,query=paste0("SELECT RaceCode,COUNT(*) AS 'num'FROM [DIS_SYNDROMIC].[dbo].[PatientVisits]WHERE YEAR([AdmissionDateTime])=2016 AND Zip LIKE '",ALLZIP$ALLZIP[1],"' GROUP BY RaceCode ORDER BY 2 DESC"))
  Togepi <-as.data.frame(Togepi)

  Togepi$RaceCode <- gsub("BL|OT|B|2056-0|AA|2052-9|2075-0","2054-5",Togepi$RaceCode)
  Togepi$RaceCode <- gsub("1004-1|1840-8","1002-5",Togepi$RaceCode)
  Togepi$RaceCode <- gsub("2041-2|2045-3|2031-3|2034-7|2039-6|AS|2032-1|2044-6|A|2037-0|2035-4|2033-9|2048-7|2050-3|2049-5|2051-1|2042-0|2030-5|2029-7|2040-4|2043-8|2038-8|2046-1|2047-9","2028-9",Togepi$RaceCode)
  Togepi$RaceCode <- gsub("2096-6|2091-7|2082-6|2500-7|2093-3|2076-8|2079-2|2087-5|2090-9|2088-3|2086-7|2081-8|2101-4|2098-22036-2|2100-6|2097-4|2080-0|2094-1|2089-1|2104-8","2076-8",Togepi$RaceCode)
  Togepi$RaceCode <- gsub("OT|O","2131-1",Togepi$RaceCode)
  Togepi$RaceCode <- gsub("WH|W","2106-3",Togepi$RaceCode)

  Togepi<-tapply(Togepi$num,Togepi$RaceCode, FUN=sum)

  #Getting a new data frame

  EL<-sum(Togepi["1002-5"],Togepi["2028-9"],Togepi["2054-5"],Togepi["2076-8"],Togepi["2131-1"],Togepi["2106-3"])

  Togetic<-c(Togepi["1002-5"],Togepi["2028-9"],Togepi["2054-5"],Togepi["2076-8"],Togepi["2131-1"],Togepi["2106-3"],sum(Togepi)-sum(Togepi["1002-5"],Togepi["2028-9"],Togepi["2054-5"],Togepi["2076-8"],Togepi["2131-1"],Togepi["2106-3"]),sum(Togepi),(Togepi["1002-5"]/sum(Togepi))*100,(Togepi["2028-9"]/sum(Togepi))*100,(Togepi["2054-5"]/sum(Togepi))*100,(Togepi["2076-8"]/sum(Togepi))*100,(Togepi["2131-1"]/sum(Togepi))*100,(Togepi["2106-3"]/sum(Togepi))*100, ((sum(Togepi)-EL)/sum(Togepi))*100)
  Togetic<-as.data.frame(Togetic)

  rownames(Togetic)<-c("American Indian or Alaska Native ","Asian","Black or African American","Pacific Islander","Other","White","No Info","Total","Percentage American Indian","Percentage Asian","Percentage Black or African American","Percentage Pacific Islander","Percentage Other","Percentage White","Percentage Missing")


  colnames(Togetic)<- paste0(ALLZIP$ALLZIP[i])

  Togekiss[,j]<-Togetic
  j <- j+1 
}

rownames(Togekiss)<-c("American Indian or Alaska Native ","Asian","Black or African American","Pacific Islander","Other","White","No Info","Total","Percentage American Indian","Percentage Asian","Percentage Black or African American","Percentage Pacific Islander","Percentage Other","Percentage White","Percentage Missing")

Togekiss<-round(Togekiss,digits=2)

Answer 1

好的，所以我不知道您的Togetic是什么样子，但是如果这确实是一个单独的行，应该添加到data.framework/矩阵中，这可能是一个可行的选择：

ALLZIP <- c(10453,10457,10460,11238,11004)
ALLZIP<-as.data.frame(ALLZIP)
allZipCodes <- apply(ALLZIP, 1, function(zipcode) {
  # Each zipcode here is basically ALLZIP[i,1]
  Togepi = as.data.frame( sqlQuery(dbhandle,query=paste0("SELECT RaceCode,COUNT(*) AS 'num'FROM [DIS_SYNDROMIC].[dbo].[PatientVisits]WHERE YEAR([AdmissionDateTime])=2016 AND Zip LIKE '",
                                          zipcode, #this is ALLZIP$ALLZIP[i]
  "' GROUP BY RaceCode ORDER BY 2 DESC")) )
  colnames(Togetic) <- zipcode
  return(Togetic) # This would be the row you wanted to update in Togekiss.
})
# allZipCodes contains a list of all your calculations, by executing a function on each of these elements with do.call we can get a combined matrix.
do.call(rbind, allZipCodes)

你能试试这个然后让我知道它是否有效吗？如果没有，你能告诉我Togetic里有什么吗？

Answer 2

您正在用每个循环迭代覆盖您的数据。试着用这个代替Togepi = sqlQuery() Togepi <-as.data.frame(Togepi)

temp.df = sqlQuery(YOUR SQL HERE)

if(i==1)
  {Togepi <-as.data.frame(temp.df)}
else
  {Togepi <- rbind(Togepi,s.data.frame(temp.df)}