In [3]:
# Import necessary libraries
import pandas as pd
import seaborn as sns
import [Link] as plt
from [Link] import MinMaxScaler
# Load the dataset from Excel
df = pd.read_excel("1673872777_ausapparalsales4thqrt2020.xlsx")
# Data Wrangling
# Inspect missing values
missing_values = [Link]().sum()
# Fill missing values with mean
numeric_columns = df.select_dtypes(include=['number']).columns
df[numeric_columns] = df[numeric_columns].fillna(df[numeric_columns].mean())
# Normalize data
scaler = MinMaxScaler()
df[['Unit', 'Sales']] = scaler.fit_transform(df[['Unit', 'Sales']])
# Descriptive statistical analysis
mean_sales = df['Sales'].mean()
median_sales = df['Sales'].median()
std_dev_sales = df['Sales'].std()
# Identify highest and lowest sales group and state
highest_sales_group = [Link]('Group')['Sales'].sum().idxmax()
lowest_sales_group = [Link]('Group')['Sales'].sum().idxmin()
highest_sales_state = [Link]('State')['Sales'].sum().idxmax()
lowest_sales_state = [Link]('State')['Sales'].sum().idxmin()
# Generate reports
weekly_report = [Link]('Date')['Sales'].sum().to_markdown()
monthly_report_df = [Link]('M', on='Date')['Sales'].sum()
monthly_report = monthly_report_df.to_markdown()
quarterly_report = [Link]('Q', on='Date')['Sales'].sum().to_markdown()
# Data Visualization
[Link](figsize=(12, 8))
# State-wise sales analysis for different groups
[Link](2, 2, 1)
[Link](x='State', y='Sales', hue='Group', data=df)
[Link]('State-wise Sales Analysis for Different Groups')
# Group-wise sales analysis across different states
[Link](2, 2, 2)
[Link](x='Group', y='Sales', hue='State', data=df)
[Link]('Group-wise Sales Analysis Across Different States')
# Time-of-the-day analysis
[Link](2, 2, 3)
[Link](x='Time', y='Sales', data=df)
[Link]('Time-of-the-day Sales Analysis')
plt.tight_layout()
[Link]("/voc/work/ds with python project/[Link]") # Save the figure
[Link]()
# Report Generation
# Use Markdown in a JupyterLab Notebook
report = f"""
# Sales Data Analysis Report
## Descriptive Statistics
- Mean Sales: {mean_sales}
- Median Sales: {median_sales}
- Standard Deviation Sales: {std_dev_sales}
## Highest and Lowest Sales
- Highest Sales Group: {highest_sales_group}
- Lowest Sales Group: {lowest_sales_group}
- Highest Sales State: {highest_sales_state}
- Lowest Sales State: {lowest_sales_state}
## Weekly, Monthly, and Quarterly Reports
### Weekly Report
{weekly_report}
### Monthly Report
{monthly_report}
### Quarterly Report
{quarterly_report}
"""
# Save the report to a markdown file
with open("/voc/work/ds with python project/[Link]", "w") as f:
[Link](report)
In [ ]: