API's - Download query and automate into excel

Hi gents,
Hope you all having a good start of Summer.

I am wondering if someone could help me on the following.

I am trying to retrieve data from this website and automate it to download it monthly on an excel sheet.

Website: USDA/NASS QuickStats Ad-hoc Query Tool

I am trying to get the:

  • PROGRAM: SURVEY
  • SECTOR: ANIMALS&PRODUCTS
  • GROUP: LIVESTOCK
  • COMMODITY: HOGS
  • CATEGORY: INVENTORY
  • DATA ITEM: HOGS - INVENTORY
  • GEOGRAPHIC: NATIONAL
  • YEAR: 2017-2022
  • PERIOD: ANNUAL

I would like to know how this can be done in order not to have to choose every category each time, so I just plug the API and code in R, and it downloads automatically.

Thank you so much.

M

For querying the API from R you can use the httr2 package, for exporting data to excel there are many package options, one example is openxlsx, and for scheduling an R script you can use taskscheduleR or cronR depending on your Operating System.

If you need more specific help, please provide a proper REPRoducible EXample (reprex) illustrating your issue.

1 Like

Hi Andres,
Thank your for your response. I am very new to R so I am kinda in a black tunnel with this task. Trying to get the: commo_to_items = {
"SOYBEANS": ["SOYBEANS - YIELD, MEASURED IN BU / ACRE", "SOYBEANS - ACRES PLANTED", "SOYBEANS - ACRES HARVESTED", "SOYBEANS - PRODUCTION, MEASURED IN BU"],
}

I built this code, however, its not working:

proc_download <- function(s, c) {
   cat(paste(s, c, "\n"))      

   tryCatch({
      data_url <- paste0(
          "http://quickstats.nass.usda.gov/api/api_GET/?key=my_API_KEY",
        "&key": api_key,
        "&source_desc": "SURVEY",
        "&commodity_desc": commo,
        "&year": year,
        "&short_desc": items,
        "&agg_level_desc": "NATIONAL",
        "&format": "JSON"
        )

     my_file <- paste0(s, "_", c, "_prices.csv")
     download.file(data_url, destfile=my_file)
     Sys.sleep(1)              

     df <- read.csv(my_file)
     return(df)

  }, error = function(e) {
        print(e)               
        return(NA)             
  })
}

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.