Writing looped web scrape to excel sheets

Question

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)}}

`"strType": "1",',i,'`, I think you mean `"strType": "1",',j,' ` ? for each j sheet, you're sending the same request containing the i parameter — Jean, Feb 07 '17 at 03:17
@waterling I might be misunderstanding - but with the i parameter, I am trying to send two separate requests- '"strStartDate": "2016-9-25", "strEndDate": "2016-11-1"' and '"strStartDate": "2016-8-25", "strEndDate": "2016-9-1"'. I know its very sloppy. — Dan L, Feb 07 '17 at 03:27
Right now I see that you use `j` purely for the sheetName. It's not used in the posting of the request. Since you are making the request AND writing to the sheet within the j loop, the request hasn't changed (and thus the response from the server hasn't changed as well) each time you wrote to the sheet. — Jean, Feb 07 '17 at 03:40

score 1 · Accepted Answer · answered Feb 07 '17 at 03:48

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"])
}

Writing looped web scrape to excel sheets

1 Answers1