API tools faq
paste
Advertisement
Guest User

blase

a guest
Dec 11th, 2018
188
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 23.92 KB | None | 0 0
raw download clone embed print report
  1. # -*- coding: utf-8 -*-
  2. """
  3. Created on Wed Dec 5 19:25:08 2018
  4.  
  5. @author: shrim
  6. """
  7.  
  8. # -*- coding: utf-8 -*-
  9. """
  10. Created on Wed Nov 7 12:34:11 2018
  11.  
  12. @author: alvin
  13. """
  14.  
  15.  
  16. import sqlite3
  17. import getpass
  18. import time
  19. import pandas as pd
  20. import dns.resolver
  21. import scipy.stats
  22. import seaborn as sns
  23. import matplotlib.pyplot as plt
  24.  
  25.  
  26. xl = pd.ExcelFile(r"SalesDataFull.xlsx")
  27. OrdersOnlyData = xl.parse("Orders")
  28. CopyOrdersData = OrdersOnlyData.copy()
  29. lines = "="*60
  30. connection = sqlite3.connect('OS_Employee.db',timeout=5)
  31. c= connection.cursor()
  32. connection2= sqlite3.connect('Orders.db',timeout=5)
  33. c2= connection2.cursor()
  34.  
  35.  
  36. def login():
  37. print("Login")
  38. print()
  39. print("Enter 1 to Login")
  40. print("Enter 2 to Create an Account")
  41. print("Enter 3 to Exit")
  42.  
  43. def get_login():
  44. accepted = False
  45. while not accepted:
  46. choice = int(input("Enter 1, 2, or 3: "))
  47. if 1 <= choice <= 5:
  48. accepted = True
  49. else:
  50. print()
  51. print("Please enter a valid value:")
  52. return choice
  53.  
  54. def main_menu():
  55. time.sleep(1)
  56. print("")
  57. print("Main Menu")
  58. print()
  59. print("1. Top 10 Most Profitable products by Year + Quarters")
  60. print("2. Top 10 Least Profitable by Year + Quarters")
  61. print("3. Log out")
  62. print("4. Top 10 Most profitable states and region")
  63. print("5. Top 10 Least Profitable States and region")
  64. print("6. See Plot for Associate between Discounts and Quantities Sold in South")
  65. print("7. See Plot for Associate between Discounts and Quantities Sold in Central")
  66. print("8. See Plot for Associate between Discounts and Quantities Sold in East")
  67. print("9. See Plot for Associate between Discounts and Quantities Sold in West")
  68.  
  69. def get_main_menu():
  70. accepted = False
  71. while not accepted:
  72. choice = int(input("Please select an option: "))
  73. if 0 <= choice <= 10:
  74. accepted = True
  75. else:
  76. print()
  77. print("Please enter a valid value: (1)")
  78. return choice
  79.  
  80. '''def plot(region, num):
  81. word = ""
  82. if num == 6:
  83. word = "Central"
  84. elif num == 2:
  85. word = "South"
  86. elif num == 3:
  87. word = "West"
  88. elif num == 4:
  89. word = "East"
  90. RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == word, :]
  91. ActiveDataFrame = RegionalSales
  92. x = "Discount"
  93. y = "Quantity"
  94. getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
  95. correlation = str(getCorr[0])
  96. pValue = str(getCorr[1])
  97. print("The correlation between " + x + " and " + y + " in the " + word + " Region is: " + correlation)
  98. print("With p value of: " + pValue)
  99. sns.lmplot(x=x, y=y, data=ActiveDataFrame)
  100. plt.xlabel(x)
  101. plt.ylabel(y)
  102. plt.title("Correlation of " + x + " and " + y + " in region: " + word)
  103. plt.show()
  104. '''
  105. def validate_data(year, num):
  106.  
  107. sort = True
  108. word = ""
  109. if num == 1:
  110. sort = False
  111. word = "Most"
  112. else:
  113. sort = True
  114. word = "Least"
  115.  
  116. prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
  117. prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
  118. months = (pd.DatetimeIndex(prod_select["Order Date"]).month.unique()).sort_values()
  119. print("Year: " + str(year) + ".")
  120.  
  121. for month in months:
  122.  
  123. months_list = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]
  124. prod_select_month = prod_select.loc[prod_select["Order Date"].dt.month == month]
  125. best_select = prod_select_month.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
  126. print("\n" + word + " profitable products in " + months_list[month-1] + "\n")
  127. print(best_select.head(10))
  128.  
  129. def validate_quarter(year, num):
  130. sort = True
  131. word = ""
  132. if num == 1:
  133. sort = False
  134. word = "Most"
  135. else:
  136. sort = True
  137. word = "Least"
  138.  
  139. prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
  140. prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
  141. quarters = (pd.DatetimeIndex(prod_select["Order Date"]).quarter.unique()).sort_values()
  142. print("Year: " + str(year) + ".")
  143.  
  144. for quarter in quarters:
  145.  
  146. quarters_list = ["1st Quarter", "2nd Quarter", "3rd Quarter", "4th Quarter"]
  147. prod_select_quarter = prod_select.loc[prod_select["Order Date"].dt.quarter == quarter]
  148. best_select = prod_select_quarter.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
  149. print("\n" + word + " profitable products in " + quarters_list[quarter-1] + "\n")
  150. print(best_select.head(10))
  151. #after year choose whether they want a specific month or every month, or by quarter = 3/12 Jan,Feb,Mar
  152.  
  153.  
  154. '''def product_by_quarter(year, num):
  155. lines = "="*25
  156. sort = True
  157. word = ""
  158. if num == 1:
  159. sort = False
  160. word = "Most"
  161. else:
  162. sort = True
  163. word = "Least"
  164.  
  165. prod_prof = OrdersOnlyData[["Product Name", "Order Date", "Profit"]]
  166. prod_select = prod_prof.loc[prod_prof["Order Date"].dt.year == year]
  167. quarters = (pd.DatetimeIndex(prod_select["Order Date"]).quarter.unique()).sort_values()
  168. print("\n" + lines + lines + "\n")
  169. print("Year: " + str(year) + ".")
  170.  
  171. for quarter in quarters:
  172.  
  173. quarters_list = ["Quarter I", "Quarter II", "Quarter III", "Quarter IV"]
  174. prod_select_quarter = prod_select.loc[prod_select["Order Date"].dt.quarter == quarter]
  175. best_select = prod_select_quarter.groupby('Product Name').sum().sort_values(by = "Profit", ascending = sort)
  176. print("\n" + word + " profitable products in " + quarters_list[quarter-1] + "\n")
  177. print("\n" + lines + lines + "\n")
  178. print(best_select.head(10))
  179. '''
  180. def main():
  181. finished = False
  182. while not finished:
  183. login()
  184. choice = get_login()
  185. if choice == 1:
  186. with sqlite3.connect('OS_Employee.db'):
  187. email = input('Please enter your email address: ')
  188. password = getpass.getpass('Please enter your password: ')
  189. try:
  190. c.execute("SELECT COUNT (*) FROM Employee WHERE (Email = '" + email +"' AND Password = '" + password + "')")
  191. connection.commit()
  192. result= c.fetchone()
  193. if result[0]==1:
  194. print('')
  195. print('Processing...')
  196. time.sleep(1.5)
  197. print("...")
  198. print("Login successful.")
  199. time.sleep(1)
  200. print("Welcome to Office Solutions!")
  201. connection.close()
  202. finished = False
  203. while not finished:
  204. main_menu()
  205. choice = get_main_menu()
  206. if choice == 1:
  207. num = 1
  208. while True:
  209. print("\nYears available: 2014, 2015, 2016, 2017")
  210. choice = input("Please choose a year or press (0) to go back to previous menu: ")
  211. if choice == "2014":
  212. print("...")
  213. time.sleep(1)
  214. print("Processing...")
  215. time.sleep(1)
  216. validate_data(2014, num)
  217. validate_quarter(2014, num)
  218. elif choice == "2015":
  219. print("...")
  220. time.sleep(1)
  221. print("Processing...")
  222. time.sleep(1)
  223. validate_data(2015, num)
  224. validate_quarter(2015, num)
  225. elif choice == "2016":
  226. print("...")
  227. time.sleep(1)
  228. print("Processing...")
  229. time.sleep(1)
  230. validate_data(2016, num)
  231. validate_quarter(2016, num)
  232. elif choice == "2017":
  233. print("...")
  234. time.sleep(1)
  235. print("Processing...")
  236. time.sleep(1)
  237. validate_data(2017, num)
  238. validate_quarter(2017, num)
  239. elif choice == "0":
  240. break
  241. else:
  242. print("Year not found")
  243. elif choice == 2:
  244. num = 2
  245. while True:
  246. print("\nYears available: 2014, 2015, 2016, 2017")
  247. choice = input("Please choose a year or press (0) to go back to previous menu: ")
  248. print("------")
  249. if choice == "2014":
  250. print("...")
  251. time.sleep(1)
  252. print("Processing...")
  253. time.sleep(1)
  254. validate_data(2014, num)
  255. validate_quarter(2014, num)
  256. elif choice == "2015":
  257. print("...")
  258. time.sleep(1)
  259. print("Processing...")
  260. time.sleep(1)
  261. validate_data(2015, num)
  262. validate_quarter(2015, num)
  263. elif choice == "2016":
  264. print("...")
  265. time.sleep(1)
  266. print("Processing...")
  267. time.sleep(1)
  268. validate_data(2016, num)
  269. validate_quarter(2016, num)
  270. elif choice == "2017":
  271. print("...")
  272. time.sleep(1)
  273. print("Processing...")
  274. time.sleep(1)
  275. validate_data(2017, num)
  276. validate_quarter(2017, num)
  277. elif choice == "0":
  278. break
  279. else:
  280. print("Year not found")
  281. elif choice ==4:
  282. state_prof_cols=OrdersOnlyData[["State", "Profit"]]
  283. state_purchases=state_prof_cols.groupby(by="State").sum().sort_values(by="Profit", ascending=False)
  284. print("\nTop 10 states with the highest profit.")
  285. print(state_purchases.head(10))
  286. print(lines)
  287.  
  288. reg_prof_cols=OrdersOnlyData[["Region", "Profit"]]
  289. region_profits=reg_prof_cols.groupby(by="Region").sum().sort_values(by="Profit", ascending = False)
  290. print("\nRegion with the highest profit.")
  291. print(region_profits.head(4))
  292. print(lines)
  293. elif choice ==5:
  294.  
  295. state_prof_cols=OrdersOnlyData[["State", "Profit"]]
  296. state_purchases=state_prof_cols.groupby(by="State").sum().sort_values(by="Profit", ascending = True)
  297. print("\nTop 10 states with most Revenue Loss")
  298. print(state_purchases.head(10))
  299. reg_prof_cols=OrdersOnlyData[["Region", "Profit"]]
  300. region_profits=reg_prof_cols.groupby(by="Region").sum().sort_values(by="Profit", ascending = True)
  301. print("\nRegion with the lowest profit.")
  302. print(region_profits.tail(4))
  303. print(lines)
  304. elif choice == 6:
  305. Reg = "South"
  306. RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
  307. ActiveDataFrame = RegionalSales
  308. x = "Discount"
  309. y = "Quantity"
  310. getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
  311. correlation = str(getCorr[0])
  312. pValue = str(getCorr[1])
  313. print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
  314. print("With p value of: " + pValue)
  315. sns.lmplot(x=x, y=y, data=ActiveDataFrame)
  316. plt.xlabel(x)
  317. plt.ylabel(y)
  318. plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
  319. plt.show()
  320.  
  321. elif choice == 7:
  322. Reg = "Central"
  323. RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
  324. ActiveDataFrame = RegionalSales
  325. x = "Discount"
  326. y = "Quantity"
  327. getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
  328. correlation = str(getCorr[0])
  329. pValue = str(getCorr[1])
  330. print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
  331. print("With p value of: " + pValue)
  332. sns.lmplot(x=x, y=y, data=ActiveDataFrame)
  333. plt.xlabel(x)
  334. plt.ylabel(y)
  335. plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
  336. plt.show()
  337.  
  338. elif choice == 8:
  339. Reg = "East"
  340. RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
  341. ActiveDataFrame = RegionalSales
  342. x = "Discount"
  343. y = "Quantity"
  344. getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
  345. correlation = str(getCorr[0])
  346. pValue = str(getCorr[1])
  347. print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
  348. print("With p value of: " + pValue)
  349. sns.lmplot(x=x, y=y, data=ActiveDataFrame)
  350. plt.xlabel(x)
  351. plt.ylabel(y)
  352. plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
  353. plt.show()
  354.  
  355. elif choice == 9:
  356. Reg = "West"
  357. RegionalSales = CopyOrdersData.loc[CopyOrdersData['Region'] == Reg, :]
  358. ActiveDataFrame = RegionalSales
  359. x = "Discount"
  360. y = "Quantity"
  361. getCorr = scipy.stats.spearmanr(ActiveDataFrame[x], ActiveDataFrame[y])
  362. correlation = str(getCorr[0])
  363. pValue = str(getCorr[1])
  364. print("The correlation between " + x + " and " + y + " in the selected Region is: " + correlation)
  365. print("With p value of: " + pValue)
  366. sns.lmplot(x=x, y=y, data=ActiveDataFrame)
  367. plt.xlabel(x)
  368. plt.ylabel(y)
  369. plt.title("Correlation of " + x + " and " + y + " in region: " + Reg)
  370. plt.show()
  371. '''num == 6
  372. while True:
  373. print("\nChoose Region: 1. Central, 2. South, 3. West, 4. East: ")
  374. choice = input("Please choose a region or press (0) to go back to previous menu: ")
  375. print("------")
  376. if choice == "1":
  377. print("...")
  378. time.sleep(1)
  379. print("Processing...")
  380. time.sleep(1)
  381. plot(1, num)
  382. elif choice == "2":
  383. print("...")
  384. time.sleep(1)
  385. print("Processing...")
  386. time.sleep(1)
  387. plot(2, num)
  388. elif choice == "3":
  389. print("...")
  390. time.sleep(1)
  391. print("Processing...")
  392. time.sleep(1)
  393. plot(3, num)
  394. elif choice == "4":
  395. print("...")
  396. time.sleep(1)
  397. print("Processing...")
  398. time.sleep(1)
  399. plot(4, num)
  400. '''
  401. elif choice == 0:
  402. print('')
  403. time.sleep(1)
  404. print("Thank you!")
  405. finished = True
  406. else:
  407. finished = True
  408. else:
  409. print()
  410. print('Processing...')
  411. time.sleep(1.5)
  412. print()
  413. print("Login failed")
  414. except:
  415. print("Connection failed")
  416. elif choice == 2:
  417. with sqlite3.connect('OS_Employee.db') as db:
  418. employee_ID =input("Please enter your Employee ID: ")
  419. while employee_ID.isnumeric() == False:
  420. employee_ID = input("Sorry, try again\nPlease enter your Employee ID:")
  421.  
  422. first_name = input("Please enter your first name: ")
  423. while first_name.isnumeric() == True:
  424. first_name = input("Sorry, try again\nPlease enter your first name: ")
  425.  
  426. last_name = input("Please enter your last name: ")
  427. while last_name.isnumeric() == True:
  428. last_name = input("Sorry, try again\nPlease enter your last name: ")
  429.  
  430. domainlist= ["@gmail.com", "@yahoo.com","@outlook.com", "@hotmail.com", "@ymail.com"]
  431. domaintuple= tuple(domainlist)
  432. while True:
  433. email = input('Please enter your working email: ')
  434. email= email.strip()
  435. if email.endswith(domaintuple) ==True:
  436. c.execute("SELECT COUNT (*) FROM Employee WHERE (Email ='" +email+ "')")
  437. emailresult= c.fetchone()
  438. if emailresult[0]!=1:
  439. print("Email is available")
  440. break
  441. else:
  442. print("Email is taken. Please enter another email")
  443. email=''
  444. continue
  445. else:
  446. print("Invalid email. Please enter another email")
  447. email=''
  448. continue
  449.  
  450. password = input("Create a password for your log-in: ")
  451. while len(password) > 24 or len(password)<3:
  452. password= input("Sorry, try again\nPlease enter a password for your log-in: ")
  453.  
  454. user_account =(employee_ID, first_name.title(), last_name.title(), email, password)
  455. try:
  456. c.execute('INSERT INTO Employee (EmployeeID,FirstName,LastName,Email,Password) VALUES (?,?,?,?,?)', user_account)
  457. db.commit()
  458. print("Processing...")
  459. time.sleep(1.5)
  460. print("Registration successful! Please log in.")
  461. except:
  462. print("Connection failed.")
  463. elif choice == 0:
  464. print('')
  465. time.sleep(1)
  466. print("Thank you!")
  467. finished = True
  468. else:
  469. finished = True
  470.  
  471. if __name__ == "__main__":
  472. DATABASE = 'OS_Employee.db'
  473.  
  474. main()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!