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

Question

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!

AdroMine · Accepted Answer · 2022-03-19 16:09:16Z

1

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

edited Mar 19, 2022 at 16:09

answered Mar 19, 2022 at 15:44

AdroMine

1,5577 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

PierreRoubaix Over a year ago

Hi @AdroMine, thank you for your answer! I tried this code, and it resulted in "# A tibble: 0 x 0"

AdroMine Over a year ago

were the dates and/or countries empty? You will also have to perform the API call after creating the api_string.

PierreRoubaix Over a year ago

Hi @AdroMine, the API call should be this part: "json <- jsonlite::fromJSON(api_string)". I don't think a data frame was created with the code, at least I didn't get something like "0 obs of 0 variables." It just said "# A tibble: 0 x 0"

PierreRoubaix Over a year ago

Ah I think I might have found the mistake (it was mine: the jurisdiction and the date were reversed in the loop example. So the position of "date" and "country" needed to be switched). Let me see if it works now

AdroMine Over a year ago

api_string is just the generated URL, you will need to make an API call (you can look at httr::GET for examples) which actually fetches data from the given url.

|

Parfait · Accepted Answer · 2022-03-19 19:32:28Z

1

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 METHODS
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_response$results$total_number)
}

add.months <- function(date, n) 
    seq.Date(date, by=paste(n, "months"), length = 2)[2]

# BUILD DATA FRAME
api_results_df <- expand.grid(
  country = countries, date = dates
) |> within({
  month_add <- sapply(date, add.months, n=1) |> `class<-`("Date")
  ranges <- paste(date, month_add-1, sep=":") 
  # PASS COLUMN DATA ELEMENTWISE INTO DEFINED METHOD
  total_number <- mapply(get_api_data, cnty=country, rng=ranges)
  rm(ranges, month_add)
})

edited Mar 19, 2022 at 19:32

answered Mar 19, 2022 at 17:01

Parfait

108k19 gold badges103 silver badges138 bronze badges

3 Comments

PierreRoubaix Over a year ago

Hi @Parfait, I tried the code without changing it, and it gives me the same total_number for all the months (though different ones for each country). It's not the total for the year either -- it's much too large.

Parfait Over a year ago

Did you try manually checking a few rows of data frame into API call? Comment out the rm to keep ranges column. Rerun code. Then spot check several rows passing single values into defined method: get_api_data("row 1 country", "row 1 range"). See if data repeats. Maybe much older ones in early 90s do but closer to now does not? Per expand.grid data frame is first ordered by country then date.

PierreRoubaix Over a year ago

Hi @Parfait, thank you for your response. I've deleted older comments and will try this solution and let you know. Thank you very much for your help -- it's my first time doing this and I'm still relatively new to R

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related