29/06/2023, 19:43 pythonFinal
Submitted By: Zubaeerul Islam ID: 223001561
Initial Tasks
Loading Libraries
In [130]:
import numpy as np
import pandas as pd
import [Link] as plt
import seaborn as sns
sns.set_style('darkgrid')
[Link](font_scale=0.8)
%matplotlib inline
Mounting Drive
In [131]:
from [Link] import drive
[Link]('/content/drive')
Drive already mounted at /content/drive; to attempt to forcibly remo
unt, call [Link]("/content/drive", force_remount=True).
Loading Dataset
In [132]:
df = pd.read_csv('/content/drive/MyDrive/foodorder [Link]')
[Link]()
Out[132]:
order_id customer_id restaurant_name cuisine_type cost_of_the_order day_of_the_week r
0 1477147 337525 Hangawi Korean 30.75 Weekend
Blue Ribbon
1 1477685 358141 Japanese 12.08 Weekend
Sushi Izakaya
2 1477070 66393 Cafe Habana Mexican 12.23 Weekday
Blue Ribbon Fried
3 1477334 106968 American 29.20 Weekend
Chicken
4 1478249 76942 Dirty Bird to Go American 11.59 Weekday
[Link] 1/24
29/06/2023, 19:43 pythonFinal
Analyzing the Dataset
Question 01: How many rows & columns are present in the data?
In [133]:
[Link]
Out[133]:
(1898, 9)
Answer: There are total 1898 rows & 9 columns.
Question 02: What are the datatypes of the different columns in the dataset?
In [134]:
[Link](include='all').T
Out[134]:
count unique top freq mean std m
order_id 1898.0 NaN NaN NaN 1477495.5 548.049724 1476547
customer_id 1898.0 NaN NaN NaN 171168.478398 113698.139743 1311
Shake
restaurant_name 1898 178 219 NaN NaN Na
Shack
cuisine_type 1898 14 American 584 NaN NaN Na
cost_of_the_order 1898.0 NaN NaN NaN 16.498851 7.483812 4.4
day_of_the_week 1898 2 Weekend 1351 NaN NaN Na
Not
rating 1898 4 736 NaN NaN Na
given
food_preparation_time 1898.0 NaN NaN NaN 27.37197 4.632481 20
delivery_time 1898.0 NaN NaN NaN 24.161749 4.972637 15
[Link] 2/24
29/06/2023, 19:43 pythonFinal
In [135]:
[Link]
Out[135]:
order_id int64
customer_id int64
restaurant_name object
cuisine_type object
cost_of_the_order float64
day_of_the_week object
rating object
food_preparation_time int64
delivery_time int64
dtype: object
Question 03: Are there any missing values in the dataset?
Method 01
In [136]:
[Link]().sum()
Out[136]:
order_id 0
customer_id 0
restaurant_name 0
cuisine_type 0
cost_of_the_order 0
day_of_the_week 0
rating 0
food_preparation_time 0
delivery_time 0
dtype: int64
Method 02: Creating a user defined function to search for missing values in each column
[Link] 3/24
29/06/2023, 19:43 pythonFinal
In [137]:
def show_missing(df):
variables = []
dtypes = []
count = []
unique = []
missing = []
pc_missing = []
for item in [Link]:
[Link](item)
[Link](df[item].dtype)
[Link](len(df[item]))
[Link](len(df[item].unique()))
[Link](df[item].isna().sum())
pc_missing.append(round((df[item].isna().sum() / len(df[item])) * 100,
2))
output = [Link]({
'variable': variables,
'dtype': dtypes,
'count': count,
'unique': unique,
'missing': missing,
'% missing': pc_missing
})
return output
In [138]:
show_missing(df)
Out[138]:
variable dtype count unique missing % missing
0 order_id int64 1898 1898 0 0.0
1 customer_id int64 1898 1200 0 0.0
2 restaurant_name object 1898 178 0 0.0
3 cuisine_type object 1898 14 0 0.0
4 cost_of_the_order float64 1898 312 0 0.0
5 day_of_the_week object 1898 2 0 0.0
6 rating object 1898 4 0 0.0
7 food_preparation_time int64 1898 16 0 0.0
8 delivery_time int64 1898 19 0 0.0
[Link] 4/24
29/06/2023, 19:43 pythonFinal
In [139]:
[Link]()
Out[139]:
array(['Not given', '5', '3', '4'], dtype=object)
In [140]:
df['rating'].value_counts()
Out[140]:
Not given 736
5 588
4 386
3 188
Name: rating, dtype: int64
As per above finding, there's no missing value in any of the columns. However, in 'rating' column there are
736 entries where no rating was given by the users.
** Replacing the 'Not given' entries with 0
In [141]:
df['rating'].mask(df['rating'] == 'Not given', 0, inplace=True)
In [142]:
df['rating'].value_counts()
Out[142]:
0 736
5 588
4 386
3 188
Name: rating, dtype: int64
[Link] 5/24
29/06/2023, 19:43 pythonFinal
Question 04: Statistical summary of the data: What is the minimum, average &
maximum time required for food to be prepared once the order is placed?
In [143]:
[Link]()
Out[143]:
order_id customer_id cost_of_the_order food_preparation_time delivery_time
count 1.898000e+03 1898.000000 1898.000000 1898.000000 1898.000000
mean 1.477496e+06 171168.478398 16.498851 27.371970 24.161749
std 5.480497e+02 113698.139743 7.483812 4.632481 4.972637
min 1.476547e+06 1311.000000 4.470000 20.000000 15.000000
25% 1.477021e+06 77787.750000 12.080000 23.000000 20.000000
50% 1.477496e+06 128600.000000 14.140000 27.000000 25.000000
75% 1.477970e+06 270525.000000 22.297500 31.000000 28.000000
max 1.478444e+06 405334.000000 35.410000 35.000000 33.000000
Minimum food preparation time: 20 minutes
Maximum food preparation time: 35 minutes
Average food preparation time: 27.37 minutes
Question 05: How many orders are not rated?
In [144]:
df['rating'].value_counts()
Out[144]:
0 736
5 588
4 386
3 188
Name: rating, dtype: int64
As processed while answering question 03, the orders in which rating were 'Not given' were masked as '0';
and the number of such orders are 736.
Univariate Analysis
[Link] 6/24
29/06/2023, 19:43 pythonFinal
Question 06: Explore all the variables and provide observations on their
distributions.
In [145]:
[Link]
Out[145]:
Index(['order_id', 'customer_id', 'restaurant_name', 'cuisine_type',
'cost_of_the_order', 'day_of_the_week', 'rating',
'food_preparation_time', 'delivery_time'],
dtype='object')
i) Order ID
In [146]:
fig = [Link](data= df, x='cuisine_type',saturation=0.75, width=0.8, dodge
=True)
[Link]('Cuisine wise Frequency')
[Link]('Cuisines')
[Link]('Frequency')
[Link](rotation=90)
for label in [Link]:
fig.bar_label(label)
[Link]()
ii) Food Preparation Time
[Link] 7/24
29/06/2023, 19:43 pythonFinal
In [147]:
[Link](x='food_preparation_time', data=df, kde=True)
[Link]('Preparation Time in Minutes')
[Link]('Frequency')
[Link]()
iii) Delivery Time
In [148]:
[Link](x=df['delivery_time'])
Out[148]:
<Axes: xlabel='delivery_time'>
iv) Cost of Order
[Link] 8/24
29/06/2023, 19:43 pythonFinal
In [149]:
[Link](data=df, x='cost_of_the_order')
[Link]('Histogram: Cost per Order')
[Link]('Cost per Order')
[Link]('Frequency')
Out[149]:
Text(0, 0.5, 'Frequency')
v) Order Concentration in terms of Days
In [150]:
order_day = df['day_of_the_week'].value_counts()
[Link](order_day, labels=order_day.index, autopct="%.0f%%")
[Link]()
[Link] 9/24
29/06/2023, 19:43 pythonFinal
vi) Order Distribution in terms of Ratings
In [151]:
rate = df['rating'].value_counts()
[Link](rate, labels=[Link], autopct="%.0f%%")
[Link]()
Question 07: Top 5 restaurants in terms of orders received.
In [152]:
fig = df['restaurant_name'].value_counts()[:5].plot(kind='barh')
for label in [Link]:
fig.bar_label(label)
[Link] 10/24
29/06/2023, 19:43 pythonFinal
Question 08: Most popular cuisine on weekends
In [153]:
for day in df['day_of_the_week'] == 'Weekends':
x = df['cuisine_type'].value_counts()
[Link](kind = 'barh')
Out[153]:
<Axes: >
American cusisine is the most popular in Weekend orders.
Question 09: What is the pecentage of order that cost more than $20?
In [154]:
total_order = [Link][0]
above_20 = df[df['cost_of_the_order'] > 20].shape[0]
percentage = (above_20/total_order)*100
print(f"The percentage of orders that cost more than $20 is: {percentage:.2f}%")
The percentage of orders that cost more than $20 is: 29.24%
Question 10: What is the mean order delivery time?
In [155]:
x = df['delivery_time'].mean()
print(f"Mean order delivery time is: {x: .2f} minutes.")
Mean order delivery time is: 24.16 minutes.
[Link] 11/24
29/06/2023, 19:43 pythonFinal
Question 11: Top 3 IDs with highest number of orders.
In [156]:
x = df['customer_id'].value_counts()[:3]
x
Out[156]:
52832 13
47440 10
83287 9
Name: customer_id, dtype: int64
Multivariate Analysis
In [157]:
[Link]
Out[157]:
Index(['order_id', 'customer_id', 'restaurant_name', 'cuisine_type',
'cost_of_the_order', 'day_of_the_week', 'rating',
'food_preparation_time', 'delivery_time'],
dtype='object')
i) Correlation
[Link] 12/24
29/06/2023, 19:43 pythonFinal
In [158]:
[Link](data=df[['restaurant_name','cuisine_type','cost_of_the_order','day_o
f_the_week', 'rating', 'food_preparation_time', 'delivery_time']].corr())
<ipython-input-158-744299572a85>:1: FutureWarning: The default value
of numeric_only in [Link] is deprecated. In a future versio
n, it will default to False. Select only valid columns or specify th
e value of numeric_only to silence this warning.
[Link](data=df[['restaurant_name','cuisine_type','cost_of_the
_order','day_of_the_week', 'rating', 'food_preparation_time', 'deliv
ery_time']].corr())
Out[158]:
<Axes: >
No significant correlation exists.
ii) Order count and Total order value by cuisine type, day to the week & rating.
In [159]:
print("Summary")
print("--------------------------------------------")
print("Total Number of Orders: ", df['order_id'].nunique())
print("Total Number of Restaurants: ", df['restaurant_name'].nunique())
print("Total Number of Cuisines: ", df['cuisine_type'].nunique())
Summary
--------------------------------------------
Total Number of Orders: 1898
Total Number of Restaurants: 178
Total Number of Cuisines: 14
Creating a user defined function to plot graphs
[Link] 13/24
29/06/2023, 19:43 pythonFinal
In [160]:
def plot_orders(x):
order_number = pd.pivot_table(data = df, index = x, values = ['order_id', 'c
ost_of_the_order'], aggfunc = {'order_id':'count', 'cost_of_the_order':'sum'}).r
eset_index().sort_values(by = 'cost_of_the_order', ascending = False)
[Link]['[Link]'] = [12,5]
ax1 = [Link](x = order_number.iloc[:,0], y = 'cost_of_the_order', data
= order_number, color = '#87CEEB', edgecolor = 'black')
[Link](rotation = 90, fontsize = 8)
[Link](None)
[Link]('Total Order Value', fontsize = 8)
ax2 = [Link]()
[Link](x = order_number.iloc[:,0], y = 'order_id', data = order_numbe
r, marker = 'o', color = '#0A6044',ax = ax2)
[Link]('#Orders', fontsize = 8)
[Link]("Orders Received and Total Order Value by " + x, fontsize = 10)
[Link]()
In [161]:
plot_orders('day_of_the_week')
[Link] 14/24
29/06/2023, 19:43 pythonFinal
In [162]:
plot_orders('cuisine_type')
In [163]:
plot_orders('rating')
[Link] 15/24
29/06/2023, 19:43 pythonFinal
Question 13: identifying the restaurants that have received more than 50
ratings & average rating is greater than 4.
In [164]:
# Creating a new dataframe of only related columns
df_1 = [Link][:,['order_id', 'restaurant_name', 'rating']]
df_1.head(3)
# Dropping the rows where rating is 0
df_2 = df_1.drop(df_1[df_1['rating'] == 0].index)
# Grouping & filtering the targeted data
grouped_data = df_2.groupby('restaurant_name')['rating'].agg(['count', 'mean'])
filtered_data = grouped_data[(grouped_data['count'] > 50) & (grouped_data['mea
n'] > 4)]
print(filtered_data)
count mean
restaurant_name
Blue Ribbon Fried Chicken 64 5.552274e+61
Blue Ribbon Sushi 73 6.089773e+70
Shake Shack 133 4.177018e+130
The Meatball Shop 84 6.494697e+81
Question 14: The company charges the restaurant 25% on the orders having
cost greater than USD20 & 15% on the orders having cost greater than USD5.
Finding the net revenue.
In [165]:
df_rev = [Link][:, [0,4]]
df_rev.head()
Out[165]:
order_id cost_of_the_order
0 1477147 30.75
1 1477685 12.08
2 1477070 12.23
3 1477334 29.20
4 1478249 11.59
[Link] 16/24
29/06/2023, 19:43 pythonFinal
In [166]:
revenue = []
for cost in df_rev['cost_of_the_order']:
if cost > 20:
[Link](cost * 0.25)
elif cost > 5:
[Link](cost * 0.15)
else:
[Link](0)
df_rev['Revenue'] = revenue
df_rev.head()
<ipython-input-166-b8a8e9939569>:11: 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: [Link]
as-docs/stable/user_guide/[Link]#returning-a-view-versus-a-co
py
df_rev['Revenue'] = revenue
Out[166]:
order_id cost_of_the_order Revenue
0 1477147 30.75 7.6875
1 1477685 12.08 1.8120
2 1477070 12.23 1.8345
3 1477334 29.20 7.3000
4 1478249 11.59 1.7385
In [167]:
net_revenue = df_rev['Revenue'].sum()
print("The company generated net revenue of $",net_revenue)
The company generated net revenue of $ 6166.303
[Link] 17/24
29/06/2023, 19:43 pythonFinal
Question 15: The company wants to analyze the total time required to deliver
the food. What percentage of orders take more than 60 minutes to get
delivered from the time the order is placed?
In [168]:
df_time = df_rev = [Link][:, [0,7,8]]
df_time.head()
Out[168]:
order_id food_preparation_time delivery_time
0 1477147 25 20
1 1477685 25 23
2 1477070 23 28
3 1477334 25 15
4 1478249 25 24
In [169]:
df_time['total_time'] = df_time['delivery_time']+df_time['food_preparation_tim
e']
df_time.head()
<ipython-input-169-8650e2560dd1>: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: [Link]
as-docs/stable/user_guide/[Link]#returning-a-view-versus-a-co
py
df_time['total_time'] = df_time['delivery_time']+df_time['food_pre
paration_time']
Out[169]:
order_id food_preparation_time delivery_time total_time
0 1477147 25 20 45
1 1477685 25 23 48
2 1477070 23 28 51
3 1477334 25 15 40
4 1478249 25 24 49
[Link] 18/24
29/06/2023, 19:43 pythonFinal
In [170]:
percentage = (df_time[df_time['total_time'] > 60].shape[0] / df_time.shape[0]) *
100
print(f"{percentage:.2f}% of total orders took more than 60 minutes to get deliv
ered from the time the order is placed.")
10.54% of total orders took more than 60 minutes to get delivered fr
om the time the order is placed.
Question 16: How does the mean delivery time vary during weekdays &
weekends?
In [171]:
def plot_time(x):
order_count = pd.pivot_table(data = df, index = x, values = ['order_id', 'de
livery_time'], aggfunc = {'order_id':'count', 'delivery_time':'mean'}).reset_ind
ex().sort_values(by = 'delivery_time', ascending = False)
[Link]['[Link]'] = [12,5]
ax1 = [Link](x = order_count.iloc[:,0], y = 'delivery_time', data = ord
er_count, color = '#87CEEB', edgecolor = 'black')
[Link](rotation = 90, fontsize = 8)
[Link](None)
[Link]('Mean Delivery Time', fontsize = 8)
for i, val in enumerate(order_count['delivery_time']):
[Link](i, val + 0.2, str(round(val, 2)), ha='center', va='bottom', fon
tsize=8)
[Link]("Orders delivered and #Orders by " + x, fontsize = 10)
[Link]()
In [172]:
plot_time('day_of_the_week')
[Link] 19/24
29/06/2023, 19:43 pythonFinal
The mean delivery time in Weekday is higher by approximately 6 minutes than Weekend.
Question 17: Business recommendations based on Cuisine type & Ratings.
i) Focusing more on customer ratings.
In [173]:
[Link](1, 2, 1)
fig = [Link](data=df, x='rating')
[Link]('Ratings')
[Link]('Rating')
[Link]('Frequency')
for label in [Link]:
fig.bar_label(label)
[Link](1, 2, 2)
rate = df['rating'].value_counts()
[Link](rate, labels=[Link], autopct="%.0f%%")
[Link]('Ratings %')
[Link]()
The above chart shows that total 736 (39%) order were not rated by the customers. This shows that users
are not motivated to rate the service of the app and the restaurants. At this trend, it will be difficult to ensure
service quality and to truly reflect customer feedback. FoodHub should focus on this to ensure continuous
improvement.
ii) Cuisine wise Analysis
[Link] 20/24
29/06/2023, 19:43 pythonFinal
In [174]:
import pandas as pd
cuisine_volume = df_ana.groupby('cuisine_type')['cost_of_the_order'].agg(['sum',
'count'])
fig, ax = [Link](figsize=(16,10), dpi=80)
# Plot 1 - Sum of Order Amounts
[Link](x=cuisine_volume.index, ymin=0, ymax=cuisine_volume['sum'], color='fir
ebrick', alpha=0.7, linewidth=2)
[Link](x=cuisine_volume.index, y=cuisine_volume['sum'], s=75, color='firebri
ck', alpha=0.7, label='Order Amounts')
# Plot 2 - Count of Orders
[Link](x=cuisine_volume.index, ymin=0, ymax=cuisine_volume['count'], color='s
teelblue', alpha=0.7, linewidth=2)
[Link](x=cuisine_volume.index, y=cuisine_volume['count'], s=75, color='steel
blue', alpha=0.7, label='Order Count')
# Title, Label, Ticks, and Legend
ax.set_title('Cuisine wise Order Amount and Count', fontdict={'size': 12})
ax.set_ylabel('Amount / Count')
ax.set_xticks(cuisine_volume.index)
[Link]()
[Link]()
# Calculate Percentages
total_amount = cuisine_volume['sum'].sum()
total_count = cuisine_volume['count'].sum()
cuisine_volume['% of Total Amount'] = cuisine_volume['sum'] / total_amount * 100
cuisine_volume['% of Total Count'] = cuisine_volume['count'] / total_count * 100
# Create Data Table
data_table = cuisine_volume.reset_index()
data_table.columns = ['Cuisine Type', 'Order Amounts', 'Order Count', '% of Tota
l Amount', '% of Total Count']
print(data_table)
[Link] 21/24
29/06/2023, 19:43 pythonFinal
Cuisine Type Order Amounts Order Count % of Total Amount \
0 American 6187.18 368 31.768211
1 Chinese 2152.81 133 11.053650
2 French 200.87 10 1.031371
3 Indian 833.17 50 4.277930
4 Italian 2947.38 172 15.133387
5 Japanese 4462.36 273 22.912085
6 Korean 118.28 9 0.607311
7 Mediterranean 508.56 32 2.611212
8 Mexican 783.33 48 4.022025
9 Middle Eastern 682.58 34 3.504722
10 Southern 244.48 13 1.255288
11 Spanish 118.31 6 0.607465
12 Thai 172.67 9 0.886578
13 Vietnamese 64.03 5 0.328763
% of Total Count
0 31.669535
1 11.445783
2 0.860585
3 4.302926
4 14.802065
5 23.493976
6 0.774527
7 2.753873
8 4.130809
9 2.925990
10 1.118761
11 0.516351
12 0.774527
13 0.430293
[Link] 22/24
29/06/2023, 19:43 pythonFinal
From the above chart & table we can see that percentage contribution of the cuisines in total amount &
volume of order is almost proportional. This signifies that there's no comparative edge of any of the cuisine
in terms of relative amount & volume. This might be because of almost similar unit pricing irrespective of
cuisine type. As such, in terms of cuisine, the app doesn't have any winning segment. However, American,
Japanese, Italian & Chinese cuisine are presumably highly demanding food type. So the app may consider
giving promotional offers in those to attract higher traffic.
iii) Impact of Weekdays & Weekends in the business.
Graph iii-a
In [175]:
# Recalling an earlier function shown in multivariate analysis.
[Link](figsize=(10, 4))
plot_orders('day_of_the_week')
[Link]()
Graph iii-b
[Link] 23/24
29/06/2023, 19:43 pythonFinal
In [176]:
# Recalling an earlier function shown in multivariate analysis.
[Link](figsize=(10, 4))
plot_time('day_of_the_week')
[Link]()
Graph iii-a clearly shows that the traffic and order volume both are significantly higher in weekends. Graph
iii-b shows that the mean delivery time is significantly lower in weekends. FoodHub may plan a strategy of
'Quick Delivery' within 'X-minutes' as a marketing campaign to attract more users in weekends.
[Link] 24/24