Quote of the Day
We have invaded space with our rocket and for the first time. We have used space as a bridge between two points on the earth; we have proved rocket propulsion practicable for space travel. This third day of October, 1942, is the first of a new era of transportation, that of space travel.
My family has strong agricultural roots – mainly in dairy and potato farming – and our holiday conversations frequently turn to discussions of crop yields (bushels per acre or lbs per acre). As I listened to the discussion between my brothers on this year's crop yields, I realized that the yield numbers they were quoting were much higher today than we saw as children. This made me curious, and I decide to go out to the US Department of Agriculture's National Agricultural Statistics Service crop database and download CSV files on the yield of some key staple crops for processing by Power Query (i.e. recently renamed Get and Transform). I will be using this file to train my staff on defining Power Query functions. No macros were used in this analysis.
I am most interested in determining which staple crop produces the most food value per acre, with food value defined as calories per acre. When I was a boy, I was told that sugar cane produced the most calories per acre. Recently, I have had various farmers tell me that apples, corn, or potatoes produce the most calories per acre.
Figure 1 provides us the answer. I would make the following observations about Figure 1.
- Apples are not even close to winning the calories per acre contest.
Rice, corn, sugarcane, or potatoes all outpace apples. Note that the USDA did not have apple yield data that went back in time very far.
- You can argue either corn or potatoes win the calorie per acre race.
The corn crops show more yield variability than potato crops. I would guess that potatoes have more consistent yield because they are usually irrigated. I would also guess would be that corn would win over potatoes on a calories per acre per cost of production metric because irrigation is expensive.
- Sugarcane did produce the most calories per acre in my youth (1960s and 1970s).
I find it interesting that while sugarcane has experienced yield improvement since the 1940s, it did not improve at the same rate as corn and potatoes.
- The rise in the yields of rice, corn, and potatoes since 1940 is remarkable.
I have to believe that this yield increase is because of the application of technology to agriculture that occurred after WW2.
- Notice how there was almost no yield growth prior to 1940.
I will do a bit more research to try and determine what happened after 1940 that was not happening for many decades prior.
The rest of this post covers how I generated Figure 1. For those interested in following my work, here is my source. You should be able to unzip my workbook and data files to any location and have it work. The data files are unmodified downloads from the USDA web page. The graph work is routine – Power Query is the interesting part.
- Crop yield refers to either volume of a crop per unit acre or the mass of a crop per unit area of land cultivated.
- staple crop
- A staple food, or simply a staple, is a food that is eaten routinely and in such quantities that it constitutes a dominant portion of a standard diet for a given people, supplying a large fraction of energy needs and generally forming a significant proportion of the intake of other nutrients as well.
- food calorie
- Food calories are measure in units of kilocalories (kcal).
I decided to look at the calories per acre for the following crops:
All my data is based on US national averages – there is quite a bit of variability between the states. Note that some US crops yields are measured by volume, and I needed to convert these volumetric units to mass units for the energy calculation by using their densities. I obtain the densities from the table shown in Appendix B.
This analysis involved some of the screwiest units I have ever used:
I am afraid these units are still commonly used in US agriculture. I cover a calorie calculation example in Appendix A. I also include a video in Appendix C that shows how complex creating sugar from sugarcane is – I was impressed with the amount of work required.
The analysis is straightforward and you can see how it is done by looking at my source. Here is my approach:
- download crop yield CSV files from the USDA National Agricultural Statistics Service.
- put together table of conversions from volumetric units to mass units.
- put together table of conversion for calories by mass unit of each crop.
- use Power Query joins to merge data and conversions.
- add column using formula to convert yields to calories.
- plot the data.
I wonder how far into the future these yield increases can continue. It would be interesting to know how much of this increase is attributable to improved techniques (e.g. fertilizer, irrigation) and how much is attributable to improved genetics. As the world's population increases, these yield increases will become more and more critical – our crop lands are limited and under much pressure.
Appendix A: Unit Conversions for Sugarcane Calories/Acre.
Figure 2 shows an example of the sugarcane calories per acre calculation.
Appendix B: Crop Mass Densities.
I used the crop densities list in Figure 3 for a number of the calculations (Source).
Appendix C: Video of Sugar Cane Processing.
Figure 4 shows how sugar cane is processed into granular sugar. The process is quite complex.
|Figure 2: Good Video Briefing on Sugarcane Processing.|