PYTHON
PROGRAMS
1
Output
Jan 31
Feb 28
Mar 31
Apr 30
dtype: int64
0 3.0
1 6.5
2 10.0
dtype: float64
2
Date: 03-04-2023
1. Create a panda’s series from a dictionary of value and a ndarray
Program
import pandas as pd
import numpy as np
# Creating a Series using dictionary
obj=pd.Series({'Jan':31,'Feb':28,'Mar': 31,'Apr':30})
print(obj)
# Creating a Series using ndarray
nda=np.arange(3,13,3.5)
ser1=pd.Series(nda)
print(ser1)
3
Output
Employees Salary Before updating
John 60000
Bala 60000
Amu 55000
Birundha 52000
dtype: int64
Do you want to update the salary of the employee y/n:y
Enter the name of the employee: Bala
Enter the new salary:3000
Salary updated successfully
Employee Salary after updating
John 60000
Bala 3000
Amu 55000
Birundha 52000
dtype: int6
4
Date: 03-04-2023
2. Write a Python program to create a Series object with Employee
names as the index and their salaries as values. Accept the name of
the employee whose salary needs to be changed, along with the
new salary, and update it in the Series.
Program
import pandas as pd
D= {'John': 60000,'Bala': 60000, 'Amu': 55000,'Birundha': 52000}
S= pd. Series(D)
print('Employees Salary Before updating')
print(S)
print("\n")
opt=input("Do you want to update the salary of the employee y/n:")
if opt=='y':
Name= input ("Enter the name of the employee: ")
if Name in S:
new_salary = float(input("Enter the new salary:"))
S[Name]=new_salary
print("Salary updated successfully")
print("Employee Salary after updating")
print(S)
else:
print("Employee not found")
else:
print("Thank you")
5
Output
The index of the Series is: Index(['a', 'b', 'c', 'd', 'e'], dtype='object')
The data type of the Series is: int64
The size of the Series is: 5
The shape of the Series is: (5,)
The NaN of the Series is: False
6
Date: 16-04-2023
3. To write a Python program to create a Series using list and display the
following attributes of the Series: (i) index (ii) dtype (iii) size (iv) shape (v)
hasnans
Program
import pandas as pd
l= [10, 45, 67, 3, 43]
s=pd. Series (l, index= ['a', 'b', 'c', 'd', 'e'])
print ("The index of the Series is:" ,s.index)
print ("The data type of the Series is:" ,s.dtype)
print ("The size of the Series is:",s.size)
print ("The shape of the Series is:",s. shape)
print ("The NaN of the Series is:", s.hasnans)
7
Output
0 1 2
Row1 25 45 60
Row2 34 67 89
Row3 88 90 56
8
Date: 25-04-2023
4. Write a program to create a dataframe from a 2D list. Specify own
index labels.
Program
import pandas as pd
list2=[[25,45,60],[34,67,89],[88,90,56]]
df2=pd.DataFrame(list2,index=['Row1','Row2','Row3'])
print(df2)
9
Output
10
Date: 25-04-2023
5. Create a data frame for examination result and display row labels,
column labels data types of each column and the dimensions.
Program
import pandas as pd
result={'English':[67,78,75,88,92],
'Economics':[78,67,89,90,56],'IP':[78,88,98,90,87],'Accounts':[77,7
0,80,67,86]}
print("Dataframe for student")
print()
df=pd.DataFrame(result,index=['Rinku','Ritu','Ajay','Pankaj','Adity
a'])
print(df)
print("Index labels")
print(df.index)
print("Column labels")
print(df.columns)
print("Datatypes of columns")
print(df.dtypes)
print("Dimension of dataframe")
print(df.ndim)
11
Output
12
Date: 02-05-2023
6. Consider the saledf shown below
Write a program to rename indexes of ‘ZoneC’ and ‘ZoneD’ as
‘Central’ and ‘Dakshin’ respectively and the column names
‘Target’ and ‘Sales’ as ‘Targeted’ and ‘Achieved’ respectively
Program
import pandas as pd
dict={'Target':[56000,70000,75000,60000],'Sales':[58000,68000,7
8000,61000] }
df1=pd.DataFrame(dict,index=['zoneA','zoneB','zoneC','zoneD'])
print(df1)
print(df1.rename(index={'zoneC':'Central','zoneD':'Dakshin'},\
columns={'Target':'Targeted','Sales':'Achieved'}))
13
Output
14
Date: 02-05-2023
7. Write python codes to create the dataframe given below and write
the statements for the following operations.
a) Delete the marks of S2 and S4
b) To remove the columns M1 and M3.
Program
import pandas as pd
df=pd.DataFrame({'M1':[100,95,89,82,56,78],'M2':[100,100,100,85,4
4,83],
'M3':[60,57,53,49,76,93],'M4':[88,99,87,65,86,79]},index=['S1','S2','S
3','S4','S5','S6'])
print(df)
print(df.drop(['S2','S4'],axis=0))
print(df.drop(['M1','M3'],axis=1))
15
Output
16
Date: 24-05-2023
8. Create a dataframe based on the data given below.
Write code statement to:
a) List only the columns Count and Price using loc.
b) List all the rows with the label “Apple” using loc. Extract all
columns
Program
import pandas as pd
df={'Color':['Red','Green','Red','Green','Green'],'Count':[3,9,25,26,9
9],'Price':[120,110,125,150,70]}
fruit=pd.DataFrame(df,index=['Apple','Apple','Pear','Pear','Lime'])
print(fruit)
print(fruit.loc[:,['Count','Price']])
print(fruit.loc['Apple',:])
17
Output
18
Date: 25-05-2023
9. Create a dataframe based on the data given below.
Write code statement to:
c) List only Ist and 3rd columns using iloc
d) List only the rows 1,3,4 using iloc.
Program
import pandas as pd
df={'Color':['Red','Green','Red','Green','Green'],'Count':[3,9,25,26,99],'Pr
ice':[120,110,125,150,70]}
fruit=pd.DataFrame(df,index=['Apple','Apple','Pear','Pear','Lime'])
print(fruit)
print(fruit.iloc[:,[0,2]])
print(fruit.iloc[[1,3,4],:])
19
Output
20
Date: 28-05-2023
10. Write the code in pandas to create the following dataframes
df1 and df2 and to do the following operations on them:
df1 df2
Score1 Score2 Score1 Score2
0 30 20 0 10 15
1 40 45 1 20 25
2 15 30 2 20 30
3 40 70 3 50 30
Write the commands
i) To add dataframes df1 and df2.
ii) To subtract df2 from df1
Program
import numpy as np
import pandas as pd
df1 = pd.DataFrame({'Score1':[30,40,15,40],
'Score2':[20,45,30,70]});
df2 = pd.DataFrame({'Score1':[10,20,20,50],
'Score2':[15,25,30,30]});
print("df1")
print(df1)
print("df2")
print(df2)
print("Sum of df1 and df2")
print(df1.add(df2))
print("Subtract df2 from df1")
print(df1.subtract(df2))
21
Output
22
Date: 28-05-2023
11. Create a Pandas Series as shown below and retrieve the data
using some conditions.
Program
import pandas as pd
ss=pd.Series([20,30,10,50,40],index=['a','b','c','d','e'])
print(ss)
print(ss<30)
print(ss[ss<30])
print(ss[ss==50])
23
Output
24
Date: 04-06-2023
12. Write a program in python to create a series Ser1 of numeric
data with labels as a,b,c,d,e. Then create a new series object
Ser2, contains squares of each values from Ser1. Display Ser2
values which are greater than 15.
Program
import pandas as pd
Ser1 = pd.Series([2,3,5,4,1], index=['a','b','c','d','e'])
#Printing of Original Series data
print("Series Ser1 data")
print(Ser1)
Ser2= Ser1**2
print(Ser2[Ser2>15])
25
Output
26
Date: 06-06-2023
13. Write a Python program to create a panda’s DataFrame called DF for
the following table using Dictionary of List and display the details of
students whose Percentage is more than 85.
Program
import pandas as pd
d={'stu_name':['Anu','Arun','Bala','Charan','Mano'],'Degree':['MBA','MC
A','M.E','M.Sc','MCA'],'Percentage':[90,85,91,76,84]}
df=pd.DataFrame(d,index=['s1','s2','s3','s4','s5'])
print(df)
print(df.loc[df["Percentage"]>85])
27
Output
28
Date: 06-08-2023
14. Write a program to read from a CSV file Employee.csv and create a
dataframe from it.
Program
import pandas as pd
df=pd.read_csv("C:\\Users\\HP\\Documents\\employee.csv")
print(df)
29
Output
30
Date: 08-08-2023
15. Consider the dataframe sportdf as shown below. Write a program to
export the contents of this dataframe to a CSV file.
Program
import pandas as pd
import numpy as np
table={'Name':['Vishal','Anil','Mayur','Viraj','Mahesh'],
'Age':[15,16,15,17,16],
'Weight':[51,48,49,51,48],
'Height':[5.1,5.2,5.1,5.3,5.1],
'Runscored':[55,25,71,53,51]}
sportdf=pd.DataFrame(table)
sportdf.to_csv("C:\\Users\\hp\\documents\\sport.csv")
31
Output
32
Date: 28-08-2023
16. Write a program to plot two lines along with proper titles and
legends.
Program
import matplotlib.pyplot as plt
x=[1,2,3]
y=[5,7,4]
plt.plot(x,y,label="First line")
x1=[1,2,3]
y1=[10,11,14]
plt.plot(x1,y1,label="Second line")
plt.xlabel("x values")
plt.ylabel("y values")
plt.title("Multiple Line Graph")
plt.legend()
plt.show()
33
Output
34
Date: 30-08-2023
17. Given the school result data, analyses the performance of the
students on different parameters, e.g subject wise.
Program
import matplotlib.pyplot as plt
sub=['Physics','Chemistry','Biology','IP','Mathematics']
per=[85,70,60,90,80]
plt.bar(sub,per)
plt.title("Result Analysis")
plt.xlabel("Subject Name")
plt.ylabel("Percentage")
plt.show()
35
Output
36
Date: 30-08-2023
18. Create a horizontal histogram of random values with bins as 12.
Program
import numpy as np
import matplotlib.pyplot as plt
x = np.random.randn(1000)
plt.hist(x, bins=12,orientation='horizontal')
plt.show()
37
Output
38
Date: 31-08-2023
19. Create a bar chart giving the details of result analysis of 3 subjects
with title and legend.
Program
import numpy as np
import matplotlib.pyplot as pl
r=['First','Second','Thrid']
ACCT=[60,50,45]
BS=[85,80,75]
IP=[98,92,70]
x=np.arange(len(r))
pl.bar(x,ACCT,color='b',width=0.25,label='ACCT')
pl.bar(x+0.25,BS,color='g',width=0.25,label='BS')
pl.bar(x+0.50,IP,color='r',width=0.25,label='IP')
pl.xticks(x,r)
pl.legend (loc="upper left")
pl.title("Result Analysis")
pl.xlabel('Rank')
pl.ylabel('Percentage')
pl.show()
39
40
SQL PROGRAMS
41
42
Date: 07-09-2023
1. Create a student table with the student id, name, and marks as
attributes where the student id is the primary key.
SQL command
create table student (studentid int(3) primary key,
name varchar(20) not null, marks decimal(5,2));
43
44
Date: 07-09-2023
2. Insert the details of 5 new students in the above table.
SQL Command
INSERT INTO student VALUES(101,”Krishna”,498);
INSERT INTO student VALUES(102,”Mariyam”,490);
INSERT INTO student VALUES(103,”Joseph”,495);
INSERT INTO student VALUES(104,”Radha”,480);
INSERT INTO student VALUES(105,”Fatima”,485);
45
46
Date: 14-09-2023
3. Delete the details of the student Radha from the student table.
SQL Command
DELETE FROM student WHERE name=”Radha”;
47
48
Date: 14-09-2023
4. Consider the following Students table.
Table : Student
Write SQL query to use the select command to get the details of the
students with marks more than 490
SQL Command
SELECT * FROM student WHERE marks>490;
49
50
Date: 14-09-2023
5. Consider the following Students table.
Table : Students
Write SQL query to find the sum of the marks of the students whose
name ends with a.
Write SQL query to delete the column marks.
SQL Command
SELECT SUM(marks) FROM student WHERE name LIKE “%a”;
ALTER TABLE student DROP COLUMN marks;
51
52
Date: 21-09-2023
6. Consider the following Student table.
Table : Students
Write SQL query to find the min, max, sum, and average of the marks in
a student marks table.
SQL Command
SELECT MIN(marks),MAX(marks),SUM(marks),AVG(marks) FROM
student;
53
54
Date: 21-09-2023
7. Consider the following Student table.
Table : Students
Write SQL query to order the (student ID, marks) table in descending
order of the marks.
SQL Command
SELECT studentid, marks FROM student ORDER BY marks DESC;
55
56
Date: 05-10-2023
8. Consider the following Student table.
Table : Student
Write a query to display the names in capital letter.
SQL Command
SELECT UCASE(name) AS Name FROM student;
57
58
Date: 05-10-2023
9. Find the total number of customers from each country in the table
Customer given below (customer ID, customer Name, country) using
group by.
cust_id cust_name country
1000 Pooja Oman
1001 Sonia India
1002 Vijay UAE
1003 Shourya India
1004 John India
1005 Samir UAE
SQL Command to Create table customer and insert records into it
CREATE TABLE customer(cust_id int(5) PRIMARY KEY, cust_name
varchar(25) NOT NULL, country varchar(25));
INSERT INTO customer VALUES(1000,”Pooja”,”Oman”);
INSERT INTO customer VALUES(1001,”Sonia”,”India”);
INSERT INTO customer VALUES(1002,”Vijay”,”UAE”);
INSERT INTO customer VALUES(1003,”Shourya”,”India”);
INSERT INTO customer VALUES(1004,”John”,”India”);
INSERT INTO customer VALUES(1005,”Samir”,”UAE”);
SQL command to find the total number of customers from each country.
SELECT country,COUNT(*) FROM customer GROUP BY country;
59
60
Date: 12-10-2023
10.Consider the following Lecturer table.
Table : LECTURER
FCODE FNAME GENDER ROOM SUBJECT JOINDATE
F100 Krishna Male 100 Multimedia 2000-10-11
F101 Mary Female 101 Web Design 2003-11-30
F102 Ali Khan Male 102 Robotics 2001-09-06
F103 Radhika Female 103 Web Design 2004-12-15
F104 Fatima Female 104 Robotics 2006-12-31
F105 John Male 105 AI 2005-05-02
Write SQL query to display the joining date of the senior most lecturer.
SQL Command to Create table Lecturer and insert records into it
CREATE TABLE lecturer(Fcode char(5) PRIMARY KEY, Fname
VARCHAR(25) NOT NULL, gender VARCHAR(10),room INT(5),subject
VARCHAR(25),joindate DATE);
INSERT INTO lecturer VALUES("F100","Krishna","Male",100,"Multimedia",
'2000-10-11');
INSERT INTO lecturer VALUES("F101","Mary","Female",101,"Web Design",
'2003-11-30');
INSERT INTO lecturer VALUES(“F102”,”Ali Khan”,”Male”,102,”Robotics”,
’2001-09-06’);
INSERT INTO lecturer VALUES("F103","Radhika","Female",103,'Web Design'
,'2004-12-15');
INSERT INTO lecturer VALUES("F104","Fatima","Female",104,'Robotics',
'2006-12-31');
INSERT INTO lecturer VALUES(“F105”,”John”,”Male”,105,’AI’,’2005-05-02’);
61
62
SQL query to display the joining date of the senior most lecturer
SELECT MIN(joindate) AS Senior Lecturer FROM lecturer;
63
64
Date: 12-10-2023
11. Consider the following Lecturer table.
Table : Lecturer
Write SQL query to count and display the number of MALE lecturers
who have joined after 2001-01-01.
SQL Command
SELECT gender, COUNT(*) FROM lecturer WHERE gender=”Male”
and joindate>‘2001-01-01’;
65
66
Date: 09-11-2023
12. Consider the following Lecturer table.
Table : Lecturer
Write SQL query to display names of the lecturers whose names are four
characters long.
SQL Command
SELECT Fname FROM lecturer WHERE LENGTH(Fname) =4;
67
68
Date: 09-11-2023
13. Consider the following Lecturer table.
Table : Lecturer
Write SQL query to display name and subject of lecturers who joined
between 01-01-2003 and 01-01-2005.
SQL Query
SELECT Fname,Subject FROM lecturer WHERE joindate between
'2003-01-01' and '2005-01-01';
69
70
Date: 09-11-2023
14. Consider the following Lecturer table.
Table : Lecturer
Write SQL query to display all unique subject in table lecturer.
SQL Command
SELECT DISTINCT subject FROM lecturer;
71
72
Date: 09-11-2023
15. Consider the following Lecturer table.
Table : Lecturer
Write a query to display the number of lecturers in each subject.
SELECT subject, COUNT(*) FROM lecturer GROUP BY subject;
73
74
Date: 20-11-2023
16. Consider the following tables: Employee and Dept
Table : Employee
Table: Dept
Write query to join two tables employee and dept on the basis of field
dno.
SQL Command
mysql> select * from employee,dept where employee.dno=dept.dno;
75
76
