# installing required package
pip install openpyxl
Collecting openpyxl
Downloading openpyxl-3.0.9-py2.py3-none-any.whl (242 kB)
|████████████████████████████████| 242 kB 13.2 MB/s
Collecting et-xmlfile
Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.0.9
WARNING: You are using pip version 20.1.1; however, version 21.3.1 is available.
You should consider upgrading via the '/usr/local/bin/python -m pip install --upgrade pip' command.
Note: you may need to restart the kernel to use updated packages.
# imported the dataset and performing eda analysis
import pandas as pd
df = pd.read_excel("/work/2019 Winter Data Science Intern Challenge Data Set.xlsx")
df.head()
order_idint64
shop_idint64
0
1
53
1
2
92
2
3
44
3
4
18
4
5
18
# to understand how many 30-day window provided
df.sort_values(by=['created_at'])['created_at']
df.describe()
order_idfloat64
shop_idfloat64
count
5000
5000
mean
2500.5
50.0788
std
1443.520003
29.00611766
min
1
1
25%
1250.75
24
50%
2500.5
50
75%
3750.25
75
max
5000
100
# check any duplicate rows
df.duplicated().sum()
total_revenue_by_shopid = df.groupby(['shop_id'])['order_amount'].agg('sum')
total_revenue_by_shopid.name ='total_revenue'
total_orders_by_shopid = df.groupby(['shop_id'])['total_items'].agg('sum')
total_orders_by_shopid.name = 'total_orders'
df_by_shopid = pd.concat([total_revenue_by_shopid, total_orders_by_shopid], axis=1)
aov_by_shopid = df_by_shopid['total_revenue']/df_by_shopid['total_orders']
aov_by_shopid.name = 'aov'
df_by_shopid = pd.concat([total_revenue_by_shopid, total_orders_by_shopid , aov_by_shopid], axis=1)
df_by_shopid
total_revenueint64
6840 - 11990176
total_ordersint64
67 - 34063
1
13588
86
2
9588
102
3
14652
99
4
13184
103
5
13064
92
6
22627
121
7
12208
109
8
11088
84
9
13806
117
10
17612
119
df_by_shopid.describe()
total_revenuefloat64
total_ordersfloat64
count
100
100
mean
157256.4
439.36
std
1216217.937
3396.366111
min
6840
67
25%
12930.5
88
50%
14887.5
100
75%
17600
111.25
max
11990176
34063
df.sort_values(by=['order_amount'], ascending= False)
order_idint64
1 - 5000
shop_idint64
1 - 100
2153
2154
42
3332
3333
42
520
521
42
1602
1603
42
60
61
42
2835
2836
42
4646
4647
42
2297
2298
42
1436
1437
42
4882
4883
42
df_shopid_42 = df[df['shop_id'] == 42]
df_shopid_42.describe()
order_idfloat64
shop_idfloat64
count
51
51
mean
2441.921569
42
std
1484.456801
0
min
16
42
25%
1366.5
42
50%
2154
42
75%
3801
42
max
4883
42
df_shopid_78 = df[df['shop_id'] == 78]
df_shopid_78.describe()
order_idfloat64
shop_idfloat64
count
46
46
mean
2663.021739
78
std
1338.52002
0
min
161
78
25%
1428.25
78
50%
2796.5
78
75%
3720.25
78
max
4919
78
# dropping aov_per_shop record for shop_id 78
clean_df_by_shopid = df_by_shopid.drop([78])
clean_df_by_shopid.describe()
total_revenuefloat64
total_ordersfloat64
count
99
99
mean
135978.1818
442.9090909
std
1203553.715
3413.464145
min
6840
67
25%
12867
88
50%
14835
100
75%
17540
111.5
max
11990176
34063
print("Mean AOV:",clean_df_by_shopid['aov'].mean())
print("Median AOV:",clean_df_by_shopid['aov'].median())
print("Mode AOV:",clean_df_by_shopid['aov'].mode()[0])
Mean AOV: 152.26262626262627
Median AOV: 153.0
Mode AOV: 153.0
print("Mean AOV:",df_by_shopid['aov'].mean())
print("Median AOV:",df_by_shopid['aov'].median())
print("Mode AOV:",df_by_shopid['aov'].mode()[0])
Mean AOV: 407.99
Median AOV: 153.0
Mode AOV: 153.0