Exploratory Data Analysis on Sales Data in Python

Introduction¶

This dataset contains sales records from an unspecified store in USA, capturing various details about each transaction, including the product, quantity ordered, price, order date, and purchase address. With over 186,000 records, it offers a comprehensive view of the store's sales activities over time.

Columns Description¶

• Order ID: A unique identifier for each order.
• Product: The name of the product sold. ( Electronic Items)
• Quantity Ordered: The number of units sold in each transaction.
• Price Each: The price of a single unit of the product. (In Dollars)
• Order Date: The date and time when the order was placed. (mm/dd/yy hr:min)
• Purchase Address: The address where the product was delivered.

Cleaning the data¶

We observe that all the data is in object format so we will convert the relevant data to int or float depending on the requirement.

Here we see that an item named "Product" is coming 355 times in the data.

Similarly , here we see that "Quantity ordered" is occuring 355 in the data which can be a mistake so first we will observe and then remove those specific column with irrelevant data.

Here we see "Price each" is also repeating 355 times . Thus , we will filter the data based on the occurance of name " quantity ordered" in the dataset .

We observer that there are 355 columns which have column name in them insted of data so we will remove them.

Removing irrelevant rows.¶

First we will collect the index of such rows with irrelevant data in list and then we will use this list to drop the rows from the data.

Above we created a list of all the indices now we will use this to remove rows.

The specified rows are removed . Now we will check for null data and remove or replace those null values based on the observations.

Handling missing data.¶

We see that there are 545 missing values in each column. So we will remove them by using dropna .

Null values are now removed .

Dropping duplicate values¶

Now we will see if there are any duplicate rows in the data.

We observe that there are some duplicate rows as well so we will drop them also .

Hence , duplicate rows are dropped and now we can move to further analysis based on the tasks provided.

Transforming the data¶

Task 1. Convert the text based categorical columns to numeric columns¶

The task involves converting text-based categorical columns to numeric columns to facilitate numerical analysis and machine learning operations.

We observe that all the columns are in object format.

1.Data Type Conversion¶

The data types of 'Order ID', 'Quantity Ordered', and 'Price Each' columns are converted to 'int32' and 'float64', respectively, ensuring these columns can be used for numerical computations.

2. Label Encoding of Product Column¶

The 'Product' column is label-encoded, converting each unique product name into a unique numeric identifier, stored in the new 'productnum' column. This transformation allows the categorical product data to be used in numerical analyses and machine learning models.

Task 2. Convert the Order Date column into Day, Month, Year and Time columns separately .¶

The task involves converting the 'Order Date' column into separate columns for the day, month, year, and time to facilitate detailed time-based analysis.

1. Converting Order Date to DateTime Format¶

The 'Order Date' column is converted to a DateTime format, enabling the extraction of specific date and time components. This step is essential for performing further time-based operations and analyses.

2. Extracting Day, Month, Year, and Time Components¶

New columns 'Day', 'Month', 'Year', and 'Time' are created by extracting respective components from the 'Order Date' column. This detailed breakdown of the date and time allows for more granular analysis of sales trends and patterns over different periods.

Task 3. Create a new column and store the Price data as rounded off in integer format¶

The task involves creating a new column that stores the price data in a rounded-off integer format, making it easier to analyze and interpret.

1. Rounding and Converting Price Data to Integer Format¶

A new column 'Price Rounded' is created by rounding off the 'Price Each' values to the nearest integer and converting them to integer format. This transformation simplifies the price data, facilitating more straightforward analysis and potential aggregation without decimal precision.

Task 4. Create a new column and store the PIN Codes from the Address column¶

The task involves extracting the PIN codes from the 'Purchase Address' column and storing them in a new column for more accessible geographical analysis.

1. Splitting the Address Column to Extract PIN Codes¶

The 'Purchase Address' column is split into multiple components using commas and spaces, isolating the segment that contains the PIN codes. This intermediate step helps in precisely locating and extracting the PIN codes from the address.

2. Storing the Extracted PIN Codes in a New Column¶

A new column 'PIN' is created to store the extracted PIN codes from the address. This addition allows for more detailed geographical analysis, enabling the identification of sales patterns and trends based on specific locations.

Task 5. Create a new column named City and store the city names from the Address column¶

The task involves creating a new column to store city names extracted from the 'Purchase Address' column, facilitating geographical analysis based on city-level data.

1. Inspecting the Address Column¶

By inspecting the 'address' DataFrame, we observe that the city names are located in the second segment (index 1) of the split addresses. This step confirms the correct position of the city names, ensuring accurate extraction.

2. Storing the Extracted City Names in a New Column¶

A new column 'City' is created to store the city names extracted from the 'Purchase Address' column. This addition allows for detailed geographical analysis, enabling the identification of sales trends and patterns based on specific cities.

Task 6. Group Product column into different categories such as Phones, Accessories, etc according to the data available.¶

The task involves grouping the products into different categories such as Phones, Accessories, etc., based on their types, to enable better analysis and reporting.

1. Identifying Unique Products¶

By listing the unique product names, we can categorize them effectively. This step provides a clear overview of the product types available in the dataset, which is essential for accurate grouping.

2. Defining the Product Grouping Function¶

The function grpproduct is defined to categorize each product into predefined categories. This function maps each product to its corresponding category based on the provided list.

3. Creating the Product Category Column¶

A new column 'Product Category' is created by applying the grpproduct function to the 'Product' column. This categorization allows for better organization and analysis of the data, enabling insights into the performance of different product categories.

Task 7. Create 12 separate date ordered dataframes for every month.¶

The task involves creating 12 separate dataframes, each corresponding to a month, to facilitate month-wise analysis of the sales data.

1. Identifying Unique Years¶

By listing the unique years, we can filter the data accordingly. This step confirms the range of years present in the dataset, which is crucial for segregating the data by month.

2. Filtering Data by Year¶

The data is filtered into separate dataframes for the years 2019 and 2020. This segregation ensures that month-wise analysis can be accurately performed within each year.

3. Initializing Data Structures for Months¶

A dictionary ds_list and lists monthnum and monthlist are initialized to store the month-wise dataframes and to facilitate iteration over the months. This setup is essential for creating and labeling the separate monthly dataframes.

4. Creating Separate DataFrames for Each Month¶

The data dataframe is grouped by the 'Month' column, and separate dataframes for each month are stored in the ds_list dictionary. This step ensures that each month has a dedicated dataframe for detailed analysis.

5: Displaying the January DataFrame:¶

Displaying the first few rows of the January dataframe confirms that the data for January has been correctly extracted and stored. This validation step helps ensure the accuracy of the monthly segregation.

Task 8. What was the best month for sales? How much was earned that month? Explain via graph/chart¶

The goal is to identify the best month for sales and determine how much was earned during that month. We will use the sales data to calculate total sales for each month and then visualize this data using a bar chart to easily identify the best month for sales.

1. Calculate Total Sales¶

This step creates a new column, Total Sales, by multiplying the quantity of items ordered (Quantity Ordered) by the price per item (Price Rounded). This provides the total sales amount for each individual order.

2. Group Sales by Month and Sum Them:¶

Here, the data is grouped by the Month column, and the total sales for each month are summed up. This results in a new Series, sale, where the index is the month and the values are the total sales for that month.

3. Find the Maximum Sales Value:¶

This line finds the highest value in the sale Series, representing the month with the highest total sales. It identifies the peak sales performance across all months.

4. Plot the Sales Data:¶

A bar plot is created using the seaborn library, with months on the x-axis and total sales values on the y-axis. This visual representation makes it easy to compare sales performance across different months and clearly identify the best month for sales.

The bar plot visualizes the total earnings per month. It confirms that December was the best month for sales, with the highest total earnings of 4,583,267.77 dollars. This visualization effectively communicates the peak sales month, making it easy to identify trends and patterns.

Conclusion :¶

Based on the calculated total sales for each month, the results indicate the following:

• The month with the highest sales is December, with total earnings of 4,608,295.70.
• The month with the second highest sales is October, with total earnings of 3,734,777.86.
• The month with the lowest sales is January, with total earnings of 1,821,413.16.

Task 9. What city sold the most product?¶

The task is to identify the city with the highest product sales and visualize the distribution of sales across different cities.

1. Counting the Number of Sales Per City¶

The output indicates that San Francisco has the highest number of sales transactions (44,662), followed by Los Angeles (29,564) and New York City (24,847). This step helps identify the city with the most sales activity.

2. Visualizing Sales Transactions Per City¶

The count plot visualizes the number of sales transactions across different cities. San Francisco is clearly the city with the highest sales volume, as reflected by the tallest bar in the plot. The rotation of x-axis labels ensures all city names are readable. This visualization provides a clear comparison of sales performance by city, highlighting San Francisco as the leading city in terms of product sales.

Conclusion :¶

The analysis reveals that San Francisco leads in sales transactions compared to other cities. The visual representation confirms this by showing San Francisco's prominent position in the sales distribution, making it evident which city contributes the most to the overall sales figures.

Task 10. What time should we display advertisements to maximize likelihood of customer's buying product?¶

The task aims to determine the optimal time to display advertisements by analyzing the hourly distribution of purchases to maximize the likelihood of customers buying products.

1. Extracting the Hour of Purchase¶

The 'Hour' column is created to capture the hour at which each purchase was made. This step prepares the data for time-based analysis, allowing us to identify peak purchasing hours.

2. Visualizing the Number of Purchases by Hour¶

The count plot displays the number of purchases made during each hour of the day. It highlights that 11-12 hour and 19 hour has the highest frequency of purchases. This visualization helps identify peak hours when customers are most active.

3. Visualizing the Distribution of Purchases by Hour¶

The distribution plot with a KDE curve shows the frequency of purchases throughout the day. The plot reveals 12 and 19 hours have the highest density of purchases, indicating the optimal times for displaying advertisements. The grid and x-axis labels make the plot easy to interpret, showing that the highest number of purchases typically occurs in the afternoon or late evening hours, suggesting these times might be ideal for ad placements.

Task 11. Which product was sold the least as per your categories and what might be the reason behind that. Explain your analysis.¶

The task is to identify which product was sold the least according to the product categories and analyze potential reasons behind its lower sales.

1. Counting Sales per Product¶

The output shows the count of sales for each product within its category. From this, it is evident that the products with the least sales are the LG Washing Machine and LG Dryer, with counts of 666 and 646, respectively, in the 'Home appliances' category. These are the least sold products among the listed categories.

2. Calculating Mean Price per Product¶

The mean price data reveals that products like the LG Washing Machine and LG Dryer are priced at 600.00 dollars , which is relatively high compared to other products in the dataset. This suggests that their high prices may be a contributing factor to their lower sales volumes.

3. Merging Sales Count with Mean Price¶

The merged data provides a comprehensive view of both the sales count and mean price for each product. The LG Washing Machine and LG Dryer have the lowest sales counts and are also among the higher-priced items. This pricing could be a significant factor in their low sales, as higher-priced products may have a smaller customer base or face more competition compared to lower-priced items.

Conclusion :¶

The analysis indicates that the LG Washing Machine and LG Dryer were sold the least. Their high prices, relative to other products, likely contribute to their lower sales volumes. Higher-priced items may have fewer buyers due to budget constraints or lower demand compared to more affordable products. Additionally, factors such as product necessity, market competition, and customer preferences may also influence the sales figures.

Task 12. Which product is most likely to be sold more during winter season, summer season and rainy season? Explain the possible reason behind the High-Volume Sale¶

The task is to determine which products are most likely to be sold more during different seasons (winter, summer, and rainy) and to explain the reasons behind these seasonal sales trends.

1. Categorizing Data by Season¶

The 'Season' column is created based on the month of the year, categorizing each month into Winter, Summer, or Rainy season. This categorization is crucial for analyzing seasonal sales patterns.

2. Grouping and Counting Sales by Season¶

The output shows which products have the highest sales counts for each season. The most popular products by season are:

• Winter: USB-C Charging Cable
• Summer: Lightning Charging Cable
• Rainy: USB-C Charging Cable This indicates that certain products perform better during specific seasons.

3. Visualizing Product Sales by Season¶

The count plot visually represents the sales of different products across seasons. It confirms that:

• Winter: USB-C Charging Cable is the top-selling product.
• Summer: Lightning Charging Cable leads in sales.
• Rainy: USB-C Charging Cable also performs well.

Conclusion :¶

• Winter: The USB-C Charging Cable tops the sales list, likely because people may purchase more tech accessories during the colder months when they stay indoors more often and might need additional charging solutions for their devices.

• Summer: The Lightning Charging Cable leads in sales. During summer, people might be on the go more frequently and need portable charging solutions, which explains the higher sales of this product.

• Rainy: The USB-C Charging Cable again stands out. Rainy weather often leads to increased indoor activities, which could drive higher sales of tech products as people spend more time using their electronic devices.

Overall, these trends highlight how seasonal weather and activities influence purchasing behavior, suggesting that advertisements and promotions could be tailored to these seasonal patterns to maximize sales.

Task 13. Which phone is sold most during the month of March?¶

The task is to identify which phone model had the highest sales in March.

1. Filtering Data for March¶

This step filters the dataset to include only sales data from March. It provides a subset of the data where the month is March, setting the stage for further analysis of phone sales during this month.

2. Filtering for Phone Products¶

This step further filters the March data to include only products categorized as phones. It isolates the phone sales data from the overall March data, focusing on the relevant product category.

3. Counting Phone Sales in March¶

The output reveals the number of units sold for each phone model in March:

• iPhone: 537 units
• Google Phone: 463 units
• Vareebadd Phone: 174 units The iPhone was the most sold phone model in March, followed by the Google Phone and Vareebadd Phone.

Conclusion :¶

The iPhone led in sales during March with 537 units sold, significantly outperforming other phone models. This high volume of sales might be attributed to factors such as brand popularity, promotions, or consumer preferences for the iPhone. The analysis highlights which phone model resonates most with customers in March, providing valuable insights for inventory management, marketing strategies, and sales forecasting.

Task 14. Which headphones are the most expensive?¶

The task is to identify which headphones are the most expensive based on their price data.

1. Filtering and Identifying the Most Expensive Headphones¶

This step filters the dataset to include only products in the "Headphones" category and identifies the headphone model with the highest price. The output reveals that the Apple Airpods Headphones are the most expensive headphones, priced at 150.00 dollars.

2. Reviewing Merged Data for Headphones¶

This step shows the merged data for headphones, listing the units sold and their prices. The output confirms:

• Wired Headphones: 11.99 d
• Apple Airpods Headphones: 150.00 d
• Bose SoundSport Headphones: 99.99 d The Apple Airpods Headphones have the highest price at 150.00 d, verifying the result from the first step.

Conclusion:¶

The Apple Airpods Headphones are the most expensive headphones in the dataset, with a price of $150.00. This high price point is likely due to the brand's premium positioning, advanced features, or market demand. Understanding the pricing of different products helps in pricing strategy decisions, market positioning, and inventory management.

Task 15. Check if the price of any product is changing in different months. If yes, explain the possible reason behind the price change¶

The task is to determine whether the price of any product changes across different months, and if so, to explore potential reasons for such variations.

1. Calculating Monthly Average Prices for Each Product¶

This step groups the data by Product and Month, then calculates the average price of each product for each month.

The result shows that there was no observed change in the average price of any product across different months. This implies that the prices remained consistent throughout the year.

Task 16. People prefer which product more in earphones, wired or wireless?¶

if we consider "bose soundsport headphones" to be wireless we can see that sum of apple and bose headphones (wireless) is greater than wired headphones, ie, 28823(sum of apple and bose headphones sold) > 18849 (wired headphones sold ).

Task 17. Which product in every category in more likely to be ordered in bulk?¶

The task is to determine which product within each category is most likely to be ordered in bulk, defined as orders of higher quantities.

1. Filtering Data by Product Category¶

This step initializes the analysis by creating a list of unique product categories and iterates over each category to filter the dataset accordingly. However, the specific handling of each category (e.g., calculating bulk orders) is not completed in this snippet.

2. Counting Order Quantities¶

The output shows the frequency of different order quantities. Orders of 1 are the most common, with 168,291 occurrences, while orders of higher quantities (e.g., 2, 3, 4) are less frequent. This distribution highlights that while bulk orders (e.g., quantities of 4 or more) are less common, they are still present in the dataset.

3. Analyzing Bulk Orders for Headphones¶

This step focuses on the "Headphones" category and identifies the maximum quantity ordered for each headphone product:

• Apple Airpods Headphones: Maximum order quantity of 3
• Bose SoundSport Headphones: Maximum order quantity of 3
• Wired Headphones: Maximum order quantity of 4

Among the headphones, the Wired Headphones are most likely to be ordered in bulk, with the highest maximum order quantity of 4.

Overall Conclusion and Summary¶

Data Cleaning and Transformation:¶

• Conversion of Categorical to Numeric: Converted text-based columns like Product into numerical formats for easier analysis.
• Date Parsing: Split Order Date into separate columns for Day, Month, Year, and Time to facilitate time-based analysis.
• Price Rounding: Created a Price Rounded column for simplicity in cost-related calculations.
• PIN Code Extraction: Extracted PIN codes from the Purchase Address for geographical analysis.
• City Extraction: Isolated city names from addresses for city-based sales analysis.
• Product Categorization: Grouped products into categories (e.g., Phones, Accessories) to analyze sales trends within these categories.

Exploratory Data Analysis (EDA):¶

• Best Month for Sales: Identified December as the best month for sales, with the highest total revenue. This suggests that end-of-year sales and holidays drive higher sales volumes.
• City with Highest Sales: Found that San Francisco had the highest number of product sales, indicating it as a major market for the products.
• Optimal Advertisement Time: Determined that the most purchases occur in the evening hours, suggesting that advertising during these times might be more effective.
• Least Sold Product: Identified LG Washing Machine as the least sold product. The lower sales could be due to its high price compared to other products or lower consumer demand.

• Seasonal Sales Patterns:

  • Winter: USB-C Charging Cable is the most popular, likely due to increased indoor activity and device usage.
  • Summer: Lightning Charging Cable sees higher sales, possibly due to the need for portable charging solutions during travel.
  • Rainy Season: USB-C Charging Cable leads in sales again, indicating higher indoor activities during the rainy season.
• Most Expensive Headphones: Apple Airpods Headphones are the most expensive, priced at D 150, reflecting their premium market positioning.
• Bulk Orders by Product Category: For the Headphones category, Wired Headphones are most frequently ordered in larger quantities compared to other products.

Recommendations:¶

• Marketing Strategy: Focus advertising efforts around December and in the evenings to capture peak purchasing times.
• City-Specific Marketing: Tailor marketing and sales strategies for San Francisco to leverage its high sales potential.
• Seasonal Promotions: Adjust product promotions and inventory based on seasonal trends to maximize sales during peak periods.
• Product Pricing and Inventory: Consider revising the pricing strategy for high-ticket items like LG Washing Machines and analyze potential factors affecting their sales.