Now that you have become familiar with the ETL process, it’s time for you to summarize your knowledge about it.
Why is ETL important? What’s your general understanding of ETL? What are the key considerations in each step of ETL? What are the key advantages of having an integrated ETL process in your Python code?
ETL, or Extract, Transform, Load, is standard way of accumulating, standardizing and centralizing data. When you need to work on several datasets to perform your work, this process is how you effectively integrate multiple data sources practically. It effectively establishes a single source of truth for the data in an organization, by ensuring data quality and centralizing its source.
That it's a broad name for the process of integrating, standardizing, cleaning, and centralizing the data of an organization. Ultimately it's deciding on standards early on for how data is to be structured, then transforming the data from different sources so it fits that structure. Then of course adding it to the appropriate database.
Python has several key advantages, perhaps better suited to a list:
I looked up some information about the dataset from its census.gov page. And that along with some attempts to arrive at the sums on the top of each Excel sheet made it clear that they would likely not be useful to me. Some strange combination of categories leads to those sums so I decided to stick to the yearly totals to act as a checksum when going through the ETL process.
I also looked up what a NAICS code is and found that it's a standard classification system. Which was somewhat helpful in understanding each business type in the data.
When it came to what data I wouldn't include, the video showed the obvious rows and columns to exclude, namely the ones that were just blank or or descriptions about the data. I also determined that the adjustments they made wouldn't be useful to me, as part of this project is analyzing the long and short-term trends in retail sales. That means their adjustment methodology would potentially remove some of the periodic trends I'm looking for. With the raw data I can make my own adjustments if I need to.
I also tried summing each set of row and columns' missing values to see if there was a pattern to the missing data. And there were quite a few actually. Most of the missing values were adjacent to each other, along long ranges of months and sometimes years. To me that suggests that the data was missing because it was never collected, or as time went on methodology changed, or the market changed enough that the categorization needed changing. For example, online retail, which is a fairly new phenomenon, wouldn't have been included in the data before 1997.
Another period of time where a lot of data went missing was during the 2020 pandemic. I'm not sure if that's because the data wasn't collected, or if it was collected but the methodology changed, or if the lockdowns made it so these businesses were no longer open. A potential hint lies in that census.gov page, where it says that data gets omitted if the reporting quality is too low, so I'm going to guess that's the case for a lot of the business types in 2020.
So I actually left in the missing data for now, and depending on the specific analysis I'm doing, I'll impute the data or change the time range of data to analyze. There doesn't seem to be any imputation method that would fit all potential analyses cases.
During the extraction process I had to iterate through every sheet, representing a year, for every business type so that I could extract every year's data for each business type. Then during the transform stage, the most complicated part of the process was verifying the data by taking the sum of each business type's monthly sales and comparing it to the Excel sheet's yearly total. It worked out alright so I could continue with the install script.
Python has a huge ecosystem of libraries that make it easy to work with. Debugging Python code is fairly easy. Whereas debugging in SQL is almost non-existent and there's no way of using libraries of software to help with formats like Excel.
I personally was most interested to see how department stores, online shopping, and warehouse clubs have changed over time. My hypothesis was that department stores would have been on a steady declining trend, while online shopping and warehouse clubs would have been on a steady increasing trend. To analyze this hypothesis I examined the percentage change in sales for the three. I also used a 12-month moving average to smooth out the data to make it easier to see the long-term trends between the three categories.
The most challenging part is probably the constant need to re-examine the process. A bad choice in the extraction phase makes it difficult to fix in the transform phase and so on. So it's important to make sure you're making the right choices at each step and be open to iterating the process as things come up.
I could have improved on the extraction phase particularly. I found out later in the webinar on this module that I could've just stuck with reading all the data directly from the Excel file and gone from there. That would've saved a lot of time and complication.
I think a lot more interesting conclusions can be drawn from the pandemic years in the data. The pandemic had a lot of unique, and unfortunately negative, effects on retail. There's probably more analysis to be done on the years 2020 and beyond.