TIA HY, HSAATATE -36 0003 KENDRIYA VIDYALAYA AHMEDABAD CANTT, HANUMAN CAMP, AHMEDABAD 380003 WZ ——. eaten PRACTICAL FILE INFORMATION PRACTICES CLASS XII Sonia Choudhary PGT CS KV Ahmedabad CanttSYLLABUS. IP PRACTICAL CLASS XII Practical Marks Distribution ‘S.No. Unit Name Proyams using Pondas and Matpatb 2 ‘aL queries Pract fle minimum of15 programs based on Pandas, Abased on MatpiotlO and 15 SL queries must be includes) Project Work (sing concepts learned in cass and) Wwevace TOTALSuggested Practical List . 1 Data Handling 1. Create a panda’s series from a dictionary of values and a ndarray 2. Given a Series, print all the elements that are above the 75th percentile. 3. Create a Data Frame quarterly sales where each row contains the item| category, item name, and expenditure. Group the rows by the category and print/ the total expenditure per category. 4, Create a data frame for examination result and display row labels, column| labels data types of each column and the dimensions 5. Filter out rows based on different criteria such as duplicate rows. 6. Importing and exporting data between pandas and CSV file 2 Visualization 1, Given the school result data, analyses the performance of the students on| different parameters, e.g subject wise or class wise. 2. For the Data frames created above, analyze, and plot appropriate charts with] title and legend. 3. Take data of your interest from an open source (e.g. data.gov.in), aggregate| and summarize it. Then plot it using different plotting functions of the Matplotlib| library. 3 Data Management 1. Create a student table with the student id, name, and marks as attributes wher the student id is the primary key. 2. Insert the details of a new student in the above table. 3. Delete the details of a student in the above table. 4. Use the select command to get the details of the students with marks more than] 80. 5. Find the min, max, sum, and average of the marks in a student marks table. 6. Find the total number of customers from each country in the table (customer ID, customer Name, country) using group by. 7. Write a SQL query to order the| (student ID, marks) table in descending order of the marks.STUDENT PROFILE XUC Informatics Practices Name of Student Fathers Name ADARSH PANDEY SATENDRA KUMAR PANDEY 9426379606 AKANSHA GHANSHYAM KOGE 8150076619 BHAVIKA HARESHKUMAR MAKHUJA, HARESHKUMAR KEWALRAM MAKHUA Si2sssesas DIP ROY DEBASHISH ROY 8871690895 FENI JAGDISHBHAL SOLGAMA JAGDISH BHAL SOLGAMA. 9909016032 GAURAV G NAIR GURUVAYURAPPAN 7600852214 HARISHIKESH MODI ASHOK MODI 9328880999 ISHITA SINGLA. AMIT SINGLA 7986366261 JAYDEEP SINGH PASR.BHAL 9737587840 KADRI RUSHAN KADRI NAIM 9714399011 RAHUL MANOHARAN MANOHARAN.P 9048627524 SHAMMI KUMARIJ OJHA IAY PRAKASH OJHA 8000708000 SUNNY SINGH (CHAUHAN, CHAUHAN SARVAN SINGH 8936078212STUDENT PROFILE XuD Informatics Practices Name of Student Fathers Name Contract Number PATEL YASH KAMLESHBHAL KAMLESH BHAL 9879184274 ANNWESA MUKHOPADHYAY, MEGHNAD MUKHOPADHYAY 9920518749 SOURAV BHOSLE SUBHASH ‘9998076143 NISHANT CHOUDHARY DILIP KUMAR (CHOUDHARY, 19501 098305 SUBHAM DUTTA SANTOSH KUMAR DUTTA 9893205041 MD NIAMUL, HEQUE, MD SHERFUL HEQUE 19520208712 ANUSKA YADAV, UMASHANKAR, 9519680898. SAHIL CHATURVEDI VIRENDRA CHATURVEDI 9518878233,#1 Create a pandas series from a dictionary of values and an ndarray. imort pandas as pd import nunpy as np s=pd.Series(np.array([1,3,4,7,8,8,9])) print(s) @ 1 2 3 4 5 6 qa type: int32 4 import the pandas Lib os pd inport pandas as pd # create 0 dictionary dictionary = {'4" : 18, 8° : 20, 'C' : 28) # create a series series = pd Senies(dictionry) print(series) A a c type: intoa#2. Given Series, print all the elements that are above the 75th percentile. inport pandas as pd ‘inport nunpy as np sepd Series(np.array((1,3,4,7,8,8,9])) print(s) reses,quantile(@-0.78) print() print('75th Percentile of the series is The elenents that are above the 75th percentile::") [s>res]) @ 1 i 3 4 5 6 types int32 75th Percentile of the series is::: 8.8 The elenents that are above the 75th percentile:: 6 9 types int32#3 Create a Data Frame quarterly sales where each row contains the item category, item name, and expenditure. Group the rows by the category, and nport pandas as pd ice{"itencat':['car’, "Ac", ‘Adrcoller", ashing Machine", ‘temane':["Ford’, Hitachi’, Syaphony', LG’), “expenditure: [7@¢e000, 52088, 12008, 1400]} quartsalessp4.DataFrane(dic) print (quartsales) as-quartsales.groupby('itencat') print( Result after Filtering Datafrane') print(gsf 'itencat’, ‘expenditure’ ].sun()) itencat itemane expenditure 8 car Ford 7000008 1 Ac Hitachi sence 2 Aircoler_ Symphony 12008 3 Washing Machine 6 14900 Result after Filtering Datafrane expenditure itencat Ac 5000 Aincoller 12000 Washing Machine 14900 car eae#4, Create a data frame based on ecommerce data and generate descriptive statistics (mean, median, mode, quartile, and variance] ‘nor pandas as pd sales = {Invoices [Loe 100, 103,004 105,096 1007], roductane': {'LED',sc','Deodant', Jean’, ‘Books','Shes', Jacket], (uantity’s (2,1,2,1,2,11], + [save See, 8,258, 95, 3802280] fp. DataFrane( sales) print df Price'].describe() round(2))] count 7.08 ean 19458.08 253,61 508.00 155.0 50.0 208.8 5000.00 anes Price, dtype: loaté4#5, Create a data frame for examination result and display row labels, column labels data types of each column and the dimensions import pandas as pd dice{'Class':[‘T', "II", "110", IV", 'V'"W", "IT", VIN, "1X", X','X0', XI"), “pass-Percentage' [100 18,100,108, 100,100,100, 1,100, 08,6,100,09]} result DataFrane(dic) print(result) print result. dtypes) print( ‘shape of the datafrane is:::::") print (result. shape) Class Pass-Percentage I 100.0 Il 100.8 m1 108.0 Nv 108.0 v 108.0 wl 108.0 vu 108.0 10.0 108.0 8.6 108.0 M1 XII 9.0 Class abject Pass-Percentage Float types object shape of the datafrane is:::: (12, 2)#6. Filter out rows based on different crite import pandas as pd ice 'Nane':['Rohit’, ‘Mohit’, ‘Deepak’, "Rohit, "Deepak 'Sohit', “eeta’], "NarksinIP':[85,45,92,85,92,96,84]} narks=pd, DataFrane(dic) + Find duplicate rovs uplicatetou = narks[narks.duplicated(keepsFalse)] print (duplicateRow) Name MarksinTP @ Rohit 2 Deepak 3° Rohit 4 Deepakid the sum of each column ‘import pandas as pd Profite{ "TCS": { “¢trt':2508, Qtr2':200@, ‘tr3’: 3689, “Qtr: 2888), ‘WIPRO: {"Qtrt :2800, Qtr2':2480, (Qtr3":3689, ‘Qtr :2408), LAT’: { "Qtr :2109, @tr2':5700, "Qtr3": 35609, “Qtr: 2100) f-pd.DataFrane Profit) print(df) print() print(Colunn wise sun in datfrane is print (fsun(axis-)) # Print mean value of each column print() print(Coluan wise nean value are print nean(axis-e)) # Returns CoLum with minimus mean value print() print( "Colum with rininun mean value f.nean(axis-t) idanin() TCS WIPRO LAT Qtri 2528 2828 2100 Qtr2 2008 2480 5700 Qtr3 3080 3080 35008 Qtra 2000 2480 2100 Column wise sum in datfrane is 1s 9500 WIPRO 11200 UT 489e0 type: ints Column wise nean value are:::: 1S 2375.8 WIPRO 2800.8 ur 1125.8 type: floated Column with minimum mean value is::: “cs"#8, Locate the 3 largest values in a data frame. inport pandas as pd dice{'Nane':['Rohit’, ‘Mohit’, ‘Deepak’ "Anil, ‘Pankaj’, ‘Sohit’, ‘Geeta’, ‘Hanks inIP':[85,45,92,85,98,96,84]} warks=pd.Datafrane dic) # Find 3 Largest Volue for NorksinIP Colunn print (narks.nlargest(3,[ YarksinIP'])) Name MarksinIP 4 Pankaj 8 5 Sohit % 2 Deepak 2subtract the mean of a row from each element of the row in a Data Fram ‘import pandas as pd Profit "TS": { “Ql :2m,gtr:20@, Qtr, tr 200}, MPH: {et 260, gta: 24, tr: 360, te :208), AT: { "Qt :29, Qtr :57@, Qtr :350, ote :2160)) .ataFrane( Profit) (af) prit() print ean ofeach row is: print fran (aise) prit() print Catafrane after Subtracting mean value ofeach rov fron each eleent of that Rov is printf subd. ean(axset), axis) Tes WIPRO LAT Qtri 2500 2808 Qtr2 2008 2408 5700 Gens 3am 388 Qtrd 208@ 2408 ean of each row is: rt 2466.666667 (tra 3366.666667 (tr313866.666667 irs 2166.666667 types Floated Datafrane after Subtracting nean value of each row from each elenent of that Row is : 1s WIPRO UT Qtr 33,333833333.333333-266,666667 tra. -1366.666667 -966.666667 2333.333333 (rd -10866. 666667 -10266.666667 21133.333333 tra -166.666667 233.333333-66.666667#10. Replace all negative values in a data frame with a inport pandas as pd ice 'Datal':[-5,-2,5,8,9,-5], ‘Datad' :(2,4,18,15,-5,-8]} spd. ataFrane( ic) print(df) print() print("éatafrane after replacing negative values with @:: ffafee)-2 print(af) Datal_ Dated 5 2 204 5 18 8 8 9 4 6 8 datatrane after replacing negative values with 0: Datal Data 2 4 1 8 e ea data frame with a 999. ingort pandas as pd ingort nung a5 1p ceapatae{ “exp: [ 10,100 13,104, 105,108], “erane's['Sachi’ Vind’ 'Lakthir’ pan, "Devinde’, 'Unaselvi, Oo "12-04-2012", "15-81-202', "5-88-2087," 1-@1- 2012 apa, op tatarane ep) print( df) of. fina nan, 'n':98}) print) print(af) mid eae M1 Sachin 12-01-2012 42 Vinod 15-01-2012 163 Lakhbir 05-09-2007 NaN 17-@1- 2812 405 Devinder Nall 106 UnaSelvi 16-01-2012 eae Oj Sachin 2-01-2012 12 Vind 15-21-2002 18 Latin 06-28-2087 10 a7. 22 115 Devinier 0 106 UiSelvd 1621-202ig and exporting data between pandas and CSV file # importing pandas module import pandas as pd # making data frame df = pd.read_csv("E:\emp.csv") print(df) empid ename doj 101 Sachin Bhardaj 12-01-2012 182 Vinod Verma 15-01-2012 103. Anand Ganesh 05-09-2007 1 import pandas as pd 2 1 [{'Wane': "Sachin’, ‘Sirliane {'Nane': ‘Vinod’, "Sirllane’ { ‘Nane": ‘Rajesh’, 'Sirllane': dflspd.DataFrane(1) # saving the datafrone dF1.to_csv("E:\Datafranel..csv") = ‘hetred ems 2 ae ee A) wale] Faeroe i en (OA EER stueracone Name SirName 0 Sachin Bhardwaj 1Vinod Verma 2 Rajesh Mishra i 1 2 2Importing Data from MySQL to Data Frame. 1n (sort pans as 1 (2): ort sl comectr 1m [5]: coneysl.comectorcamect(tst" Iocan uses"ron'passad' ot, ath print con) (85,78,65 02 100] plt.bar( Subject, Percentage, aligns" center’ ,color<'green') plt.xlabel( 'UBTECTS nu) plt.ylabel( ‘ASS PERCENTAGE") plt.title('Bar Graph For Result Analysis’) plt.show() ar Graph For Result Anas g & t 5 § & & 4 4 & Prysks Chemisty Hindi Bilogy Computes SUBECTS WME#15. For the Data frames created above, analyze and plot appropriate charts with title and legend. ingort natplot]ib.pyplot as plt nport numpy as. np s=[‘Ast' 2nd" 3rd") xenp.arange(len(s)) plt.bar(x,per_sc,label-'science’ jddths8.25,color=' green’) plt.bar(x+.25, per_con,label=" connerce’ ,width=8.25,color='red'’) plt.bar(xs.52,per hun, label='Hunanities'uidth8.25,color=' gold" plt.xticks(x,s) plt.xlabel( ‘Position ) plt.ylabel (‘Percentage’) plt.title('sar Graph For Result Analysis") pt. legend(), pt. show() Bar Graph For Result Analysis Science fee Humanities is Position#16. Take data of your interest from an open source (e.g. data.gov.in), aggregate and summarize it. Then plot it using different plotting functions of the Matplotlib import pandas as pd import matplotlib.pyplot as plt df = pd.read_csv("E:\census.csv") print(df) S.No. State/UT Total Population of other 1 Andaman & Nicobar Islands 47 2 ‘Andhra Pradesh 43769 3 Arunachal Pradesh 495 4 Assan 11374 5 Bihar 40827 6 Chandigarh 142 Zz Chhattisgarh 6501 8 Dadra & Nagar Haveli 43 9 Daman & Diu 59 10 Goa a Gujarat 2 Haryana 3 Himachal Pradesh 4 Jammu & Kashair 5 Jharkhand 16 Karnataka 7 Kerala 48 Lakshadweep 19 Madhya Pradesh 20 Maharashtra a Manipur 2 Meghalaya 23 Mizoram 24 Nagaland 25 NCT of Delhi 26 Odisha a Puducherry 28 Punjab 29 Rajasthan 30 Sikkim 31 Tamil Nadu 32 Tripura 33 Uttar Pradesh 34 Uttarakhand 35 West Bengal Totalnport pandas as pd mort natplotlib, OF = dread c i tal Populati Tatelsestates, colors=cols, startangle-92, explodesexp, shadoweTrue, autopct='. 1f%8') 1 Census Data’) plt.shou() 2011 Census Data f ofan NM daa Nard intra (Arata san mm ita Anan LS Ott Awa ote an#17. Create a student table with the student id, name, and marks as attributes where the student id is the primary key. student student_id io pore #18. Insert the details of a new student in the above table. of a particular student in the above table. 416 102 Mohit 425 14 Virat 495 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 joi two tables (order ID, customer ID, and order Date) and (customer ID, customer pt Select’* from orders; INDIA INDIA AUSTRALIA | AUSTRALIA | nystT> create table orderdetails as select orderid,custonemaneorderdate ~> fron orders customer where orders custoneridscustoner. custonerid: ysql> select * from orderdetails; ae eee mane | orderid | customername 1 orderdate | | 2020-10-10 | | 2020-04-05 | | 2019-01-01 | +#22. Create a foreign key in one of the two tables mentioned above hysql> alter table orders add foreign key(custonerid) references custorer(custonerid); ery ok, 3 rns affected (1.22 see) #23. Find the min, max, sum, and average of the marks in a student marks table. 4104951805 451,2508 #24, Find the total number of customers from each country in the table (customer ID, customer Name, country) using group by. hrysal> select country,count(*) "TOTAL CUSTONER" fron customer group by country; | country | TOTAL CUSTONER | he | AUSTRALIA | INDTA#25. Create a new table (name, date of birth) by joining two tables (student id, name) and (student id, date of birth). mysql> select * from student: [studentia | name SACHIN: ama 2 rows in Set (0.01 sec) mysqi> select * from studentbirth; studentid | dob T 1982-06-17 _1981-05-20 2 rows in set (0.00 secd mysql> 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; settee + | name | dob | | SACHIN | 1982-06-17 | | awer | 1981-05-20 | es 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. BaLong time ago, people who sacrif sleep, family, food, laughter a luxuries of life were called SAINTS. Now, they are called PROGRAMMERS Thank You WIP