`

Informatics Practices
#Practical no 1
#Create a pandas series from a dictionary of values and an
ndarray
import pandas as pd
import numpy as np
students={"Harshil":10,"Sunny":10,"Sanya":12,"Shivani":13,"ABC"
:14}
print("Original
Dictionary")
print(students)
s1= pd.Series(students)
print("Series created from dictionary:")
print(s1)
arr=np.arange(6)
print("Array
is:",arr)
s2=pd.Series(arr)
print("Series created from ndarray is :")
print(s2)
#Output
# Sort the series S1 in descending order.
import pandas as pd
section=["A","B","C","D"]
contri= [6700,7800,5500,4300]
S1=pd.Series(data=
contri,index=section) print(S1)
print(S1.sort_values(ascending=False))
#Output
mport pandas as pd
#Practical no 6
Program to create a DataFrame to store weight, age and
names of 3 people. Print the DataFrame and its transpose.
import pandas as pd
df=pd.DataFrame({"Weight":[42,75,66],"Name":["Arnav","Charle
s","Guru"], "Age":[15,22,23]})
print("Original
DataFrame:") print(df)
print("Transpose:")
print(df.T)
#Output
#Practical no 7
Create a DataFrame DF showing population, hospitals and
schools in 4 cities. Add a column Density to the DataFrame.
import pandas as pd df=pd.DataFrame({"Population":
[10234542,12567875,46251466,
45897152],"Schools":[7895,8547,7258,7345],"Hospitals":
[189,208,149,157]},index=["Delhi" ,"Mumbai","Kolkata"
, "Chennai"])
print("Original
DataFrame:") print(df)
df["Density"]=1200
print("After adding column:")
print(df)
#Output
#Practical no 8
#Adding/Modifying a row to the DataFrame DF .
import pandas as pd df=pd.DataFrame({"Population":
[10234542,12567875,46251466,
45897152],"Schools":[7895,8547,7258,7345],"Hospitals":
[189,208,149,157]},index=["Delhi" ,"Mumbai","Kolkata"
, "Chennai"])
print("Original DataFrame:")
print(df)
print("After adding a new row:")
df.loc["Banglore"]=[23456783,9845,234]
print(df)
df.Schools["Delhi"]=5420
print("After modifying a new row:")
print(df)
#Output

# Practical no 9
Create another DafaFrame from DF and it must not contain the
column "Population" and the row "Bangalore".
import pandas as pd df=pd.DataFrame({"Population":
[10234542,12567875,46251466,
45897152],"Schools":[7895,8547,7258,7345],"Hospitals":
[189,208,149,157]},index=["Delhi" ,"Mumbai","Kolkata",
"Chennai"]) print("Original
DataFrame:") print(df)
print("After adding a new row:")
df.loc["Banglore"]=[23456783,9845,234]
print(df)
df1=pd.DataFrame(df)
del df1["Population"]
df1=df1.drop(["Banglore"])
print(df1)

#Output
# Practical no 10
Create a data frame for examination result and display row
labels, column labels data types of each column and the
dimensions.
import pandas as pd
Dic={"Class":[ 1,2,3,4,5,6] , "Pass Percentage":
[100,100,100,100,95,96]}
Result=pd.DataFrame(Dic)
print(Result)
print(Result.dtypes)
print("Shape of the
dataframe:") print(Result.shape)
#output
#Practical no 11
Importing and exporting data between pandas and CSV
file import pandas as pd DF=pd.read_csv(“C:\\Python\\
abc.csv”)
print(DF)
Output
Roll no Name
0 1201 abc
1 1202 xyz
2 1203 pqr
3 1204 lmn
4 1205 qwe

import pandas as pd people={'Sales':

{'name':'ABC','age':'24','Gender':'Male'} ,
'Marketing':{'name':'xyz','age':'22','Gender':'Female'}}
df1=pd.DataFrame(people)
print(df1) df1.to_csv("C:\\Python\\
abc.csv") print()
#Output
#Practical no 12
#Given the school result data, analyse the performance of the
students on different parameters e.g subject wise or class wise
import matplotlib.pyplot as plt
Subject=["Physics","Chemistry","Biology","Math",
"Informatics Practices"]
Percentage= [85,78,65,90,100]
plt.bar(Subject,Percentage,align="center",color="Green")
plt.xlabel("Subjects Name")
plt.ylabel("Pass Percentage")
plt.title("Bar Graph for result Analysis")
plt.show()
output

#Practical no 13
#For the DataFrame created above, analyze and plot
appropriate charts with title and legend.
import matplotlib.pyplot as plt
import numpy as np
s=["1st","2nd" ,"3rd"]
per_sc=[95,89,77]
per_com=[90,93,75]
per_hum=[97,92,77]
x=np.arange(len(s))
plt.bar(x,per_sc,label="Science",width=0.25,color="Green")
plt.bar(x+.25,per_com,label="Commerce",width=0.25,color="red
")
plt.bar(x+0.50,per_hum,label="Humanities",width=0.25,color="g
old")
plt.xlabel("Position")
plt.ylabel(" Percentage")
plt.title("Bar Graph for result Analysis")
plt.legend()
plt.show()
#Practical no 14
#Take data of your interest from an open source( eg:
data.gov.in), aggregate and summarize it. Then plot it using
different plotting functions of the Matplotlib library.
import pandas as pd
import matplotlib.pyplot as plt DF=pd.read_csv("C:\\Python\\
Census.csv") slices=(DF["2019"].head(6))
states=(DF["State"].head(6))
#plt.plot(slices,states)
plt.title("2019 Census Data")
plt.ylabel("Cities")
plt.xlabel("Population")
#plt.bar(slices,states)
plt.hist(slices,bins=20,cumulative=True,histtype='barstacked',orie
ntation="vertical")
plt.show()
#Practical no 15
#Create a Data Frame based on ecommerce data and generate
descriptive statistics(mean, mode, median, quartile,and
variance)
import pandas as pd sales={"InvoiceNo":
[1001,1002,1003,1004,1005,1006,1007],

"ProductName":["LED","AC","Deodrant","Jeans","Books","Shoes"
,"Jacket"], "Quantity":
[2,1,2,1,2,1,1],
"Price":[65000,55000,500,2500,950,3000,2200] }
df=pd.DataFrame(sales)
print(df["Price"].describe().round(2))
#Output:

#Practical no 16
#Find the sum of each columns, or find the column with the
lowest mean.
import pandas as pd
Profit= { "TCS":{'Qtr1':2500,
"Qtr2":2000,"Qtr3":3000,"Qtr4":2000} ,
"WIPRO":{'Qtr1':2800,
“Qtr2":2400,"Qtr3":3600,"Qtr4":2400},
"L&T": {'Qtr1':2100,
"Qtr2":5700,"Qtr3":35000,"Qtr4":2100}}
df=pd.DataFrame(Profit)
print(df)
print()
print("Column wise sum in dataframe is :::")
print(df.sum(axis=0))
print()
print("Column wise mean value are::::")
print(df.mean(axis=0))
print()
print("Column with minimum mean value is :::")
print(df.mean(axis=0).idxmin())
#output
#Practical no 17
#Replace all negative values in a dataframe with a 0
import pandas as pd
dic={"Data1":[−5,−2,5,8,9,−6], "Data2":[2,4,10,15,−5,−8]}
df=pd.DataFrame(dic)
print(df)
print()
print("DataFrame after replacing negative values with 0::")
df[df<0]=0
print(df)
#Output
#Practical no 18
#Replace all missing values in a dataframe with a 999
import pandas as pd
import numpy as np
dic={"EmpID":[1,2,np.nan,4,5], "Empname":
["Rahul","Rohan","Raj",np.nan,"Ramesh"]}
df=pd.DataFrame(dic)
print(df)
print()
df=df.fillna({"EmpID":999,"Empname":999})
print()
print(df)
#Output
#Practical no 19
#Locate 3 largest values in a dataframe
import pandas as pd
dict1=
{'Name':['Rahul','Riya','Rohan','Raja'],'Marks':[55,94,75,67],\
'Roll no':[12,34,45,35]}
dmarks=pd.DataFrame(dict1)
print(dmarks) print(dmarks.nlargest(3,
['Marks']))
#Output

Practical no 20 : Write a query to display the list of tables stored in a database

USE DATABASE1

SHOW TABLES

Output:
#Practical no 21
Create a student table with the student id, name, and marks as
attributes where the student id is the primary key.

Create table student(student_is varchar(10) primary key not

null, name varchar(30), marks integer(5));
#Output

#Practical no 22
Insert the details of a new student in the above table
insert into student values (101),”Rohit”,410);
insert into student values (102,”Mohit”,425);
insert into student values (103,”Rahul”,475);
insert into student values (104,”Virak”,495);
Output:

#Practical no 23
Delete the details of a student in the above table
delete from student where name= “Rahul”;

Output:
#Practical no 24
Use the select command to get the details of the students with
marks more than 80.
Select * from Student where marks>80;
Output:
#Practical no 25
Find the min, max, sum, and average of the marks in a student
marks table.
Select min(marks),max(marks),sum(marks),avg(marks) from
student;

Output:

#Practical no 26
Find the total number of customers from each country in the table
(customer ID, customer Name, country) using group by.
Select country, count(*) “Total Customer” from customer group
by country;
Output :

#Practical no 27
Write a SQL query to order the (student ID, marks) table in descending order of the
marks.

Select student_id, marks from student order by marks desc;

Output:
Practical no 28:

Convert and display string "Informatics Practices " into upper case.

Select upper(“Informatics Practices”) “Uppercase”;

Output:

Practical no 29:

Write a query to display current date on your system.

Select curdate() “Current Date”;

Output:
Practical no 30:

Write a query to extract year part from date 3rd August 2023.

Select year(“2023-08-03”) “Year”;

Output:

Practical no 31:

Display 4 characters extracted from 3rd left character onwards from the string
"Informatics Practices".

Select substr(“Informatics Practices”,3,4) “Substring”;

Output:
Practical no 32:

Write a query to remove leading and trailing spaces from string " SNBP
INTERNATIONAL ".

Select trim(“ SNBP INTERNATIONAL “);

Output:

Practical no 33:

Display the position of occurrence of string "for" in string "Informatics Practices".

Select instr(“Informatics Practices”,”for”) ;

Output:
Practical no 34:

Count the number of characters in string" School".

Select length (“School”) “Length of “;