Scribd
Upload
6 views22 pages

Pandas and SQL Practical Exercises

This document is a practical file for Grade XII students at St. Xavier's High School, focusing on programming tasks using Pandas and Matplotlib, as well as SQL queries. It includes an index of various programming exercises, such as creating DataFrames, filtering data, and visualizing results, along with example code snippets. Additionally, it contains SQL queries for creating tables, inserting, updating, and selecting data from a student database.

Uploaded by

manasvisingh2405
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views22 pages

Pandas and SQL Practical Exercises

This document is a practical file for Grade XII students at St. Xavier's High School, focusing on programming tasks using Pandas and Matplotlib, as well as SQL queries. It includes an index of various programming exercises, such as creating DataFrames, filtering data, and visualizing results, along with example code snippets. Additionally, it contains SQL queries for creating tables, inserting, updating, and selecting data from a student database.

Uploaded by

manasvisingh2405
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

ST.

XAVIERS HIGH SCHOOL


GREATER NOIDA WEST

CBSE BOARD – PRACTICAL FILE


GRADE XII

NAME: ___________________________
ROLL NO: _________________________
SUBMITTED TO: [Link]
SIGNATURE: _______________________

1
INDEX

[Link] PROGRAM/TOPIC SIGN


1. Create a Pandas Series from a dictionary of values and a NumPy
ndarray.
2. Given a Series, print all elements above the 75th percentile.
3. Create a DataFrame quarterly sales and display sum category-wise
4. Create a data frame for exam result and display row labels, column
labels, data types and dimensions.
5. WAP to Filter duplicate rows.
6. WAP to Import & export CSV file using Pandas.
7. WAP to Apply conditional filtering on DataFrame.
8. WAP to Add a new column to DataFrame
9. WAP to Sort DataFrame, Replace values in DataFrame, Display
summary statistics.
10. WAP to Change column datatype.
11. WAP to Merge two DataFrames.
12. WAP to Create DataFrame from dictionary.
13. WAP to Delete column from DataFrame.
14. WAP to Plot Bar Graph – Subject wise Result.
15. WAP to create Line Chart – Class Performance
16. WAP to create Histogram.
17. Write SQl Querry based on the table to
1. Create table
2. Insert values
3. Delete a row
4. Select query
5. Aggregate functions
6. Updation and modification

2
SECTION A — PANDAS PROGRAMS

Program–1: Create a Pandas Series from a dictionary of values and a NumPy ndarray .
import pandas as pd
import numpy as np

data = {'A':10, 'B':20, 'C':30}


s1 = [Link](data)

arr = [Link]([5,10,15,20])
s2 = [Link](arr)

print(s1)
print(s2)

Output :-

3
Program-4: Create a data frame for exam result and display rows, columns, and
dimensions

import pandas as pd
data = { 'Name': ['Amit', 'Riya', 'Sonal'],
'Maths': [78, 85, 90],
'Science': [82, 88, 91]}
df = [Link](data)
print("Exam Result DataFrame:")
print(df)
print("\nRows:")
print([Link])
print("\nColumns:")
print([Link])
print("\nDimensions of DataFrame:")
print([Link])

Output:

4
Program–2: Given a Series, print all elements above the 75th percentile.

import pandas as pd
s = [Link]([10,50,30,60,90,20,70])
limit = [Link](0.75)
print("75th Percentile Value:", limit)
print("Values above 75th percentile:\n", s[s > limit])

Output :-

5
Program–3: Create a DataFrame quarterly sales and display sum
category-wise.

import pandas as pd

data = {'Category':['Electronics','Electronics','Grocery','Grocery'],
'Item':['TV','Laptop','Rice','Wheat'],
'Expenditure':[25000,45000,5000,8000]}

df = [Link](data)
print([Link]('Category')['Expenditure'
].sum())

Output :-

6
Program–5: Write A Program to Filter duplicate rows.

import pandas as pd
df = [Link]({'Name':['A','B','A','C'],'Marks':[80,70,80,75]})
print(df.drop_duplicates())

Output :-

7
Program–6: Write a Program to Import & export CSV file using Pandas .
import pandas as pd

data = { 'Name': ['Amit', 'Riya', 'Sonal'],


'Marks': [85, 72, 90]}
df = [Link](data)
df.to_csv('[Link]', index=False)
df2 = pd.read_csv('[Link]')
print(df2)
Output:-

8
Program–7: Write a Program to Apply conditional filtering on DataFrame.
import pandas as pd
df = [Link]({'Name':['A','B','C'],'Marks':[85,65,95]})
print(df[df['Marks'] > 80])

Output :-

9
Program–8: Add a new column to DataFrame.
import pandas as pd
df=[Link]()
df['Result'] = ['Pass','Fail','Pass']
print(df)
Output :-

10
Program–9: Sort DataFrame, Replace values in DataFrame, Display summary
statistics.

import pandas as pd

print(df.sort_values(by='Marks',ascending=False))

df['Result'] = df['Result'].replace({'Fail':'Needs Improvement'})


print(df)

print([Link]())

Output :-

11
Program10: Add a new column to DataFrame
import pandas as pd
df = [Link]({'Name': ['Amit', 'Riya', 'Sonal'],
'Marks': [85, 45, 90],
'Result': ['Pass', 'Fail', 'Pass']})
df['Grade'] = ['A', 'C', 'A+']
print(df)

12
Program 11: Write a program to merge two DataFrames
import pandas as pd
df1 = [Link]({ 'ID': [1, 2, 3],
'Name': ['Amit', 'Riya', 'Sonal']})
df2 = [Link]({ 'ID': [1, 2, 3],
'Marks': [85, 78, 90})
df = [Link](df1, df2, on='ID')
print(df)

13
Program–12: Write a Program to Change column datatype.
import pandas as pd
df = [Link]({'Marks': ['85', '78', '90']})
df['Marks'] = df['Marks'].astype(float)
print([Link])
Output :-

14
Program–13: Write a Program to Merge two DataFrames.
import pandas as pd
df1 = [Link]({'ID':[1,2],'Name':['A','B']})
df2 = [Link]({'ID':[1,2],'Marks':[90,80]})
print([Link](df1,df2,on='ID'))

Output :-

15
Program–14: Create DataFrame from dictionary, Delete column from
DataFrame.

import pandas as pd
data = {'Roll':[1,2,3],'Age':[15,16,17]}
df = [Link](data)
print(df)
df = [Link]('Age', axis=1)
print(df)
Output :-

16
SECTION B — MATPLOTLIB VISUALIZATION

Program 15: Plot Bar Graph – Subject wise Result.

import [Link] as plt


subjects = ['Math','CS','English','Physics']
marks = [88,92,80,78]
[Link](subjects,marks)
[Link]("Subject Performance")
[Link]("Subjects")
[Link]("Marks")
[Link]()

Output :-

17
1. Line Chart – Class Performance.
import [Link] as plt
classes = ['10A','10B','10C','10D']
avg = [75,82,78,85]

[Link](classes,avg,marker='o')
[Link]("Class wise Performance")
[Link]()

Output :-

18
2. Write a Program to create Histogram.
import [Link] as plt
data=[45,50,65,70,75,80,85,90,95,97]
[Link](data)
[Link]("Marks Histogram")
[Link]()

Out
put :-

19
SECTION C — SQL QUERIES

Table: STUDENT
StID Name Marks
1 Ali 80
2 Riya 92
3 Sam 75
4 Raj 95

Q1. Write SQL Query to Create table.


Ans:-

CREATE TABLE Student (


StID INT PRIMARY KEY,
Name VARCHAR(20),
Marks INT
);

Q2. Write SQL Query to insert table.


Ans:-

INSERT INTO Student VALUES (5,'Tom',88);

Q3. Write SQL Query to Delete Row of Sam.


Ans:-

DELETE FROM Student WHERE StID=3;

Q4. Write SQL Query to Select students with marks > 80.
Ans:-

SELECT * FROM Student WHERE Marks > 80;

Q5. Write SQL Query for Min, Max, Sum, Average.


Ans:-

SELECT MIN(Marks), MAX(Marks), SUM(Marks), AVG(Marks) FROM Student;


20
Q6. Write SQL Query for Order by marks in descending
Ans:-

SELECT StID, Marks FROM Student ORDER BY Marks DESC;

Q7. Write SQL Query for Count customers by country


Ans:-

SELECT Country, COUNT(CustomerID) FROM Customer GROUP BY Country;

Q8. Write SQL Query for Display total students


Ans:-

SELECT COUNT(*) FROM Student;

Q9. Write SQL Query to Update Marks


Ans:-

UPDATE Student SET Marks=90 WHERE StID=1;

Q10. Write SQL Query for Display unique marks


Ans:-

SELECT DISTINCT Marks FROM Student;

Q11. Write SQL Query to Show average marks per section


Ans:-

SELECT Section, AVG(Marks) FROM Student GROUP BY Section;

Q12. Write SQL Query to Find students between 70 and 90


Ans:-

SELECT * FROM Student WHERE Marks BETWEEN 70 AND 90;

21
***

22

You might also like