API's - Download query and automate into excel

[mickey]

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

[andresrcs]

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.

[mickey]

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