Excel Worksheet has been attached below for Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on vehicle categories. Also Screenshot attached For option d. Customized ribbon Named ABC.

*Create VBA Code to convert selected data into pdf from applicant data Sub CreatePDF()ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:="Sample Excel File Saved As PDF", _ Quality:=xlQualityStandard, _ IncludeDocProperties:=False, _ IgnorePrintAreas:=False, _ From:=1, _ To:=5, _ OpenAfterPublish:=TrueEnd Sub…

All the given task has been performed for Business model Customer to Customer (C2C) and same .sql file attached below. create database ecommerce;use ecommerce;select * from users_data;DESC users_data;# first 100 rows of the databaseselect * from users_data limit 100;# distinct values exist in table for field country…

For Question no.4 Jupyter Notebook attached. For Question no. 5 and 13 Excel file has been attached. SQL file attachewd for remaining Questions.

English Dictionary App import json print('Welcome to the Dictionary App') print('Please select your choice') print('1. Add a word') print('2. Find the meaning') print('3. Update the word') print('4. Exit') f = open('words.txt','w') coll_words = {} json.dump(coll_words,f) f.close() while True: n = int(input('Enter the Choice:')) if n == 1: import json f = open('words.txt','w') word = input('Enter a word : ') meaning = input('Enter its meaning : ') coll_words[word] = meaning json.dump(coll_words,f) f.close() elif n == 2: import json f = open('words.txt','r+') dic = json.load(f) find = input('Enter the word to find: ') if find in coll_words: print('The meaning is',coll_words.get(find)) else: print('Sorry the word is not found') json.dump(coll_words,f) f.close() elif n == 3: import json f = open('words.txt','w') word1 = input('Enter the word:') update_meaning = input('Enter the updated meaning: ') coll_words[word1] = update_meaning json.dump(coll_words,f) f.close() elif n == 4: print('Thank you for choosing Dictionary app') break else: print('Please enter the mentioned choice') print('1. Add a word') print('2. Find the meaning') print('3. Update the word') print('4. Exit') Library Book Management System from tkinter import * import sqlite3 import tkinter.ttk as ttk import tkinter.messagebox as tkMessageBox root = Tk() root.title('Library Book Management System') # Methods def Database(): global conn, cursor conn = sqlite3.connect('Book_Management.db') cursor = conn.cursor() cursor.execute('CREATE TABLE IF NOT EXISTS member(booktitle TEXT, author TEXT, year INTEGER, isbn INTEGER)') def Display(): tree.delete(*tree.get_children()) Database() cursor.execute("SELECT * FROM member ORDER BY booktitle ASC") fetch = cursor.fetchall() for data in fetch: if len(data) >= 4: tree.insert('', 'end', values=(data[0], data[1], data[2], data[3])) else: print("Invalid data format:", data) cursor.close() txt_result.config(text="All Data Displayed !", fg="green") # Call the Add function to update the treeview def Search(): tree.delete(*tree.get_children()) Database() # Get the booktitle to search search_booktitle = str(BOOKTITLE.get()) # Check if the search_booktitle is empty if not search_booktitle: txt_result.config(text="Please enter a booktitle for search", fg="red") return # Execute the SELECT query with a WHERE clause cursor.execute("SELECT * FROM member WHERE booktitle LIKE ?", ('%' + search_booktitle + '%',)) fetch = cursor.fetchall() if not fetch: txt_result.config(text="Book not found", fg="red") else: for data in fetch: if len(data) >= 4: tree.insert('', 'end', values=(data[0], data[1], data[2], data[3])) else: print("Invalid data format:", data) txt_result.config(text="Successfully fetched data !", fg="green") cursor.close() def Add(): if BOOKTITLE.get() == "" or AUTHOR.get() == "" or YEAR.get() == "" or ISBN.get() == "": txt_result.config(text="Please enter all the fields", fg="red") else: Database() cursor.execute("INSERT INTO member(booktitle, author, year, isbn) VALUES (?,?,?,?)", (str(BOOKTITLE.get()), str(AUTHOR.get()), str(YEAR.get()), str(ISBN.get()))) conn.commit() BOOKTITLE.set("") AUTHOR.set("") YEAR.set("") ISBN.set("") cursor.close() conn.close() txt_result.config(text="Record Inserted !", fg="green") def Issue(): selected_item = tree.selection() if not selected_item: txt_result.config(text="Please select a book to Issue", fg="red") return Database() for item in selected_item: booktitle_to_issue = tree.item(item, 'values')[0] cursor.execute("SELECT * FROM member WHERE booktitle=?",(booktitle_to_issue,)) conn.commit() conn.close() result = tkMessageBox.askquestion('Confirmation', 'Do you want to issue this book?', icon='question') if result == 'yes': txt_result.config(text="Book Issued", fg="green") def Delete(): selected_item = tree.selection() if not selected_item: txt_result.config(text="Please select a book to delete", fg="red") return Database() for item in selected_item: booktitle_to_delete = tree.item(item, 'values')[0] cursor.execute("DELETE FROM member WHERE booktitle=?", (booktitle_to_delete,)) conn.commit() conn.close() result = tkMessageBox.askquestion('Confirmation','Are you sure to delete ? (y/n)', icon='question') if result == 'yes': tree.delete(*selected_item) Display() txt_result.config(text="Successfully deleted data !", fg="green") else: txt_result.config(text="Book not deleted", fg="green") def Exit(): result = tkMessageBox.askquestion('Confirmation','Do you want to exit ? (y/n)', icon='warning') if result == 'yes': root.destroy() exit() # Variable declaration BOOKTITLE = StringVar() AUTHOR = StringVar() YEAR = StringVar() ISBN = StringVar() # Frame Top = Frame(root, width=900, height=50, bd=8, relief='raise') Top.pack(side=TOP) Left = Frame(root, width=300, height=500, bd=8, relief='raise') Left.pack(side=LEFT) Right = Frame(root, width=300, height=500, bd=8, relief='raise') Right.pack(side=RIGHT) Forms = Frame(Left, width=300, height=450) Forms.pack(side=TOP) Buttons = Frame(Left, width=300, height=100, bd=8, relief='raise') Buttons.pack(side=BOTTOM) # Labels Widget txt_title = Label(Top, width=900, font=('arial', 24), text='Library Book Management System') txt_title.pack() txt_booktitle = Label(Forms, text="Booktitle:", font=('arial', 16), bd=15) txt_booktitle.grid(row=0, stick='e') txt_author = Label(Forms, text='Author:', font=('arial', 16), bd=15) txt_author.grid(row=1, stick='e') txt_year = Label(Forms, text='Year:', font=('arial', 16), bd=15) txt_year.grid(row=2, stick='e') txt_isbn = Label(Forms, text='ISBN:', font=('arial', 16), bd=15) txt_isbn.grid(row=3, stick='e') txt_result = Label(Buttons) txt_result.pack(side=TOP) # Entry Widget booktitle = Entry(Forms, textvariable=BOOKTITLE, width=30) booktitle.grid(row=0, column=1) author = Entry(Forms, textvariable=AUTHOR, width=30) author.grid(row=1, column=1) year = Entry(Forms, textvariable=YEAR, width=30) year.grid(row=2, column=1) isbn = Entry(Forms, textvariable=ISBN, width=30) isbn.grid(row=3, column=1) # BUTTONS WIDGET btn_create = Button(Buttons, width=10, text='Display', command=Display) btn_create.pack(side=LEFT) btn_create = Button(Buttons, width=10, text='Search', command=Search) btn_create.pack(side=LEFT) btn_create = Button(Buttons, width=10, text='Add', command=Add) btn_create.pack(side=LEFT) btn_create = Button(Buttons, width=10, text='Issue', command=Issue) btn_create.pack(side=LEFT) btn_create = Button(Buttons, width=10, text='Delete', command=Delete) btn_create.pack(side=LEFT) btn_create = Button(Buttons, width=10, text='Exit', command=Exit) btn_create.pack(side=LEFT) # LIST WIDGET scrollbary = Scrollbar(Right, orient=VERTICAL) scrollbarx = Scrollbar(Right, orient=HORIZONTAL) tree = ttk.Treeview(Right, columns=("Booktitle", "Author", "Year", "ISBN")) scrollbary.config(command=tree.yview) scrollbary.pack(side=RIGHT, fill=Y) scrollbarx.config(command=tree.xview) scrollbarx.pack(side=BOTTOM, fill=X) tree.heading('Booktitle', text="Booktitle", anchor=W) tree.heading('Author', text="Author", anchor=W) tree.heading('Year', text="Year", anchor=W) tree.heading('ISBN', text="ISBN", anchor=W) tree.column('#0', stretch=NO, minwidth=0, width=0) tree.column('#1', stretch=NO, minwidth=0, width=200) tree.column('#2', stretch=NO, minwidth=0, width=200) tree.column('#3', stretch=NO, minwidth=0, width=200) tree.pack() # INITIALIZATION if __name__ == '__main__': root.mainloop()

EDA on Vehicle Insurance Customer Data import pandas as pd import numpy as np import matplotlib.pyplot as plt df1 = pd.read_csv(r"C:\Users\megha\Downloads\customer_details (1).csv") df1 0 1 2 3 4 5 6 7 0 1.0 Male 44.0 1.0 28.0 0.0 > 2 Years Yes 1 2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No 2 3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes 3 4.0 Male 21.0 1.0 11.0 1.0 < 1> 2 Years Yes 381108 381109.0 Male 46.0 1.0 29.0 0.0 1-2 Year No 381109 rows × 8 columns # Column Name for customer details table: df1.columns=["customer_id","Gender","age","driving licence present","region code","previously insured","vehicle age","vehicle damage"] df1 customer_id Gender age driving licence present region code previously insured vehicle age vehicle damage 0 1.0 Male 44.0 1.0 28.0 0.0 > 2 Years Yes 1 2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No 2 3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes 3 4.0 Male 21.0 1.0 11.0 1.0 < 1> 2 Years Yes 381108 381109.0 Male 46.0 1.0 29.0 0.0 1-2 Year No 381109 rows × 8 columns df2 = pd.read_csv(r"C:\Users\megha\Downloads\customer_policy_details.csv") df2 0 1 2 3 4 0 1.0 40454.0 26.0 217.0 1.0 1 2.0 33536.0 26.0 183.0 0.0 2 3.0 38294.0 26.0 27.0 1.0 3 4.0 28619.0 152.0 203.0 0.0 4 5.0 27496.0 152.0 39.0 0.0 ... ... ... ... ... ... 381104 381105.0 30170.0 26.0 88.0 0.0 381105 381106.0 40016.0 152.0 131.0 0.0 381106 381107.0 35118.0 160.0 161.0 0.0 381107 381108.0 44617.0 124.0 74.0 0.0 381108 381109.0 41777.0 26.0 237.0 0.0 381109 rows × 5 columns # Column Name for customer_policy table: df2.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"] df2 customer_id annual premium (in Rs) sales channel code vintage response 0 1.0 40454.0 26.0 217.0 1.0 1 2.0 33536.0 26.0 183.0 0.0 2 3.0 38294.0 26.0 27.0 1.0 3 4.0 28619.0 152.0 203.0 0.0 4 5.0 27496.0 152.0 39.0 0.0 ... ... ... ... ... ... 381104 381105.0 30170.0 26.0 88.0 0.0 381105 381106.0 40016.0 152.0 131.0 0.0 381106 381107.0 35118.0 160.0 161.0 0.0 381107 381108.0 44617.0 124.0 74.0 0.0 381108 381109.0 41777.0 26.0 237.0 0.0 381109 rows × 5 columns Checking and Cleaning Data Quality: # Generate a summary of count of all the null values column wise for customer detail table df1_null = df1.isnull() print('True = Null values in customer detail table') print("-"*50) print('False = Not Null values in customer details table') print("-"*50) for i in df1_null.columns.values.tolist(): print(df1_null[i].value_counts()) print("-"*26) True = Null values in customer detail table -------------------------------------------------- False = Not Null values in customer details table -------------------------------------------------- customer_id False 380723 True 386 Name: count, dtype: int64 -------------------------- Gender False 380741 True 368 Name: count, dtype: int64 -------------------------- age False 380741 True 368 Name: count, dtype: int64 -------------------------- driving licence present False 380716 True 393 Name: count, dtype: int64 -------------------------- region code False 380717 True 392 Name: count, dtype: int64 -------------------------- previously insured False 380728 True 381 Name: count, dtype: int64 -------------------------- vehicle age False 380728 True 381 Name: count, dtype: int64 -------------------------- vehicle damage False 380702 True 407 Name: count, dtype: int64 -------------------------- # Generate a summary of count of all the null values column wise for customer-policy-details table df2_null = df2.isnull() print('True = Null values in customer-policy-details table') print("-"*60) print('False = Not Null values in customer-policy-details table') print("-"*60) for i in df2_null.columns.values.tolist(): print(df2_null[i].value_counts()) print("-"*26) True = Null values in customer-policy-details table ------------------------------------------------------------ False = Not Null values in customer-policy-details table ------------------------------------------------------------ customer_id False 380722 True 387 Name: count, dtype: int64 -------------------------- annual premium (in Rs) False 380763 True 346 Name: count, dtype: int64 -------------------------- sales channel code False 380709 True 400 Name: count, dtype: int64 -------------------------- vintage False 380721 True 388 Name: count, dtype: int64 -------------------------- response False 380748 True 361 Name: count, dtype: int64 -------------------------- # Drop Null values for customer_id because central tendencies for id’s is not feasible. df1_drop = df1.dropna(subset=['customer_id'],inplace=True) print('Null values in customer_id column after dropping null values from customer details table : ',df1['customer_id'].isna().sum()) Null values in customer_id column after dropping null values from customer details table : 0 df2_drop = df2.dropna(subset=['customer_id'],inplace=True) print('Null values in customer_id column after dropping null values from customer policy details table : ',df2['customer_id'].isna().sum()) Null values in customer_id column after dropping null values from customer policy details table : 0 # Replace all null values for numeric columns by mean. df1.head() customer_id Gender age driving licence present region code previously insured vehicle age vehicle damage 0 1.0 Male 44.0 1.0 28.0 0.0 > 2 Years Yes 1 2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No 2 3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes 3 4.0 Male 21.0 1.0 11.0 1.0 < 1 mean(),inplace=True) mean(),inplace=True) mean(),inplace=True) mean(),inplace=True)> 2 Years Yes 1 2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No 2 3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes 3 4.0 Male 21.0 1.0 11.0 1.0 < 1 mode()[0],inplace=True) mode()[0],inplace=True) mode()[0],inplace=True) Q1=df1.describe().loc[ Q3=df1.describe().loc[ xss=removed>(Q3+IQR*1.5),"age"].count()) customer detail table Outliers present in age columns are : 0 Outliers present in age columns are : 0 Q1=df1.describe().loc["25%","region code"] Q3=df1.describe().loc["75%","region code"] IQR = Q3-Q1 print('customer detail table') print('Outliers present in region code columns are : ',df1.loc[df1["region code"]<(Q1-IQR*1.5),"region code"].count()) print('Outliers present in region code columns are : ',df1.loc[df1["region code"]>(Q3+IQR*1.5),"region code"].count()) customer detail table Outliers present in region code columns are : 0 Outliers present in region code columns are : 0 df2.describe() customer_id annual premium (in Rs) sales channel code vintage response count 380722.000000 380722.000000 380322.000000 380722.000000 380361.000000 mean 190547.491663 30563.999774 112.036687 154.347192 0.122526 std 110013.824148 17190.147550 54.205529 83.628096 0.327892 min 1.000000 2630.000000 1.000000 10.000000 0.000000 25?276.250000 24416.000000 29.000000 82.000000 0.000000 500536.500000 31656.000000 133.000000 154.000000 0.000000 75(5818.750000 39391.750000 152.000000 227.000000 0.000000 max 381109.000000 540165.000000 163.000000 299.000000 1.000000 Q1=df2.describe().loc["25%","annual premium (in Rs)"] Q3=df2.describe().loc["75%","annual premium (in Rs)"] IQR=Q3-Q1 print('Customer-policy-details table') print('Outliers present in annual premium (in Rs) column are :',df2.loc[df2["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count()) print('Outliers present in annual premium (in Rs) column are :',df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count()) Customer-policy-details table Outliers present in annual premium (in Rs) column are : 0 Outliers present in annual premium (in Rs) column are : 10332 Q1=df2.describe().loc["25%","vintage"] Q3=df2.describe().loc["75%","vintage"] IQR=Q3-Q1 print('Customer-policy-details table') print('Outliers present in vintage column are :',df2.loc[df2["vintage"]<(Q1-IQR*1.5),"vintage"].count()) print('Outliers present vintage column are :',df2.loc[df2["vintage"]>(Q3+IQR*1.5),"vintage"].count()) Customer-policy-details table Outliers present in vintage column are : 0 Outliers present vintage column are : 0 # Replace all outlier values for numeric columns by mean. mean=df2["annual premium (in Rs)"].mean() print ('mean of annual premium (in Rs) outliers are : ',mean) mean of annual premium (in Rs) outliers are : 30563.999773909116 df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean()) 0 40454.0 1 33536.0 2 38294.0 3 28619.0 4 27496.0 ... 381104 30170.0 381105 40016.0 381106 35118.0 381107 44617.0 381108 41777.0 Name: annual premium (in Rs), Length: 380722, dtype: float64 iii. White spaces Remove white spaces df1.head() customer_id Gender age driving licence present region code previously insured vehicle age vehicle damage 0 1.0 Male 44.0 1.0 28.0 0.0 > 2 Years Yes 1 2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No 2 3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes 3 4.0 Male 21.0 1.0 11.0 1.0 < 1> 2 YEARS YES 1 2.0 MALE 76.0 1.0 3.0 0.0 1-2 YEAR NO 2 3.0 MALE 47.0 1.0 28.0 0.0 > 2 YEARS YES 3 4.0 MALE 21.0 1.0 11.0 1.0 < 1 dummies_gender=pd.get_dummies(df1[ dummies_vehicle_age=pd.get_dummies(df1[ dummies_vehicle_damage=pd.get_dummies(df1[ dummies_dlp=pd.get_dummies(df1[ dummies_pre=pd.get_dummies(df1[ xss=removed pd.merge(df1,df2,on='customer_id'> 2 YEARS YES 40454.0 26.0 217.0 1.0 1 2.0 MALE 76.0 1.0 3.0 0.0 1-2 YEAR NO 33536.0 26.0 183.0 0.0 2 3.0 MALE 47.0 1.0 28.0 0.0 > 2 YEARS YES 38294.0 26.0 27.0 1.0 3 4.0 MALE 21.0 1.0 11.0 1.0 < 1> 2 YEARS YES 44617.0 124.0 74.0 0.0 380335 381109.0 MALE 46.0 1.0 29.0 0.0 1-2 YEAR NO 41777.0 26.0 237.0 0.0 380336 rows × 12 columns Company needs some important information from the master table to make decisions for future growth.They needs following information: # i. Gender wise average annual premium Gender_avg = Master.groupby(["Gender"])["annual premium (in Rs)"].mean() Gender_avg Gender FEMALE 30492.028478 MALE 30624.622150 Name: annual premium (in Rs), dtype: float64 # ii. Age wise average annual premium Age_avg = Master.groupby(["age"])["annual premium (in Rs)"].mean() Age_avg age 20.0 26924.620173 21.0 30564.475810 22.0 30823.778102 23.0 30688.606298 24.0 31183.802890 ... 81.0 31201.571429 82.0 37705.379310 83.0 31012.727273 84.0 35440.818182 85.0 29792.363636 Name: annual premium (in Rs), Length: 67, dtype: float64 # iii. Is your data balanced between the genders? Master.groupby("Gender").count() # The data is balanced between the gender as the data for gender is approximately same and Average almost same. customer_id age driving licence present region code previously insured vehicle age vehicle damage annual premium (in Rs) sales channel code vintage response Gender FEMALE 174485 174485 174485 174485 174485 174309 174300 174485 174284 174485 174329 MALE 205484 205484 205484 205484 205484 205279 205266 205484 205288 205484 205281 # iv. Vehicle age wise average annual premium. Vehicle_age_avg = Master.groupby(["vehicle age"])["annual premium (in Rs)"].mean() Vehicle_age_avg vehicle age 1-2 YEAR 30522.464972 < 1> 2 YEARS 35657.520845 Name: annual premium (in Rs), dtype: float64 Is there any relation between Person Age and annual premium? Relation = Master["age"].corr(Master["annual premium (in Rs)"]) Relation 0.06771515986613913 # There is no relationship between Person age and annual premium because Correlation coefficient < 0.5

Please find attachment for project 1. Feel free to access project through below link. https://public.tableau.com/views/WomeninSTEMFields_17062399397430/Dashboard2?:language=en-US&:display_count=n&:origin=viz_share_link

Please find attachment for project 2. Feel free to access through below link. https://public.tableau.com/app/profile/megha.kumari5564/viz/GenderBiasinScienceandTechnicalfield_17063849829910/Dashboard1