
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import os


jan_data = pd.read_csv("Sales_Report/Sales_January_2019.csv")
feb_data = pd.read_csv("Sales_Report/Sales_February_2019.csv")
mar_data = pd.read_csv("Sales_Report/Sales_March_2019.csv")
apr_data = pd.read_csv("Sales_Report/Sales_April_2019.csv")
may_data = pd.read_csv("Sales_Report/Sales_May_2019.csv")
jun_data = pd.read_csv("Sales_Report/Sales_June_2019.csv")
jul_data = pd.read_csv("Sales_Report/Sales_July_2019.csv")
aug_data = pd.read_csv("Sales_Report/Sales_August_2019.csv")
sep_data = pd.read_csv("Sales_Report/Sales_September_2019.csv")
oct_data = pd.read_csv("Sales_Report/Sales_October_2019.csv")
nov_data = pd.read_csv("Sales_Report/Sales_November_2019.csv")
dec_data = pd.read_csv("Sales_Report/Sales_December_2019.csv")


all_data = pd.concat([jan_data, feb_data, mar_data, apr_data, may_data, 
                      jun_data, jul_data, aug_data, sep_data, oct_data, 
                      nov_data, dec_data])


files = [file for file in os.listdir("./Sales_Report/")]

all_months_data = pd.DataFrame()

for file in files:
    df = pd.read_csv("./Sales_Report/"+file)
    all_months_data = pd.concat([all_months_data, df])


all_data


all_data.to_csv("./Sales_Report/Total Months.csv", index = False)
total_months = pd.read_csv("./Sales_Report/Total Months.csv")


total_months.head()


nan_df = total_months[total_months.isna().any(axis=1)]
total_months = total_months.dropna(how='all')


total_months = total_months[total_months['Order Date'].str[0:2] != 'Or']


total_months['Quantity Ordered'] = pd.to_numeric(total_months['Quantity Ordered']) #to int
total_months['Price Each'] = pd.to_numeric(total_months['Price Each']) #to float


total_months['Month'] = total_months['Order Date'].str[0:2]
total_months['Month'] = total_months['Month'].astype('int32')


total_months['Sales'] = total_months['Quantity Ordered'] * total_months['Price Each']


total_months = total_months[['Order ID', 'Product', 'Month','Quantity Ordered', 'Price Each', 'Sales', 'Order Date',
                             'Purchase Address']]
total_months


results = total_months.groupby('Month').sum()
results


months = range(1,13)

plt.bar(months, results['Sales'])
plt.xticks(months)
plt.xlabel('Months')
plt.ylabel('Sales in ($)')
plt.show()


total_months


def get_city(address):
    return address.split(',')[1]

def get_state(address):
    return address.split(',')[-1].split(' ')[1]

total_months['City'] = total_months['Purchase Address'].apply(lambda x: get_city(x)+ ' (' + get_state(x) + ')')

total_months.head()

C:\Users\dondr\AppData\Local\Temp\ipykernel_2584\938415794.py:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  total_months['City'] = total_months['Purchase Address'].apply(lambda x: get_city(x)+ ' (' + get_state(x) + ')')


results = total_months.groupby('City').sum()
results


cities = [city for city, df in total_months.groupby('City')]

plt.bar(cities, results['Sales'])
plt.xticks(cities, rotation='vertical', size=8)
plt.xlabel('City Names')
plt.ylabel('Sales in ($)')
plt.show()


total_months


total_months['Order Date'] = pd.to_datetime(total_months['Order Date'])

C:\Users\dondr\AppData\Local\Temp\ipykernel_2584\3704197767.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  total_months['Order Date'] = pd.to_datetime(total_months['Order Date'])


total_months['hours'] = total_months['Order Date'].dt.hour
total_months

C:\Users\dondr\AppData\Local\Temp\ipykernel_2584\2683211630.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  total_months['hours'] = total_months['Order Date'].dt.hour


hours = [hour for hour, df in total_months.groupby(['hours'])]

plt.plot(hours, total_months.groupby(['hours']).count())
plt.xticks(hours)
plt.grid()
plt.xlabel('Hours')
plt.ylabel('number of orders')
plt.title('number of order per hours')
plt.show()


total_months


df = total_months[total_months['Order ID'].duplicated(keep=False)]
df


df['Purchased Items'] = df.groupby('Order ID')['Product'].transform(lambda x: ' , '.join(x))
df = df[['Order ID', 'Purchased Items']].drop_duplicates()
df

C:\Users\dondr\AppData\Local\Temp\ipykernel_2584\3060875203.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df['Purchased Items'] = df.groupby('Order ID')['Product'].transform(lambda x: ' , '.join(x))


from itertools import combinations
from collections import Counter


count = Counter()

for row in df['Purchased Items']:
    row_list = row.split(',')
    count.update(Counter(combinations(row_list, 2)))
    
for key, value in count.most_common(10):
    print(key, value)

('iPhone ', ' Lightning Charging Cable') 886
('Google Phone ', ' USB-C Charging Cable') 859
('iPhone ', ' Wired Headphones') 443
('Google Phone ', ' Wired Headphones') 405
('iPhone ', ' Apple Airpods Headphones') 329
('Vareebadd Phone ', ' USB-C Charging Cable') 312
('Google Phone ', ' Bose SoundSport Headphones') 192
('Vareebadd Phone ', ' Wired Headphones') 140
('Google Phone ', ' USB-C Charging Cable ') 128
('iPhone ', ' Lightning Charging Cable ') 119


total_months


grouped_products = total_months.groupby('Product')
Quantity_Ordered = grouped_products.sum()['Quantity Ordered']


Quantity_Ordered

Product
20in Monitor                   4129
27in 4K Gaming Monitor         6244
27in FHD Monitor               7550
34in Ultrawide Monitor         6199
AA Batteries (4-pack)         27635
AAA Batteries (4-pack)        31017
Apple Airpods Headphones      15661
Bose SoundSport Headphones    13457
Flatscreen TV                  4819
Google Phone                   5532
LG Dryer                        646
LG Washing Machine              666
Lightning Charging Cable      23217
Macbook Pro Laptop             4728
ThinkPad Laptop                4130
USB-C Charging Cable          23975
Vareebadd Phone                2068
Wired Headphones              20557
iPhone                         6849
Name: Quantity Ordered, dtype: int64


products = [products for products, df in grouped_products]

plt.bar(products, Quantity_Ordered)
plt.xticks(products, rotation='vertical')
plt.xlabel('Product Name')
plt.ylabel('number of orders')
plt.title('Number of orders per product')
plt.show()

	Order ID	Product	Quantity Ordered	Price Each	Order Date	Purchase Address
0	141234	iPhone	1	700	01/22/19 21:25	944 Walnut St, Boston, MA 02215
1	141235	Lightning Charging Cable	1	14.95	01/28/19 14:15	185 Maple St, Portland, OR 97035
2	141236	Wired Headphones	2	11.99	01/17/19 13:33	538 Adams St, San Francisco, CA 94016
3	141237	27in FHD Monitor	1	149.99	01/05/19 20:33	738 10th St, Los Angeles, CA 90001
4	141238	Wired Headphones	1	11.99	01/25/19 11:59	387 10th St, Austin, TX 73301
...	...	...	...	...	...	...
25112	319666	Lightning Charging Cable	1	14.95	12/11/19 20:58	14 Madison St, San Francisco, CA 94016
25113	319667	AA Batteries (4-pack)	2	3.84	12/01/19 12:01	549 Willow St, Los Angeles, CA 90001
25114	319668	Vareebadd Phone	1	400	12/09/19 06:43	273 Wilson St, Seattle, WA 98101
25115	319669	Wired Headphones	1	11.99	12/03/19 10:39	778 River St, Dallas, TX 75001
25116	319670	Bose SoundSport Headphones	1	99.99	12/21/19 21:45	747 Chestnut St, Los Angeles, CA 90001

	Order ID	Product	Quantity Ordered	Price Each	Order Date	Purchase Address
0	141234	iPhone	1	700	01/22/19 21:25	944 Walnut St, Boston, MA 02215
1	141235	Lightning Charging Cable	1	14.95	01/28/19 14:15	185 Maple St, Portland, OR 97035
2	141236	Wired Headphones	2	11.99	01/17/19 13:33	538 Adams St, San Francisco, CA 94016
3	141237	27in FHD Monitor	1	149.99	01/05/19 20:33	738 10th St, Los Angeles, CA 90001
4	141238	Wired Headphones	1	11.99	01/25/19 11:59	387 10th St, Austin, TX 73301

	Order ID	Product	Month	Quantity Ordered	Price Each	Sales	Order Date	Purchase Address
0	141234	iPhone	1	1	700.00	700.00	01/22/19 21:25	944 Walnut St, Boston, MA 02215
1	141235	Lightning Charging Cable	1	1	14.95	14.95	01/28/19 14:15	185 Maple St, Portland, OR 97035
2	141236	Wired Headphones	1	2	11.99	23.98	01/17/19 13:33	538 Adams St, San Francisco, CA 94016
3	141237	27in FHD Monitor	1	1	149.99	149.99	01/05/19 20:33	738 10th St, Los Angeles, CA 90001
4	141238	Wired Headphones	1	1	11.99	11.99	01/25/19 11:59	387 10th St, Austin, TX 73301
...	...	...	...	...	...	...	...	...
186845	319666	Lightning Charging Cable	12	1	14.95	14.95	12/11/19 20:58	14 Madison St, San Francisco, CA 94016
186846	319667	AA Batteries (4-pack)	12	2	3.84	7.68	12/01/19 12:01	549 Willow St, Los Angeles, CA 90001
186847	319668	Vareebadd Phone	12	1	400.00	400.00	12/09/19 06:43	273 Wilson St, Seattle, WA 98101
186848	319669	Wired Headphones	12	1	11.99	11.99	12/03/19 10:39	778 River St, Dallas, TX 75001
186849	319670	Bose SoundSport Headphones	12	1	99.99	99.99	12/21/19 21:45	747 Chestnut St, Los Angeles, CA 90001

	Quantity Ordered	Price Each	Sales
Month
1	10903	1811768.38	1822256.73
2	13449	2188884.72	2202022.42
3	17005	2791207.83	2807100.38
4	20558	3367671.02	3390670.24
5	18667	3135125.13	3152606.75
6	15253	2562025.61	2577802.26
7	16072	2632539.56	2647775.76
8	13448	2230345.42	2244467.88
9	13109	2084992.09	2097560.13
10	22703	3715554.83	3736726.88
11	19798	3180600.68	3199603.20
12	28114	4588415.41	4613443.34

	Order ID	Product	Month	Quantity Ordered	Price Each	Sales	Order Date	Purchase Address
0	141234	iPhone	1	1	700.00	700.00	01/22/19 21:25	944 Walnut St, Boston, MA 02215
1	141235	Lightning Charging Cable	1	1	14.95	14.95	01/28/19 14:15	185 Maple St, Portland, OR 97035
2	141236	Wired Headphones	1	2	11.99	23.98	01/17/19 13:33	538 Adams St, San Francisco, CA 94016
3	141237	27in FHD Monitor	1	1	149.99	149.99	01/05/19 20:33	738 10th St, Los Angeles, CA 90001
4	141238	Wired Headphones	1	1	11.99	11.99	01/25/19 11:59	387 10th St, Austin, TX 73301
...	...	...	...	...	...	...	...	...
186845	319666	Lightning Charging Cable	12	1	14.95	14.95	12/11/19 20:58	14 Madison St, San Francisco, CA 94016
186846	319667	AA Batteries (4-pack)	12	2	3.84	7.68	12/01/19 12:01	549 Willow St, Los Angeles, CA 90001
186847	319668	Vareebadd Phone	12	1	400.00	400.00	12/09/19 06:43	273 Wilson St, Seattle, WA 98101
186848	319669	Wired Headphones	12	1	11.99	11.99	12/03/19 10:39	778 River St, Dallas, TX 75001
186849	319670	Bose SoundSport Headphones	12	1	99.99	99.99	12/21/19 21:45	747 Chestnut St, Los Angeles, CA 90001

	Month	Quantity Ordered	Price Each	Sales
City
Atlanta (GA)	104794	16602	2779908.20	2795498.58
Austin (TX)	69829	11153	1809873.61	1819581.75
Boston (MA)	141112	22528	3637409.77	3661642.01
Dallas (TX)	104620	16730	2752627.82	2767975.40
Los Angeles (CA)	208325	33289	5421435.23	5452570.80
New York City (NY)	175741	27932	4635370.83	4664317.43
Portland (ME)	17144	2750	447189.25	449758.27
Portland (OR)	70621	11303	1860558.22	1870732.34
San Francisco (CA)	315520	50239	8211461.74	8262203.91
Seattle (WA)	104941	16553	2733296.01	2747755.48

	Order ID	Product	Month	Quantity Ordered	Price Each	Sales	Order Date	Purchase Address	City	hours
41	141275	USB-C Charging Cable	1	1	11.95	11.95	2019-01-07 16:06:00	610 Walnut St, Austin, TX 73301	Austin (TX)	16
42	141275	Wired Headphones	1	1	11.99	11.99	2019-01-07 16:06:00	610 Walnut St, Austin, TX 73301	Austin (TX)	16
57	141290	Apple Airpods Headphones	1	1	150.00	150.00	2019-01-02 08:25:00	4 1st St, Los Angeles, CA 90001	Los Angeles (CA)	8
58	141290	AA Batteries (4-pack)	1	3	3.84	11.52	2019-01-02 08:25:00	4 1st St, Los Angeles, CA 90001	Los Angeles (CA)	8
133	141365	Vareebadd Phone	1	1	400.00	400.00	2019-01-10 11:19:00	20 Dogwood St, New York City, NY 10001	New York City (NY)	11
...	...	...	...	...	...	...	...	...	...	...
186761	319584	Wired Headphones	12	1	11.99	11.99	2019-12-11 00:30:00	537 Sunset St, Portland, OR 97035	Portland (OR)	0
186773	319596	iPhone	12	1	700.00	700.00	2019-12-01 08:44:00	436 14th St, New York City, NY 10001	New York City (NY)	8
186774	319596	Lightning Charging Cable	12	1	14.95	14.95	2019-12-01 08:44:00	436 14th St, New York City, NY 10001	New York City (NY)	8
186809	319631	34in Ultrawide Monitor	12	1	379.99	379.99	2019-12-17 22:35:00	363 Highland St, Austin, TX 73301	Austin (TX)	22
186810	319631	Lightning Charging Cable	12	1	14.95	14.95	2019-12-17 22:35:00	363 Highland St, Austin, TX 73301	Austin (TX)	22

	Order ID	Purchased Items
41	141275	USB-C Charging Cable , Wired Headphones
57	141290	Apple Airpods Headphones , AA Batteries (4-pack)
133	141365	Vareebadd Phone , Wired Headphones
153	141384	Google Phone , USB-C Charging Cable
220	141450	Google Phone , Bose SoundSport Headphones
...	...	...
186710	319536	Macbook Pro Laptop , Wired Headphones
186731	319556	Google Phone , Wired Headphones
186760	319584	iPhone , Wired Headphones
186773	319596	iPhone , Lightning Charging Cable
186809	319631	34in Ultrawide Monitor , Lightning Charging Cable

The business task of this project is to analyze the Sales Report from 12 months worth of data. the data comes in¶

12 individual files representing the sales of that month, and my job is to analyze the data and give recommendation per¶

question asked¶

Sales Report¶

importing libraries¶

task no.1 merging 12 months worth of data into one folder¶

Another method of concatination is through using a for loop¶

Saving and loading the updated data¶

Cleaning the data¶

1. dropping the NAN values¶

2. Finding "Or" value and deleting it¶

3. Converting the 'Quantity Ordered' and 'Price Each' columns to their correct data types¶

4. Creating a month column¶

Task no2. What was the highest earning month, and how much was the amount earned¶

1st I will create a column that will represent the total amount sold¶

re-ordering the columns on my data Frame¶

summing all the month's sales together and grouping the data by months¶

Creating a visualization of my findings¶

from the above analysis we can see that the highest earning month was December¶

task no3. Which city had the had the highest number of sales¶

creating a column that has names of cities and state only¶

summing all the city's sales together and grouping them by cities¶

creating visualization from my findings¶

from the above analysis, we can see that San Francisco (CA) was the city with the most sales¶

Task no.4 What time should we desplay adverts to maximize the likelihood of customer's buying product¶

1. Converting 'Order Date' column to a DateTime object¶

2. Grabbing the hour, from the datetime object¶

3. visualizing the data, grouping by hours¶

base on the above analysis, we recomend that the company displays its adverts between 11am and 7pm¶

Task no.5 Which products are most often sold together¶

1. create a data frame with data that share the same 'Order ID'¶

2. Group items that fall under the same 'Order ID' together, and give them a column¶

3. Dropping duplicates¶

Inserting libraries to help me iterate throught strings¶

4. counting the two products that are most sold together¶

Which product is the most sold product, and why?¶

1. Grouping all the products together¶

2. Summing all the orders of a product¶

3. Visualizing the data¶

From our analysis, we can see the the most ordered product is 'AAA Batteries (4-pack)'.....reason being that most gadgets in a house hold¶

are battery powered¶

THE END¶