Business Analysis#

<class 'pandas.core.frame.DataFrame'>
    RangeIndex: 51290 entries, 0 to 51289
    Data columns (total 26 columns):
    #   Column          Non-Null Count  Dtype
    ---  ------          --------------  -----
    0   category        51290 non-null  object
    1   city            51290 non-null  object
    2   country         51290 non-null  object
    3   customer_id     51290 non-null  object
    4   customer_name   51290 non-null  object
    5   discount        51290 non-null  float64
    6   market          51290 non-null  object
    7   order_date      51290 non-null  datetime64[ns]
    8   order_id        51290 non-null  object
    9   order_priority  51290 non-null  object
    10  product_id      51290 non-null  object
    11  product_name    51290 non-null  object
    12  profit          51290 non-null  float64
    13  quantity        51290 non-null  int64
    14  region          51290 non-null  object
    15  row_id          51290 non-null  int64
    16  sales           51290 non-null  int64
    17  segment         51290 non-null  object
    18  ship_date       51290 non-null  datetime64[ns]
    19  ship_mode       51290 non-null  object
    20  shipping_cost   51290 non-null  float64
    21  state           51290 non-null  object
    22  sub_category    51290 non-null  object
    23  year            51290 non-null  int64
    24  market2         51290 non-null  object
    25  weeknum         51290 non-null  int64
    dtypes: datetime64[ns](2), float64(3), int64(5), object(16)
    memory usage: 10.2+ MB

print(df['country'].value_counts(),"\n")
# Mostly US. Followed by Australia, France, Mexico, ...
print(df['city'].value_counts(),"\n")
# New York City, Los Angeles, Philadelphia, ...
print(df['category'].value_counts(),"\n")
# Office Supplies, Technology, Furniture
print(df['sub_category'].value_counts(),"\n")
# Lots of subcategories
print(df['market'].value_counts(),"\n")
# Global Market Abbr, will expand later
print(df['product_id'].value_counts(),"\n")
# Highest product ID sold is OFF-AR-10003651 = Newell 350 (Some kind of Art)
print(df['ship_mode'].value_counts(),"\n")
# Standard Class, Second Class, First Class, Same Day
print(df['weeknum'].value_counts().head(10),"\n")
# Gives us the weeks with the highest sales.
df["pocessing_time"] = (df["ship_date"] - df["order_date"])
# difference between customer orders and us shipping
df["pocessing_time"] = df["pocessing_time"].apply(lambda x: x.days)
df['pocessing_time'].value_counts() # gives us the days between customer orders and us shipping

# Products overview based on subcategory
product_sales = df.groupby(['sub_category', 'product_name'])['sales'].sum().reset_index()

top_5_products_per_subcategory = (
    product_sales
    .sort_values(['sub_category', 'sales'], ascending=[True, False])
    .groupby('sub_category')
    .head(5)
)

print(top_5_products_per_subcategory)

category_counts = df['category'].value_counts()

plt.figure(figsize=(5, 5))
plt.pie(
    category_counts,
    labels=category_counts.index,
    autopct='%1.2f%%',
    startangle=180,
    colors=sns.color_palette('plasma', len(category_counts))
)
plt.title('Category Distribution')
plt.show()

fig = px.treemap(
product_sales,
path=['sub_category', 'product_name'],
values='sales',
title='Treemap of Product Sales Grouped by Subcategory',
color='sales',
color_continuous_scale='Plasma',
hover_data={'sales': ':.2f'}
)

fig.show()

customer_lifetime_value = (
    df.groupby('customer_name')['profit']
    .sum()
    .sort_values(ascending=False)
    .reset_index()
)

print(customer_lifetime_value)

top_30 = customer_lifetime_value.head(30)
bottom_30 = customer_lifetime_value.tail(30)

fig, axs = plt.subplots(nrows=1, ncols=2, figsize=(14, 8))
# I want to show these side by side so we needd subplots.
sns.set(style="whitegrid")

sns.barplot(
    data=top_30,
    y='customer_name',
    x='profit',
    palette='plasma',
    ax=axs[0]
)
axs[0].set_title('Top 30 Customers by Lifetime Profit')
axs[0].set_xlabel('Total Profit')
axs[0].set_ylabel('Customer Name')

sns.barplot(
    data=bottom_30,
    y='customer_name',
    x='profit',
    palette='plasma',
    ax=axs[1]
)
axs[1].set_title('Bottom 30 Customers by Lifetime Profit')
axs[1].set_xlabel('Total Profit')
axs[1].set_ylabel('Customer Name')

plt.show()

cindy_data = df[df['customer_name'] == 'Cindy Stewart']

# Find the list of all products she bought and the profit of all of those.
product_profits = (
cindy_data.groupby('product_name')['profit']
    .sum()
    .reset_index()
    .sort_values('profit', ascending=False)
)

plt.figure(figsize=(8, 10))
sns.barplot(
    data=product_profits,
    x='profit',
    y='product_name',
    palette='plasma'
)

plt.title("Products Purchased by Cindy Stewart and Profits")
plt.xlabel("Total Profit")
plt.ylabel("Product Name")
plt.show()

# Total sales of all subcategories
subcategory_sales = df.groupby('sub_category')['sales'].agg(['sum', 'mean']).reset_index()
# mean = total sales for subcategory / number of rows (transactions) in that subcategory
# sum = total sale for subcategory

plt.figure(figsize=(10, 6))
sns.barplot(
data=subcategory_sales,
x='sub_category',
y='sum',
palette='plasma'
)
plt.title('Total Sales by Subcategory')
plt.xlabel('Subcategory')
plt.ylabel('Total Sales')
plt.xticks(rotation=45)
plt.show()

plt.figure(figsize=(10, 6))
sns.barplot(
data=subcategory_sales,
x='sub_category',
y='mean',
palette='plasma'
)
plt.title('Mean Sales per Transaction by Subcategory')
plt.xlabel('Subcategory')
plt.ylabel('Mean Sales')
plt.xticks(rotation=45)
plt.show()

# Sales in each market for each country
sales_by_market_country = (
df.groupby(['market_expanded', 'country'])['sales']
    .sum()
    .reset_index()
    .sort_values(['market_expanded', 'sales'], ascending=[True, False])
)

markets = sales_by_market_country['market_expanded'].unique()

figs = []
for market in markets:
market_data = sales_by_market_country[sales_by_market_country['market_expanded'] == market]
fig = px.bar(
    market_data,
    x='country',
    y='sales',
    title=f'Sales by Country in {market} Market',
    labels={'sales': 'Total Sales', 'country': 'Country'},
    color='country'
)
fig.show()
figs.append(fig)

# Category Gross Margin and Sales and profit of sub categories

sales_profit_by_category_market = (
df.groupby(['category', 'market_expanded'])[['sales', 'profit']]
    .sum()
    .reset_index()
)

# melting lets us plot different columns in the same figure
melted_main = sales_profit_by_category_market.melt(
    id_vars=['category', 'market_expanded'],
    value_vars=['sales', 'profit'],
    var_name='Metric',
    value_name='Value'
)


fig_main = px.bar(
    melted_main,
    x='category',
    y='Value',
    color='Metric',
    barmode='group',
    facet_col='market_expanded',
    title='Total Sales and Profit by Category Across Markets',
    labels={'Value': 'Amount', 'category': ' '}
)
    # we don't need to rewrite category so many times

for i in range(len(fig_main.layout.annotations)):
    fig_main.layout.annotations[i].text = fig_main.layout.annotations[i].text.split('=')[-1]
    # Just the name of the market not the market_expanded part
fig_main.write_html("category_gross.html")
fig_main.show()


sales_profit_by_category_market['gross_margin'] = sales_profit_by_category_market['profit'] / sales_profit_by_category_market['sales']

# gross margin to based on category/market
fig_margin = px.bar(
    sales_profit_by_category_market,
    x='category',
    y='gross_margin',
    facet_col='market_expanded',
    color='category',
    title='Gross Margin by Category Across Markets',
    labels={'gross_margin': 'Gross Margin', 'category': ' '}
)

for i in range(len(fig_margin.layout.annotations)):
    fig_margin.layout.annotations[i].text = fig_margin.layout.annotations[i].text.split('=')[-1] # Just the name of the market not the market_expanded part
fig_main.write_html("category_gross_margin.html")
fig_margin.show()