Jovian
⭐️
Sign In
In [70]:
import numpy as np
import pandas as pd
from datetime import datetime
import smtplib
In [71]:
server=smtplib.SMTP('smtp.gmail.com',587)
In [72]:
server.ehlo()
Out[72]:
(250,
 b'smtp.gmail.com at your service, [106.208.232.92]\nSIZE 35882577\n8BITMIME\nSTARTTLS\nENHANCEDSTATUSCODES\nPIPELINING\nCHUNKING\nSMTPUTF8')
In [73]:
server.starttls()
Out[73]:
(220, b'2.0.0 Ready to start TLS')
In [74]:
server.login("vikas805gupta@gmail.com","mlwwjmsqzoqcezkj")
Out[74]:
(235, b'2.7.0 Accepted')
In [75]:
message='''
{}-{}
1: Total sales amount within the period:- {}
2:Total number of sales within the period:- {}
3:number of sales city wise within the period:- {}
4:number of sales by productline within the period:- {}
5:What is the average price of items sold:- {}
6:How many items were sold which costed above the average price:- {}
7:how many items were sold which costed less than the average? (within the period):- {} 
8:How many orders were cancelled:- {}
9:how many were shipped:- {}
10:Which product should be given focus to increase profitability. (it would ideally be the product which has a price above average price and is being sold the most):- {} 
11:which product should be eliminated. (the product which is cheap and is still not being sold enough ie has the minimum quantity):- {}



'''.format(Start_date,End_date,Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11)

In [76]:
message

Out[76]:
'\n2003-02-20 00:00:00-2003-05-07 00:00:00\n1: Total sales amount within the period:- 420870.17\n2:Total number of sales within the period:- 4162\n3:number of sales city wise within the period:- CITY\nBarcelona         36\nCharleroi         27\nLule              52\nMakati City      561\nManchester       570\nMelbourne        525\nNYC              439\nParis            351\nPhiladelphia     212\nReims            185\nSalzburg         442\nSan Francisco    217\nSan Rafael       143\nSingapore        402\nName: QUANTITYORDERED, dtype: int64\n4:number of sales by productline within the period:- Classic Cars        38\nVintage Cars        24\nMotorcycles         24\nPlanes              12\nTrucks and Buses    11\nShips                9\nTrains               3\nName: PRODUCTLINE, dtype: int64\n5:What is the average price of items sold:- 3478.2658677685963\n6:How many items were sold which costed above the average price:- 1809\n7:how many items were sold which costed less than the average? (within the period):- 2353 \n8:How many orders were cancelled:- 121\n9:how many were shipped:- 0\n10:Which product should be given focus to increase profitability. (it would ideally be the product which has a price above average price and is being sold the most):- PRODUCTLINE\nClassic Cars        489\nMotorcycles         603\nPlanes              272\nShips               201\nTrains               69\nTrucks and Buses    165\nVintage Cars        619\nName: QUANTITYORDERED, dtype: int64 \n11:which product should be eliminated. (the product which is cheap and is still not being sold enough ie has the minimum quantity):- PRODUCTLINE\nClassic Cars        489\nMotorcycles         603\nPlanes              272\nShips               201\nTrains               69\nTrucks and Buses    165\nVintage Cars        619\nName: QUANTITYORDERED, dtype: int64\n\n\n\n'
In [77]:
server.sendmail("vikas805gupta@gmail.com","info@appstone.in",message)
Out[77]:
{}
In [ ]:
 
In [2]:
data=pd.read_csv("data.csv")
In [3]:
data.head(20)

Out[3]:
In [4]:
type("ORDERDATE")
Out[4]:
str
In [8]:
def convert_date(date):
    return datetime.strptime(date,'%m/%d/%Y %H:%M')
In [9]:
convert_date
Out[9]:
<function __main__.convert_date(date)>
In [10]:
convert_date(date)
--------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-10-fa99d80e45ae> in <module> ----> 1 convert_date(date) NameError: name 'date' is not defined
In [11]:
data["ORDERDATE"]=data["ORDERDATE"].apply(lambda row: convert_date(row))
In [12]:
data["Average"]=data["SALES"].mean(axis=0)
data.head()

In [13]:
data.head()
Out[13]:
In [38]:
Start_date=input("Enter the 1st date= ")
End_date=input("Enter the last date= ")

Start_date= datetime.strptime(Start_date, "%d-%m-%Y" )
End_date= datetime.strptime(End_date, "%d-%m-%Y" )

print("1: Total sales amount within the period:-")
C=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)]["SALES"].sum()
print(Q1)

print("2:Total number of sales within the period:-")
Q2=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)]["QUANTITYORDERED"].sum()
print(Q2)

print("3:Number of sales city wise within the period:-")
Q3=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)].groupby("CITY")["QUANTITYORDERED"].sum()
print(Q3)

print("4:Number of sales by productline within the period:-")
Q4=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)].groupby("PRODUCTLINE")["QUANTITYORDERED"].value_counts()
print(Q4)

print("5:What is the average price of items sold.") ##How many items were sold which costed above the average price and how many items were sold which costed less than the average? (within the period):- ")
Q5=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)]["SALES"].mean()
print(Q5)


print("How many items were sold which costed above the average price")
q5=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["SALES"]>3478.2658677685963)]["QUANTITYORDERED"].sum()
print(q5)





Enter the 1st date= 20-02-2003 Enter the last date= 07-05-2003 1: Total sales amount within the period:- 420870.17 2:Total number of sales within the period:- 4162 3:Number of sales city wise within the period:- CITY Barcelona 36 Charleroi 27 Lule 52 Makati City 561 Manchester 570 Melbourne 525 NYC 439 Paris 351 Philadelphia 212 Reims 185 Salzburg 442 San Francisco 217 San Rafael 143 Singapore 402 Name: QUANTITYORDERED, dtype: int64 4:Number of sales by productline within the period:- Classic Cars 38 Vintage Cars 24 Motorcycles 24 Planes 12 Trucks and Buses 11 Ships 9 Trains 3 Name: PRODUCTLINE, dtype: int64 5:What is the average price of items sold. 3478.2658677685963 How many items were sold which costed above the average price 1809
In [15]:
print("1: Total sales amount within the period:-")
data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)]["SALES"].sum()

1: Total sales amount within the period:-
Out[15]:
420870.17
In [16]:
print("2:Total number of sales within the period:-")
data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)]["QUANTITYORDERED"].sum()
2:Total number of sales within the period:-
Out[16]:
4162
In [18]:
print("3:Number of sales city wise within the period:-")
data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)].groupby("CITY")["QUANTITYORDERED"].sum()

3:Number of sales city wise within the period:-
Out[18]:
CITY
Barcelona         36
Charleroi         27
Lule              52
Makati City      561
Manchester       570
Melbourne        525
NYC              439
Paris            351
Philadelphia     212
Reims            185
Salzburg         442
San Francisco    217
San Rafael       143
Singapore        402
Name: QUANTITYORDERED, dtype: int64
In [47]:
print("4:Number of sales by productline within the period:-")
data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)].groupby("PRODUCTLINE")["QUANTITYORDERED"].sum()

4:Number of sales by productline within the period:-
Out[47]:
PRODUCTLINE
Classic Cars        1394
Motorcycles          796
Planes               401
Ships                323
Trains                69
Trucks and Buses     372
Vintage Cars         807
Name: QUANTITYORDERED, dtype: int64
In [ ]:
 
In [ ]:
 
In [ ]:
 
In [ ]:
 
In [50]:
print("5:What is the average price of items sold.")
Q5=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)]["SALES"].mean(axis=0)
print(Q5)
5:What is the average price of items sold. 3478.2658677685963
In [51]:
print("How many items were sold which costed above the average price")
Q6=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["SALES"]>3478.2658677685963)]["QUANTITYORDERED"].sum()
print(Q6)
How many items were sold which costed above the average price 1809
In [52]:
print("how many items were sold which costed less than the average")
Q7=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["SALES"]<3478.2658677685963)]["QUANTITYORDERED"].sum()
print(Q7)
how many items were sold which costed less than the average 2353
In [53]:
print("Orders were Shipped")
Q8=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["STATUS"]=="Shipped")]["STATUS"].count()
print(Q8)
print("Orders were cancelled")
Q9=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["STATUS"]=="Cancelled")]["STATUS"].count()
print(Q9)

Orders were Shipped 121 Orders were cancelled 0
In [25]:
data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["SALES"]>3553.889072)][["SALES","PRODUCTLINE"]]
Out[25]:
In [26]:
data.head()
Out[26]:
In [27]:
data[data["SALES"]== 9264.86]["PRODUCTLINE"]
Out[27]:
81    Motorcycles
Name: PRODUCTLINE, dtype: object
In [66]:
Q10=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["SALES"]<3553.889072)].groupby("PRODUCTLINE")["QUANTITYORDERED"].sum()
print(Q10)
PRODUCTLINE Classic Cars 489 Motorcycles 603 Planes 272 Ships 201 Trains 69 Trucks and Buses 165 Vintage Cars 619 Name: QUANTITYORDERED, dtype: int64
In [68]:
Q11=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["SALES"]<3553.889072)].groupby("PRODUCTLINE")["QUANTITYORDERED"].sum()
print(Q11)
PRODUCTLINE Classic Cars 489 Motorcycles 603 Planes 272 Ships 201 Trains 69 Trucks and Buses 165 Vintage Cars 619 Name: QUANTITYORDERED, dtype: int64
In [29]:
Classic_Cars=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Classic Cars")&(data["SALES"]>3553.889072)]["QUANTITYORDERED"].sum()
print("Classic Cars={}".format(Classic_Cars))

Vintage_Cars=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Vintage Cars")&(data["SALES"]>3553.889072)]["QUANTITYORDERED"].sum()
print("Vintage Cars={}".format(Vintage_Cars))

Motorcycles=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Motorcycles")&(data["SALES"]>3553.889072)]["QUANTITYORDERED"].sum()
print("Motorcycles={}".format(Motorcycles))

Planes =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Planes ")&(data["SALES"]>3553.889072)]["QUANTITYORDERED"].sum()
print("Planes={}".format(Planes))

Trucks_and_Buses =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Trucks and Buses ")&(data["SALES"]>3553.889072)]["QUANTITYORDERED"].sum()
print("Trucks and Buses={}".format(Trucks_and_Buses))

Ships =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Ships ")&(data["SALES"]>3553.889072)]["QUANTITYORDERED"].sum()
print("Ships={}".format(Ships))

Trains =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Trains ")&(data["SALES"]>3553.889072)]["QUANTITYORDERED"].sum()
print("Trains={}".format(Trains))





Classic Cars=905 Vintage Cars=188 Motorcycles=193 Planes=0 Trucks and Buses=0 Ships=0 Trains=0
In [30]:
Classic_Cars=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Classic Cars")&(data["SALES"]<3553.889072)]["QUANTITYORDERED"].sum()
print("Classic Cars={}".format(Classic_Cars))

Vintage_Cars=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Vintage Cars")&(data["SALES"]<3553.889072)]["QUANTITYORDERED"].sum()
print("Vintage Cars={}".format(Vintage_Cars))

Motorcycles=data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Motorcycles")&(data["SALES"]<3553.889072)]["QUANTITYORDERED"].sum()
print("Motorcycles={}".format(Motorcycles))

Planes =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Planes ")&(data["SALES"]<3553.889072)]["QUANTITYORDERED"].sum()
print("Planes={}".format(Planes))

Trucks_and_Buses =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Trucks and Buses ")&(data["SALES"]<3553.889072)]["QUANTITYORDERED"].sum()
print("Trucks and Buses={}".format(Trucks_and_Buses))

Ships =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Ships ")&(data["SALES"]<3553.889072)]["QUANTITYORDERED"].sum()
print("Ships={}".format(Ships))

Trains =data[(data["ORDERDATE"]>=Start_date)&(data["ORDERDATE"]<=End_date)&(data["PRODUCTLINE"]=="Trains ")&(data["SALES"]<3553.889072)]["QUANTITYORDERED"].sum()
print("Trains={}".format(Trains))



Classic Cars=489 Vintage Cars=619 Motorcycles=603 Planes=0 Trucks and Buses=0 Ships=0 Trains=0
In [78]:
import jovian
In [ ]:
jovian.commit()
[jovian] Saving notebook..
In [ ]:
 
In [ ]:
import jovian as jvn