PRACTICALS

DATA HANDLING
PRACTICAL 1
Problem Statement: Create a Series object using the python sequence with 4
elements:
Solution:
Source Code:

Import pandas as pd
list= [3,6,9,12]
S1 = pd.Series(list)
print(S1)

Output:
PRACTICAL 2
Problem Statement: Create a Series object using ndarray that has 5 elements in the
range 10 and 50
Solution:
Source Code:
import pandas as pd
import numpy as np
S= pd.Series(np.arange (10, 50, 10))
print(S)

OUTPUT:
PRACTICAL 3
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
D1 = {‘A’ : 34, ‘B’ : 45, ‘C’ : 50, ‘D’ : 56}
S = pd.Series(D1)
Print(S)

OUTPUT:
PRACTICAL 4
Problem Statement: Create a Series object ‘Item’ that stores rate of each product as
given below:
Biscuit 10
Salt 25
Chips 20
Write code to modify rate of biscuit to 20 and chips to 30. Print the changed rate:
Solution:
Source Code:
Import pandas as pd
S2= pd.Series ([10,25,20], [‘Biscuit’, ’Salt’, ’Chips’])
Print(S2)
S2[‘Biscuit’] = 20
S2[‘Chips’] = 30
print(‘After Updating Values’)
print(S2)

OUTPUT:
PRACTICAL 5
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 and class 12 stream wise:
Solution:
Source Code:
import pandas as pd
D1 = {‘Science’:27, ’Commerce’: 35, ‘Humanities’:23}
D2 = {‘Science’: 28, ’Commerce’: 38, ‘Humanities’:25}

Class11 = pd.Series(D1)
Class12 = pd.Series(D2)
print(Class11)
print(Class12)
print(‘Total Students’)
print(Class11+Class12)
OUTPUT:
PRACTICAL 6
Problem statement: Create a series ‘temp’ that stores temperature of seven days in it.
Its index should be ‘Sun’, ‘Mon’, and so on. Write a script to:
1. Display temp of first 2 days
2. Display temp of last 2 days
3. Display all temp in reverse order
4. Display temp from Tuesday to Friday
5. Display square of all temperature
Solution : Source Code
Import pandas as pd
Temp= pd.Series([34,23,43,41,35,44,38],[‘Sun’,’Mon’,’Tue’,’Wed’,’Thu’,’Fri’,’Sat’])
Print(temp)
Print(“Temp of first two days\n”, temp.head(2))
Print(“Temp of last two days\n”, temp.tail(2))
Print(“Temp in reverse order\n”, temp[: : -1])
Print(“Temp from Tuesday to Friday\n”, temp[‘Tue’: ‘Fri’]
Print(“Square of all temp\n”, temp*temp)
OUTPUT:
PRACTICAL 7
Problem statement: Create a Series object ‘employee’ that stores salary of 4
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 = {‘Sejal’ : 43000, ‘Shyam’ : 45000, ‘Chanda’ : 47000, ‘Arjun’ : 50000}
Emp = pd.Series(D)
print(Emp)
print(“Total no of employees”, Emp.size)
if Emp.empty:
print(“Series is empty”)
else:
print(“Series is not empty”)
if Emp.hasnans:
print(“Series contains NaN elements”)
else:
print(“Series does not contain NaN elements”)
print(“Total no of Non NA elements” , Emp.count())
print(“Axis labels\n”, Emp.axes)
OUTPUT:
PRACTICAL 8
Problem Statement: Create the following dataframe ‘Sports’ containing sport wise
marks for five students. Use 2D dictionary to create dataframe.
Student Sport Marks
i. Karan Football 78
ii. Raman Cricket 80
iii. Vadini Tennis 92
iv. Ajay Kabaddi 89
v. Pihu Hockey 94
Solution:
Source Code:
import pandas as pd
D = {‘Student’: [‘Karan’, ‘Raman’, ‘Vadini’, ‘Ajay’, ‘Pihu’],
‘Sport’ : [‘Football’, ‘Cricket’, ‘Tennis’, ‘Kabaddi’, ‘Hockey’]
‘Marks’: [ 78, 80, 92, 89, 94 ]}
Stud = pd.DataFrame(D, [1,2,3,4,5])
print(Stud)
OUTPUT:
PRACTICAL 9
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':'Honda','Cost':50000}
L2 = {'Name': 'Splendor', 'Cost':60000}
L3 = {'Name': 'Sports', 'Cost':65000}
Bike = [L1,L2,L3]
DF = pd.DataFrame(Bike, [ 'Hero', 'TVS', 'Bajaj' ])
print(DF)

OUTPUT:
PRACTICAL 10
Problem Statement: Create the following dataframe ‘sales’ containing year wise sales
figure for five sales persons in INR. Use the year as column labels, and sales person
names as row labels.
2014 2015 2016 2017
Sneha 2400 4500 9000 8000
Maya 7000 4000 6000 7500
Sandeep 8000 3500 5500 6500
Manav 3000 3000 3600 8500
Write program to do the followings
1. Display row labels of ‘sales’
2. Display column labels of ‘sales’
3. Display last three rows of the ‘sales’
4. Display first three rows of the ‘sales.
Solution:
Source Code:
import pandas as pd
D=
{2014:[2400,7000,8000,3000],2015:[4500,4000,3500,3000],2016:[9000,6000,5500,360
0],2017:[8000,7500,6500,8500]}
sale = pd.DataFrame(D,['Sneha', 'Maya', 'Sandeep', 'Manav' ])
print("----DataFrame----")
print(sale)
print("----Row Labels----")
print(sale.index)
print("----Column Labels----")
print(sale.columns)
print("----Bottom two Rows----")
print(sale.tail(2))
print("----Top two Rows----")
print(sale.head(2))
OUTPUT:
PRACTICAL 11
Problem statement: Create a dataframe ‘cloth’ as given below and write program to do
followings:
 Check ‘cloth’ is empty or not
 Change ‘cloth’ such that it becomes its transpose
 Display no of rows and columns of ‘cloth’
 Count and display Non NA values for each column
 Count and display Non NA values for each row
CName Size Price
CL1 Gown S 1500
CL2 Skirt XS 500
CL3 Suit L 1800
CL4 Shirt XL 1000

Solution:
Source Code:
import pandas as pd
d1 = {'CNmae': ['Gown', 'Skirt', 'Suit', 'Shirt'], 'Size': ['S','XS','L','XL'], 'Price':
[1500,500,1800,1000]}
cloth = pd.DataFrame(d1)
print("----DataFrame----")
print(cloth)
print("----checking dataframe is empty or not----")
if cloth .empty:
print("Cloth is Empty")
else:
print("Cloth is not Empty")
print("----Transpose DataFrame----")
print(cloth.T)
print("----Total no of rows and columns----")
print(cloth.shape)
print("----No of Non NA elements in each columns----")
print(cloth.count())
print("----No of Non NA elements in each row----")
print(cloth.count(1))
OUTPUT:
PRACTICAL 12
Problem statement: Create a dataframe ‘cloth’ as given below and write
program to do followings:
o Change the name of ‘Shirt’ to ‘Pant’ and ‘Skirt’ to ‘Top’
o Increase price of all cloth by 10%
o Rename all the indexes to [CLO1, CLO2, CLO3, CLO4]
o Delete the data of CL2(CLO2) from the ‘cloth’
o Delete size from ‘cloth’
CName Size Price
CL1 Gown S 1500
CL2 Skirt XS 500
CL3 Suit L 1800
CL4 Shirt XL 1000
Solution:

Source code:
import pandas as pd
d1 = {'CNmae': ['Gown', 'Skirt', 'Suit', 'Shirt'], 'Size': ['S','XS','L','XL'], 'Price':
[1500,500,1800,1000]}
cloth = pd.DataFrame(d1)
print("----DataFrame----")
print(cloth)
print("----change name of Shirt to Pant----")
cloth.at['CL4', 'CName'] = 'Pant'
#cloth.CName['CL4'] = 'Pant'
#cloth.loc['CL4', 'CName'] = 'Pant'
print(cloth)
print("----Increase price of all cloth by 10%----")
cloth['Price'] = cloth['Price'] + cloth['Price']*10/100
print(cloth)
print("----Rename of all the indexes to [CLO1,CLO2,CLO3,CLO4]----")
cloth.rename(index = {'CL1':'CLO1','CL2':'CLO2','CL3':'CLO3','CL4':'CLO4'},
inplace = 'True')
print(cloth)
OUTPUT:
PRACTICAL 13
Problem statement: Create a dataframe ‘aid’ as given below and write
program to do followings:
1. Display the books and shoes only
2. Display toys only
3. Display quantity in MP and CG for toys and books
4. Display quantity of books in AP
Toys Books Shoes
MP 3000 2000 1000
UP 5000 6500 1500
AP 7000 7500 2000
CG 9000 8500 5000
Solution :
Source Code:
import pandas as pd
d1 = {'Toys': {'MP':3000,'UP':5000,'AP':7000,'CG':9000}, 'Books':
{'MP':2000,'UP':6500,'AP':7500,'CG':8500},'Shoes':{'MP':1000,'UP':1500,'AP':20
00,'CG':5000}}
aid = pd.DataFrame(d1)
print("----DataFrame----")
print(aid)
print("----Display the books and shoes only----")
print(aid.loc[:,['Books', 'Shoes']])
print("----Display toys only----")
print(aid['Toys'])
print("----Display quantity in MP and CG for toys and books----")
print(aid.loc [['MP','CG'],['Toys','Books']])
print("----Display quantity of books in AP----")
print(aid.at ['AP','Books'])

OUTPUT:
PRACTICAL 14
Problem statement: Create a dataframe ‘aid’ as given below and write program to
write the values of aid to a comma separated file ‘aidfigures.csv’ on the disk. Do not
write the row labels and columns labels.
Toys Books Shoes
MP 3000 2000 1000
UP 5000 6500 1500
AP 7000 7500 2000
CG 9000 8500 5000
Solution:
Source Code:

import pandas as pd
D = {'Toys':{'MP':3000,'UP':5000,'AP':7000,'CG':9000},
'Books':{'MP':2000,'UP':6500,'AP':7500,'CG':8500},
'Shoes':{'MP':1000,'UP':1500,'AP':2000,'CG':5000}}
aid = pd.DataFrame(D)
print(aid)
aid.to_csv(path_or_buf = "C:\\Users\\ak547\\Documents\\aidfigure..txt", header = False,
index = False)
PRACTICAL 15
Problem statement: Read the data in the file ‘aidfigure.csv’ 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 14.
Toys Books Shoes
MP 3000 2000 1000
UP 5000 6500 1500
AP 7000 7500 2000
CG 9000 8500 5000
Solution:
Source Code:
import pandas as pd
aidretrieved = pd.read_csv("C:\\Users\\ak547\\Documents\\aidfigure..txt", names=
['Toys', 'Books', 'Shoes'])
aidretrieved.index = ['MP', 'UP', 'AP', 'CG']
print(aidretrieved)
OUTPUT:
Practical16
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 matplotlib.pyplot as plt
medals = [200,177,163,143,187,157,199,100,98,67]
country =
['India','China','Russia','USA','Japan','Itlay','Germany','Australia','Ethopia','Argentina']
plt.bar(country, medals)
plt.title('Olympics Medal Tally')
plt.show()
OUTPUT:
PRACTICAL 17
Problem statement: Techtipnow Automobiles is authorized dealer of different Bikes
companies. They record the entire sales of bike month wise as given below:
JAN FEB MAR APR MAY JUNE
Honda 45 48 50 104 105 108
TVS 67 87 97 103 106 109
Suzuki 76 78 90 98 110 120

To get proper analysis of sales performance create multiple line chart on a

comma plot where all bikes sales data are plotted.
Display appropriate x and y axis labels, legend and chart title.
Solution:
Source code:
import matplotlib.pyplot as plt
month = ['JAN','FEB','MAR','APR','MAY','JUN']
Honda = [45,48,50,104,105,108]
TVS = [67,87,97,103,106,109]
Suzuki = [76,78,90,98,110,120]
plt.plot(month,Honda, label = 'Honda')
plt.plot(month,TVS, label = 'TVS')
plt.plot(month,Suzuki, label = 'Suzuki')
plt.title('Techtiupnow Automobiles Sales Analysis')
plt.xlabel('Month')
plt.ylabel('No of Bikes')
plt.legend(loc = 'lower center')
plt.show()
OUTPUT:
PRACTICAL 18
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.

Acc Eco B.st IP Eng

13 34 67 96 56 71
14 45 87 93 53 72
15 56 94 85 67 73
16 32 50 83 76 74

Solution:
Source Code:
import pandas as pd
import matplotlib.pyplot as plt

d = {'Acc':[34,45,56,32],'Eco':[67,87,94,50],
'B.st':[96,93,85,83],'IP':[56,53,67,76],
'Eng':[71,72,73,74]}
df = pd.DataFrame(d,[13,14,15,16])
print(df)
df.plot(kind = 'bar',title = 'Class Wise Marks Analysis',
xlabel = 'Class', ylabel = 'Marks')
df1 = df.T
df1.plot(kind = 'bar',title = 'Subject Wise Marks Analysis',
xlabel = 'Class', ylabel = 'Marks')
plt.legend(loc = 'lower center')
plt.show()
OUTPUT:
PRACTICAL 19
Problem statement: The following seat bookings are the daily records of a month December
from PVR cinemas:
134,123,125,167,154,176,198,145,198,200,216,235,287,265,289,300,304,307,318,327,352
Construct a histogram from above data with 10 bin.
Solution:
Source Code:
import pandas as pd
import matplotlib.pyplot as plt
L1 =
[134,123,125,167,154,176,198,145,198,200,216,235,287,265,289,300,304,307,318,327
,352]
plt.hist(L1)
plt.title('Booking Records @ PVR')
plt.show()
OUTPUT:
MYSQL
PRACTICALS
PRACTICAL 1
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(20),
-> marks int -> ) ;

SCREENSHOT:
PRACTICAL 2
Problem statement: In the table ‘student’ created in practical 26, insert the details of new
students.
Solution:
Source Code:
mysql> insert into student values(1, 'Sandhya', 87);
mysql> insert into student values(2, 'Chanda', 81);
mysql> insert into student values(3, 'Aryan', 64);
mysql> insert into student values(4, 'Rakesh', 82);
mysql> insert into student values(5, 'Bittu', 98);

SCREENSHOT:
PRACTICAL 3
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>=90;
SCREENSHOT:
PRACTICAL 4
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:
PRACTICAL 5
Problem statement: Delete the details of a student table created in Practical 26.
Solution:
Source Code:
delete from student;

SCREENSHOT:
PRACTICAL 6
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:
PRACTICAL 7
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_customer' from customer group by country;

SCREENSHOT:
PRACTICAL 8
Problem statement: for the given table ‘HOSPITAL’ write SQL command to display
name all patient admitted in month of October.

PID PNAME ADMITDATE DEPT FEES

AP/PT/ 001 Jaspreet 24/10/2024 ENT 500
AP/PT/ 002 Manish 25/10/2024 Cardio 350
AP/PT/ 003 Vijay 27/10/2024 Cardio 400
AP/PT/ 004 Pari 30/10/2024 Neuro 450

Solution:
Source code:
Select * from HOSPITAL where monthname(admitdate) = ‘October’;
SCREENSHOT:
PRACTICAL 9
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 Jaspreet 24/10/2024 ENT 500
AP/PT/ 002 Manish 25/10/2024 Cardio 350
AP/PT/ 003 Vijay 27/10/2024 Cardio 400
AP/PT/ 004 Pari 30/10/2024 Neuro 450

Solution:
Source Code:
Select UPPER(PNAME) as ‘Patient Name’, year(ADMITDATE) as ‘Admit
Year’ from HOSPITAL;
SCREENSHOT:
PRACTICAL 10
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 Jaspreet 24/10/2024 ENT 500
AP/PT/ 002 Manish 25/10/2024 Cardio 350
AP/PT/ 003 Vijay 27/10/2024 Cardio 400
AP/PT/ 004 Pari 30/10/2024 Neuro 450

Solution:
Source code:
Select LEFT(PNAME,3) as ‘PNAME’, length(PNAME) as ‘length’
from HOSPITAL;
SCREENSHOT:
PRACTICAL 11
Problem statement: Create a Table ‘Students’ and insert values in it.

Solution:
Source Code:
create table STUDENTS(
-> Roll int primary key,
-> Stname varchar(20),
-> Gender char(1),
-> Marks int,
-> DOB date,
-> Stream varchar(10)
-> );
insert into STUDENTS value (1,'Saurab','F',78,'1990/5/21','Commerce');
insert into STUDENTS value (2,'Geeta','F',88,'1990/5/21','Commerce');
insert into STUDENTS value (3,'Ankush','M',90,'1995/4/12','Science');
insert into STUDENTS value (4,'Kirti','F',56,'2000/2/10','Humanities');
insert into STUDENTS value (5,'Kush','M',69,'2002/1/19','Humanities');
insert into STUDENTS value (6,'Love','M',79,'2002/1/20','Humanities');
insert into STUDENTS value (7,'Gaurav','M',80,'2003/2/25','Science');
insert into STUDENTS value (8,'Reena','F',90,'2005/4/23','Commerce');
insert into STUDENTS value (9,'Suhani','F',95,'2007/10/23','Commerce');
insert into STUDENTS value (10,'Shivam','M',75,'2006/5/10','Science');
SCREENSHOT:
PRACTICAL 12
Sum() Function :
Problem 1: Display sum of all the marks in Students table.
Solution:
Source Code:
select sum(Marks) from STUDENTS;
SCREENSHOT:

Problem 2: Display sum of all marks greater than 85 in table Students.

Solution:
Source Code :
select sum(Marks) from STUDENTS where Marks>85;
SCREENSHOT:
PRACTICAL 13
Avg() Function :
Problem 1: Display Average of all marks in a table.
Source Code:
select avg(Marks) from STUDENTS;
SCREENSHOT:

Problem 2:Display average marks of all those students who are born before 1st
April, 1999.
Source Code:
select avg(Marks) from STUDENTS where DOB < ‘1999/04/01’;
SCREENSHOT:
PRACTICAL 14
Max() Function:
Problem: Display maximum marks from students table for males only.
Solution:
Source Code:
select max(Marks) from STUDENTS where Gender = ‘M’;
Screenshot:
PRACTICAL 15
Min() Function:
Problem: Display the minimum marks for science students only.
Solution:
Source Code:
select min(Marks) from STUDENTS where Stream = ‘Science’ ;
SCREENSHOT:
Practical 16
Count() Function:
Problem 1: Display total number of records from Students table.
Solution:
Source Code :
select count(*) from STUDENTS;
Screenshot:

Problem 2: Display total number of not-null marks from Students table.

Solution:
Source Code:
select count(Marks) from STUDENTS;
SCREENSHOT:
Problem 3: Display total number of streams ignoring
duplicate values from Students Table.
Solution:
Source Code:
select count(distinct Stream) from STUDENTS;
SCREENSHOT:
PRACTICAL 17
ORDER BY Function:
Problem 1: Display Roll no, Name and Marks of Students on the basis of their
Names in ascending order
Solution:
Source Code:
select Roll, Stname, Marks from STUDENTS order by Stname;
Screenshot:

Problem 2. Display Roll no, Name, Marks of all students in descending order of
their Marks and ascending order of their names.
Solution:
Source Code:
select Roll, Stname, Marks from STUDENTS order by Marks desc, Stname;
SCREENSHOT:

Problem 3: Display Roll no, Name, Marks from table Students in ascending order
using alias defined for the column Marks.
Solution: Source Code:
select Roll, Stname, Marks as ‘Marks_Obtained’ from STUDENTS order by
Marks_Obtained;
SCREENSHOT:
PRACTICAL 18
GROUP BY Clause:
Problem: Display Name, Stream, and count the total number of
students who have secured more than 90 marks according to their
stream.
Solution:
Source Code:
select Stname, Stream, count(*) as ‘Number_of_Students’ from
STUDENTS where marks>70 group by Stname, Stream;
SCREENSHOT:
PRACTICAL 19
HAVING Clause:
Problem: Display Stream, and sum of Marks of all students from table
Students according to their streams having maximum marks less than
85.
Solution:
Source Code :
select Stream, sum(Marks) as ‘Total_Marks’ from STUDENTS group by
Stream having max(Marks) <80
SCREENSHOT: