SYLLABUS IP PRACTICAL CLASS XII Practical Mars Distribution No. ‘Unit Name Programs using Pandas and Matpotib 2 SL Overes Practica fle minimum of 5 programs based on Pandas, 4 based on Malet and 15 SQL queries must be included) Project Work (sing concepts eared in class x and) TOTAL#1 Create a pandas series from a dictionary of values and an ndarray. inport pandas as pd import nunpy as np Spd. Series([Link]([1,3,4,7,8,8,9])) print(s) a 1 2 3 4 5 6 a type: int32 4 inport the pondas Lib as pd ingort pandas as pd # create 0 dictionary dictionary = {'A' 5 18, "8" : 28, ‘Cs 38} # crete 0 series series = pdSeres( dictionary) print series) A 18 ae) a] type: intoall the elements that are above the 75th percent inport pandas as pd inport nunpy 35 np spd, Series([Link]([1,3,4,7,8,8,9])) print(s) [Link](q:8.75) print() ("75th Percentile of the series is print(res) print() print( "The elenents that are above the 75th percentile::') print(s[sores]) dtype: int32 75th Percentile of the series 4: 8.0 The elenents that are above the 75th percentile: 6 9 type: int32#3 Create a Data Frame quarterly sales where each row contains the item er’, ‘Washing Machine"), »"Syaphony', 16], “ expenditure’ : [7008000 50000, 12000, 14800]) [Link]( ic) print(quartsales) qs=[Link]("itencat') print(‘Result after Filtering Datafrane') print(qs{'itencat’ expenditure" ].sun()) ftencat itenane expenditure 8 car Ford Teoa@00 1 Ac Hitachi see 2 Aircoller Symphony 12200 3 Washing Machine 1 14000 Result after Filtering Datafrane expenditure itencat Ac 50002 Aircoller 12008 hashing Machine 148@@ car 70800CE#4, Create a data frame based on ecommerce data and generate descriptive statistics (mean, median, mode, quartile, and variance). ort pandas as pt sales = {'Tnvoicelo's [124,100,100 104 105,108,187], "Producten: ['LED', ‘AC, "Deodrat', "eas, 'Soks', Shoes’, "Jacket, “quantity’: (2,1,2,4,2,1], ce [6,580,500 150, 250,200, 2208] fey tara sales) prin df Price] describe} .round(2) count, 1a wean 18458,08 Hane: Price, dtyp: float#5. Create frame for e» ion result and lay row labels, column labels data types of each column and the dimensions ‘nport pandas as pd ice{’Class':['1',' M0110", "IV", 'V1,"VU', VIN, ‘VII, 1X", ""', XC, X10"), “pass-Pencentage [100,100,100 128, 100,108,100, 162, 108, 98.6,109,99)} [Link](dic) print(result) print([Link]) print( shape of the datafrane is: print result. shape) Class. Pass-Percentage I 100.0 0 100.0 I 100.0 100.9 108.0 100.0 100.0 108.9 109.0 9 98.6 eM 100.0 uM 9.9 Class object Pass-Percentage float type: object shage of the datafrane is (12, 2)import pandas as pd dice('Wane':['Rohit', Mohit’, ‘Deepak’, 'Rohit', Deepak’, 'Sohit',"Geeta'], ‘NarksinIP' [85,45 92,85,92,96,84]} narksepdDatafrane(dic) # Find duplicate ros duplicateRow = narks[narks duplicated (teep-False prin (duplicateRou) Nane MarksinIP @ Rohit 85 2 Deepak 2 3 Rohit 5 4 ‘Deepak 2#7. Find the sum of each column, or find the column with the lowest mean. import pandas as pd Profitef "TCS": { ‘Qtrt':2508, Qtr2":2088, "Qtr3':3688, “Qtra”:2000), “wrpno": {Qtr 2860, Qtr2':2409, ‘Qtr :3608, ‘gtr :2400), ‘ (ert :210@, ‘Qtr2' 25709, ‘Qtr3':35000, "Gers :2100}} |.DataFrane( Profit) print (df) print() print( "Column wise sun in datfrane is print([Link](axis=2)) # Print meon value of each column print() print( Column wise nean value are: print([Link](axis2)) # Returns Colum with winimun meon value print() print( "Column with minimum nean value fs: F mean(axis-2) idenin() Tes WIPRO. LAT Qtrd 2580 2880 2100 Qtr2 20¢0 2400 570 Qtr3 3000 3600 35000 Qtra 2000 2400 2108 Column wise sum in datfrane is 1s 9500 wipro 11208 UT 44900 type: int6e Coluan wise wean value are:::::::1: 1s 2375.0 WrPRO 2800.8 UT 112250 type: floatea Coluan with minimum nean value is:: "1s"#8. Locate the 3 largest values in a data frame. port pandas as pd dice('Nane':[' Rohit, "Mohit, ‘Deepak’, "andl’,'pankaj', 'Sohit’, Geeta’), “NarksinIP":[85,45,92, 85,98, 96,84] [Link]( dic) # Find 3 Longest Value for MarksinIP Column print([Link](3,['NarksinI?"])) Name MarksintP 4 Pankaj 98 5 Sohit 96 2 Deepak 2#9. Subtract the mean of a row from each element of the row in a Data Fram Sort pandas 25 pt Profite{ “Ics': { “Qtr :2508, ‘Qtr2': 2808, ‘tr3': 3880, “Qtr 2088), “WIPRO: {"Qtrt':2808, (Qtr2’:2400, "Qtr: 3600, “Qtrd 2480}, “LAT: { "drt" :200, tra :570, tra" :358, "tre :210)) .Ditatrane(Profit) print(f) print() print(ean ofeach rov is ") print([Link](adss1)) print() print(Dtafran after Subtracting ean value ofeach row fron each elenet of that Rov is print(.sub([Link] axis), axis2)) Tes MPRO LAT tri 2500 2802 2108 Qtr2 20e0 2402 5708 tra 3000 3602 35000 tre 2000 2408 2108 Mean of each row is: tr 2466666667 Qtr 3366.666667 Qtr3 13866666667 rs 2166, 666667 type: floated Datafrane after Subtracting mean value of each rou fron each elenent of that Rou is: 1s PRO ur Qtrd—33,333833193,333393 — -366,666667 (Qtrd. -1366.666667 -966.666667 2333,232233 (tr -10066.666667 -18266, 666667. 21133,333333 tes -166.666667 233.333333—-66.666667#10. Replace all negative values in a data frame ort pandas as pd icf Datal':[-5,-2,5,8,9,-6], patat:[2,4,10,5,-5,-8]} fap. atafrane(dic) print df) print() print("dataFrane after replacing negative values with @:::") dFlaee)-2 print df) Datal Dated 52 4 e 8 4 dataFrane after replacing negative values with 0: Datal Data? @ 2 8 4 5 8 8 1 9 a Q efeplace all missing values in a data frame wi eee, 6 1518), Sachin’, Vinod’ ,'Lltbir’[Link],Deinder’, "Wali, ‘Doj's['12-81-2012', 15-61-2012" "85-09-2867", "17-01-2012" [Link],'16-€1-2012"]} pd Dataraneenpdata) printf) tf fila ean’ 38, 0o:29)) print() printf) enane tj Sachin 2-0-2012 Vinod 15-81-2012 Laltbin 6-28-2087 Nal 17-81- 2012 Devinder hal UnaSelvi 160-2012 rane Vinod akhbir 999 17-01- 2012 105 Devinder 9 106 UnaSelvi 16-01-20121g and exporting data between pandas and CSV file 1 # importing pondas module import pandas as pd # making data frane 4 df = pd.read_csv("E:\[Link]") print (df) enpid enane oj 181 Sachin Bhardwaj 12-01-2012 122 Vinod Vera 15-01-2012 183 Anand Ganesh 05-09-2007 1 import pandas as pd 2 1 [{*Wane': "Sachin’, ‘Sinliane’:‘Bhardwaj’}, {'lane': "Vinod", "Sinllane’:'Verna"), { ‘Nane's ‘Rajesh’, 'Sirliane' :'Nishra’}] 5 [Link](1) # saving the datafrane dfl.to_csv("E:\Datafranel..csv') Ce ’ bot Name SirName 0 Sachin Bhardwaj 1 Vinod Verma 2 Rajesh MishraImporting Data from MySQL to Data Frame. Tn [1] ior pandas asp In 2]: sort msl comector 1 [5]: coms .[Link](tst" oases“ rents ut’ tate’ sachin) peirt( cn) «ayst].comector. convection aysUcomectionobjct at eneaeerweacaTFb> 1n [6]: expr] urs tts fron sa @ ut): Tes satin emgExporting data from Data Frame to MYSQL. ne. clos’ user" roat psd ot” attseeach print(con) carso() 1415) in iter) ts) yee values("+ enpid 4°," enate +," Oj +")") ny] comer comection sqcomrection bec at eae TICS empid = enane bj 0 I schin za-e. 1 Vio) 2-4 218 ltr 207 3 tal ez-7 4 105 Devinder 2 166 sels 22-01-16 Dy#14, Given the school result data, analyse the performance of the students on different parameters, e.g subject wise or class wise. inport [Link] as plt Subyjecte['Physics’, ‘Chenistry’, "Hind, Biology’, ConputerSc"] Percentage=[85,78,65,98, 108] [Link]( Subject, Percentage, align’ center’, color='green’) [Link]] ('UBIECTS IME") [Link] (PASS PERCENTAGE") [Link]('Bar Graph For Result Analysis’) [Link]() Ba Graph For Result analysis 8 g i é $ t % 4 ‘ 0 Fhysks Censry Hd Boy Conus SIBECTS NE#15. For the Data frames created above, analyze and plot appropriate charts with title and leger inport [Link] as plt fnport nunpy as np s=[‘4st', ‘2nd’, " per_sc=[95,89,77] 6 ,93,75] 7 92,77] [Link](len(s)) [Link](x,per_sc,label='Science' juddthe0.25, colors" green’) [Link](x+.25,per_com, label connerce’ ,width-0.25, color="red’ ) [Link](x+.50,per_hun,label='Hunanities' ywidth8.25,color=' gold") [Link](x,s) [Link]( Position’) [Link] (‘Percentage’) [Link]('ar Graph For Result Analysis’) plt. legend() [Link]() Bar Graph For Result Analysis mE Science fem Humanities Percentage Position#16. Take data of your interest from an open source (e.g. [Link]), aggregate and summarize it. Then plot it using different plotting functions of the Matplotlib import pandas as pd import [Link] as plt df = pd.read_csv("E:\census. csv") print (df) [Link]. State/UT Total Population of other 1 Andaman & Nicobar Islands 47 2 Andhra Pradesh 43769 3 Arunachal Pradesh 495, 4 Assam 11374 5 Bihar 49827 6 Chandigarh 142 7 Chhattisgarh 6591 8 Dadra & Nagar Haveli 3 Daman & Diu 59 10 Goa 398 n Gujarat 11544 2 Haryana 3422 3B Himachal Pradesh 2051 16 Jammu & Kashmir 4137 5 Jharkhand 13463 16 Karnataka 20266 17 Kerala 3902 18 Lakshadweep 2 19 Madhya Pradesh 29507 20 Maharashtra 49891 a Manipur 1343 2 Meghalaya 627 23 Mizoram 165 26 Nagaland 308 25 NCT of Delhi 4213, 26 Odisha 20332 2 Puducherry 252 28 Punjab 10243 29 Rajasthan 16517 38 Sikkim 126 31 Tamil Nadu 22364 32 Tripura 833, 33 uttar Pradesh 137465 34 Uttarakhand 4555 35 West Bengal 30349 Total 487803exp=(00,0,0,0,0.1) [Link](slices, labelssstates, colorsscols, startangles%, explodesexp, shadovcTrue, autopct='.1f%8') plt-title' ott Census Data') plt. legend() [Link]() 2011 Census Data em i Nas aca Pradesh cath msn mir Andee (O30 Arh Pas) gan#17. Create a student table with the student id, name, and marks as attributes where the student id is the primary key. #18. Insert the details of a new student in the above table. #19. Delete the details of a particular student in the above table. Acris rs Big 2]#20. Use the select command to get the details of the students with marks more than 80. #21. Create a new table (order ID, customer Name, and order Date) by joining two tables (order ID, customer ID, and order Date) and (customer ID, customer mysql> select * from orders; | 2020-10-1 | 2020-04~ | 2019-01- | INorA | | iNora | | ausTRALzA | | AUSTRALIA | nysql> create table orderdetails as select orderid, custonernane,ordrdate ~> from orders custoner where orders custoneridscustonercustoner'd; ysqi> select * from orderdetails -o- + | orderdate 2020-10-10 2020-04-05 2019-01-01#22. Create a foreign key in one of the two tables mentioned above hysal> alter table orders add foreign key(custonerid) references custoner(custonerid); Query OK, 3 rons affected (1,22 sec) #23. Find the min, max, sum, and average of the marks in a student marks table. OR Ries 4104951805 451.2500 #24. Find the total number of customers from each country in the table (customer ID, customer Name, country) using group by. hysql> select country,count(*) "TOTAL CUSTOMER” from customer group by country; bee sttece: aot | country | TOTAL CUSTOMER | be + | AUSTRALIA | 2] | inora | 2] [race .#25, Create a new table (name, date of birth) by joining two tables (student id, name) and (student id, date of birth). name SACHIN sql> select * from studentbirth; 1 1982-06-17 1981-05-20 rows in set (0.00 sec) nt mysq7> create table stu2 as select name,dob from student studentbirth -> where student studentid=studentbirth studentid; Query ok, 2 rows affected (0.37 sec) Records:'2 Duplicates: 0 Warnings: 0 mysql> select * from stu2; + — | name | | SACHIN | 1982-06-17 | | AMET | 1981-05-20 | te a+ 2 rows in set (0.00 sec) #26, Write a SQL query to order the (student ID, marks) table in descending order of the marks.