Q1: Find Missing Values in a DataFrame
Write a Python program using Pandas to identify missing values in a dataset and replace missing numerical values with their column mean. Step 1: Create or load the dataset. Step 2: Check missing values using isnull().sum(). Step 3: Calculate the mean of numerical columns. Step 4: Replace missing values using fillna().
import pandas as pd
# Create sample dataset
data = {
"Name": ["Amit", "Riya", "Rahul", "Priya", "Karan"],
"Age": [22, 25, None, 28, 30],
"Salary": [25000, None, 35000, 40000, 45000]
}
df = pd.DataFrame(data)
print("Original Dataset:")
print(df)
print("\nMissing Values:")
print(df.isnull().sum())
# Fill missing values with column mean
df["Age"] = df["Age"].fillna(df["Age"].mean())
df["Salary"] = df["Salary"].fillna(df["Salary"].mean())
print("\nCleaned Dataset:")
print(df)Q2: Find the Top 3 Highest-Paid Employees
Given an employee dataset containing Name, Department, and Salary, find the three employees with the highest salaries. Step 1: Load the dataset into a Pandas DataFrame. Step 2: Sort the Salary column in descending order. Step 3: Select the first three records using head(3).
import pandas as pd
# Create employee dataset
data = {
"Name": ["Amit", "Riya", "Rahul", "Priya", "Karan", "Neha"],
"Department": ["IT", "HR", "Sales", "IT", "Finance", "Sales"],
"Salary": [45000, 55000, 40000, 70000, 65000, 60000]
}
df = pd.DataFrame(data)
# Sort employees by salary
highest_paid = df.sort_values(by="Salary", ascending=False).head(3)
print("Top 3 Highest-Paid Employees:")
print(highest_paid)Q3: Calculate Average Sales by Product Category
Given a sales dataset containing Product, Category, and Sales columns, calculate the average sales for every category. Step 1: Group the data using Category. Step 2: Select the Sales column. Step 3: Apply the mean() function. Step 4: Display the category-wise average sales.
import pandas as pd
# Create sales dataset
data = {
"Product": ["Laptop", "Mouse", "Phone", "Keyboard", "Tablet", "Monitor"],
"Category": ["Electronics", "Accessories", "Electronics", "Accessories", "Electronics", "Electronics"],
"Sales": [80000, 2000, 50000, 3000, 35000, 25000]
}
df = pd.DataFrame(data)
# Calculate average sales by category
average_sales = df.groupby("Category")["Sales"].mean()
print("Average Sales by Category:")
print(average_sales)Q4: Detect Outliers Using the IQR Method
Write a Python program to identify outliers in an employee salary dataset using the Interquartile Range (IQR) method. Step 1: Calculate Q1 and Q3. Step 2: Calculate IQR as Q3 minus Q1. Step 3: Calculate the lower and upper limits. Step 4: Select values outside these limits as potential outliers.
import pandas as pd
# Salary dataset
data = {
"Employee": ["A", "B", "C", "D", "E", "F", "G", "H"],
"Salary": [30000, 32000, 35000, 36000, 38000, 40000, 42000, 150000]
}
df = pd.DataFrame(data)
# Calculate quartiles
Q1 = df["Salary"].quantile(0.25)
Q3 = df["Salary"].quantile(0.75)
# Calculate IQR
IQR = Q3 - Q1
# Calculate limits
lower_limit = Q1 - 1.5 * IQR
upper_limit = Q3 + 1.5 * IQR
# Find outliers
outliers = df[
(df["Salary"] < lower_limit) |
(df["Salary"] > upper_limit)
]
print("Q1:", Q1)
print("Q3:", Q3)
print("IQR:", IQR)
print("Lower Limit:", lower_limit)
print("Upper Limit:", upper_limit)
print("\nPotential Outliers:")
print(outliers)Q5: Build a Linear Regression Model
Build a machine learning model that predicts student marks from study hours. Step 1: Prepare StudyHours as the feature and Marks as the target. Step 2: Split the dataset into training and testing sets. Step 3: Train LinearRegression. Step 4: Predict marks for the test data. Step 5: Calculate the R2 score to evaluate the model.
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import r2_score
# Create sample dataset
data = {
"StudyHours": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
"Marks": [35, 40, 45, 50, 55, 62, 68, 75, 82, 90]
}
df = pd.DataFrame(data)
# Features and target
X = df[["StudyHours"]]
y = df["Marks"]
# Split data
X_train, X_test, y_train, y_test = train_test_split(
X, y, test_size=0.2, random_state=42
)
# Create and train model
model = LinearRegression()
model.fit(X_train, y_train)
# Make predictions
y_pred = model.predict(X_test)
# Evaluate model
score = r2_score(y_test, y_pred)
print("Actual Marks:", list(y_test))
print("Predicted Marks:", y_pred)
print("R2 Score:", score)
# Predict marks for 7.5 study hours
new_data = [[7.5]]
prediction = model.predict(new_data)
print("Predicted Marks for 7.5 Hours:", prediction[0])Q6: Classify Customers Using Logistic Regression
Build a classification model that predicts whether a customer will purchase a product based on age and annual income. Step 1: Create the customer features. Step 2: Define 0 as no purchase and 1 as purchase. Step 3: Split the data. Step 4: Train LogisticRegression. Step 5: Predict the class of a new customer.
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import accuracy_score
# Features: Age and Income in thousands
X = np.array([
[20, 20],
[22, 25],
[25, 30],
[28, 35],
[30, 40],
[35, 45],
[40, 50],
[45, 60],
[50, 70],
[55, 80]
])
# 0 = No Purchase, 1 = Purchase
y = np.array([0, 0, 0, 0, 0, 1, 1, 1, 1, 1])
# Split data
X_train, X_test, y_train, y_test = train_test_split(
X, y, test_size=0.3, random_state=42
)
# Train model
model = LogisticRegression()
model.fit(X_train, y_train)
# Test predictions
y_pred = model.predict(X_test)
# Calculate accuracy
accuracy = accuracy_score(y_test, y_pred)
print("Accuracy:", accuracy)
# Predict for a new customer
new_customer = [[38, 48]]
prediction = model.predict(new_customer)
probability = model.predict_proba(new_customer)
print("Predicted Class:", prediction[0])
print("Prediction Probability:", probability)Q7: Calculate Classification Performance Metrics
Given actual and predicted labels from a classification model, calculate accuracy, precision, recall, and F1 score. Step 1: Store actual and predicted values. Step 2: Use the corresponding scikit-learn metric functions. Step 3: Compare the metrics to understand different aspects of model performance.
from sklearn.metrics import accuracy_score
from sklearn.metrics import precision_score
from sklearn.metrics import recall_score
from sklearn.metrics import f1_score
# Actual values
actual = [1, 1, 0, 1, 0, 0, 1, 0, 1, 0]
# Predicted values
predicted = [1, 1, 0, 0, 0, 1, 1, 0, 1, 0]
accuracy = accuracy_score(actual, predicted)
precision = precision_score(actual, predicted)
recall = recall_score(actual, predicted)
f1 = f1_score(actual, predicted)
print("Accuracy:", accuracy)
print("Precision:", precision)
print("Recall:", recall)
print("F1 Score:", f1)Q8: Write a SQL Query to Find the Second Highest Salary
Assume an Employee table contains EmployeeID, EmployeeName, Department, and Salary. Write a SQL query to find the second highest distinct salary. Step 1: Select distinct salaries. Step 2: Sort salaries in descending order. Step 3: Skip the highest salary using OFFSET. Step 4: Return the next salary.
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;Explanation
The DISTINCT keyword prevents duplicate salary values from affecting the result. ORDER BY sorts salaries from highest to lowest, while LIMIT 1 OFFSET 1 skips the highest salary and returns the second highest distinct salary.