#Program 1: Write a Program to show the utility of head and tail functions of series in python.

import pandas as pd
my_series=pd.Series([1,2,3,"A String",56.65,-100], index=[1,2,3,4,5,6])
print(my_series)
print("Head")
print(my_series.head(2))
print("Tail")
print(my_series.tail(2))
my_series1=pd.Series([1,2,3,"A String",56.65,-100], index=['a','b','c','d','e','f'])
print(my_series1)
print(my_series1[2])
my_series2=pd.Series({'London':10, 'Tripoli':100,'Mumbai':150})
print (my_series2)
print("According to Condition")
print (my_series2[my_series2>10])
dic=({'rohit':[98266977,'rohit@gmail.com'], 'prakash':[9826972,'prakash@gmail.com'],
'vedant':[788990,'vedant@gmail.com']})
s=pd.Series(dic)
print (s)
print("According to First Condition")
print(s[1])
print("According to Second Condition")
l=s.size
print("No of items" ,l)
for i in range(0,l):
if s[i][0]==9826972:
print (s[i])
#Program 2: Write a Program to create series using pre-defined array/ create series using userdefined
array/list/ create series using pre-defined list/create Series using Predefined Dictionary/create series
using User-defined Dictionary/ change index in series/print head and tail elements/print according to
index position and condition in python.”””

import pandas as pd #creating series using pre-defined array

data=['a','b','c']
s=pd.Series(data)
print("THE VAUUES IN SERIES ARE \n",s)
#creating series using user-defined array/list
#creating an array
ar1=list()
n=int(input("Enter the values for an array"))
print("Enter numbers")
for i in range(0,n):
num=int(input("num:"))
ar1.append(num)
s=pd.Series(ar1)
print("THE VALUES IN NEW SERIES ARE \n",s)
#creating series using pre-defined list
list=['a','b','c']
s=pd.Series(list)
print(s)
list=[[0,1,2,3],['a','b','c'],["vedant","purnendu","rupali"]]
s=pd.Series(list)
print("\nTHE VALUES IN SERIES CREATED FROM LIST ARE\n ",s)
#creating Series using Predefined Dictionary
dic=({'rupali':[9826386977,'rupali@gmail.com'], 'purnendu':[9826911972,'purnendup@gmail.com'],
'vedant':[788990,'vedant@gmail.com']})
s=pd.Series(dic)
print ("\nTHE VALUES IN SERIES CREATED FROM PRE-DEFINED DICTIONARY ARE \n",s)
#creating series using User-defined Dictionary
key=input("Enter the Key")
value=int(input("enter the value"))
dict[key]=value
s=pd.Series(dict)
print ("\nTHE VALUES IN SERIES CREATED FROM USER-DEFINED DICTIONARY ARE \n",s)
#change index in series
s=pd.Series(data,index=[1,2,3])
print (s)
#printing head and tail elements
print("\nTHE STARTING VALUES ARE\n",s.head(2)) #displays first 2 elements
print("\nTHE LAST VALUES ARE \n",s.tail(1)) #displays last 1 elements'''
#printing according to index position and condition
print(s[1])
print("According to Condition")
print(s[s==9826386977])
#Program 3: Write a Program to enter data and show data in python using dataFrames and pandas.

import pandas as pd
data = [['Rajiv',10],['Sameer',12],['Kapil',13]]
df = pd.DataFrame(data,columns=['Name','Age'])
print ("\nTHE VALUES IN DATAFRAME ARE \n",df)
data1 = {'Name':['Rajiv', 'Sameer', 'Kapil', 'Nischay'],'Age':[28,34,29,42],
'Designation':['Accountant','Cashier','Clerk','Manager']}
df1 = pd.DataFrame(data1)
print ("THE VALUES IN SECOND DATAFRAME ARE \n",df1)
#‘’’Program 4: Write a Program to enter multiple values based data in multiple columns/rows and show
that data in python using statistical functions on dataFrames and pandas.’’’

import pandas as pd
weather_data={
'day':['01/01/2018','01/02/2018','01/03/2018','01/04/2018','01/05/2018','01/01/2018'],
'temperature':[42,41,43,42,41,40],
'windspeed':[6,7,2,4,7,2],
'event':['Sunny','Rain','Sunny','Sunny','Rain','Sunny']}
df=pd.DataFrame(weather_data)
print("\nthe values in dataframe showing weather :\n",df)
print("\nNumber of Rows and Columns")
print(df.shape)
print(df.head())
print("Tail")
print(df.tail(2))
print("\nSpecified Number of Rows\n")
print(df[2:5])
print("\nPrint Everything\n")
print(df[:])
print("\nPrint Column Names:\n")
print(df.columns)
print("\nData from Individual Column:\n")
print(df['day']) #or df.day
print(df['temperature'])
print("Maximum Temperature : ", df['temperature'].max())
#‘’’Program 5: Write a Program to enter multiple values based data in multiple columns/rows and show
that data in python using SORTING on dataFrames and pandas.’’’

import pandas as pd
weather_data={
'day':['01/01/2018','01/02/2018','01/03/2018','01/04/2018','01/05/2018','01/01/2018'],
'temperature':[42,41,43,42,41,40],
'windspeed':[6,7,2,4,7,2],
'event':['Sunny','Rain','Sunny','Sunny','Rain','Sunny']}
df=pd.DataFrame(weather_data)
print("\nthe values in dataframe showing weather :\n",df)
print("\nPrinting According to Condition:\n")
print(df[df.temperature>41])
print("\nPrinting the row with maximum temperature:\n")
print(df[df.temperature==df.temperature.max()])
print("\nPrinting specific columns with maximum temperature\n")
print(df[['day','temperature']][df.temperature==df.temperature.max()])
print("\nAccording to index:\n")
print(df.loc[3])
print("Changing of Index:\n")
df.set_index('day',inplace=True)
print(df)
print("Searching according to new index:\n")
print(df.loc['01/03/2018'])
print("Resetting the Index:\n")
df.reset_index(inplace=True)
print(df)
print("Sorting\n:")
print(df.sort_values(by=['temperature'],ascending=False))
print("Sorting on Multiple Columns:\n")
print(df.sort_values(by=['temperature','windspeed'],ascending=True))
print("Sorting on Multiple Columns one in ascending, another in descending:\n")
print(df.sort_values(by=['temperature','windspeed'],ascending=[True,False]))
print("Sum Operations on Data Frame")
print(df['temperature'].sum())
print("Group By Operations:\n")
print(df.groupby('windspeed')['temperature'].sum())
#PROGRAM 6: Write a program to show working of pivot() and pivot_table() along with statistical
functions

#import OrderedDict from pandas

#import DataFrame
import pandas as pd
import numpy as np
table={"item":['tv','tv','ac','ac'],
'company':['lg','videocon','lg','sony'],
'rupees':[12000,10000,15000,14000],
'usd':[700,650,800,750]}
d=pd.DataFrame(table)
print("DATA OF DATAFRAME")
print(d)
p=d.pivot(index='item',columns='company',values='rupees')
print("\n\nDATA AFTER PIVOT :")
print(p)
print(p[p.index=='tv'].lg.values)
p1=d.pivot_table(index='company',values='rupees',aggfunc='sum')
print("\n\nDATA IN PIVOT TABLE WITH SUM:\n")
print(p1)
p2=d.pivot_table(index=['item','company'],values=['usd'],aggfunc='mean')
print("\n\nDATA IN PIVOT TABLE WITH MEAN:\n")
print(p2)
#PROGRAM 7: Write a program to show result of quantile and sorting of values on two different
dataframes

import pandas as pd
import numpy as np
df=pd.DataFrame(np.array([[1,1],[2,10],[3,100],[4,1000]]),columns=['a','b'])
print("the elements of dataframe1 are \n",df)
print("\nthe elements of dataframe1 with quantile are \n",df)
print(df.quantile(0.5))

unsorted_df=pd.DataFrame(np.random.randn(10,2),index=[1,4,6,2,3,5,9,8,0,7],columns=['col2','col1'])
sorted_df=unsorted_df.sort_index(ascending=False)
print("\nthe elements of dataframe1 after sorting are \n",sorted_df)

#Program 8: Write a program to plot a line chart with title ,xlabel , ylabel and line style.
import matplotlib.pyplot as p
a=[1,2,3,4]
b=[2,4,6,8]
p.plot(a,b)
p.xlabel("values")
p.ylabel("doubled values")
p.title("LINE CHART")
p.plot(a,b,ls="dashed",linewidth=4,color="r")
p.show()

#Program 9: Write a program to plot bar chart having Cities along with their Population on xaxis and
yaxis.
import matplotlib.pyplot as p
a=["delhi","mumbai","kolkata","chennai"]
b=[423517,34200,63157,99282]
p.xlabel("cities")
p.ylabel("polpulation")
p.title("BAR CHART")
p.bar(a,b,color=["red","green"])
p.show()

#PROGRAM 10: Write a program to plot bar chart having data x,y on xaxis and yaxis, showing in
different color with different width.
import numpy as np
import matplotlib.pyplot as p1
x=[1,2,3,4,5]
y=[6,7,8,9,10]
p1.xlabel("DATA FROM A")
p1.ylabel("DATA FROM B")
p1.title("DATA ALL")
p1.bar(x,y,width=[0.3,0.2,0.4,0.1,0.5],color=["red","black","b","g","y",])
p1.show()

#PROGRAM 11: Write a program to plot SCATTER CHART having data x,y on xaxis and yaxis, with
marker and color attribute.
import matplotlib.pyplot as p1
x=[1,2,3,4,5,6,7,8,9,10]
y=[13,7,2,11,0,17,1,11,22,14]
print(x)
print(y)
p1.xlabel("Overs")
p1.ylabel("Score")
p1.title("IPL 2019")
p1.scatter(x,y,marker='x', color='y')
p1.show()

#Program 12: Write a program to create boxplot having an ndarray with 10 values.
import numpy as np
import matplotlib.pyplot as p1
ary=[5,20,30,45,60,80,100,140,150,200,240]
p1.boxplot(ary)
p1.show()

#Program 13: Write a program to plot HISTOGRAM from an ndarray with 14 bins(intervals)
import numpy as np
import matplotlib.pyplot as p1
ary=[5,20,30,45,60,80,100,140,150,200,240,260,275,290]
p1.hist(ary,bins=14)
p1.show()

#Program 14: Write a program to plot CUMULATIVE HISTOGRAM of BARSTACKED type from two
ndarrays with 14 bins(intervals)
import numpy as np
import matplotlib.pyplot as p1
ary1=[5,20,30,45,60,80,100,140,150,200,240,260,275,290]
ary2=[10,25,35,50,70,90,110,130,150,220,25,280,295,310]
p1.hist([ary1,ary2],histtype='barstacked', cumulative=True)
p1.show()

#PROGRAM 15: Write a program to plot a MULTI-LINE chart with title , xlabel , ylabel and line style.
import numpy as np
import matplotlib.pyplot as plt
data=[[5.,25.,45.,20.],[8.,13.,29.,27.,],[9.,29.,27.,39.]]
x=np.arange(4)
plt.plot(x,data[0],color='b',label='range1',linestyle=”dashed”)
plt.plot(x,data[1],color='g',label='range2',linestyle=”solid”)
plt.plot(x,data[2],color='r',label='range3',linestyle=”dotted”)
plt.legend(loc='upper left')
plt.title("multiRange line chart")
plt.xlabel('x')
plt.ylabel('y')
plt.show()

#PROGRAM 16:Write a program to write dictionary data in CSV file

import csv
with open('example5.txt', 'w') as csvfile:
fieldnames = ['first_name', 'last_name', 'Grade']
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
writer.writerows([{'Grade': 'B', 'first_name': 'Alex', 'last_name': 'Brian'},
{'Grade': 'A', 'first_name': 'Rachael',
'last_name': 'Rodriguez'},
{'Grade': 'C', 'first_name': 'Tom', 'last_name': 'smith'},
{'Grade': 'B', 'first_name': 'Jane', 'last_name': 'Oscar'},
{'Grade': 'A', 'first_name': 'Kennzy', 'last_name': 'Tim'}])
print("writing complete")

#Program 17: Write a Program to read CSV file and show its data in python using dataFrames and
pandas.’’’

import pandas as pd
df=pd.read_csv("student.csv", nrows=3)
print("\nTo display selected number of rows from beginning")
print(df)
df=pd.read_csv("student.csv")
print(df)
print("\nNumber of Rows and Columns : ",df.shape)
print("\nHead-Records from starting : ")
print(df.head(2))
print("\nTail-records from bottom :")
print(df.tail(2))
print("\nSpecified Number of Rows")
print(df[2:5])
print("\nPrint Everything")
print(df[:])
print("\nPrint Column Names")
print(df.columns)
print("\nData from Individual Column")
print(df.Name) #or df.Name
print(df['Marks'])
print("Maximum Marks : ", df['Marks'].max())
print("Printing According to Condition")
print(df[df.Marks>70])
print("Printing the row with maximum temperature")
print(df[df.Marks==df.Marks.max()])
print("Printing specific columns with maximum Marks")
print(df[['Name','Marks']][df.Marks==df.Marks.max()])
print("According to index")
print(df.loc[3])
print("Changing of Index")
df.set_index('Scno',inplace=True)
print(df)
#print("Searching according to new index")
#print(df.loc[4])
print("Resetting the Index")
df.reset_index(inplace=True)
print(df)
print("Sorting")
print(df.sort_values(by=['Marks'],ascending=False))
print("Sorting on Multiple Columns")
print(df.sort_values(by=['Class','Section'],ascending=True))
print("Sorting on Multiple Columns one in ascending, another in descending")
print(df.sort_values(by=['Marks','Name'],ascending=[False,True]))
print("Sum Operations on Data Frame")
print(df['Marks'].sum())
print("Group By Operations")
print(df.groupby('Class')['Marks'].sum())
#‘’’Program 18: Write a Program to enter values in python using dataFrames and show these
values/rows in 4 different excel files .’’’
import pandas as pd
data = [['Rajiv',10],['Sameer',12],['Kapil',13]]
df = pd.DataFrame(data,columns=['Name','Age'])
print ("THE VALUES IN DATAFRAME ARE \n",df)
df.to_csv('new.csv')
df.to_csv('new1.csv', index=False)
df.to_csv('new2.csv', columns=['Name'])
df.to_csv('new4.csv', header=False)

#PRACTICAL NO-19: Problem Definition : Write a python program to generate line graph with suitable title and
labels. Where x is the year of performance with values 2014, 2015, 2016, 2017,2018 and 2019. And y axis
shows the profit of a particular company in Rs.(Millions).

CODING:

OUTPUT:
#PRACTICAL NO-20: • Problem Definition : Given the following data of rainfall in different zones of
India in mm for 12 months.
Create multiple line charts in a Figure to observe any trends from Jan to Dec.

CODING:

OUTPUT:

PRACTICAL NO-21
 Problem Definition: Consider the data given below in the table, Create a bar chart
depicting the downloads of the app.

CODING:

OUTPUT:

PRACTICAL NO-22
PROBLEM DEFINTION: Given the following data of rainfall in north and south zones of India in mm for

12 months.
Create multiple bar chart in a figure to compare rainfall in north and south zones
CODING:

OUTPUT:

PRACTICAL NO-23
 • Problem Definition : Given the
ages of 50 participants in some
game. Write a program to plot
a histogram from given data
with 10 bins.
Data = [
10,11,13,13,15,16,17,18,19,21,23,23,23,24,
24,25,25,25,25,25,26,26,27,27,27, 27,
29,30,30,30,30,31,33,34,34,35,36,36,37,37,
37, 38,39,40,40,40,41,42,43,43]
Also plot a cumulative frequency
histogram.

CODING:
 OR

OUTPUT:

PRACTICAL NO-24
 a. Problem Definition :
Given below are the sugar
levels for men and women
in a city. Compare the
sugar levels amongst them.
men =
[113,85,90,150,149,88,93,115,135,80,77,8
2,129]
women=[67,98,89,120,133,150,84,69,89,79,
120,112,100]

CODING:
 OR

OUTPUT:

2
 Section-B
PYTHON PANDAS

PRACTICAL NO-25
 • Problem Definition : Create a
Series from a Dictionary and
another series using n-d array. (
Create the dictionary with
month name as key and no. of
days as value and create a 1-d
array of n integers.)
CODING:
OUTPUT:
 PRACTICAL NO-26

Problem Definition : Create two Series, one from array and another from List object, one with default index
and another with user defined index, one int data dtype and another string data type. Now print the following
attributes in the given format.
Attribute Name Series1 Series2
Data type :
Shape :
No. of bytes
No. of Dimensions
Has NaNs ?
Empty?
Index
values

CODING:
OUTPUT:

PRACTICAL NO-27
 • Problem Definition : Write a
python code to create a Series object
Temp1 that stores temperature of seven
days in it, Take any random 7 values of
temperatures and print this Series. Now
define indexes as 'Sunday',
'Monday'.......... Up to 'Saturday' and
print the Series again. Now print all
attributes of the series as well as average
temperature of 7 days.
CODING:

OUTPUT:
 PRACTICAL NO-28

Problem Definition : Define two series

named Population Storing the population of 4
metro cities of India and AvgIncome stores the
total average income reported in previous year in
each of these 4 cities. Calculate income per capita
for each of these metro cities. Print it in the
following tabular form.
(Take real data from the net)
CODING:
OUTPUT:

PRACTICAL NO-29
 Problem Definition : Create a
Series that stores the area of all
2
states of India in km . Create this
series through user's input. Now
find out the biggest and the
smallest area of 3 states. Also find
out the area and states having
2
area more than 5000 km .
CODING:

INPUT:
OUTPUT:
 PRACTICAL NO-30
PROBLEM DEFINITION:
 “Create a Series from an array of 25
random numbers between 10 to 60. Print
the series. Now print as per the following
queries:
a) Print 75 percentile of this series.
b) Print all values above 75 percentile.
c) Print second element.
d) Print 5th element onwards
e) Print all elements in reverse order i.e.
CODING:
last to first
f) Print 10th to 20th element
g) Print 5th to last element jumping 2
elements.
h) Print 20th to last element by adding 5
in each elements.
i) Print twice of each elements.
j) Print bottom 5 elements by slicing. (
without tail() function)
k) Print element number 3,7,10,15,19,24.
.”
OUTPUT:
PRACTICAL NO-31
PROBLEM DEFINITION:

Three Series objects store the

marks of 10 students in three
terms. Roll number of students
form the index of these series
objects. The three Series
objects have the same indices.
Calculate the total weighted
CODING:

marks obtained by students as

per following formula.
Final marks = 25% of Term1 +
25% of Term2 + 50% of Term3
Store the Final marks of
students in another Series
object.

INPUT:
OUTPUT:

PRACTICAL NO-32
PROBLEM DEFINITION:
 Write a Program to store the sales of 5 fast moving items of
a store for each month in 12 Series objects, i.e. S_Jan Series
object store sale of these 5 items in January month, S_Feb
Series object store sale of these 5 items in February month,
and so on.
The program should display the summary sales report like
this-
a) Total yearly sales, item-wise: ( should display sum of
items' sales over the months)
b) Maximum sales of the item made: < Name of the item
that was sold the maximum in whole year>.
c) Maximum sales for individual items
d) Maximum Sales of item1 made
CODING:
Maximum Sales of item2 made :
Maximum Sales of item3 made
Maximum Sales of item4 made :
Maximum Sales of item5 made :
e) Display those items whose sales was more than 50000 in
the month January.
f) Display those items whose yearly sales is between 500000
to 900000.
g) Display those items whose total yearly sale is greater
than it average sales.
h) Print the total sale of a month having highest sales overall
for all items.
OUTPUT:
PRACTICAL NO-33
PROBLEM DEFINITION:

Write a Python program to create a

DataFrame as given below, and display all
its attributes ( index, columns, shape,
size, axes, rows and columns.

CODING:

OUTPUT:

PRACTICAL NO-34
PROBLEM DEFINITION:

Write a Python Program to

Create a Data Frame
Cricketdf with columns
Playername, age,
TotalScore, Century, and
Fifties for ten Players.
CODING:

(Add player_id from 101 to

110 in the dataframe
during creation)
a) Now using head() and tail()
function, display top 5 rows,
bottom 5 rows, top 3 rows, last
6 rows.
b) Display detail of player_id
101(using .loc)
c) Display rows of player_id
103,104,107 using .loc.
OUTPUT:

d) Display rows of player_id

103,105,107,109 using .iloc.
e) Using slicing display row no.
PRACTICAL NO-35
PROBLEM DEFINITION:

Write a Python Program to create

the following DataFrame namely
aid_df that stores the aid by NGOs
for different states.
Now display the following:

CODING:

OUTPUT:
PRACTICAL NO-36
PROBLEM DEFINITION :

Create the following Data

Frame showing quarterly
sale year-wise, now write

CODING :

OUTPUT:
PRACTICAL NO-37
PROBLEM DEFINITION :
 Write a Python
Program to
create two
CODING:

dataframes and
perform 4 basic
binary operations
i.e. add, subtract,
multiply and
divide. (
Make it menu
driven).
OUTPUT:
PRACTICAL NO-38
PROBLEM DEFINITION :

Create a data frame as per

given row and columns in
it. Display it. Now perform
the following by writing

CODING :

OUTPUT:
PRACTICAL NO-39
PROBLEM DEFINITION :
 Consider the data frame as created
below:
import pandas as pd
dict1={'empcode':['E101','E102','E102
','E103','E104'],
'Ename':['Sam','Kain','Scott','Joy','To
m'],
'Job':['Clerk','Manager','Clerk','Analys
CODING:
t','Manager'],
'Salary':[20000,50000,18000,55000,6
5000],
'bonus':[1000,2500,1000,7000,5000],
'Zone':['West','East','East','North','We
st‘]}
empdf=pd.DataFrame(dict1,index=[1
0,20,30,40,50])
print(empdf)
Now Write Command and output to
perform the following queries in
Pandas.
a) To display those employees whose
bonus is >= 5000
b) To display all those records whose
OUTPUT:
Salary lies in the range of 50000 to
70000.
c) To display empcode, ename and
salary who are 'Manager'.
 Section-C
MySQL- SQL Commands

SQL Commands
Q.1- Create a database 'School' on the database server, show the list of databases and open it.
Q 2- Create the following table named 'STUDENT' with appropriate data type, size and constraint(s) if any.

Q.3- insert the following records into the table.

Q.4- Display all the information of males whose city is NEITHER Delhi or Mumbai.
Q.5- Display the details of all the students whose date of birth is after Nishant’s Birth date 1995-12-06.
(consider the Sql’s standard date format)

Q.6- Display all information about class XII students rank wise.

Q.7- Display all columns arranged in descending order of city and within the city in the ascending order their
marks.

Q.8- List names of all students whose name has the character ‘a’.
Q.9- Display Name and Marks of those students whose marks is in the range 400 and 500 ( both are inclusive)

Q.10- Display average marks, highest marks and total no. of students for each class

Q.11- Display total no of males and females separately, along with their average marks.

Q.12- Increase the marks of 10th class students by 5% marks.

Q.13- Add a new column named Address. ( Take the data type and size yourself)

Q.14- Add Primary key to the column Name.

Q.15- Display name, class and city of 'XII' class students who lives in 'Mumbai'.
Q.16- Display unique sex names.

Q.17- Display name, data of birth and day name (like Sunday, Monday,.), of all females, whose marks is greater
than or equal to 400.

Q.18- Display City and highest marks of students of each city, but only for Delhi and Mumbai.

Q.19- Display round of average marks up to zero places, truncate of average marks up to 1 decimal place for
class 'X' students only.
Q.20- Write a query to display name and the position of character 'a' in the names of students having 'a'
anywhere in their names.

Q.21- Display name, character number 3rd and 4th of each name and counting of number of characters in each
name.

Q.22- Display current date and current time together.

Q.23- Display name, marks and round up the Square root of marks up to 2 decimal places