in Uncategorized

Working with the USDA ERS Feed Grains Database #tidyverse

The USDA ERS Feed Grain Database  contains among other things historical entries from the WASDE report. I need this information a lot both in teaching and research. It’s absolutely essential to maintain an accurate historical view of our most important food crops. But the data is presented in an Excel sheet that’s great for viewing, but really hard to work with and automatically analyze.

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!

 

Update: Boiler Up!

Many of you have already heard the news; my family and I moved to Purdue and West Lafayette IN this summer. I’m now in the Agricultural Economics department at Purdue as Associate Professor and Clearing Corporation Chair of Food and Agricultural Marketing. Today marks one month on the job, in fact, and am so excited…

Soybean Exports Update: Argentina is Buying U.S. Soybeans for the First Time in Recent Years

  This week's commodity export reports were released this morning and it was another abysmal week for sales and inspections. We are headed into the biggest weeks of the marketing year for shipments doing about half what we usually do. But we all knew that was happening. I thought it would be interesting to look…

Weekly Export Inspections of Soybeans during the 2018 Trade War with China

Photo by Ronan on Unsplash All eyes are on export sales and shipments of soybeans in the midst of the 2018 trade war. I always have my commodity price analysis students produce those ‘export pace of use’ charts as a lab assignment because it is data that is very actively followed in the commodity marketing…

You Mean I Can Get Paid to Stay in School?!?

If the title caught your attention, read on to find out how funding and money works in graduate school. Photo by Good Free Photos on Unsplash Graduate versus Professional Degree The biggest distinction in economics is between ‘traditional’ graduate degrees (which typically pay you) and ‘professional’ graduate degrees (which typically you pay for). The Professional…

Haspel’s Washington Post article: Why do taxpayers subsidize rich farmers?

Image from http://www.cgpgrey.com [CC BY 2.0], via Wikimedia Commons Tamar Haspel has an article in yesterday’s Washington Post titled, “Why do taxpayers subsidize rich farmers?” The article is a take on the USDA’s recent report “The Evolving Distribution of Payments From Commodity, Conservation, and Federal Crop Insurance Programs”“, by McFadden and Hoppe that looks at…

Incentives Matter: A Proposal to Speed up Academic Publishing

Publishing in an academic journal is a long and grueling process. But much of the length of time is spent simply waiting for reviewers - who are volunteer and unpaid - to do the hard work of determining contribution and quality of the submitted manuscript. **Edited: Thanks to @FarmerHayek and @AparnaHowlader for pointing me to…