Skip to content

Project Brief: Walmart is the biggest retail store in the United States. Just like others, they have been expanding their e-commerce part of the business. By the end of 2022, e-commerce represented a roaring $80 billion in sales, which is 13% of total sales of Walmart. One of the main factors that affects their sales is public holidays, like the Super Bowl, Labour Day, Thanksgiving, and Christmas.

In this project, you have been tasked with creating a data pipeline for the analysis of supply and demand around the holidays, along with conducting a preliminary analysis of the data. You will be working with two data sources: grocery sales and complementary data. You have been provided with the grocery_sales table in PostgreSQL database with the following features:

grocery_sales

  • "index" - unique ID of the row
  • "Store_ID" - the store number
  • "Date" - the week of sales
  • "Weekly_Sales" - sales for the given store

Also, you have the extra_data.parquet file that contains complementary data:

extra_data.parquet

  • "IsHoliday" - Whether the week contains a public holiday - 1 if yes, 0 if no.
  • "Temperature" - Temperature on the day of sale
  • "Fuel_Price" - Cost of fuel in the region
  • "CPI" – Prevailing consumer price index
  • "Unemployment" - The prevailing unemployment rate
  • "MarkDown1", "MarkDown2", "MarkDown3", "MarkDown4" - number of promotional markdowns
  • "Dept" - Department Number in each store
  • "Size" - size of the store
  • "Type" - type of the store (depends on Size column)

You will need to merge those files and perform some data manipulations. The transformed DataFrame can then be stored as the clean_data variable containing the following columns:

  • "Store_ID"
  • "Month"
  • "Dept"
  • "IsHoliday"
  • "Weekly_Sales"
  • "CPI"
  • ""Unemployment""

After merging and cleaning the data, you will have to analyze monthly sales of Walmart and store the results of your analysis as the agg_data variable that should look like:

MonthWeekly_Sales
1.033174.178494
2.034333.326579
......

Finally, you should save the clean_data and agg_data as the csv files.

To start with, we use sql to query the grocery_sales data from the sql database it is stored on

Spinner
DataFrameas
grocery_sales
variable
SELECT * FROM public.grocery_sales

#imports and installations...
import pandas as pd
import os
!pip install fastparquet
Hidden output

Create an extract function to read the parquet data, and merge it with the grocery sales data.

def extract(store_df, extra_data):
    extra_data= pd.read_parquet(extra_data, engine="fastparquet")
    merged_df = store_df.merge(extra_data, on = 'index')
    return merged_df
    
#Quick-observe the structure of the data
merged_df.head(5)
merged_df.columns
#create a new DataFrame by calling the extract function on the grocery_sales and extra_data
merged_df = extract(grocery_sales, 'extra_data.parquet')

Make a function that fills the numerical values in the data, by the mean. Then create a month column by converting the date column to datetime object, and extracting the month value from it. We then drop the unnecessary (for the sake of this project) columns.

def transform(merged_df):
    merged_df.fillna({'Weekly_Sales':merged_df['Weekly_Sales'].mean(),
                      'Temperature':merged_df['Temperature'].mean(),
                      'Fuel_Price': merged_df['Fuel_Price'].mean(),
                      'CPI': merged_df[ 'CPI'].mean(),
                      'Unemployment':merged_df['Unemployment'].mean(),
                      'Size':merged_df['Size'].mean
    }, inplace = True)
    
    merged_df['Date']= pd.to_datetime(merged_df['Date'], format= "%Y-%m-%d")
    merged_df['Month'] = merged_df['Date'].dt.month
    merged_df = merged_df.loc[merged_df['Weekly_Sales']> 10000,:]
    merged_df= merged_df.drop(['index', 'Date',
       'Temperature', 'Fuel_Price', 'MarkDown1', 'MarkDown2', 'MarkDown3',
       'MarkDown4', 'MarkDown5', 'Type', 'Size'], axis = 1)
    return merged_df
#create a cleaned DataFrame using the transform function
clean_data = transform(merged_df)
clean_data.head(5)

To find out the average monthly sales, we create a function that selects the relevant columns- Month and Weekly Sales. It then groups the data by month, and aggregates by Average sales. Average sales is elicited using the mean. It resets the index, and rounds the figures to 2 decimal places.

def avg_monthly_sales(cleaned_data):
    selected = clean_data[['Month', 'Weekly_Sales']]
    
    selected = selected.groupby('Month').agg(Avg_Sales = ('Weekly_Sales', 'mean')).reset_index().round(2)
    return selected
‌
‌
‌