ज़रूरी Programs और Queries

# 1. Create a Series from a dictionary and from a NumPy array
import pandas as pd
import numpy as np
fruits = pd.Series({'Apple': 120, 'Banana': 40, 'Mango': 90})
squares = pd.Series(np.arange(1, 6) ** 2, index=list('abcde'))
print(fruits)
print(squares)

# 2. Print the elements of a Series that are above the 75th percentile
import pandas as pd
s = pd.Series([42, 12, 77, 56, 91, 33, 68, 85, 24, 60])
q3 = s.quantile(0.75)
print('75th percentile =', q3)
print(s[s > q3])

# 3. Quarterly sales DataFrame: total sales per category
import pandas as pd
sales = pd.DataFrame({'Category': ['Mobile', 'Laptop', 'Mobile', 'TV', 'Laptop', 'TV', 'Mobile'],
                      'Item': ['A12', 'Inspire', 'N8', 'Smart43', 'Pro14', 'LED32', 'Z5'],
                      'Expenditure': [45000, 120000, 38000, 60000, 98000, 22000, 51000]})
print(sales.groupby('Category')['Expenditure'].sum())

# 4. Examination result DataFrame: row labels, column labels, data types, dimensions
import pandas as pd
res = pd.DataFrame({'Eng': [80, 65, 92], 'Maths': [91, 58, 85], 'IP': [95, 70, 88]},
                   index=['Riya', 'Sahil', 'Tanya'])
print('Row labels   :', res.index.tolist())
print('Column labels:', res.columns.tolist())
print('Data types   :', res.dtypes.tolist())
print('Dimensions   :', res.shape)

# 5. Filter out duplicate rows
import pandas as pd
df = pd.DataFrame({'Name': ['Asha', 'Ravi', 'Asha', 'Neha', 'Ravi'],
                   'Class': ['XII-A', 'XII-B', 'XII-A', 'XII-A', 'XII-C']})
print(df[df.duplicated()])          # the repeated rows
print(df.drop_duplicates())

# 6. Import a CSV file into a DataFrame and export after adding a column
import pandas as pd
pd.DataFrame({'Item': ['Pen', 'Book', 'Bag'], 'Qty': [10, 4, 1], 'Rate': [10, 120, 900]}).to_csv('bill.csv', index=False)
bill = pd.read_csv('bill.csv')
bill['Amount'] = bill['Qty'] * bill['Rate']
bill.to_csv('bill_final.csv', index=False)
print(bill)
print('Grand total:', bill['Amount'].sum())

# 7. Analyse school result data and plot it (subject-wise average)
import pandas as pd
import matplotlib.pyplot as plt
res = pd.DataFrame({'Eng': [80, 65, 92, 74], 'Maths': [91, 58, 85, 69], 'IP': [95, 70, 88, 81]},
                   index=['Riya', 'Sahil', 'Tanya', 'Uday'])
avg = res.mean()
print(avg)
avg.plot(kind='bar', color=['#0ea5e9', '#f59e0b', '#10b981'], title='Subject-wise Average')
plt.ylabel('Marks')
plt.savefig('practical_avg.png')

# 8. Line chart of a city's monthly rainfall with full customisation
import matplotlib.pyplot as plt
months = ['Jun', 'Jul', 'Aug', 'Sep', 'Oct']
rain = [140, 310, 285, 160, 40]
plt.plot(months, rain, color='navy', marker='o', linestyle='--', linewidth=2, label='Rainfall')
plt.title('Monsoon Rainfall (mm)'); plt.xlabel('Month'); plt.ylabel('mm')
plt.legend(); plt.grid(True)
plt.savefig('practical_rain.png')
print('Wettest month:', months[rain.index(max(rain))])

# 9. Histogram of ages
import matplotlib.pyplot as plt
ages = [16, 17, 17, 16, 18, 17, 15, 16, 17, 18, 17, 16, 19, 17, 16]
plt.hist(ages, bins=[15, 16, 17, 18, 19, 20], edgecolor='black', color='#f472b6')
plt.title('Age of Students'); plt.xlabel('Age'); plt.ylabel('Count')
plt.savefig('practical_hist.png')
print('Most common age:', max(set(ages), key=ages.count))

SELECT * FROM STUDENT;

+--------+---------+--------+-------+------------+---------+
| RollNo | Name    | Gender | Marks | DOB        | City    |
+--------+---------+--------+-------+------------+---------+
|      1 | Ananya  | F      |  91.5 | 2008-03-12 | Delhi   |
|      2 | Bhavesh | M      |  76.0 | 2007-11-30 | Jaipur  |
|      3 | Chhavi  | F      |  84.5 | 2008-07-04 | Delhi   |
|      4 | Dhruv   | M      |  58.0 | 2008-01-21 | Lucknow |
|      5 | Ekta    | F      |  67.5 | 2007-09-15 | Jaipur  |
|      6 | Farhan  | M      |  88.0 | 2008-05-09 | Delhi   |
+--------+---------+--------+-------+------------+---------+

DELETE FROM STUDENT WHERE RollNo = 4;

SELECT Name, Marks FROM STUDENT WHERE Marks > 80 ORDER BY Marks DESC;

+--------+-------+
| Name   | Marks |
+--------+-------+
| Ananya |  91.5 |
| Farhan |  88.0 |
| Chhavi |  84.5 |
+--------+-------+

SELECT MIN(Marks), MAX(Marks), SUM(Marks), ROUND(AVG(Marks), 2) FROM STUDENT;

+------------+------------+------------+----------------------+
| MIN(Marks) | MAX(Marks) | SUM(Marks) | ROUND(AVG(Marks), 2) |
+------------+------------+------------+----------------------+
|       67.5 |       91.5 |      407.5 |                81.50 |
+------------+------------+------------+----------------------+

SELECT Gender, COUNT(*) FROM STUDENT GROUP BY Gender ORDER BY Gender;

+--------+----------+
| Gender | COUNT(*) |
+--------+----------+
| F      |        3 |
| M      |        2 |
+--------+----------+

SELECT City, ROUND(AVG(Marks), 1) AS AvgMarks FROM STUDENT GROUP BY City HAVING COUNT(*) > 1 ORDER BY City;

+--------+----------+
| City   | AvgMarks |
+--------+----------+
| Delhi  |     88.0 |
| Jaipur |     71.8 |
+--------+----------+

SELECT POWER(3, 4), ROUND(456.789, 1), ROUND(456.789, -2), MOD(29, 6);

+-------------+-------------------+--------------------+------------+
| POWER(3, 4) | ROUND(456.789, 1) | ROUND(456.789, -2) | MOD(29, 6) |
+-------------+-------------------+--------------------+------------+
|          81 |             456.8 |                500 |          5 |
+-------------+-------------------+--------------------+------------+

SELECT UCASE(Name), LENGTH(Name), LEFT(Name, 3), INSTR(Name, 'a') FROM STUDENT;

+-------------+--------------+---------------+------------------+
| UCASE(Name) | LENGTH(Name) | LEFT(Name, 3) | INSTR(Name, 'a') |
+-------------+--------------+---------------+------------------+
| ANANYA      |            6 | Ana           |                1 |
| BHAVESH     |            7 | Bha           |                3 |
| CHHAVI      |            6 | Chh           |                4 |
| EKTA        |            4 | Ekt           |                4 |
| FARHAN      |            6 | Far           |                2 |
+-------------+--------------+---------------+------------------+

SELECT Name, MONTHNAME(DOB) AS BirthMonth, YEAR(DOB) AS BirthYear, DAYNAME(DOB) AS BornOn FROM STUDENT;

+---------+------------+-----------+-----------+
| Name    | BirthMonth | BirthYear | BornOn    |
+---------+------------+-----------+-----------+
| Ananya  | March      |      2008 | Wednesday |
| Bhavesh | November   |      2007 | Friday    |
| Chhavi  | July       |      2008 | Friday    |
| Ekta    | September  |      2007 | Saturday  |
| Farhan  | May        |      2008 | Friday    |
+---------+------------+-----------+-----------+

SELECT Name, MID(City, 2, 3) AS Part FROM STUDENT WHERE City LIKE '%i';

+--------+------+
| Name   | Part |
+--------+------+
| Ananya | elh  |
| Chhavi | elh  |
| Farhan | elh  |
+--------+------+