In this article we will analyze USDA wheat yearbook dataset (linked here) to find if there is a shortage of wheat flour at the beginning of the year 2023. This is the same dataset I used in my previous data visualization article here on wheat crop production.
There are multiple sheets in this excel dataset. The relevant data for our analysis is present in the sheet 28 and 29.
Let’s first read the sheet 28 and visualize the data.
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
wheat_flour_raw_df = pd.read_excel('Wheat Data-All Years_13Jan2023.xlsx',sheet_name ='WheatYearbookTable28-Full',skiprows=1,skipfooter=8,dtype=str)
print(wheat_flour_raw_df.shape)
Now let’s preprocess the data for further analysis. Here we are renaming the columns to more readable and code friendly values
columns = ['CalendarYear','WheatGroundForFlour','WheatFlourExtractionRate','WheatFlourProduction','WheatMillFeedProduction']
wheat_flour_raw_df.columns = columns
units = {
"WheatGroundForFlour":"1000 bushels",
"WheatFlourExtractionRate":"percent",
"WheatFlourProduction" : "1,000 cwt",
"WheatMillFeedProduction": "1,000 tons"
}
wheat_flour_raw_df = wheat_flour_raw_df.drop(index=0,axis=0)
wheat_flour_raw_df.head()
Wheat Flour Production
We are now ready to plot our first chart of the Wheat Flour Production column. It will show how this has varied over the years. Below is the code and the chart it produces for Wheat Production
wheat_flour_raw_df['WheatFlourProduction'] = wheat_flour_raw_df['WheatFlourProduction'].astype(float) wheat_flour_raw_df.plot(x='CalendarYear',y='WheatFlourProduction')
As you can see there is a sharp decline in the year 2023 in the wheat flour production in the US. Wheat Flour production is measured in cwt (Hundredweight). In US, 1 cwt (hundredweight) is 100 pounds. In 2022, there was a fall of almost 100,000 cwt in the amount of wheat produced.
Same thing is depicted more clearly in the bar plot below
sns.barplot(data=wheat_flour_raw_df,x='CalendarYear',y='WheatFlourProduction',color='lightblue')
plt.ylabel('Wheat Flour Production (in 1,000 cwt)')
plt.xticks(rotation=90);
Wheat Flour Extraction Rate
Lets see if the drop in production is because of the Extraction Rate, which is the percentage of flour extracted per unit of wheat
wheat_flour_raw_df['WheatFlourExtractionRate'] = wheat_flour_raw_df['WheatFlourExtractionRate'].astype(float)
sns.lineplot(data=wheat_flour_raw_df,x='CalendarYear',y='WheatFlourExtractionRate',color='blue')
plt.ylabel('Wheat Flour ExtractionRate (in percent %)')
plt.xticks(rotation=90);
The data shows that there was a slight increase in the extraction rate of wheat flour per unit of wheat. Therefore the cause of lower production of flour is due to the lower produce of the wheat crop itself. You can see in my previous article here.
Wheat Ground For Flour
Lets’s look how much wheat (in 1000 bushels) was available for grinding for flour.
sns.lineplot(data=wheat_flour_raw_df,x='CalendarYear',y='WheatGroundForFlour',color='blue')
plt.ylabel('Wheat Ground For Flour (in 1000 Bushels)')
plt.xticks(rotation=90);
This went down almost 250 Million bushels. It was also due to lower wheat production as well
Wheat Ground For Flour vs Wheat Flour Production
In order to compare Wheat Flour Production vs the Wheat Ground for Flour, we first need to convert them into the same scale. Wheat Ground For Flour is in 1,000 bushels whereas Wheat Flour Production is in 1,000 cwt.
To convert both of them to common unit (tons), we need to implement following formula
1 cwt = 0.056 tons 1 bushel = .0272155 metric ton
Below is the code to perform this calculation and add two new columns to our dataframe
wheat_flour_raw_df['WheatGroundForFlour_tons'] = wheat_flour_raw_df['WheatGroundForFlour'] * .0272155 wheat_flour_raw_df['WheatFlourProduction_tons'] = wheat_flour_raw_df['WheatFlourProduction'] * 0.056
Let’s create the comparison chart below
sns.lineplot(data=wheat_flour_raw_df,x='CalendarYear',y='WheatGroundForFlour_tons',color='blue',label='Wheat Ground For Flour (in tons)')
sns.lineplot(data=wheat_flour_raw_df,x='CalendarYear',y='WheatFlourProduction_tons',color='red',label='Wheat Flour Production (in tons)')
plt.ylabel('Wheat Ground vs Wheat Flour Production')
plt.xticks(rotation=90);
Wheat Mill Feed Production
Wheat Mill Feed is the feed for cattle, the remaining part of wheat that is leftover after flour is extracted from it. Let’s see how it varied over the year
sns.lineplot(data=wheat_flour_raw_df,x='CalendarYear',y='WheatMillFeedProduction',color='green')
plt.ylabel('Wheat MillFeed Production (in 1000 tons)')
plt.xticks(rotation=90);
Wheat Flour Import, Export and Disappearance
Sheet 29 in the excel book contains the data for Wheat Flour Import, Export and Disappearance. Let’s read that into a data frame and visualize the data
wheat_flour_snd_raw_df = pd.read_excel('Wheat Data-All Years_Dec2022.xlsx',sheet_name ='WheatYearbookTable29-Full',skiprows=1,skipfooter=12,dtype=str)
print(wheat_flour_raw_df.shape)
Preprocess the Dataframe
columns = ['CalendarYear','WheatFlourProduction','FSPBCImports','TotalSupply','FlourExports','SPBCExports','DomesticDisappearance','USPopulation','PerCapitaDisappearance']
wheat_flour_snd_raw_df.columns = columns
wheat_flour_snd_raw_df.head()
units_wheat_snd = {
"WheatFlourProduction":"1,000 cwt",
"FSPBCImports":"1,000 flour-equivalent cwt",
"TotalSupply" : "1,000 cwt",
"FlourExports": "1,000 cwt",
"SPBCExports": "1,000 flour-equivalent cwt",
"DomesticDisappearance": "1,000 cwt",
"USPopulation": "1,000 persons",
"PerCapitaDisappearance": "pounds"
}
wheat_flour_snd_raw_df = wheat_flour_snd_raw_df.drop(index=0,axis=0)
wheat_flour_snd_raw_df.head()
Data looks like below after preprocessing
Wheat Flour Exports
wheat_flour_snd_raw_df['FlourExports'] = wheat_flour_snd_raw_df['FlourExports'].astype(float)
sns.lineplot(data=wheat_flour_snd_raw_df,x='CalendarYear',y='FlourExports',color='blue')
plt.ylabel('Wheat Flour Exports (in 1000 cwt)')
plt.xticks(rotation=90);
As you can see Wheat Flour exports are also going downwards from 2020 onwards, also signifying lower wheat crop production. Data for 2022 is not available as of now, but there are high chances of it going down.
Flour Imports
There is no single column just for Flour Imports, but we can visualize the combined Import column for Flour, Semolina, Pasta, Bulgur and Couscous Imports. Some countries like India have also banned any wheat flour exports for supporting their domestic consumption
wheat_flour_snd_raw_df['FSPBCImports'] = wheat_flour_snd_raw_df['FSPBCImports'].astype(float)
sns.lineplot(data=wheat_flour_snd_raw_df,x='CalendarYear',y='FSPBCImports',color='blue')
plt.ylabel('Wheat Flour Imports (1,000 flour-equivalent cwt)')
plt.xticks(rotation=90);
So even the import have gone down in the year 2021, and 2022 would have followed the same trend. I will update the charts once USDA release data for 2022.
Domestic Disappearance
Let’s look at the domestic disappearance of the Wheat flour. It is the measure of how much of wheat flour is consumed domestically in US
wheat_flour_snd_raw_df['DomesticDisappearance'] = wheat_flour_snd_raw_df['DomesticDisappearance'].astype(float)
sns.lineplot(data=wheat_flour_snd_raw_df,x='CalendarYear',y='DomesticDisappearance',color='blue')
plt.ylabel('Wheat Flour Domestic Disapperance (in 1000 cwt)')
plt.xticks(rotation=90);
Domestic Disappearance also shows a downward trend for 2021.
Per Capita Disappearance
Per Capita disappearance measures how much wheat flour is consumed per person in USA.
sns.lineplot(data=wheat_flour_snd_raw_df,x='CalendarYear',y='PerCapitaDisappearance',color='blue')
plt.ylabel('Wheat Flour Per Capita Disappearance (in pounds)')
plt.xticks(rotation=90);
This is the most important chart of all the charts as it shows how the per capita wheat consumption has gone down significantly in US from year 2000 onwards.
It is a good thing as the lower production of wheat will not impact as many people as it would have impacted in the year 1999, when the consumption was at an all time high.
Conclusion
Overall we can see that there is a downward trend in all the parameters related to wheat flour, whether it is production, export or import. It is all caused because of lower production of the wheat crop itself.
Let me know in the comments below if you have any questions or feedback.
