Maple Syrup Production in the US and Canada

Data

Maple Syrup Production in the US and Canada

This blog came out of a desire to see which Canadian province is the “most Canadian.” After some extensive research into what metrics might quantify this (see below) and some searches for data, I settled on maple syrup production to at least get started. Who knows, maybe one day I will be able to fully quantify which province is the most Canadian, and you will see it happen right here on the InterWorks blog!

Canadian products

Side Note: If you have data on any of these quintessentially Canadian topics, send me an email!

One of the first issues here was the quantity and quality of the data. For Canada, only the new millennium has been captured and this is with varying units of data. For the US, there is a great amount of data, but it is not adjusted for inflation; this is one of my pet peeves with any monetary or economic data I encounter.

You can see below that there are overlaps and discrepancies in the Canadian data. In order to stretch out the data as much as possible, I made the decision to standardize the units used. After extracting this data with my trusty sidekick since college, pdftoexcel.com, there was plenty of work to be done in Alteryx:

standardizing units in US and Canada

My Alteryx work was done in a couple of stages:

  1. Separating the Statistics Canada and USDA data
  2. Adjusting the data for inflation and foreign exchange fluctuations, separately
  3. Bringing them back together

Separating the Data in Alteryx

From the table above, we can see that some data is from the US Department of Agriculture and some from Statistics Canada. In an effort to organize my workflow, I used Select tools to separate these two sets of variables into distinct areas of my flow, so I could make the necessary adjustments to each slice of data independently:

standardizing units in US and Canada

Adjusting for Inflation and Foreign Exchange Rate Fluctuations

This is the real meat of what we are doing, or should I say, the real sap. In the modern economy, money is always devaluing. Sometimes it is a hardly noticeable incentive to consume now and circulate money through the economy, and sometimes it gets completely out of hand—see Venezuela today. What we are concerned about in this dataset, which goes back 100 years, is that a dollar in today’s money is different than a dollar in 1919.

Governments have indexes that track the prices of bundles of goods across time, such as a bundle of basic foodstuffs. These Consumer Price Indexes (CPI) are not accurate, but they give us a good estimation of the scale of change. Anytime I need macroeconomic data like inflation index data, the first place I look is the St. Louis Federal Reserve Bank—not in the vault but in their online database, FRED.

Every country has a central bank that collects economic data that’s easily accessible, whether it is on the bank’s site or through a general-data portal managed by the government. Japan has Bank of Japan, the wizarding world has Gringotts, and Canada has Bank of Canada and Statistics Canada, which is where I found their inflation data.

In a separate workflow, I adjusted the Consumer Price Index to set 2018 to the base value of 100 and aggregated monthly inflation data to the year level. To adjust inflation indexes to a new base year, we simply divide all index values by the ratio of the index value in the desired year to 100:

adjusting inflation indexes

standardizing units in US and Canada

To adjust the dollar values themselves, we can multiply the values by (100/CPI):

standardizing units in US and Canada

So for 2018, which has the CPI value of 100, the multiplying coefficient is 1, (100/100). In 2006, our CPI value in the US is 66, so a price of $40 in 2006 would be 40*(100/66), or $60 now. Remember, this is an estimate.

To adjust Canada’s dollar amounts into US dollars, we can take the Canadian dollar value divided by the CADUSD rate, Canadian dollars per US dollar:

standardizing units in US and Canada

standardizing units in US and Canada

Bringing the Datasets Together

After some more cleaning, I unioned the Canadian data with the US data and was ready to go:

standardizing units in US and Canada

Satisfy your sweet tooth and check out the history of maple products in the US and Canada below!

More About the Author

Grant Eisenmenger

Analytics Consultant
Options for Row-Level Security in Tableau Now that you know you need Row-Level Security (RLS) and you are familiar with the basic concepts and building blocks, we can take a ...
Row-Level Security Basics in Tableau In this blog, we will run through fundamental concepts and building blocks for Row-Level Security (RLS) solutions. This will include: ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!