PART 01) DATA HANDLING USING PANDAS

Q.1. Write a python code to create the series.

import pandas as pd
data = [10, 20, 30, 40, 50]
series = [Link](data)
print("Series:\n", series)
Output:
Series:
0 10
1 20
2 30
3 40
4 50
dtype: int64

Q.2. Write a python program to calculate sum, mean, max, min.

import pandas as pd
data = [12, 24, 36, 48, 60]
series = [Link](data)
print("Series:\n", series)
print("\nSum of all elements:", [Link]())
print("Mean of all elements:", [Link]())
print("Maximum value:", [Link]())
print("Minimum value:", [Link]())

Series:
0 12
1 24
2 36
3 48
4 60
dtype: int64

Sum of all elements: 180

Mean of all elements: 36.0
Maximum value: 60
Minimum value: 12

Q.3. Write a Python program to calculate the sum, mean, maximum, and minimum of a
different Pandas Series.
import pandas as pd
data = [5, 15, 25, 35, 45]

1|Page
series = [Link](data)
print("Series:\n", series)
print("\nSum of all elements:", [Link]())
print("Mean of all elements:", [Link]())
print("Maximum value:", [Link]())
print("Minimum value:", [Link]())

Series:
0 5
1 15
2 25
3 35
4 45
dtype: int64

Sum of all elements: 125

Mean of all elements: 25.0
Maximum value: 45
Minimum value: 5

Q.4. Write a Python program to demonstrate the use of the head() and tail() methods on a
Pandas Series.
import pandas as pd
data = [100, 200, 300, 400, 500, 600, 700, 800, 900, 1000]
series = [Link](data)
print("Series:\n", series)
print("\nFirst 5 elements (using head()):")
print([Link]())
print("\nLast 5 elements (using tail()):")
print([Link]())

Series:
0 100
1 200
2 300
3 400
4 500
5 600
6 700
7 800
8 900
9 1000
dtype: int64

First 5 elements (using head()):

0 100
1 200
2 300
3 400

2|Page
4 500
dtype: int64

Last 5 elements (using tail()):

5 600
6 700
7 800
8 900
9 1000
dtype: int64

Q.5. Write a Python program to create a Pandas DataFrame from a dictionary containing
employee data.
import pandas as pd
data = {
'EmployeeID': [1, 2, 3],
'Name': ['Ishika', 'Verma', 'Pun'],
'Age': [28, 34, 29],
'Department': ['HR', 'Finance', 'IT'],
'Salary': [50000, 60000, 55000]
}
df = [Link](data)
print(df)

EmployeeID Name Age Department Salary

0 1 Ishika 28 HR 50000
1 2 Verma 34 Finance 60000
2 3 Pun 29 IT 55000

Q.6. Write a Python program to create a Pandas DataFrame using a NumPy array.
import numpy as np
import pandas as pd
employee_data = [Link]([
[1, 'Ishika', 28, 'HR', 50000],
[2, 'Verma', 34, 'Finance', 60000],
[3, 'Pun', 29, 'IT', 55000],
[4, 'Diya', 30, 'Marketing', 58000]
])
columns = ['EmployeeID', 'Name', 'Age', 'Department', 'Salary']
df = [Link](employee_data, columns=columns)
print(df)

EmployeeID Name Age Department Salary

0 1 Ishika 28 HR 50000
1 2 Verma 34 Finance 60000
2 3 Pun 29 IT 55000
3 4 Diya 30 Marketing 58000

3|Page
Q.7. Write a Python program to create a Pandas DataFrame from two lists: one for product
names and another for their corresponding prices.
import pandas as pd
products = ['Laptop', 'Tablet', 'Smartphone', 'Monitor', 'Keyboard']
prices = [800, 300, 600, 200, 50]
df = [Link]({
'Product': products,
'Price': prices
})
print(df)

Product Price
0 Laptop 800
1 Tablet 300
2 Smartphone 600
3 Monitor 200
4 Keyboard 50

Q.8. Write a Python program to perform arithmetic operations (multiplication, division, and
addition) on columns of a Pandas DataFrame.
import pandas as pd
data = {
'X': [10, 20, 30, 40],
'Y': [5, 15, 25, 35]
}
df = [Link](data)
print("Original DataFrame:")
print(df)
df['X * Y'] = df['X'] * df['Y']
df['X / Y'] = df['X'] / df['Y']
df['X + Y'] = df['X'] + df['Y']
print("\nUpdated DataFrame with Multiply, Divide, and Add functions:")
print(df)

Original DataFrame:
X Y
0 10 5
1 20 15
2 30 25
3 40 35

Updated DataFrame with Multiply, Divide, and Add functions:

X Y X * Y X / Y X + Y
0 10 5 50 2.000000 15
1 20 15 300 1.333333 35
2 30 25 750 1.200000 55
3 40 35 1400 1.142857 75
4|Page
Q.9. Write a Python program to filter a Pandas DataFrame based on conditions such as
greater than, less than, and equal to.
import pandas as pd
data = {
'Employee_Name': ['Ishika', 'Verma', 'Pun', 'Diya', 'Prachi'],
'Employee_ID': [101, 102, 103, 104, 105],
'Salary': [50000, 60000, 55000, 58000, 62000],
'Age': [28, 34, 29, 30, 25]
}
df = [Link](data)
print("Original DataFrame:")
print(df)
greater_than_58000 = df[df['Salary'] > 58000]
print("\nEmployees with Salary greater than 58000:")
print(greater_than_58000)
less_than_30_age = df[df['Age'] < 30]
print("\nEmployees with Age less than 30:")
print(less_than_30_age)
equal_to_55000_salary = df[df['Salary'] == 55000]
print("\nEmployees with Salary equal to 55000:")
print(equal_to_55000_salary)

Original DataFrame:
Employee_Name Employee_ID Salary Age
0 Ishika 101 50000 28
1 Verma 102 60000 34
2 Pun 103 55000 29
3 Diya 104 58000 30
4 Prachi 105 62000 25

Employees with Salary greater than 58000:

Employee_Name Employee_ID Salary Age
1 Verma 102 60000 34
4 Prachi 105 62000 25

Employees with Age less than 30:

Employee_Name Employee_ID Salary Age
0 Ishika 101 50000 28
2 Pun 103 55000 29
4 Prachi 105 62000 25

Employees with Salary equal to 55000:

Employee_Name Employee_ID Salary Age
2 Pun 103 55000 29

Q.10. Write a Python program to create a Pandas DataFrame and display its contents.
import pandas as pd
data = {
5|Page
 'Product': ['Laptop', 'Tablet', 'Smartphone', 'Monitor',
'Keyboard'],
'Price': [800, 300, 600, 200, 50],
'Stock': [10, 20, 15, 30, 50]
}
df = [Link](data)
print("Product DataFrame:")
print(df)

Product DataFrame:
Product Price Stock
0 Laptop 800 10
1 Tablet 300 20
2 Smartphone 600 15
3 Monitor 200 30
4 Keyboard 50 50

Q.11. Write a Python program to create a Pandas DataFrame and add a new column for
ratings, then display the DataFrame.
import pandas as pd
product_data = {
'Product_ID': [1, 2, 3, 4, 5],
'Product_Name': ['Laptop', 'Tablet', 'Smartphone', 'Monitor',
'Keyboard'],
'Price': [800, 300, 600, 200, 50],
'Stock': [10, 20, 15, 30, 50]
}
df = [Link](product_data)
print("Product DataFrame:")
print(df)
df['Rating'] = [4.5, 4.0, 4.7, 4.2, 4.8]
print("\nDataFrame after adding 'Rating' column:")
print(df)

Product DataFrame:
Product_ID Product_Name Price Stock
0 1 Laptop 800 10
1 2 Tablet 300 20
2 3 Smartphone 600 15
3 4 Monitor 200 30
4 5 Keyboard 50 50

DataFrame after adding 'Rating' column:

Product_ID Product_Name Price Stock Rating
0 1 Laptop 800 10 4.5
1 2 Tablet 300 20 4.0
2 3 Smartphone 600 15 4.7
3 4 Monitor 200 30 4.2
6|Page
4 5 Keyboard 50 50 4.8

Q.12. Write a Python program to demonstrate the use of DataFrame attributes such as index,
axes, size, shape, and count.
import pandas as pd
data = {
'Employee_Name': ['Ishika', 'Verma', 'Pun', 'Diya', 'Prachi'],
'Employee_ID': [101, 102, 103, 104, 105],
'Salary': [50000, 60000, 55000, 58000, 62000],
'Age': [28, 34, 29, 30, 25]
}
df = [Link](data)
print("DataFrame:")
print(df)
print("\nIndex of the DataFrame:")
print([Link])
print("\nSize of the DataFrame:")
print([Link])
print("\nShape of the DataFrame:")
print([Link])
print("\nCount of non-NA/null entries for each column:")
print([Link]())

DataFrame:
Employee_Name Employee_ID Salary Age
0 Ishika 101 50000 28
1 Verma 102 60000 34
2 Pun 103 55000 29
3 Diya 104 58000 30
4 Prachi 105 62000 25

Index of the DataFrame:

RangeIndex(start=0, stop=5, step=1)

Size of the DataFrame:

20

Shape of the DataFrame:

(5, 4)

Count of non-NA/null entries for each column:

Employee_Name 5
Employee_ID 5
Salary 5
Age 5
dtype: int64

7|Page
Q.13. Write a Python program to demonstrate the use of iat and at attributes for accessing
and modifying DataFrame elements.
import pandas as pd
data = {
'Employee_Name': ['Ishika', 'Verma', 'Pun', 'Diya', 'Prachi'],
'Employee_ID': [101, 102, 103, 104, 105],
'Salary': [50000, 60000, 55000, 58000, 62000],
'Age': [28, 34, 29, 30, 25]
}
df = [Link](data)

# Accessing and modifying using 'at'

original_value_at = [Link][1, 'Salary']
[Link][1, 'Salary'] = 65000
modified_value_at = [Link][1, 'Salary']

# Accessing and modifying using 'iat'

original_value_iat = [Link][3, 2]
[Link][3, 2] = 59000
modified_value_iat = [Link][3, 2]

print("Original value at index 1, 'Salary':", original_value_at)

print("Modified value at index 1, 'Salary':", modified_value_at)
print("Original value at row 3, column 2:", original_value_iat)
print("Modified value at row 3, column 2:", modified_value_iat)

Original value at index 1, 'Salary': 60000

Modified value at index 1, 'Salary': 65000
Original value at row 3, column 2: 58000
Modified value at row 3, column 2: 59000

Q.14. Write a Python program to retrieve and access specific rows and columns from a
Pandas DataFrame.
import pandas as pd
employee_data = {
'Employee_ID': [101, 102, 103],
'Employee_Name': ['Ishika', 'Verma', 'Pun'],
'Salary': [50000, 60000, 55000],
'Age': [28, 34, 29]
}
df = [Link](employee_data)
print("Employee DataFrame:")
print(df)

8|Page
print("\nAccessing the 'Employee_Name' column:")
print(df['Employee_Name'])

print("\nAccessing the 'Employee_Name' and 'Salary' columns:")

print(df[['Employee_Name', 'Salary']])

print("\nAccessing the row at index 1 (second employee):")

print([Link][1])

print("\nAccessing the rows at index 0 and 2 (first and third

employees):")
print([Link][[0, 2]])

print("\nAccessing rows where Salary is greater than 55000:")

print(df[df['Salary'] > 55000])

Employee DataFrame:
Employee_ID Employee_Name Salary Age
0 101 Ishika 50000 28
1 102 Verma 60000 34
2 103 Pun 55000 29

Accessing the 'Employee_Name' column:

0 Ishika
1 Verma
2 Pun
Name: Employee_Name, dtype: object

Accessing the 'Employee_Name' and 'Salary' columns:

Employee_Name Salary
0 Ishika 50000
1 Verma 60000
2 Pun 55000

Accessing the row at index 1 (second employee):

Employee_ID 102
Employee_Name Verma
Salary 60000
Age 34
Name: 1, dtype: object

Accessing the rows at index 0 and 2 (first and third employees):

Employee_ID Employee_Name Salary Age
0 101 Ishika 50000 28
2 103 Pun 55000 29

Accessing rows where Salary is greater than 55000:

Employee_ID Employee_Name Salary Age
1 102 Verma 60000 34

9|Page
Q.15. Write a Python program to group a Pandas DataFrame by a specific column and
calculate the mean of another column for each group.
import pandas as pd
data = {
'Employee_Name': ['Ishika', 'Verma', 'Pun', 'Diya', 'Prachi'],
'Department': ['HR', 'Finance', 'IT', 'HR', 'Finance'],
'Salary': [50000, 60000, 55000, 58000, 62000]
}
df = [Link](data)
print("Original DataFrame:")
print(df)

mean_salary_by_department = [Link]('Department')['Salary'].mean()
print("\nMean Salary by Department:")
print(mean_salary_by_department)

Original DataFrame:
Employee_Name Department Salary
0 Ishika HR 50000
1 Verma Finance 60000
2 Pun IT 55000
3 Diya HR 58000
4 Prachi Finance 62000

Mean Salary by Department:

Department
Finance 61000.0
HR 54000.0
IT 55000.0
Name: Salary, dtype: float64

Q.16. Write a Python program to sort a Pandas DataFrame by a specific column in ascending
and descending order.
import pandas as pd
data = {
'Employee_Name': ['Ishika', 'Verma', 'Pun', 'Diya', 'Prachi'],
'Salary': [50000, 60000, 55000, 58000, 62000]
}
df = [Link](data)
print("Original DataFrame:")
print(df)

sorted_df_ascending = df.sort_values(by='Salary', ascending=True)

print("\nDataFrame sorted by Salary (Ascending):")
print(sorted_df_ascending)

10 | P a g e
sorted_df_descending = df.sort_values(by='Salary', ascending=False)
print("\nDataFrame sorted by Salary (Descending):")
print(sorted_df_descending)

Original DataFrame:
Employee_Name Salary
0 Ishika 50000
1 Verma 60000
2 Pun 55000
3 Diya 58000
4 Prachi 62000

DataFrame sorted by Salary (Ascending):

Employee_Name Salary
0 Ishika 50000
2 Pun 55000
3 Diya 58000
1 Verma 60000
4 Prachi 62000

DataFrame sorted by Salary (Descending):

Employee_Name Salary
4 Prachi 62000
1 Verma 60000
3 Diya 58000
2 Pun 55000
0 Ishika 50000

11 | P a g e
PART B) DATA VISUALISATION USING MATPLOTLIB
Q.1. Write a Python program to create a histogram using [Link]. Use a list of
numerical data to visualize the distribution.
import [Link] as plt
import numpy as np

data = [Link](1000) # Generate 1000 random numbers

[Link](data, bins=30, alpha=0.7, color='green')
[Link]('Histogram Example')
[Link]('Value')
[Link]('Frequency')
[Link]()
[Link]()

Q.2. Write a Python program to create a pie chart using [Link]. Use a list of
categories and their corresponding values.
import [Link] as plt

labels = ['Python', 'Java', 'C++', 'JavaScript']

sizes = [45, 30, 15, 10]
colors = ['gold', 'lightcoral', 'lightskyblue', 'lightgreen']
explode = (0.1, 0, 0, 0) # explode the 1st slice

[Link](sizes, explode=explode, labels=labels, colors=colors,

autopct='%1.1f%%', shadow=True, startangle=140)
[Link]('Programming Language Popularity')
[Link]('equal') # Equal aspect ratio ensures that pie is drawn as a
circle.
[Link]()

12 | P a g e
Q.3. Write a Python program to create a bar chart with custom colors and labels for each bar.
import [Link] as plt

categories = ['Apples', 'Bananas', 'Cherries', 'Dates']

values = [25, 40, 30, 20]
colors = ['red', 'yellow', 'pink', 'brown']

[Link](categories, values, color=colors)

[Link]('Fruit Count')
[Link]('Fruits')
[Link]('Count')
[Link]()

13 | P a g e
Q.4. Write a Python program to create a line chart with multiple lines representing different
datasets.
import [Link] as plt

x = [1, 2, 3, 4, 5]
y1 = [2, 3, 5, 7, 11]
y2 = [1, 4, 6, 8, 10]

[Link](x, y1, marker='o', label='Dataset 1', color='blue')

[Link](x, y2, marker='s', label='Dataset 2', color='orange')
[Link]('Multiple Line Chart Example')
[Link]('X-axis')
[Link]('Y-axis')
[Link]()
[Link]()
[Link]()

Q.5. Write a Python program to create a bar chart that includes error bars.
import [Link] as plt
import numpy as np

categories = ['Group A', 'Group B', 'Group C']

values = [20, 35, 30]
error = [2, 3, 4]

[Link](categories, values, yerr=error, capsize=5, color='skyblue')

14 | P a g e
[Link]('Bar Chart with Error Bars')
[Link]('Groups')
[Link]('Values')
[Link]()

15 | P a g e
PART C) QUERING USING SQL
Q.1. Aarav, a bookstore owner, creates a table BOOKS to manage the inventory of books with
the attributes Book_ID, Title, Author, Price, and Quantity. After creating the table, he enters
data for 10 books.
BOOKS
Book_ID Title Author Price Quantity
B001 The White Tiger Aravind Adiga 500.00 15
B002 Train to Pakistan Khushwant Singh 300.00 4
B003 The Immortals of Meluha Amish Tripathi 400.00 14
B004 Midnight's Children Salman Rushdie 350.00 10
B005 The God of Small Things Arundhati Roy 550.00 9
B006 2 States Chetan Bhagat 350.00 7
B007 The Palace of Illusions Chitra Banerjee Divakaruni 450.00 11
B008 The Inheritance of Loss Kiran Desai 400.00 3
B009 The Namesake Jhumpa Lahiri 400.00 8
B010 The Guide R.K. Narayan 300.00 12
Write SQL query to create above table structure and insert data.
CREATE TABLE BOOKS (
Book_ID VARCHAR(5) PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(100),
Price DECIMAL(8, 2),
Quantity INT
);

INSERT INTO BOOKS (Book_ID, Title, Author, Price, Quantity) VALUES

('B001', 'The White Tiger', 'Aravind Adiga', 500.00, 15),
('B002', 'Train to Pakistan', 'Khushwant Singh', 300.00, 4),
('B003', 'The Immortals of Meluha', 'Amish Tripathi', 400.00, 14),
('B004', 'Midnight''s Children', 'Salman Rushdie', 350.00, 10),
('B005', 'The God of Small Things', 'Arundhati Roy', 550.00, 9),
('B006', '2 States', 'Chetan Bhagat', 350.00, 7),
('B007', 'The Palace of Illusions', 'Chitra Banerjee Divakaruni',
450.00, 11),
('B008', 'The Inheritance of Loss', 'Kiran Desai', 400.00, 3),
('B009', 'The Namesake', 'Jhumpa Lahiri', 400.00, 8),
('B010', 'The Guide', 'R.K. Narayan', 300.00, 12);

Based on the table BOOKS, write SQL statements for the following:
i. Write the statement to update the record with Book_ID = B005 to change the Price to 450 and
Quantity to 20.
UPDATE BOOKS
SET Price = 450.00, Quantity = 20
WHERE Book_ID = 'B005';

ii. Delete all records where Quantity is less than 5.

DELETE FROM BOOKS
WHERE Quantity < 5;

16 | P a g e
iii. Change the data type of the Title column to VARCHAR (100) and add a NOT NULL constraint.
ALTER TABLE BOOKS
MODIFY Title VARCHAR(100) NOT NULL;

iv. Remove the Author column from the BOOKS table.

ALTER TABLE BOOKS
DROP COLUMN Author;

v. Retrieve all books where the Price is greater than 400, ordering the results by Quantity in
descending order.
SELECT Book_ID, Title, Price, Quantity
FROM BOOKS
WHERE Price > 400
ORDER BY Quantity DESC;

Q.2. Consider the following tables:

Customers Table (CUSTOMERS)
Customer_ID Name City Phone
C001 Rahul Sharma Delhi 9876543210
C002 Priya Verma Mumbai 9123456789
C003 Aman Kapoor Bangalore 9876541230
C004 Sneha Iyer Hyderabad 9988776655
C005 Rohan Mehta Pune 9345678901

Orders Table (ORDERS)

Order_ID Customer_ID Order_Date Amount
O001 C001 2024-01-15 1500
O002 C002 2024-01-20 2500
O003 C003 2024-01-25 2000
O004 C001 2024-01-30 3000
O005 C004 2024-02-05 3500
O006 C005 2024-02-10 4000
Write SQL query to create a above table structure, insert data and answer the following
questions.
INSERT INTO CUSTOMERS (Customer_ID, Name, City, Phone) VALUES
('C001', 'Rahul Sharma', 'Delhi', '9876543210'),
('C002', 'Priya Verma', 'Mumbai', '9123456789'),
('C003', 'Aman Kapoor', 'Bangalore', '9876541230'),
('C004', 'Sneha Iyer', 'Hyderabad', '9988776655'),
('C005', 'Rohan Mehta', 'Pune', '9345678901');

INSERT INTO ORDERS (Order_ID, Customer_ID, Order_Date, Amount) VALUES

('O001', 'C001', '2024-01-15', 1500),
('O002', 'C002', '2024-01-20', 2500),
('O003', 'C003', '2024-01-25', 2000),
('O004', 'C001', '2024-01-30', 3000),
('O005', 'C004', '2024-02-05', 3500),
('O006', 'C005', '2024-02-10', 4000);
a) Write an SQL statement to update the Amount for the order with Order_ID = O006 to 3500.
17 | P a g e
 UPDATE ORDERS
SET Amount = 3500
WHERE Order_ID = 'O006';

b) A new customer, Aditi Singh, has placed an order of ₹2000. First, write an SQL statement to
insert her details into the CUSTOMERS table, and then insert a new order for her with
Order_ID = O007, Order_Date as '2024-02-15', and Amount as 2000.
-- Inserting Aditi Singh into CUSTOMERS table
INSERT INTO CUSTOMERS (Customer_ID, Name, City, Phone)
VALUES ('C006', 'Aditi Singh', 'Delhi', '9999887766');

-- Inserting Aditi's order into ORDERS table

INSERT INTO ORDERS (Order_ID, Customer_ID, Order_Date, Amount)
VALUES ('O007', 'C006', '2024-02-15', 2000);

c) Write a query to retrieve all customer names along with their order amounts, displaying
the results in ascending order by the Amount.
SELECT [Link], [Link]
FROM CUSTOMERS C
JOIN ORDERS O ON C.Customer_ID = O.Customer_ID
ORDER BY [Link] ASC;

d) Write a query to find the total amount spent by each customer and display their
Customer_ID, Name, and total Amount, ordered by total Amount in descending order.
SELECT C.Customer_ID, [Link], SUM([Link]) AS Total_Amount
FROM CUSTOMERS C
JOIN ORDERS O ON C.Customer_ID = O.Customer_ID
GROUP BY C.Customer_ID, [Link]
ORDER BY Total_Amount DESC;

e) Remove the record of ‘Sneha Iyer’ from the table CUSTOMER and their associativity with
ORDER table.
DELETE FROM ORDERS
WHERE Customer_ID = 'C004';

-- Deleting Sneha Iyer's record from CUSTOMERS table

DELETE FROM CUSTOMERS
WHERE Customer_ID = 'C004';

18 | P a g e