Dan L Dan L - 4 years ago 75
R Question

Writing looped web scrape to excel sheets

I am trying to scrape web data using a loop and write each instance into a different sheet in the same excel file. I have included the script below. The sheet names are correctly created, but the same dataframe is written into both sheets. Really appreciate any help. Thanks!

library(httr)
library(jsonlite)
library(tidyverse)
options(width=120)
xyz=c('"strStartDate": "2016-9-25", "strEndDate": "2016-11-1"','"strStartDate": "2016-8-25", "strEndDate": "2016-9-1"')
abc=c("2016-9-25","2016-8-25")
for (i in xyz){
for(j in abc){
ret <- fromJSON(content(POST("http://www.fangraphs.com/splitstool.aspx/getsplitleaders",
content_type_json(),
body = paste0('{"strPlayerId": "all", "strSplitArr": "[]", "strGroup": "season", "strPosition": "B", "strType": "1",',i,',"strSplitTeams": "false", "dctFilters": [], "strStatType": "player", "strAutoPt": "true"}')), as="text"))

df <- setNames(as_data_frame(ret$d$v), ret$d$k)
write.xlsx(df,"Slas.xlsx",append=TRUE,sheetName=j)}}

Answer Source
req.df<-data.frame(strStartDate = c("2016-9-25", "2016-8-25"),
                   strEndDate = c("2016-11-1", "2016-9-1"), stringsAsFactors = FALSE)

for(i in 1:nrow(req.df)){
  ret<- fromJSON(content(POST("http://www.fangraphs.com/splitstool.aspx/getsplitleaders",
                              content_type_json(),
                              body = paste0('{"strPlayerId": "all", "strSplitArr": "[]", "strGroup": "season", "strPosition": "B", "strType": "1",', 
                                            '"strStartDate": "', req.df[i,"strStartDate"] ,'", "strEndDate":"', req.df[i,"strEndDate"],'"',
                                            ',"strSplitTeams": "false", "dctFilters": [], "strStatType": "player", "strAutoPt": "true"}')), as="text"))

  df <- setNames(as_data_frame(ret$d$v), ret$d$k)
  write.xlsx(df,"Slas.xlsx",append=TRUE,sheetName=req.df[i,"strStartDate"])
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download