Practicals
Data Handling
1
PRATICAL 1
Problem statement: 1. Create a Series object using the python sequence with 5 elements:
Solution:
Source Code:
import pandas as pd
L = [12,23,34,45,56]
S= [Link](L)
print(S)
Screenshot:
2
PRATICAL 2
Problem statement: 1. Create a Series object ‘vowel’ to store all vowels individually. Its
index should be 1,2,3,4 & 5.:
Solution:
Source Code:
import pandas as pd
vowels = [Link](['a','e','i','o','u'], index = [1,2,3,4,5])
print(vowels)
Screenshot:
3
PRATICAL 3
Problem statement: Create s Series object using ndarray that has 5 elements in the range
50 and 100:
Solution:
Source Code:
import pandas as pd
import numpy as np
S = [Link]([Link](50,100,10))
print(S)
Screenshot:
4
PRATICAL 4
Problem statement: Create a Series object using dictionary to that stores the no of
students in each section of class 12th of your school:
Solution:
Source Code:
import pandas as pd
D = {'A':23,'B':34,'C':36,'D':40,'E':32}
S = [Link](D)
print(S)
Screenshot:
5
PRATICAL 5
Problem statement: Total no of students to be admitted is 350 in Yojna School every year.
Write code to create a Series object ‘School’ that stores these total no of students for the
year 2015 to 2022:
Solution:
Source Code:
import pandas as pd
S = [Link](350, range(2016,2023))
print(S)
Screenshot:
6
PRATICAL 6
Problem statement: Create a Series object ‘Item’ that stores rate of each product as given
below:
Soap 54
Salt 20
Sugar 39
Write code to modify rate of soap to 44 and sugar to 42. print the changed rate:
Solution:
Source Code:
import pandas as pd
S = [Link]([54,20,39],['soap','salt','sugar'])
print(S)
S['soap']=44
S['sugar']=42
print("After Updating values")
print(S)
Screenshot:
7
8
PRATICAL 7
Problem statement: No of students in class 11 and class 12 in three streams (science,
commerce and humanities) are stored in 2 series object class 11 and class 12. write code to
find total no of students in class 11 & class 12 stream wise:
Solution:
Source Code:
import pandas as pd
D1= {'Science':32,'Commerce':36,'Humanities':20}
D2= {'Science':28,'Commerce':34,'Humanities':22}
Class11 = [Link](D1)
Class12 = [Link](D2)
print(Class11)
print(Class12)
print('Total Students')
print(Class11 + Class12)
Screenshot:
9
PRATICAL 8
Problem statement: Create a Series object ‘population’ to store population of 5 different
metro cities and display the population that are more than 300000:
Solution:
Source Code:
import pandas as pd
population = [Link]([400000, 25400, 301100, 100500,505000],
['Mumbai','Kolkata','Delhi','Chennai','Bangluru'])
print(population)
print('Poplulation more than 300000')
print(population[population>300000])
Screenshot:
10
PRATICAL 9
Problem statement: Create a series ‘temp’ that stores temperature of seven days in it. Its
index should be ‘Sunday’, ‘Monday’ ….
Write script to
1. Display temp of first 3 days.
2. Display temp of last 3 days.
3. Display all temp in reverse order like Saturday, Friday,….
4. Display temp from Tuesday to Friday.
5. Display square of all temperature.:
Solution:
Source Code:
import pandas as pd
temp = [Link]([45,42,40,46,39,38,40],
['Sunday','Monday','Tuesday','wednesday','Thursday','Friday','Saturday']
)
print(temp)
print("Temp of first three days\n",[Link](3))
print("Temp of last three days\n",[Link](3))
print("Temp in reverse order\n", temp[::-1])
print("Temp from Tuesday to Friday\n",temp['Tuesday':'Friday'])
print("Square of all Temprature\n",temp*temp)
Screenshot:
11
12
PRATICAL 10
Problem statement: Create a Series object ‘employee’ that stores salary of 7 employees.
Write script to print
1. Total no of elements
2. Series is empty or not
3. Series consist NaN value or not
4. Count Non-NA elements
5. Axis labels:
Solution:
Source Code:
import pandas as pd
D =
{'ram':34000,'hari':42000,'suman':30000,'chandan':45000,'raghu':23000}
employee = [Link](D)
print(employee)
print("Total no of Employees",[Link])
if [Link]:
print("Series is empty")
else:
print("Series is not empty")
if [Link]:
print("Series contains NaN elements")
else:
print("Series does not contains NaN elements")
print("Total no of Non NA elements ",[Link]())
print("Axis labels\n", [Link])
Screenshot:
13
14
PRATICAL 11
Problem statement: Create the following dataframe ‘Sport’ containing sport wise marks for
five students. Use 2D dictionary to create dataframe.
Student Sport Marks
I Jai Cricket 80
II Raj Football 76
III John Tennis 89
IV Karan Kabaddi 92
V Chandu Hockey 97
Solution:
Source Code:
import pandas as pd
D = {'student':['jai','raj','john','karan','chandu'],
'sport':['cricket','football','tennis','kabaddi','hockey'],
'marks':[80,76,89,92,97]}
sport = [Link](D, ['I','II','III','IV','V'])
print(sport)
Screenshot:
15
PRATICAL 12
Problem statement: Create a dataframe from list containing dictionaries of most economical
bike with its name and rate of three companies. Company name should be the row labels.
Solution:
Source Code:
import pandas as pd
L1 = {'Name':'Sports','Cost':60000}
L2 = {'Name':'Discover','Cost':62000}
L3 = {'Name':'splendor','Cost':63000}
Bike = [L1,L2,L3]
df = [Link](Bike, ['TVS','Bajaj','Hero'])
print(df)
Screenshot:
16
17
PRATICAL 13
Problem statement: Consider two series object staff and salaries that stores the number of
people in various office branches and salaries distributed in these branches respectively.
Write a program to create another Series object that stores average salary per branch and
then create a dataframe object from these Series object.
After creating dataframe rename all row labels with Branch name.
Solution:
Source Code:
import pandas as pd
staff = [Link]([20,24,30,18])
salary = [Link]([240000,336000,450000,270000])
avg = salary/staff
org = {'Employees':staff,'Amount':salary,'Average':avg}
df = [Link](org)
print("Without Row Label")
print(df)
[Link] = ['sale','store','marketing','maintenence']
print("With Row Label")
print(df)
Screenshot:
18
19
20
PRATICAL 14
Problem statement: Read the data in the file ‘[Link]’ into a dataframe ‘aidretrieved’
and display it. Now update the row labels and column labels of ‘aidretrieved’ to be the same
as that of ‘aid’ of practical 19.
Toys Books Shoes
MP 7000 4300 6000
UP 3400 3200 1200
AP 7800 5600 3280
CG 4100 2000 3000
Solution:
Source Code:
import pandas as pd
aidretrieved = pd.read_csv('C:/sample/[Link]',
names=['Toys','Books','Shoes'],)
[Link] = ['MP','UP','AP','CG']
print(aidretrieved)
Screenshot:
21
Practicals
Matplotlib
22
PRATICAL 15
Problem statement: Collect and store total medals won by 10 countries in Olympic games
and represent it in form of bar chart with title to compare an analyze data.
Solution:
Source Code:
import [Link] as plt
medals = [213,224,300,59,100,140,256,98,60,24]
country = ['Ger','Itly','USA','Jamca','Japan',
'India','China','Aus','Arg','Ethopia']
[Link](country, medals)
[Link]('Olympics Medal Tally')
[Link]()
Screenshot:
23
24
PRATICAL 16
Problem statement: Techtipnow Automobiles is authorized dealer of different Bikes
companies. They record the entire sale of bikes month wise as give below:
Jan Feb Mar Apr May Jun
Honda 23 45 109 87 95 100
Suzuki 45 57 75 60 50 30
Tvs 97 80 84 68 80 108
To get proper analysis of sale performance create multiple line chart on a common plot
where all bike sale data are plotted.
Display appropriate x and y axis labels, legend and chart title.
Solution:
Source Code:
import [Link] as plt
month = ['jan','feb','mar','apr','may', 'jun']
honda = [23,45,109,87,95,100]
suzuki = [45,57,75,60,50,30]
tvs = [97,80,84,68,80,108]
[Link](month,honda, label = 'honda')
[Link](month,suzuki, label = 'suzuki')
[Link](month,tvs, label = 'tvs')
[Link]('Techtipnow Automobiles Sale Analysis')
[Link]('Month')
[Link]('No of Bikes')
[Link](loc = 'lower center')
[Link]()
25
Screenshot:
26
27
PRATICAL 17
Problem statement: Given the school result data, analyses the performance of the student
on different parameters, e.g. subject wise or class wise. Create a dataframe for the above,
plot appropriate chart with title and legend.
Eng Math Phy Chm IT
9 78 89 69 92 96
10 89 91 84 90 98
11 90 80 76 82 90
12 94 98 90 96 100
Solution:
Source Code:
import pandas as pd
import [Link] as plt
d = {'eng':[78,89,90,94],'math':[89,91,80,98],
'phy':[69,84,76,90],'chm':[92,90,82,96],
'IT':[96,98,90,100]}
df = [Link](d,[9,10,11,12])
print(df)
[Link](kind = 'bar',title = 'Class Wise Marks Analysis',
xlabel = 'Class', ylabel = 'Marks')
df1 = df.T
[Link](kind = 'bar',title = 'Subject Wise Marks Analysis',
xlabel = 'Class', ylabel = 'Marks')
[Link](loc = 'lower center')
[Link]()
SCREENSHOT
28
29
PRATICAL 18
Problem statement: The following seat bookings are the daily records of a month
December from PVR cinemas:
124,124,135,156,128,189,200,150,158, 150,200,124,143,142,130,130, 170,
189,200,130, 142,167,180,143,143, 135,156,150,200,189,189,142
Construct a histogram from above data with 10 bin..
Solution:
Source Code:
import pandas as pd
import [Link] as plt
L = [124,124,135,156,128,189,200,150,158,
150,200,124,143,142,130,130, 170,
189,200,130, 142,167,180,143,143,
135,156,150,200,189,189,142]
[Link](L)
[Link]("Booking Records @ PVR")
[Link]()
Screenshot:
30
31
PRATICAL 19
Problem statement: 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
library.
Solution:
Source Code:
import pandas as pd
import [Link] as plt
df = pd.read_csv('c:\sample\Crime_data.csv')
print(df)
[Link]([Link],[Link],width = 0.25)
[Link](0.25,[Link],width = 0.25)
[Link](0.5,[Link],width = 0.25)
[Link](kind = 'bar', x = 'Crime')
[Link](kind = 'bar', x= 'Crime', y= ['Boys','Girls', 'Transgender'])
[Link]()
Screenshot:
32
MySQL
Practicals
33
PRATICAL 26
Problem statement: Create a student table with the student id, name, and marks as
attributes where the student id is the primary key.
Solution:
Source Code:
create table student
( -> studid int primary key,
-> name varchar(30),
-> marks int
-> );
Screenshot:
34
PRATICAL 27
Problem statement: In the table ‘student’ created in practical 26, insert the details of new
students.
Solution:
Source Code:
insert into student values(1, 'sanjay', 67);
mysql> insert into student values(2, 'surendra', 88);
mysql> insert into student values(3, 'Jamil', 74);
mysql> insert into student values(4, 'Rahul', 92);
mysql> insert into student values(5, 'Prakash', 78);
Screenshot:
35
PRATICAL 28
Problem statement: Write SQL command to get the details of the students with marks
more than 80.
Solution:
Source Code:
select * from student where marks >=80;
Screenshot:
36
PRATICAL 29
Problem statement: Write SQL command to Find the min, max, sum, and average of the
marks in a student marks table..
Solution:
Source Code:
select min(marks) as Min_marks, max(marks) as Max_Marks, sum(marks) as
Total_Marks, avg(marks) as Average_Marks from student;
Screenshot:
37
PRATICAL 30
Problem statement: Delete the details of a student table created in Practical 26.
Solution:
Source Code:
Delete from student;
Screenshot:
38
PRATICAL 31
Problem statement: Find the total number of customers from each country in the table
(customer ID, customer Name, country) using group by.
Solution:
Source Code:
select country, count(cname) as 'Total_Customers' from customer
group by country;
Screenshot:
39
PRATICAL 32
Problem statement: Write a SQL query to order the (student ID, marks) table in descending
order of the marks.
Solution:
Source Code:
select * from student Order By name DESC;
Screenshot:
40
PRATICAL 33
Problem statement: for the given table ‘Hospital’ write SQL command to display name all
patient admitted in month of May.
PID PNAME ADMITDATE DEPT FEES
AP/PT/001 Rahil Khan 21/04/2019 ENT 250
AP/PT/002 Jitendal Pal 12/05/2019 Cardio 400
AP/PT/003 Suman Lakra 19/05/2019 Cardio 400
AP/PT/004 Chandumal Jain 24/06/2019 Neuro 600
.
Solution:
Source Code:
select * from hospital where monthname(admitdate) = 'May';
Screenshot:
41
PRATICAL 34
Problem statement: for the given table ‘Hospital’ write SQL command to Display patient
name in upper case with year of admission.
PID PNAME ADMITDATE DEPT FEES
AP/PT/001 Rahil Khan 21/04/2019 ENT 250
AP/PT/002 Jitendal Pal 12/05/2019 Cardio 400
AP/PT/003 Suman Lakra 19/05/2019 Cardio 400
AP/PT/004 Chandumal Jain 24/06/2019 Neuro 600
.
Solution:
Source Code:
Select UPPER(pname) as ‘patient name’, YEAR(admitdate) as ‘admit year’
From hospital;
Screenshot:
42
PRATICAL 35
Problem statement: for the given table ‘Hospital’ Create sql query to display first four
letters of the patient name along with length of their name who admitted before may.
PID PNAME ADMITDATE DEPT FEES
AP/PT/001 Rahil Khan 21/04/2019 ENT 250
AP/PT/002 Jitendal Pal 12/05/2019 Cardio 400
AP/PT/003 Suman Lakra 19/05/2019 Cardio 400
AP/PT/004 Chandumal Jain 24/06/2019 Neuro 600
.
Solution:
Source Code:
Select LEFT(pname,4) as ‘pname’, length(pname) as ‘length’
From hospital
Where month(admitdate) < 5;
Screenshot:
43
PRATICAL 36
Problem statement: For given string “techtipnow computer education”, Write SQL
command to display the position of “education”.
.
Solution:
Source Code:
Select INSTR(“techtipnow computer education”, “education”);
Screenshot:
44
PRATICAL 37
Problem statement: Create a SQL Query to display monthname, weekday name of today’s
date.
Solution:
Source Code:
Select monthname(curdate()) as monthname, dayname(curdate()) as day;
Screenshot:
45
PRATICAL 38
Problem statement: Create a SQL Query to retrieve and display ‘computer’ from given text
‘techtipnow computer education’
Solution:
Source Code:
Select substr(‘techtipnow computer education’, 12, 8);
Screenshot:
46
PRATICAL 39
Problem statement: for the given table ‘staff’ Create SQL query to display records starting
from oldest to younger female members.
Staffno Name Department Salary Dob Gender
1 Ram kumar Computer 8200 1978-02-21 Male
2 Jai singh Chemistry 7800 1990-07-14 Male
3 Savita lal Biology NULL 1992-11-08 Female
4 Geeta bai Biology 6900 2004-01-06 Female
5 Jyoti dua Physics 6800 2003-10-28 Female
6 Jatin Shankar Chemistry 9800 2002-11-23 male
.
Solution:
Source Code:
Select * from staff
Where gender = ‘female’
Order by dob;
Screenshot:
47
PRATICAL 40
Problem statement: for the given table ‘club’ Create SQL query to display first four
characters of member name who pays fees monthly.
Mcode Mname Age Fees Type
1 Anshuman 35 8100 Monthly
2 Aradhya 24 8910 Monthly
3 Sushmita 42 24100 Yearly
4 Poorvika 27 12100 quartly
.
Solution:
Source Code:
Select left(mname,4)
From club
Where type in (“monthly”);
Screenshot:
48