Skip to content

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 demand and supply around the holidays and running 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 and extra_data.parquet file that contains complementary data.

Here is information about all the available columns in the two data files:

  • "index" - unique ID of the row
  • "Store_ID" - the store number
  • "Date" - the week of sales
  • "Weekly_Sales" - sales for the given store
  • "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 for further data manipulations and store the merged file in the clean_data.csv file that should contain 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 in the agg_date.csv file that should look like:

MonthWeekly_Sales
1.033174.178494
2.034333.326579
......

It is recommended to use pandas for this project.

Spinner
DataFrameas
store_df
variable
-- Write your SQL query here
-- This is loaded and stored in "store_df"
SELECT * FROM grocery_sales
# Import required packages
import pandas as pd
import numpy as np
import logging
import os

1. Extracting the data

# Create the extract() function
def extract(store_data, extra_data):
    extra_df = pd.read_parquet(extra_data) # Read parquet data
    merged_df = store_data.merge(extra_df, on = "index") # Merge
    return merged_df
merged_df = extract(store_df, "extra_data.parquet")
merged_df.head()
merged_df.shape
# Check missing value
merged_df.isnull().sum()

There some missing values on some columns => replace them by others value

2. Transforming data

# Define the transform function

def transform(merged_data):
    # Fill NaN by using mean value
    merged_data.fillna(
        {
            "Weekly_Sales": merged_data["Weekly_Sales"].mean(),
            "CPI": merged_data["CPI"].mean(),
            "Unemployment": merged_data["Unemployment"].mean()
      }, inplace = True
    )
    
    # Define the type of the "Date"
    merged_data["Date"] = pd.to_datetime(merged_data["Date"], format="%Y-%m-%d")
    
    # Extract the month value from the "Date" column to calculate monthly sales later on
    merged_data["Month"] = merged_data["Date"].dt.month

    # Filter the rows that have Weekly Sales > 10 000
    merged_data = merged_data.loc[merged_data["Weekly_Sales"] > 10000,:]
    
    # Drop unnecessary columns
    merged_data = merged_data.drop(["index", "Temperature", "Fuel_Price", "MarkDown1", "MarkDown2", "MarkDown3", "MarkDown4", "MarkDown5", "Type", "Size", "Date"], axis = 1)
    return merged_data
clean_data = transform(merged_df)
clean_data
def avg_monthly_sales(clean_data):
    '''Avg sales by month'''
    
    # Select the "Month" and "Weekly_Sales" columns
    holiday_sales = clean_data[["Month", "Weekly_Sales"]]
    
    # Group by Month and compute AVG
    holiday_sales = holiday_sales.groupby("Month")["Weekly_Sales"].mean().reset_index().round(2)
    
    # Rename column
    holiday_sales.rename(columns={"Weekly_Sales": "Avg_Sales"}, inplace=True)
    return holiday_sales
    
agg_data = avg_monthly_sales(clean_data)
agg_data
‌
‌
‌