<jupyter_start><jupyter_text>Sales Analysis  Import the necessary libraries<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import os
import calendar
from warnings import filterwarnings
filterwarnings("ignore")<jupyter_output><empty_output><jupyter_text>Task 1: Merge the 12 months of sales data into a single csv<jupyter_code>path = "./SalesData"
files = [file for file in os.listdir(path) if not file.startswith('.')] # Ignore hidden files

all_months_data = pd.DataFrame()

for file in files:
    current_data = pd.read_csv(path+"/"+file)
    all_months_data = pd.concat([all_months_data, current_data])
    
all_months_data.to_csv("all_data_copy.csv", index=False)<jupyter_output><empty_output><jupyter_text>Read in updated CSV<jupyter_code>all_data = pd.read_csv("all_data_copy.csv")
all_data.head()<jupyter_output><empty_output><jupyter_text>Clean up the Data  Drop NAN Values<jupyter_code>nan_data = all_data[all_data.isna().any(axis=1)]
nan_data.head()

all_data = all_data.dropna(how="all")

all_data.head()<jupyter_output><empty_output><jupyter_text>Augument Data with additional columns  Task 2: Add Month Number Column<jupyter_code>all_data["Month Number"]=all_data["Order Date"].str[0:2]
all_data["Month Number"]= all_data["Month Number"].astype("int32")
all_data.head()<jupyter_output><empty_output><jupyter_text>Find "Or" in the Month Number Column and delete it<jupyter_code>all_data = all_data[all_data["Month Number"] != "Or"]<jupyter_output><empty_output><jupyter_text>Convert columns to the correct types<jupyter_code>all_data["Quantity Ordered"] = all_data["Quantity Ordered"].astype("int32")
all_data["Price Each"] = pd.to_numeric(all_data["Price Each"])
all_data.head()<jupyter_output><empty_output><jupyter_text>Task 3: Add Month Name Column<jupyter_code>all_data["Month Name"]= all_data["Month Number"].apply(lambda x : calendar.month_name[x])

all_data.head()<jupyter_output><empty_output><jupyter_text>Task 4: Add the sales column<jupyter_code>all_data["Sales"]= all_data["Quantity Ordered"] * all_data["Price Each"]

all_data.head()<jupyter_output><empty_output><jupyter_text>Task 5: Add city column<jupyter_code>def getCity(city):
    return city.split(",")[1]

def getState(state):
    return state.split(",")[2].split(" ")[1]

all_data["City"] = all_data["Purchase Address"].apply(lambda x: f"{getCity(x)} ({getState(x)})")

all_data.head()
all_data["Month"] = all_data["Month Name"].apply(lambda x: x[0:3])

all_data.head()<jupyter_output><empty_output><jupyter_text>Question 1: What was the best month for sales? How much was earned in that month?<jupyter_code>sns.set_style("darkgrid")
plt.figure(figsize=(13,7))
sns.barplot(x="Month Number", y="Sales", data=all_data, estimator=np.sum, color="red")
plt.title("SALES BY MONTH NUMBER", fontsize=20)
plt.xlabel("Month", fontsize=14)
plt.ylabel("Sales in USD ($)", fontsize=14)
plt.tick_params(axis="x", labelsize = 14)
plt.tick_params(axis="y", labelsize = 14)
plt.xticks(rotation=0)
plt.tight_layout()<jupyter_output><empty_output><jupyter_text>Question 2: What city had the highest sales?<jupyter_code>df2 = all_data.groupby("City").sum().sort_values("Sales")

df2
sns.set_style("darkgrid")
df2["Sales"].plot(kind="barh", figsize=(11,6), color="r")
plt.title("SALES BY CITY", fontsize=20)
plt.xlabel("Sales in USD ($)", fontsize=14)
plt.ylabel("City", fontsize=14)
plt.tick_params(axis="x", labelsize = 14)
plt.tick_params(axis="y", labelsize = 14)
plt.xticks(rotation=0)
plt.tight_layout()

#Do not forget to add data labels<jupyter_output><empty_output><jupyter_text>Question 3: What time should we display advertisements to maximize the likelihood of customer's buying product?<jupyter_code>all_data["Order Date"]=pd.to_datetime(all_data["Order Date"])
all_data["Hour"] = all_data["Order Date"].dt.hour
all_data["Minute"] = all_data["Order Date"].dt.minute
all_data.head()
df3 = all_data.groupby("Hour").sum()

df3
sns.set_style("darkgrid")
df3["Quantity Ordered"].plot(kind="line", figsize=(11,6), color="r")
plt.tick_params(axis="x", labelsize = 14)
plt.tick_params(axis="y", labelsize = 14)
plt.xlabel("Hour", fontsize = 14)
plt.ylabel("No of Orders", fontsize = 14)
plt.title("Number of Products Ordered by Hour", fontsize = 20)<jupyter_output><empty_output><jupyter_text>Question 4: What products are most often sold together?<jupyter_code>df = all_data[all_data["Order ID"].duplicated(keep=False)]

df["Grouped"]=df.groupby("Order ID")["Product"].transform(lambda x : ",".join(x))

df.head()
df = df[["Order ID", "Grouped"]].drop_duplicates()

df.head()
from itertools import combinations
from collections import Counter

count = Counter()

for row in df["Grouped"]:
    row_list = row.split(",")
    count.update(Counter(combinations(row_list, 2)))

for key, value in count.most_common(10):
    print(key, value)<jupyter_output>('iPhone', 'Lightning Charging Cable') 1005
('Google Phone', 'USB-C Charging Cable') 987
('iPhone', 'Wired Headphones') 447
('Google Phone', 'Wired Headphones') 414
('Vareebadd Phone', 'USB-C Charging Cable') 361
('iPhone', 'Apple Airpods Headphones') 360
('Google Phone', 'Bose SoundSport Headphones') 220
('USB-C Charging Cable', 'Wired Headphones') 160
('Vareebadd Phone', 'Wired Headphones') 143
('Lightning Charging Cable', 'Wired Headphones') 92<jupyter_text>Question 5: What were the top 5 and bottom 5 selling products?  Top 5<jupyter_code>df4 = all_data.groupby("Product").sum().sort_values("Sales", ascending=False).head()

df4.sort_values("Sales", ascending=True)
df4["Sales"].plot(kind="bar", figsize=(13,6), color="r")
plt.tick_params(axis="x", labelsize = 14)
plt.tick_params(axis="y", labelsize = 14)
plt.xlabel("Products", fontsize = 14)
plt.ylabel("Sales", fontsize = 14)
plt.title("Sales by Products", fontsize = 20)
plt.xticks(rotation=0)<jupyter_output><empty_output><jupyter_text>Bottom 5<jupyter_code>df5 = all_data.groupby("Product").sum().sort_values("Sales", ascending=False).tail()
df5.head()
df5["Sales"].plot(kind="bar", figsize=(14,6), color="r")
plt.tick_params(axis="x", labelsize = 13)
plt.tick_params(axis="y", labelsize = 14)
plt.xlabel("Products", fontsize = 14)
plt.ylabel("Sales", fontsize = 14)
plt.title("Sales by Products", fontsize = 20)
plt.xticks(rotation=0)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16019648233243544, 'fraction_numerical': 0.022500396133734748, 'mean_word_length': 8.228070175438596, 'pattern_counts': {'":': 0, '<': 64, '<?xml version=': 0, '>': 64, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '221837', 'license': 'MIT', 'n_tokens_mistral': 2449, 'n_tokens_neox': 2254, 'n_words': 466, 'path': 'Sales Analysis.ipynb', 'repo_name': 'dexmokizzi/Python-for-Data-Science', 'size': '6311'}