1. Abstract

This analysis examines economic trends in the Retail, Automobile, and Food Industries. Such as Books, Sporting Goods, Hobbies and Toys, Women's Clothing, and Men's Clothing. Using Python and SQL queries.

The study aims to understand why these sectors were key indicators of economic health in 2020 and how they influenced spending patterns. It also includes comparisons of growth rates, seasonal trends, and overall performance to assess industry dynamics.

Additionally, the study investigates percentage change as a key metric for understanding consumer behavior, particularly within Bookstores, Sporting Goods, Hobbies and Toys, Women's Clothing, and Men's Clothing industries. The use of rolling time windows is also explored as a technique for analyzing long-term spending trends, such as quarterly and semiannual patterns, and assessing economic stability.

The findings provide insights into how these industries evolved over time and evaluate the effectiveness of monthly versus yearly analysis in capturing economic shifts.

2. Introduction

This project focuses on building an ETL, Analysis, and Visualization pipeline using the MRTS dataset provided. The process involves extracting, transforming, and loading (ETL) data, conducting in-depth analysis, and visualizing key trends. Here's an overview of the steps followed in this project:

ETL Process - Python

I began by exploring the raw MRTS dataset from an Excel file, performing basic data cleaning, and exporting it as a CSV file. Next, I used Python scripts to filter, analyze, and format the data, ensuring it was properly structured and accessible. Once the dataset was debugged and refined, I connected it to SQL Workbench via a Python script. In SQL, I created a table and inserted the cleaned and processed data to prepare it for further analysis.

Data Analysis - SQL

After setting up the database, I wrote SQL queries to filter the relevant NAICS Codes and aggregate monthly sales data. The extracted information was passed as dictionaries to my Python code for further processing.

Visualization - Python

Using Matplotlib, I created various plots to illustrate key trends. Additionally, I designed bar charts to highlight the impact of rolling time windows, providing insights into long-term trends.

3. Extract-Transform-Load

3. 1 The ETL Process

3. 2 Data Exploration and Preparation

To perform ETL (Extract, Transform, and Load) on the MRTS dataset, I first explored the raw data from the Excel file and performed basic cleaning before exporting it as a CSV file. Next, I used Python scripts to filter, analyze, and format the data, ensuring it was properly structured. After debugging and refining the dataset, I connected it to SQL Workbench using a Python script. After the SQL installation file, I created a table in SQL workbench and inserted the cleaned and processed data, making it ready for further analysis.

In the Excel file, I deleted the first three rows, which contained descriptive text about the data, as well as the bottom eight rows. I then shifted the first two columns from row 4 to row 5 and removed row 4. Additionally, I deleted the 'ADJUSTED(2)' row.

After importing the CSV file into Python, I refined the month columns by removing the year from their labels, since I knew I would only be working on the year of 2020. I also re-name the column 'NAICS Code' as 'NAICS' and 'Kind of Business' as 'Business'. I found later on the code that my SQL had some issues with formatting. Therefore, as a way to simply things and avoid these errors I decided to do that.

After editing the columns I assigned a special NAICS code to rows where the 'Kind of Business' column contained the word 'retail' and did the same for rows containing 'GAFO' and 'automobile and other motor vehicle dealers'.

3. 3 Read the Data Using Python

Using Pandas might be the easiest way to read CSV files into a DataFrame.

The Monthly Retail Trade Survey (MRTS), conducted by the U.S. Census Bureau, provides key economic data on retail and food service sales and inventories in the U.S. This dataset helps assess consumer spending trends and supports economic analysis.

The dataset includes monthly sales figures, capturing revenue from various retail businesses. Data is classified using the North American Industry Classification System (NAICS). This dataset is an essential tool for tracking retail industry performance and predicting future market trends.

Pandas is preferred for handling large datasets due to its built in functions for filtering,, transforming, and analyzing data efficiently.

3. 4 Writing an Installation Script

To read my dataset in MySQL Workbench using Python, I used 'mysql.connector.connect()':

Once the connection was established, I created a database table using the CREATE TABLE SQL statement, executed via the connect.execute() method. Here’s the code for creating the table:

Finally, I inserted the data from the pandas DataFrame into SQL using the to_sql() method, ensuring that the existing data would be replaced if necessary and printed a result:

4. Analysis and Visualization

4. 1 Linear Regression

4. 2 Explore Trends

For my second trend analysis, I focused on the automobile industry. I selected various NAICS codes based on business type, including:

* Motor vehicle and parts dealers (441)

* Automobile dealers (4411)

* New car dealers (44111)

* Used car dealers (44112)

* Automotive parts, accessories, and tire stores (4413)

* Automobile and other motor vehicle dealers (4)

I first ran: use mrt20; SHOW TABLES;

to make sure that the table was loaded successfully. After that I wanted to make sure that the data was imported correctly as well so I ran: use mrt20; SELECT * FROM mrt-2020;

After making sure that all the data was transfered, I inspected the table structure to ensure there were no additional columns and that my edited column names were correctly reflected: use mrt20; SELECT NAICS, Business FROM mrt-2020;

I also tested the additional NAICS codes I assined (1, 2, and 4) and they returned successfully after a few tweaks to the python code. use mrt20; SELECT * FROM mrt-2020 WHERE NAICS = 1 OR WHERE NAICS = 2 OR WHERE NAICS = 4;

What is an Economic Trend?

An economic trend is a pattern or series of patterns observed over a specific period. These trends are essential for predicting spending behaviors and market movements, as they offer insights into consumer behavior, market conditions, and future economic prospects. Analyzing these trends helps understand how a particular sector evolves and what factors drive those changes.

Key Findings from 2020 MRT Data Analysis (Dani's selected sectors)

After analyzing the 2020 data for retail, food services, and the automobile industry, I observed that, despite the global challenges posed by COVID-19, these sectors were not as severely impacted as expected. While there was a decline from April to May, the trend reversed as the year progressed, showing a strong recovery toward the holiday season.

At the beginning of the year (January–March), both sectors experienced a slow decline. In the retail and food services category, I analyzed businesses specifically within this classification. For the automobile industry, my analysis included both new and used vehicle sales, as well as auto parts.

Trends in Bookstores, Sporting Goods, and Hobby Stores

Among bookstores, sporting goods stores, and hobby/toy/game stores, bookstores experienced the highest growth in December, with a significant rise in sales compared to November. Meanwhile, sporting goods and hobby stores saw a notable increase in sales during May compared to April.

Bookstores showed the most substantial overall growth throughout the year, with clear seasonal patterns:

Sales peaked in May, August, November, and December.

The sharp rise in May could be linked to people spending more time indoors during the early months of the pandemic, leading to increased reading.

The back-to-school season in late summer and the holiday season toward the end of the year also contributed to significant growth.

Sporting goods and hobby stores followed a similar yearly trend to bookstores but had more stable fluctuations. Bookstores started the year with a decline in sales but saw a rapid upward shift toward the end of the year. In contrast, sporting goods and hobby stores experienced more gradual growth, steadily increasing over time.

Anual vs Monthly Analysis

To analyze trends effectively, monthly and quarterly data should be examined closely. For broader insights into sector-wide or business-wide changes, annual data provides a more comprehensive view.

4. 4 Explore Percentage Change

I selected the "Retail and Food Services" category, previously assigned to NAICS code 1, for my first trend analysis. For my first query in this analysis, I first selected 'NAICS', all months, 'TOTAL' values to confirm and analyze the data before running further queries. The results were accurate, and the necessary cells were correctly selected.

For the second query in this analysis, I calculated the SUM for each month and assigned a new column name for better display.

Then I created a plot based on that information:

Once I identified the relevant NAICS codes, I calculated the SUM for each month, renamed the columns accordingly, and used this data to create my plot.

For this part of my analysis I ran three queries for three different sectors. I chose bookstores (451211), sporting goods(45111), and hobbies (451).

When I first started looking at the database I realized that bookstores and sporting goods were both placed as their own indivual business but they were also put together with hobbies, musical instruments, and book stores.

So I decided to analyze all three different businesses.

I first wanted to find what the NAICS code was for each one so I wrote: ```use mrt20; SELECT * FROM mrt_2020 WHERE Business LIKE '%book%' OR Business LIKE '%sport%' OR Business LIKE '%hobby%';```

I then selected the SUM of all the months and assigned a new name per column to display the total of the sales. Then I filtered the NAICS code for each Business. For example, this is the one for 'Books':```SELECT SUM(JAN) AS JAN_TOTAL, SUM(FEB) AS FEB_TOTAL, SUM(MAR) AS MAR_TOTAL... FROM mrt-2020 WHERE NAICS = 451211;```

I then selected the SUM of all the months and assigned a new name per column to display the total of the sales. Then I filtered the NAICS code for each Business. For example, this is the one for 'Books':```SELECT SUM(JAN) AS JAN_TOTAL, SUM(FEB) AS FEB_TOTAL, SUM(MAR) AS MAR_TOTAL... FROM mrt-2020 WHERE NAICS = 451211;```

After doing that for Books, Sporting Goods, and Hobbies, I created a plot for each one of them.

(Example of code use for Books Plot)

Books Percentage Change Plot

In economics, a percentage change measures the change between the increase or decrease of an initial value. It's vital for understanding economic trend and making informed decisions.

They're important to track inflation rates, economic growth, and other economiv indicators.

These two businesses are realed when it comes to their business and their demographic is very similar. They might not be mutually exclusive.

However, based on the records of sales from the beginning of the year to the end of the year, women's clothing had a percentage change of 4.68%. On the other hand, men's clothes had a decline in sales of 21.65%.

Sporting Goods Percentage Change Plot

Hobbies Percentage Change Plot

After analyzing it individually I created a plot to compare all three in the same space:

4. 4 Explore Rolling Time Window

In economics, a "Rolling Time Window" is a method of a analyzing time-series data in a fixed-size window of data points. It is considered important because it is not static. Rolling time windows allow the user to analyze data over time, capturing trends and changes.

For my first rolling time window I used the previous query I analyzed for Retail. I wanted to focus on the Holiday peak and highlight it to be able to compare it with the rest of the chart.

The previous python code was:

For my second rolling time window I used the previous auto dataframe I created to analyze trends and I ran a six months highlight/ focus to distinguish from the beginning of the year.

As an addition to my previous plots I wanted to show the data in a different way. Therefore, I created bar visualizations for each 'Retail' and 'Auto' Rolling Windows' Data.

The retail bar is represented by quaters, and this confirmed that during Q4, meaning Octover to December. They had the highest period of sales.

In the Auto industry we can also see that the second half of the year contributed to the most sales, with almost 4 billions of dollars versus 3.2 billions that were sold on the first half of the year.

5. Conclusion

This analysis underscores the importance of tracking economic trends in the 'Retail and Food Services,' 'Automobile,' 'Books,' 'Sporting Goods', 'Hobbies and Toys', 'Women's Clothing', and 'Men's Clothing' industries as key indicators of consumer behavior and financial stability.

The study reveals that different industries exhibit distinct growth trajectories, seasonal patterns, and spending trends, with some sectors expanding more rapidly than others. Notably, 'Men's Clothing' emerged as the slowest-growing market among the industries analyzed, whereas 'Retail and Food Services' generated the highest revenue.

The comparison between monthly and yearly analyses offers valuable insights into the optimal timeframe for economic evaluations, while percentage change analysis proves to be a crucial metric in understanding shifts in consumer preferences. The examination of men's and women's clothing industries further illustrates how market segments interact and evolve over time.

Finally, rolling time windows demonstrate their effectiveness in capturing long-term spending patterns and economic trends, providing a more nuanced view of market fluctuations.

These findings highlight the significance of data-driven approaches in economic analysis, offering a deeper understanding of market dynamics and consumer behavior.

6. References

• The Python Software Foundation, “SQLite3 — DB-API 2.0 interface for SQLite databases” Docs.Python. Python Software Foundation License Version 2, Last updated on Apr 02, 2025."https://docs.python.org/3/library/sqlite3.html"

• Refsnes Data, "Python String strip() Method", W3Schools. 03.31.2025."https://www.w3schools.com/python/ref_string_strip.asp"

• By Examples, Spark, "Pandas.to_numeric()", Sparkbyexamples. 04.28.2024."https://sparkbyexamples.com/pandas/use-pandas-to-numeric-function/"

• Hunter John, Dale Darren, Firing Eric, Droettboom Michael, "Marker reference", Matplotlib. Matplotlib Development Team, 2012-2025 "https://matplotlib.org/stable/gallery/lines_bars_and_markers/marker_reference.html"

• Doe, John. “Data Engineering.” Data Engineer Resource. Cengage, 2021. www.dataengineerresource.com .

• https://study.com/academy/lesson/what-is-an-economic-trend-definition-lesson-quiz.html