Working with the USDA ERS Feed Grains Database #tidyverse

I have spent many hours manually creating dates dragging them by hand in order to create a basic chart from this dataset.

So today I wrote an R script that grabs the most recent spreadsheet imports it into R, and makes it “tidy”.

library(tidyverse)
library(readxl)
library(httr)
library(tidyr)


tmp <- tempfile(fileext = ".xls")

httr::GET(url = "https://www.ers.usda.gov/webdocs/DataFiles/50048/Feed%20Grains%20Yearbook%20Tables-All%20Years.xls",
          write_disk( tmp) )

data_xl <- read_excel(tmp, sheet = "FGYearbookTable04-Full", 
                      skip = 4, 
                      col_names = c("MktYear", "Quarter", "BeginningStocks", "Production", "Imports", 
                                    "TotalSupply", "FoodAlcoholInd", "Seed", "FeedandResidual", 
                                    "TotalDomestic", "Exports", "TotalUse", "EndingStocks")) %>%
  fill(MktYear) %>% 
  # Make columns for more standard date handling
  mutate(month = ifelse(Quarter =="Q1 Sep-Nov", "09-01", 
                        ifelse(Quarter == "Q2 Dec-Feb", "12-01", 
                               ifelse(Quarter == "Q3 Mar-May", "03-01", 
                                      ifelse(Quarter == "Q4 Jun-Aug", "06-01",
                                             ifelse(Quarter == "MY Sep-Aug", "09-01", NA)))))) %>%
  mutate(year = ifelse(Quarter =="Q1 Sep-Nov" | Quarter == "Q2 Dec-Feb" | Quarter == "MY Sep-Aug", as.numeric(str_sub(MktYear, 1,4)), 
                       as.numeric(str_sub(MktYear, 1,4)) +1 )) %>%
  mutate(date = as.Date(paste0(as.character(year), "-", month))) # Now we have a proper date column.

Making the data “Tidy”

Now lets transform the data from ‘wide’ formet to ‘long’ format so we can make nice graphs with ggplot2.

# Make tidy getting ready for ggplot
data_tidy <- data_xl %>% 
  select(c(-MktYear, -month, -year)) %>%
  pivot_longer(c(-date, -Quarter), names_to = "UseCategory", values_to = "Value")

data_tidy

## # A tibble: 2,959 x 4
##    Quarter    date       UseCategory       Value
##    <chr>      <date>     <chr>             <dbl>
##  1 Q1 Sep-Nov 1975-09-01 BeginningStocks  558   
##  2 Q1 Sep-Nov 1975-09-01 Production      5841.  
##  3 Q1 Sep-Nov 1975-09-01 Imports            0.24
##  4 Q1 Sep-Nov 1975-09-01 TotalSupply     6399.  
##  5 Q1 Sep-Nov 1975-09-01 FoodAlcoholInd   124.  
##  6 Q1 Sep-Nov 1975-09-01 Seed              NA   
##  7 Q1 Sep-Nov 1975-09-01 FeedandResidual  928.  
##  8 Q1 Sep-Nov 1975-09-01 TotalDomestic   1051.  
##  9 Q1 Sep-Nov 1975-09-01 Exports          373.  
## 10 Q1 Sep-Nov 1975-09-01 TotalUse        1424.  
## # ... with 2,949 more rows

Now let’s plot the corn disappearance

# Plot the Marketing Year Data
data_tidy %>% filter(Quarter == "MY Sep-Aug") %>%
  filter(UseCategory %in% c("FoodAlcoholInd", "Seed", "FeedandResidual", "Exports", "EndingStocks")) %>%
  ggplot(aes(x = date, y = Value, fill = UseCategory)) + 
  geom_area() + 
  theme_bw() +
  labs(x = "", y = "Millions of bushels", title = "Corn Use Categories Since 1975")

This is one of those posts I know I will come back to again and again myself. I hope you find it helpful!