Storing Result from API Call in Data Frame through Loop in R-CodePudding

I would like to store a result of an API call in a data frame. The code should loop through different time periods and countries.

If I do one example, it's like this:

testapicall <- jsonlite::fromJSON("https.api.companyname/jurisdiction=eu_ger&date=2018-01-01:2018-01:31&api_token=XYZ")

testapicall[["results"]]$total_number

Now I want to to get this "total number" for different jurisdictions and date ranges. One column should be country name, one should be the date (e.g., 01-2018), and one should be the total_number.

To set up the loop, I've split the API key into 3 parts:

base_string1 <- "https.api.companyname/jurisdiction="
base_string2 <- "&date="
end_string <- "api_token=XYZ"

Then, I can create the dates and countries the dates and countries like this:

dates <- seq(as.Date("1990-01-01"), as.Date("2022-01-01"), by = "month")
dates <- paste(head(dates, -1), tail(dates-1, - 1), sep = ":")

countries<- paste0("eu_", c("fra", "ger"))

Now, I'd like to get the results for each country-date into a data frame, but this is the part I am not sure how to do. I know I have to make an empty data frame and then fill it somehow.


for (y in date){
  for(c in countries){
    api_string <-  paste0(base_string1,y, base_string2,c, end_string)
    json <- jsonlite::fromJSON(api_string) 
     json[["results"]]$total_number
  }
}

Any help is appreciated!

CodePudding user response：

You can use map_dfr from purrr to iterate over the countries and dates and generate a dataframe with a single row for each iteration. map_dfr will row bind all the dataframes together.

library(purrr)
map_dfr(dates, function(date){
    map_dfr(countries, function(country){
        api_string <-  paste0(base_string1, date, base_string2, country, end_string)
        json <- jsonlite::fromJSON(api_string)
        data.frame(country = country, 
                   date = date, 
                   total_number = json[["results"]]$total_number)
    })
})

CodePudding user response：

Consider expand.grid to build all possible pairwise combinations of country and month dates into data frame and then mapply to create a new column to retrieve the API data elementwise between each country and month range.

Also, consider a user-defined method that uses tryCatch (useful with API calls) to return NA on error and not stop on problematic urls.

# INPUTS
dates <- seq(as.Date("1990-01-01"), as.Date("2022-01-01"), by="month") 
countries <- paste0("eu_", c("fra", "ger"))

# USER-DEFINED METHOD
get_api_data <- function(cnty, rng) {
    url <- paste0(
        "https.api.companyname/jurisdiction=", cnty,
        "&date=", rng, 
        "api_token=XYZ"
    )

    tryCatch({
        api_response <- jsonlite::fromJSON(url) 
    }, error = function(e) {
        paste0(url, " : ", e)
        return(NA_real_)
    })

    return(api_result$results$total_number)
}

# BUILD DATA FRAME
api_results_df <- expand.grid(
    country = countries, date = dates
) |> within({
    ranges <- paste(head(date, -1), tail(date-1, -1), sep=":") 
    total_number <- mapply(get_api_data, country, ranges)
    rm(ranges)
})