Part 1: Data Handling Using Pandas 1. Create a series of these numbers: 33,55,65,29,19,23, Find the sum of those values which are ending with 3 or 5. [& Prot.py - E:/Website Content/IP XIV/Practical File Programs/Prol py (3.8.5) "| File Edit Format un Options Window Help import pandas as pd list = [33,55,65,29,19,23] ser = pd.Series(list) val_sum=0 sum5 = sum(ser[ser % 10 == 5]) sum3 = sum(ser[ser % 10 == 3]) print(sum3+sum5)ROMO, Tul 20 2020, 15:43:08) [MSC v.1926 32 bit (Tatel)] om wind? "credits" or "Heense()" for more inform: Yebsite Content/IP XII/Practical File Programs/Pro py 2. Create aseries of 10 numbers starting with 41 and with the increment of 3. Now add 7 all odd values and subtract 3 in even values. Reprint the updated series. import pandas as pd ser=pd.Series(range(41,71,3)) for iin range(0,ser.size): if ser[i]%2==0: ser[i]=ser[i]-3 elif ser[i]%2!=0: ser[i]=ser[i]+7 print(ser) 48 41 54 47 60 53 66 359 72 65 dtype: int64 CSSADAEWNeE S 3. Create aseries of 10 numbers. Change the value of all the elementsthose values are multiples of 4. [& Prot py - EvWebste Content/P X/Practical File Programs/Prol py (3.85) File fit Format lun Options Window Help import pandas as pd numbers = [] # Generating a series for iin range(1,11): val = int(input(""Enter a number :")) numbers.append(val) ser = pd.Series(numbers) # Changing the values of multiple of four and assigning them a value 21 ser[ser % 4 == 0] =21 print(ser)F: Website Content/P XUW/Practical File Programs/Prol.py === a number :13 anumber :14 4, Create a series and print the top 3 elements using the head function. (B *Prod.py - Es Website Content/IP XiyPractical File Progeams/Prot.py (3.85)" file Edit Format Bun Options Window Help import pandas as pd # generating the series ser_length = int(input("'Enter the length of the series: '')) data = [] for iin range(ser_length): val = int(input("'Enter a val:")) data.append(val) ser= pd.Series(data) print(ser.head(3))RESTART: E:/Website Content/IP X1/Practical File Programs/Pro4.py ————-— Enter the length of the series: 5 2 55 dtype: inte4 5. Create a series and print the bottom 3 elements using the tail function. ProS.py - E:/Website Content/IP Xil/Practical File Programa/ProS py (2.8.5)° le_Edit_Format_Run Options Window Help import pandas as pd # generating the series ser_length ntQ@input(" Enter the length of the series data = [] for range(ser_length): val = int(input("'Enter a val:"')) data.append(val) ser = pd.Series(data) #Printing 3 elements print(ser.tail(3))p> an Enter a val: m4 2 63 385 4 74 dope: into 6. Create a series with these numbers: 21, 51, 71, 31, 12. Exchange all these values of series by shifting each of them one to one position before and by shifting the first value to last position... UB ProGpy - E/Website Content/P XIV/Practical File Programs/Pro6 py (2.25) file Edt Format Bun Options Window Help import pandas as pd import numpy as np s = pd.Series([21,51,71,31,12]) print(pd.Series(np-roll(s.values, -1), index=s.index)) — Yebsite Content/IP XI/Practical File Pragramy/ProS.py ——————= <== RESTART: E:/Website Content/IP X1V/Practical File Programs/Pro6.py ———————= S1 71 31 12 21 type: inted Beunss 7. Create a dataframe named as students using a list of names of 5 students.TW (J Pro? py - E/Website Contert/P 1UPrectical Fle Programs/Pro7 py (65) Bile [dt Format Bun Qptions Window Help import pandas as pd students = ["Ram"," Aman"," Akash"," Ramesh". students = pd.DataFrame(students,columns=["' print(students) Virat"] ame"']) 8. Create a dataframe players using a list of names and scores of the previous three matches. (Using Nested list) (i Prehpy leans Come ahaa ae Property GS) import pandas as pd data = [["Virat",$5,66,31],[" Rohit" ,88,66,43],["Dhoni",99,85,68]] players = pd.DataFrame(data, columns = ["Name","M1",""M2","M3"]) print(players) >>> RESTART: E:/Website Content/IP XI/Practical File Programs/Pro8.py Name M1 M2 M3 0 Virat 55 66 31 1 Rohit 88 66 43 2 Dhoni 99 85 68 9. Create a dataframe countries using a dictionary which stored countryname, capitals and populations of the country. | [& Prospy - Exwebste ContentuP KI\Practical File Programs\Prosipy (3.85) ‘Fie_Eét_Fommst_fun Options Window _Help import pandas as pd country_data = {"Country Name":["India","Canada"," Australia], "Capital": ["New Delhi","Ottawa","Canberra"], "Population" : ["136 Cr","10 Cr","50 Cr"]}} countries = pd.DataFrame(country_data) print(countries) RESTART: E:\Website Content\IP XI\Practical File Programs\Pro9.py ———= Name Capital Population 0 India New Delhi 136 Cr 1 Canada Ottawa 10 Cr 2 Australia Canberra 50 Cr 10. Iterate dataframe created in question no. 8 by its rows. [Dratigy tren cote hace Pegometettey 851 (E44 Fem espns Window lp import pandas as pd #Creating Dataframe ‘irat",$5,66,31],[" Rohit" ,88,66,43],["Samson",99,101,68]] players = pd.DataFrame(data, columns = [""Name","Match-1","Match-2","Match-3"]) #Iterating dataframe using iterrows ‘for index, row in players.iterrows(): print(index, row.values)= RESTART: E:/Website Content/P XIl/Practical File Programs/Prol 0.py === 0 ['Virat’ 55 66 31] 1 ['Rohit' 88 66 43] 2 [Samson 99 101 68] e>| Tl. Print scores of previous two matches along with their names using iterrows function. (Use dataframe created in question 8) [ib Pret y= Eveane Cote MPa file Pech B83) Fle Edi Format fn Oyo import pandas as pd ndow dp data = |[" Virat",55,66,31|,[" Rohit" ,88,66,43],[" players = pd.DataFrame(data, columns = ["Name", son",99,101,68]] 'Match-1","Match-2","Match-3"]) for index, row in players.iterrows(): print(index, row[""Name"],row["Match-2"],row["Match-3"]) RESTART: E:/Website Content/IP XII/Practical File Programs/Prol1.py 0 Virat 66 31 1 Rohit 66 43 2 Samson 101 68 12. Make a total of score from the dataframe players and display their rank according the their scores.Wary Bow import pandas as pd data = [["Virat'',58,66,31],["Robit",88,66,43],[" Dhoni" ,99,53,68},["Pant",77,55,21]] players = pd.DataFrame(data, columns = ["Name","Match-1","Mateh-2","Match-3"]) players[’Total_score'] = players[’Match-1'] + players["Match-2"] + players["Match-3"] players['Rank’] = players['Total_score'].rank(ascending=0) print(players) == RESTART: E:/Website Content/IP XIl/Practical File Programs/Prol2.py ===== Name Match-1 Match-2 Match-3 Total_score Rank OVirat 55 66 31 152 4.0 1 Rohit 88 66 43 197 2.0 2 Dhoni 99 S368 220 10 3 Pant 77 55 21 1533.0 >>| 18. Print the batsman name along with runs scored in Test and T20 using column names and dot notation. import pandas as pa ij 4 Crewting the Data "'Name"':|""Virat Kohli","Ajinkya Rahane","Rohit Sharma" “Shikhar Dhawan","Tardik Pandya"), " : [3545,2578,2280,2158,1879], [2245,2165,2080,1957,1856], + [1925,1853,1522,1020,980] 1 data = pd.Data¥rame(player_data) 4 The following ling is used to start the index from 1 dataindex = data.index +1 iw — a #Printing Names pein t(data, Name) fest Record print("Test Record:"y print(data, Lest) 4120 Record print(""120 Records") +Be tere ===== RESTART: E:/Website Content/IP XiU/Practical File Programs/Prol3.py === 1 Virwt Kohli 2 Ajinkya Rahane 3 Rohit Sharma 4 Shikhar Dhaws § Hardik Pandya Name: Name, dtype: abject Test Record: 1 3843 2 2878 3 2280 4 2158 5 1879 Name: Test, dtype: inté4 120 Record: 1 1925 2 1853 3 1822 4 1020 5 980 Name: 120, dtype: inté4 o> 14. Display the Batsman name along with runs scored in ODI using loc. fo import pandas as pd # Creating the Data Ajinkya Rahane","Rohit Sharma’ ."Hardik Pandya’ 3543,2578,2280,2158,1879], 2245,2165,2080,1957,1856], [1925,1853,1522,1020,980] data = pd.DataFrame(player_data) # The following line is used to start the index from 1 data.index = data.index +1 a print(da’ ‘Name',}ODI'Y/)Website Content/IP XI/Practical File Programs/Prol4.py Virnt Konni 2245 Ajinicya Rahane 2165 1 2 3 a 5 Hardik Pandya 1856 15. Display the batsman details who scored a. More than 2000 in ODI b. Less than 2500 in Test c. More than 1500 in T20 Lm Pint py Funes Canter RNIB aport pandas as pd # Creating the Data player_data = {Name nikya Raha: | $543,25 78,2280 8.1879], = [2845.2665.2540,1957_18356]_ 2 [1925,1853,1522,1020,0% data = pd_DataFrame(player_data) |# ‘The following line is used to start the index frem 1 data index = data.index + 1 # runs more than 2500 in ODL nt("———- Runs greater than 2: ni(dataloe|data [ODI |>=2500, | han 2500 runs in test Kuns less than 2500 in ‘Test # More than 1500 runs in 120 nt¢"-—-- Runs more than 1500 int(dataloc[data|RESTART: E:/Website Content/IP XII/Practical File Programs/Pro15.py ===== Runs greater than 2500 in ODI --------- Name 1 Virat Kohli 2 Ajinkya Rahand 3 Rohit Sharma -——- Runs less than 2500 in Test — Name 3 Rohit Sharma 4 Shikhar Dhawan 5 Hardik Pandya ---- Runs more than 1500 in 120 ------- — Name 1 Virat Kohli 2 Ajinkya Rahane 3 Rohit Sharma Part 2: Data Visualization 16. Plot following data on line chart and follow the given instructions: D Mond Tuesd; ‘Wednesda: Thursdé Frid Income [510 350 A75 580 600 a. Writeatitle for the chart "The Weekly Income Report”. b. Write the appropriate titles of both the axes. ¢. Write code to Display legends. d. Display red color for the line. e f. ; Use thelinestyle - dashed Display diamond style markers on data points[a Prot py -E/Website Conten/P X1/Practical Fle Programs/Data Viulzation/Prol.py 2.85) File Edit Format fun Options Window Help import matplotlib.pyplot as pp day =['Monday',’Tuesday',"Wednesday' ,'Thursday’,'Friday'] inc = [510,350,475,580,600] pp-plot(day,inc,label="Income' ,color=" pp.title(""The Weekly Income Report") pp-xlabel(" Days") pp-ylabel(" Income") pp-legend() pp-show() inestyle="'dashed',marker='D' The Weekly Income Report a €\>) $1Ols) 17. Consider the following data of a medical store and plot the data on the line chart and Customize the chart as you wish: Month Masks Sanitizer Hand wash March 1500 4400 6500 April 3500 4500 5000 May 6500 5500 5800 June 6700 6000 6300 July 6000 5600 6200 August 6800 6300 4500[a “Pro2.py - E/Website Content? all/Practical File Prograrns/Data Visudlicston/ProZ.py (38.5)" fite fst Format fur Qptons Window Help import matplotlib.pyplot as pp ‘March','April',"May','June’,'July',' August’) 1500,3500,6500,6700,6000,6800] san = [4400,4500,5500,6000,5600,6300] hw = [6500,5000,5800,6300,6200,4500] pp-plot(mon,mask,label="Mask',color="g" linestyle='dashed’, linewidth=4,\ marker='o', markerfacecolor="k’, markeredgecolor='r') pp.plot(mon,san,label="Sanitizer' color='b’,linestyle="dashed', linewidth=4,\ marker="'3', markerfacecolor='k', markeredgecolor='g') pp-plot(mon, hw, label=' Handwash’ color='r' linestyle='dashed’, linewidth=4,\ marker="'v', markerfacecolor="k"’, , markeredgecolor=" ‘by pp-title(""Fitwell Medical Store") pp-xlabel(" Months") pp-ylabel("'Covid Protections") pp-legend() pp-show) Ftwell Medical Store ‘inane ale) $105) 5)LB *Pro3.py - E:/Website Content/P IVPractical File Programs/Dats Visuaication/Pro3py (3.8.5)" | File Edit Format fun Options Window Help import matplotlib.pyplot as pp mon =['March','April',"May','June','July', gust") san = [4400,4500,5500,6000,5600,6300) hw = [6500,5000,5800,6300,6200,4500] #subploti for Sanitizer| pp.subplot(2,1,1) pp-plot(mon,san, label=' Sanitizer’ ,color="b' linestyle='dashed’, linewidth=4,\ marker='o', markerfacecolor='k', markeredgecolor='r') pp.title(" Fitwell Medical Store") pp-legendQ) #subplot? for Handwash pp.subplot(2,1,2) pp.plot(mon,hw,label='Handwash' ,color='r',linestyle='dashed', linewidth=4,\ marker='y', markerfacecolor='k', markeredgecolor='b') pp-xlabel(" Months") pp-ylabel("Covid Protections") pp.legendQ) pp.show() © Figure | - ao x Fitwell Medical Store - == Seniuser - 000 -~. - - ~ 500 - ~ o s000 7 7 soo} March Ten ay bine Tay agus e00 a 5 coo) 7 —N i s. - 2 ‘ E =00 Noo, ‘% 3 sooo ‘= 5 coo | Haneivasth . och aon ay a ey mages19. Display following bowling figures through bar chart: [B Protpy -EAWebsite Content/iP X1VPractcal Fle Programs/Data Visualzation/Prod py (38.5) File Edit Format Run Options Window Help import matplotlib.pyplot as pp overs =[1,2,3,4] runs=[6,18,10,5] pp.bar(overs,runs,color='m') pp-xlabel(‘Overs') pp-xlabel('Runs') pp.title('Bowling Spell Analysis") pp-show()organ Bowling Spell Analysis os 10 615) (20 (25) 3803S DS puns ale) 4a) Part 3: Database query using MySQL 20. Create the following table products and write queries given below: Table: Products Company P1001 iPad 120 15000 Apple P1002 |LED TV 100 85000 Sony P1003 DSLR Camera 10 25000 Philips P1004 | iPhone 50 95000 Apple P1005 |LEDTV 20 45000 MI P1006 | Bluetooth Speaker 100 20000 Ahuja Constraints: 1. Pcode - Primary Key 2. Pname- Not Nullcreate table products €pno varchar(5) primary key, pname varchar(25) not null, qty int(3), price int(5), company varchar(15))>5 Query OK, @ rows affected (8.23 sec) mysql> insert into products values('P10@1','iPad',120,15@@0, ‘Apple'); Query OK, 1 row affected (@.08 sec) 1. Tojoin product and company and display in tabular form like - manufatured by mysql> select concat(pname,concat(" manufactured by ",company)) from products;| , f eee ee atc ad | , 7 PeCCCRU eras ered | Pee entrees ened | | | i i wr Le) Pete east eee) ea Metastases | LED TV manufactured by MI | Bluetooth Speaker manufactured by Ahuja (eet Sa: eT) 2. Convert all product name into capital Tect ucase(pname) From produc I care) | aa | eae ee I! aero isd | (aoa | BLUETOOTH SPEAKER | rows in set (®.063 . Display the cube of products quantity for more than or 100 quantity.select qty,pow(qty,3) from products where qty>=100; a |p spn nn nn nnn nan + | a1) | | Mle) | 16eeeeee | + Saat in set (0.07 sec) 4. Divide the price by 3 and display the result with 1 fraction digit for price of more than 40,000. 28333.3 | 31666.7 | 150e0.0 | 7 ENaC ett) 5. Display pname (last four letters only), qty, price with 2 decimal points and company for price in between 30000 to 80000. Inysql> select right(pname,4),qgty,round(price,2),company from products where pry Pe ae ee Le i t-- | right(pname,4) | qty | round(price,2) a eT Na panna een nen anne Soe eo Soe + rca PT a i ri | a 1 row in set (@.01 sec) 6. Display maximum price of productsmysql> select max€price) from products; + | max(price) mysql> select sum(qty) from products; ra n sumC qty) roe aa] €@.08 sec) | od H row in set (8.00 sec) 9. Find the difference between maximum price and minimum price from the table. mysql> select max(price)-min(price) from products; | max(price)-min(price) | 1 row in set (@.04 sec) Display unique Products from the table.distinct(pname) from products; Peetu | ert) | od | foe er tar) | iPhone | Bluetooth Speaker | rows in set (@.02 sec) select count(distinct(company)) from products; PTA G Bae Mala l @ate) 12. Display the product number, product name and company in the descending order of their price. nysql> select pno,pname,company,price from products order by price desc; eee ELS P1004 | iPhone Apple | 95000 aCe ng eae er) eg ise ec Cee er) Philips | 25000 P1006 | Bluetooth Speaker PE) | 2@ee0 | | [oa err) Lv) an 15000 rows in 13. _ Display product minimum price for each company. mysql> select company,min(price) from products group by company; | company | min(price) | rs as ule) | PIT) a | Tl) as | 45000 | Philips | 25000 ea | 85000 IS rows in set (0.02 sec | | | | )14. Display product number and product names in their ascending order of names. PCC Cmn ans ste te oa Ta Sa ET Cy) 3 ; Bis Lens rr) P10e0e1 pa eda =o a set (0.00 sec)