SERIES

IN
PYTHON
#Program4: Write a Program to show the utility of List and series in python.

import pandas as pd
data =['a','b','c','d']
s = pd.Series(data)

print("The data in series form is\n", s)

s1 = pd.Series(data,index=[100,101,102,103])

print("the data in series form with index value is \n" ,s1)

#Program5: 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])
#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])
DATAFRAME
IN
PYTHON
#Program7: 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)
#Program8: 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/2020','01/02/2020','01/03/2020','01/04/2020','01/05/2020','01/01/2020'],
'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())
#Program9: 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/2020','01/02/2020','01/03/2020','01/04/2020','01/05/2020','01/01/2020'],
'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())
MATPLOTLIB
#Program10: 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()
#Program11: 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 12: 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 13: 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()
#PROGRAM14: 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()
 #PROGRAM15: Write a python program to plot a bar graph to show the number of
gold medals earned by all houses. Do add heading for chart x and y axis. Add colors of
your choice for the bars and the headings. Make the font size as14 for the headings.

import pandas as pd
import matplotlib.pyplot as plt
data={'House':['Namrata','Anand','Nishtha','Satya'],
'Gold':[10,8,12,5],
'Silver':[5,10,9,3],
'Bronze':[4,15,2,10]}
house=pd.DataFrame(data)
print(house)
x=Sports.House
y=Sports.Gold
plt.bar(x,y, color=[‘maroon’,‘green’’,’yellow’,’red’])
plt.title(“ Gold Medal tally of all Houses “)
plt.xlabel(“Houses“,fontsize=”14”)
plt.ylabel(“Gold medals Tally “,fontsize=”14”)
plt.show()
#PROGRAM 16: Write a python program to plot a line graph to show the number of gold medals
earned by all houses. Do add heading for chart x and y axis. Add colors of your choice for the bars
and the headings. Make the font size as14 for the headings.
CSV
FILES
#PROGRAM 22: Create a csv file from a Dataframe , which is created from a
dictionary and save to a specified location.

# importing pandas as pd
import pandas as pd

# list of name, degree, score

nme = ["aparna", "pankaj", "sudhir", "Geeku"]
deg = ["MBA", "BCA", "M.Tech", "MBA"]
scr = [90, 40, 80, 98]
# dictionary of lists
dict = {'name': nme, 'degree': deg, 'score': scr}

df = pd.DataFrame(dict)
print(df)

# saving the dataframe

df.to_csv(r'C:\Users\Admin\Desktop\file3.csv', index=False)
df.to_csv(r'C:\Users\Admin\Desktop\file4.csv', index=False, sep=‘|’)

# will create csv file with separator as |

MYSQL
#Program 24: a) Write the outputs of the SQL queries (i) to (iii) based on the relations Teacher
and Posting given below:

i SELECT Department, count(*) FROM Teacher GROUP BY Department;

ii SELECT Max(Date_of_Join),Min(Date_of_Join) FROM Teacher;
iii SELECT Teacher.name,Teacher.Department, Posting.Place FROM Teacher, Posting
WHERE Teacher.Department = Posting.Department AND Posting.Place=”Delhi”;

b) Write SQL Commands:

i To show all the records in ascending order of department
ii To display teacher’s name, salary, age for male teachers only and in Mathematics
department.
iii To show all the name and Joining date in descending order of joining date
iv To display name, salary and age for Male teachers
v To display name, bonus for each teacher where bonus is 10% of salary.

Ans:
i. SELECT * FROM teacher order by department;
ii. SELECT name FROM teacher WHERE department= “Mathematics” AND gender=“F”;
iii. SELECT name, date_of_join FROM teacher ORDER BY date_of_join desc;
iv. SELECT name, salary, age FROM teacher WHERE gender=’M’;
v. SELECT name, salary*0.1 AS Bonus FROM teacher;
 #PROGRAM 25: Study the following table and write SQL commands for the following:

(a) To show all information of students where capacity is more than the no of student in the order of
Rtno.

Select * from schoolbus where capacity>noofstudents order by rtno;

(b) To show area_covered for buses covering more than 20 km., but charges less than 80000.

Select area_covered from SchoolBus where distance>20 and charges<80000;

(c) To show transporter,Area_covered, no. of students traveling.

Select transporter, area_covered, noofstudents from SchoolBus;

(d) Add a new record with following data: (11, “ Moti bagh”,35,32,10,” kisan tours “, 35000)

Insert into schoolbus (rtno,area_covered,capacity,noofstudents,distance,transporter,charges)

values (11, “ Moti bagh”,35,32,10,” kisan tours “, 35000);

(e) Give the output considering the original relation as given:

(i) Select distance from schoolbus where transporter= ‘ Yadav Co.’;

Distance
30
20

(ii) Select noofstudents from schoolbus;

(iii) Select charges from schoolbus where transporter= ‘Anand travels’;

Charges
85000
60000
100000

(iv) Select distinct transporter from schoolbus;

Distinct transporter
ShivamTravels
Bhalla travels
Speed travels
Kisan Tours
Anand Travels
Yadav Co.
#PROGRAM 26: Consider the Employee table

(i) Display the various department numbers from the table Employee. A department number
should be displayed only once
(ii) Display the details of the table whose name contains ‘a’ as the second character.
(iii) Display the highest and the lowest salaries being paid in department 10.
(iv) Display the number of employees working in department 10.
(v) Display the average age of employees in each department only for those departments in
which average age is more than 30.

Ans:

(i) Select distinct dept from employee;

(ii) Select * from employee where name like ‘_a%’;
(iii) Select max(salary),min(salary) from employee where dept=10;
(iv) Select count(*) from employee where dept=10;
(v) Select dept,avg(age) from employee group by dept having avg(age)>30;