Data Handling using Pandas - II

Overview

Learning outcomes

• Students will be able to find the 75th percentile (and other quantiles) of a Series or column and filter the values above it.
• Students will be able to group rows with groupby() and compute totals, averages and counts per group.
• Students will be able to compute max, min, count, sum, mean, median, mode, std and var on a Series or DataFrame, along rows or columns.
• Students will be able to read the summary produced by describe().
• Students will be able to sort a DataFrame by one or more columns with sort_values() and by labels with sort_index().
• Students will be able to detect, drop and fill missing values with isnull(), dropna() and fillna().
• Students will be able to import a MySQL table into a DataFrame with read_sql_query() and export a DataFrame to MySQL with to_sql().

Lesson plan

Notes

import pandas as pd
df = pd.DataFrame({'Name': ['Aditi', 'Bharat', 'Chirag', 'Devika', 'Eshan', 'Fatima'],
                   'Maths': [88, 67, 92, 75, 59, 81],
                   'Science': [79, 72, 95, 81, 64, 81],
                   'English': [91, 58, 84, 77, 70, 86]})
print(df)

     Name  Maths  Science  English
0   Aditi     88       79       91
1  Bharat     67       72       58
2  Chirag     92       95       84
3  Devika     75       81       77
4   Eshan     59       64       70
5  Fatima     81       81       86

import pandas as pd
marks = pd.Series([56, 78, 91, 45, 67, 88, 72, 95, 60, 83])
print(sorted(marks))
print(marks.quantile(0.75))
print(marks[marks > marks.quantile(0.75)])
print(marks.quantile([0.25, 0.5, 0.75]))

[45, 56, 60, 67, 72, 78, 83, 88, 91, 95]
86.75
2    91
5    88
7    95
dtype: int64
0.25    61.75
0.50    75.00
0.75    86.75
dtype: float64

import pandas as pd
df = pd.DataFrame({'Name': ['Aditi', 'Bharat', 'Chirag', 'Devika', 'Eshan', 'Fatima'],
                   'Maths': [88, 67, 92, 75, 59, 81],
                   'Science': [79, 72, 95, 81, 64, 81]})
print(df.quantile(0.75, numeric_only=True))
print(df.quantile([0.25, 0.75], numeric_only=True))
print(df[df['Maths'] > df['Maths'].quantile(0.75)]['Name'].tolist())

Maths      86.25
Science    81.00
Name: 0.75, dtype: float64
      Maths  Science
0.25  69.00    73.75
0.75  86.25    81.00
['Aditi', 'Chirag']

import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales)
print()
print(sales.groupby('Category')['Expenditure'].sum())
print()
print(sales.groupby('Category').size())
print()
print(sales.groupby('Category').get_group('Lab'))

  Quarter    Category            Item  Expenditure
0      Q1  Stationery       Registers        18500
1      Q1      Sports    Cricket kits        42000
2      Q1         Lab       Chemicals        26000
3      Q2  Stationery     Chalk boxes         6500
4      Q2         Lab       Glassware        31500
5      Q2      Sports       Footballs        12000
6      Q3  Stationery   Printer paper        22000
7      Q3      Sports  Badminton sets        15500
8      Q4         Lab     Microscopes        64000
9      Q4  Stationery       Registers        17000

Category
Lab           121500
Sports         69500
Stationery     64000
Name: Expenditure, dtype: int64

Category
Lab           3
Sports        3
Stationery    4
dtype: int64

  Quarter Category         Item  Expenditure
2      Q1      Lab    Chemicals        26000
4      Q2      Lab    Glassware        31500
8      Q4      Lab  Microscopes        64000

import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales.groupby('Category')['Expenditure'].agg(['sum', 'mean', 'count']))
print()
q = sales.groupby(['Quarter', 'Category'])['Expenditure'].sum()
print(q.head(4))
print()
print(q.reset_index().head(3))

               sum          mean  count
Category                               
Lab         121500  40500.000000      3
Sports       69500  23166.666667      3
Stationery   64000  16000.000000      4

Quarter  Category  
Q1       Lab           26000
         Sports        42000
         Stationery    18500
Q2       Lab           31500
Name: Expenditure, dtype: int64

  Quarter    Category  Expenditure
0      Q1         Lab        26000
1      Q1      Sports        42000
2      Q1  Stationery        18500

import pandas as pd
df = pd.DataFrame({'Maths': [88, 67, 92, 75, 59, 81],
                   'Science': [79, 72, 95, 81, 64, 81],
                   'English': [91, 58, 84, 77, 70, 86]},
                  index=['Aditi', 'Bharat', 'Chirag', 'Devika', 'Eshan', 'Fatima'])
print(df.max())
print(df.min(axis=1))
print(df.sum(axis=1).idxmax(), df['English'].idxmin())
print(df['Maths'].count(), df.count().sum())

Maths      92
Science    95
English    91
dtype: int64
Aditi     79
Bharat    58
Chirag    84
Devika    75
Eshan     59
Fatima    81
dtype: int64
Chirag Bharat
6 18

import pandas as pd
runs = pd.Series([45, 12, 78, 45, 102, 12, 33])
print(runs.mean(), runs.median())
print(runs.mode())
print(runs.mode()[0])
df = pd.DataFrame({'House': ['Red', 'Blue', 'Red', 'Green'], 'Points': [12, 9, 15, 9]})
print(df.mean(numeric_only=True))
print(df.mode())

46.714285714285715 45.0
0    12
1    45
dtype: int64
12
Points    11.25
dtype: float64
  House  Points
0   Red       9

import pandas as pd
batterA = pd.Series([40, 42, 38, 41, 39])
batterB = pd.Series([5, 90, 12, 85, 8])
print(batterA.mean(), batterB.mean())
print(batterA.var(), round(batterB.var(), 2))
print(round(batterA.std(), 2), round(batterB.std(), 2))
print(round(batterA.std(ddof=0), 2))

40.0 40.0
2.5 1889.5
1.58 43.47
1.41

import pandas as pd
df = pd.DataFrame({'Name': ['Aditi', 'Bharat', 'Chirag', 'Devika', 'Eshan', 'Fatima'],
                   'Maths': [88, 67, 92, 75, 59, 81],
                   'Science': [79, 72, 95, 81, 64, 81]})
print(df.describe())
print(df['Name'].describe())

           Maths    Science
count   6.000000   6.000000
mean   77.000000  78.666667
std    12.569805  10.366613
min    59.000000  64.000000
25%    69.000000  73.750000
50%    78.000000  80.000000
75%    86.250000  81.000000
max    92.000000  95.000000
count         6
unique        6
top       Aditi
freq          1
Name: Name, dtype: object

import pandas as pd
df = pd.DataFrame({'Name': ['Aditi', 'Bharat', 'Chirag', 'Devika', 'Eshan', 'Fatima'],
                   'Section': ['A', 'B', 'A', 'C', 'B', 'C'],
                   'Total': [258, 197, 271, 233, 193, 248]})
print(df.sort_values(by='Total', ascending=False).head(3))
print()
print(df.sort_values(by=['Section', 'Total'], ascending=[True, False]))
print()
print(df.sort_index(ascending=False).head(2))

     Name Section  Total
2  Chirag       A    271
0   Aditi       A    258
5  Fatima       C    248

     Name Section  Total
2  Chirag       A    271
0   Aditi       A    258
1  Bharat       B    197
4   Eshan       B    193
5  Fatima       C    248
3  Devika       C    233

     Name Section  Total
5  Fatima       C    248
4   Eshan       B    193

import pandas as pd
import numpy as np
ut = pd.DataFrame({'Maths': [18, np.nan, 22, 15],
                   'Science': [20, 17, np.nan, np.nan],
                   'English': [19, 21, 24, 16]},
                  index=['Aditi', 'Bharat', 'Chirag', 'Devika'])
print(ut.isnull())
print(ut.isnull().sum())
print(ut.isnull().sum().sum(), ut.isnull().any().tolist())
print(ut[ut['Science'].isnull()].index.tolist())
print(np.nan == np.nan)

        Maths  Science  English
Aditi   False    False    False
Bharat   True    False    False
Chirag  False     True    False
Devika  False     True    False
Maths      1
Science    2
English    0
dtype: int64
3 [True, True, False]
['Chirag', 'Devika']
False

import pandas as pd
import numpy as np
ut = pd.DataFrame({'Maths': [18, np.nan, 22, 15],
                   'Science': [20, 17, np.nan, np.nan],
                   'English': [19, 21, 24, 16]},
                  index=['Aditi', 'Bharat', 'Chirag', 'Devika'])
print(ut.dropna())
print(ut.dropna(axis=1))
print(ut.fillna(0))
print(ut.fillna(ut.mean()).round(1))

       Maths  Science  English
Aditi   18.0     20.0       19
        English
Aditi        19
Bharat       21
Chirag       24
Devika       16
        Maths  Science  English
Aditi    18.0     20.0       19
Bharat    0.0     17.0       21
Chirag   22.0      0.0       24
Devika   15.0      0.0       16
        Maths  Science  English
Aditi    18.0     20.0       19
Bharat   18.3     17.0       21
Chirag   22.0     18.5       24
Devika   15.0     18.5       16

import pandas as pd
df = pd.DataFrame({'Name': ['Aditi', 'Bharat', 'Chirag', 'Devika'],
                   'Section': ['A', 'B', 'A', 'C'], 'Maths': [88, 67, 92, 75]})
secA = df[df['Section'] == 'A']
print(secA)
print(secA.reset_index(drop=True))
print(df.set_index('Name').loc['Devika', 'Maths'])

     Name Section  Maths
0   Aditi       A     88
2  Chirag       A     92
     Name Section  Maths
0   Aditi       A     88
1  Chirag       A     92
75

import pandas as pd
sales = pd.read_csv('sales.csv')
grid = pd.pivot_table(sales, index='Category', columns='Quarter',
                      values='Expenditure', aggfunc='sum', fill_value=0)
print(grid)

Quarter        Q1     Q2     Q3     Q4
Category                              
Lab         26000  31500      0  64000
Sports      42000  12000  15500      0
Stationery  18500   6500  22000  17000

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:tiger@localhost:3306/school')

SELECT * FROM student;

+--------+--------+---------+-------+
| RollNo | Name   | Section | Marks |
+--------+--------+---------+-------+
|   1201 | Aditi  | 12A     |    88 |
|   1202 | Bharat | 12B     |    67 |
|   1203 | Chirag | 12A     |    92 |
|   1204 | Devika | 12C     |    75 |
|   1205 | Eshan  | 12B     |    59 |
|   1206 | Fatima | 12C     |    81 |
+--------+--------+---------+-------+
6 rows in set (0.00 sec)

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:tiger@localhost:3306/school')
df = pd.read_sql_query('SELECT * FROM student', engine)
print(df)
top = pd.read_sql_query('SELECT Name, Marks FROM student WHERE Marks > 80', engine)
print(top)
print(df.groupby('Section')['Marks'].mean())

   RollNo    Name Section  Marks
0    1201   Aditi     12A     88
1    1202  Bharat     12B     67
2    1203  Chirag     12A     92
3    1204  Devika     12C     75
4    1205   Eshan     12B     59
5    1206  Fatima     12C     81
     Name  Marks
0   Aditi     88
1  Chirag     92
2  Fatima     81
Section
12A    90.0
12B    63.0
12C    78.0
Name: Marks, dtype: float64

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:tiger@localhost:3306/school')
clubs = pd.DataFrame({'ClubId': [1, 2, 3],
                      'Club': ['Robotics', 'Eco', 'Quiz'],
                      'Members': [24, 31, 18]})
clubs.to_sql('club', engine, if_exists='replace', index=False)
more = pd.DataFrame({'ClubId': [4], 'Club': ['Music'], 'Members': [27]})
more.to_sql('club', engine, if_exists='append', index=False)
print(pd.read_sql_query('SELECT * FROM club', engine))

   ClubId      Club  Members
0       1  Robotics       24
1       2       Eco       31
2       3      Quiz       18
3       4     Music       27

Key terms

Descriptive statistics

Numbers that summarise data, such as mean, median, mode, range and standard deviation.

Mean

Sum of the values divided by their number; mean().

Median

Middle value of sorted data (average of the two middle values for an even count); median().

Mode

Most frequent value; mode() returns all modes as a Series.

Quantile

Value below which a given fraction of the data lies; quantile(q).

Percentile

A quantile expressed out of 100; the 75th percentile is quantile(0.75).

Quartile

The 25th, 50th and 75th percentiles, which split data into four equal parts.

Variance

Average squared distance of values from the mean (Pandas uses n - 1); var().

Standard deviation

Square root of the variance, a measure of spread in the data's own units; std().

Aggregation

Reducing many values to one, e.g. sum, mean or count; agg() applies several at once.

GroupBy

Splitting rows into groups by a column's values, applying a function to each group and combining the results.

Missing value

A value that is not available, shown as NaN in Pandas.

Imputation

Replacing missing values with an estimate such as 0 or the column mean, using fillna().

Pivot table

A grid that summarises data with one column's values as rows, another's as columns and an aggregate in each cell.

Connection string

Text such as mysql+pymysql://user:pwd@localhost:3306/db that tells SQLAlchemy how to reach a database.

SQLAlchemy engine

The object returned by create_engine() that Pandas uses to read from and write to a database.

pymysql

A Python driver library that lets Python programs talk to a MySQL server.

MCQs

Tap an option to check it, or use Show answers above to reveal them all.

1. s = pd.Series([10, 20, 30, 40, 50]). What is printed by print(s.quantile(0.75))?

   1. 40.0
   2. 45.0
   3. 37.5
   4. 50.0
   Answer

   (a) 40.0

   Position = (5 - 1) × 0.75 = 3, which is exactly the 4th value, 40.

2. s = pd.Series([2, 4, 6, 8]). What is printed by print(s.quantile(0.5))?

   1. 4.0
   2. 6.0
   3. 5.0
   4. 4.5
   Answer

   (c) 5.0

   The 0.5 quantile is the median: the average of 4 and 6.

3. Which statement prints the elements of Series s that are above the 75th percentile?

   1. print(s > s.quantile(0.75))
   2. print(s[s > s.quantile(0.75)])
   3. print(s.quantile(75))
   4. print(s[s.quantile(0.75)])
   Answer

   (b) print(s[s > s.quantile(0.75)])

   The comparison makes a Boolean Series and the outer brackets filter with it. Option 1 prints only True/False values; quantile(75) is invalid because q must be between 0 and 1.

4. What does df.groupby('City')['Sales'].sum() return?

   1. The total of all sales
   2. A DataFrame sorted by City
   3. A Series with the total sales for each city
   4. The number of rows for each city
   Answer

   (c) A Series with the total sales for each city

   Rows are grouped by City, Sales is summed in each group, and the city names become the index of the resulting Series.

5. Which method gives the number of rows in each group, including rows with NaN?

   1. count()
   2. sum()
   3. nunique()
   4. size()
   Answer

   (d) size()

   size() counts rows; count() counts non-NaN values in each column.

6. s = pd.Series([3, 7, 3, 9, 7, 1]). What is printed by print(s.mode().tolist())?

   1. [3, 7]
   2. [3]
   3. 7
   4. [1, 3, 7, 9]
   Answer

   (a) [3, 7]

   3 and 7 each occur twice, so both are modes, in ascending order.

7. s = pd.Series([12, 5, 9, 20]). What is printed by print(s.median())?

   1. 9.0
   2. 11.5
   3. 10.5
   4. 12.0
   Answer

   (c) 10.5

   Sorted: 5, 9, 12, 20. The median is the average of 9 and 12, i.e. 10.5.

8. A DataFrame has a text column Name and numeric columns. Which call works in Pandas 1.x, 2.x and 3.x to get the mean of each numeric column?

   1. df.mean()
   2. df.mean(axis=1)
   3. df.average()
   4. df.mean(numeric_only=True)
   Answer

   (d) df.mean(numeric_only=True)

   numeric_only=True skips text columns. Without it, Pandas 2.0+ raises TypeError; there is no average() method.

9. Which function returns count, mean, std, min, quartiles and max of numeric columns in one call?

   1. describe()
   2. info()
   3. summary()
   4. agg()
   Answer

   (a) describe()

   describe() produces this summary table.

10. What does df.sum(axis=1) compute?

    1. The total of each column
    2. The total of each row
    3. The total of all cells
    4. The number of columns
    Answer

    (b) The total of each row

    axis=1 works across columns, giving one total per row (for example each student's total marks).

11. s = pd.Series([4, None, 8, None, 6]). What is printed by print(s.count(), s.size)?

    1. 5 5
    2. 3 3
    3. 3 5
    4. 5 3
    Answer

    (c) 3 5

    count() ignores the two missing values; size counts all five elements.

12. Which statement sorts DataFrame df in descending order of the column Marks?

    1. df.sort_values(by='Marks', ascending=False)
    2. df.sort_values('Marks', descending=True)
    3. df.sort_index(by='Marks')
    4. df.sort('Marks', reverse=True)
    Answer

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

    sort_values sorts by column values; descending order is ascending=False.

13. Which statement counts the missing values in each column of df?

    1. df.isnull().sum()
    2. df.count()
    3. df.isnull().count()
    4. df.dropna().sum()
    Answer

    (a) df.isnull().sum()

    isnull() gives True for NaN; summing True values counts them. isnull().count() counts all cells.

14. What does df.dropna(how='all') do?

    1. Drops every row with at least one NaN
    2. Drops all columns
    3. Replaces NaN with 0
    4. Drops rows in which every value is NaN
    Answer

    (d) Drops rows in which every value is NaN

    With how='all', a row is removed only when all of its values are missing.

15. s = pd.Series([10, None, 30]). What is printed by print(s.fillna(s.mean()).tolist())?

    1. [10.0, 0.0, 30.0]
    2. [10.0, 20.0, 30.0]
    3. [10.0, 13.33, 30.0]
    4. [10.0, nan, 30.0]
    Answer

    (b) [10.0, 20.0, 30.0]

    The mean skips NaN: (10 + 30) / 2 = 20, which then fills the gap.

16. Which library provides create_engine() for connecting Pandas to MySQL?

    1. pymysql
    2. sqlalchemy
    3. numpy
    4. mysql.pandas
    Answer

    (b) sqlalchemy

    create_engine() comes from SQLAlchemy; pymysql is the driver it uses underneath.

17. In create_engine('mysql+pymysql://root:abc@localhost:3306/shop'), what is shop?

    1. The table name
    2. The password
    3. The host name
    4. The database name
    Answer

    (d) The database name

    The format is driver://user:password@host:port/database.

18. Which call reads the result of an SQL SELECT statement into a DataFrame?

    1. pd.read_sql_query(query, engine)
    2. pd.read_csv(query, engine)
    3. engine.read(query)
    4. df.to_sql(query, engine)
    Answer

    (a) pd.read_sql_query(query, engine)

    read_sql_query() runs the query through the engine and returns a DataFrame.

19. df.to_sql('marks', engine, if_exists='append', index=False) is run when the table marks already exists. What happens?

    1. ValueError is raised
    2. The old table is dropped and recreated
    3. The rows of df are added to the table
    4. Nothing happens
    Answer

    (c) The rows of df are added to the table

    append inserts the rows into the existing table. fail raises ValueError and replace recreates the table.

20. What is the default value of if_exists in to_sql()?

    1. 'append'
    2. 'replace'
    3. 'ignore'
    4. 'fail'
    Answer

    (d) 'fail'

    By default to_sql() refuses to touch an existing table and raises ValueError.

21. df has columns Item and Price. What is printed by print(df.groupby('Item')['Price'].max().loc['Pen']) if the Pen rows have prices 10, 25 and 15?

    1. 10
    2. 50
    3. 25
    4. 15
    Answer

    (c) 25

    The largest Pen price is 25.

22. Which statement is true about std() in Pandas?

    1. It divides by n
    2. It divides by n - 1 by default
    3. It is the square of the variance
    4. It works only on DataFrames
    Answer

    (b) It divides by n - 1 by default

    Pandas uses the sample formula (ddof=1). The standard deviation is the square root of the variance.

Assertion–Reason

1. Assertion (A): s.mode() can return more than one value.

   Reason (R): A data set can have two or more values that occur with the same highest frequency.

   Answer

   (A)

   Because several values can tie for most frequent, Pandas returns all of them as a Series.

2. Assertion (A): df.mean() raises TypeError in Pandas 2.x when df contains a column of names.

   Reason (R): From Pandas 2.0, numeric_only defaults to False, so text columns are included in the calculation.

   Answer

   (A)

   Pandas tries to average the names and fails; R explains why. Passing numeric_only=True avoids it.

3. Assertion (A): s.quantile(0.75) always returns one of the values present in s.

   Reason (R): By default Pandas uses linear interpolation between the two nearest values.

   Answer

   (D)

   A is false: interpolation can produce a value between two data points, e.g. 86.75. R is true.

4. Assertion (A): df.count() may return a smaller number than len(df) for a column.

   Reason (R): count() does not count NaN values.

   Answer

   (A)

   Missing values are skipped by count() but are still rows, so the count can be smaller.

5. Assertion (A): df.to_sql('result', engine) run twice raises an error the second time.

   Reason (R): to_sql() writes the DataFrame index as a column by default.

   Answer

   (B)

   Both are true. The error comes from the default if_exists='fail', not from the index, so R does not explain A.

Short answer

1. 2 marks

   Write the output:

   s = pd.Series([15, 30, 45, 60, 75])
print(s.quantile(0.25), s.quantile(0.75))
print(s[s > s.quantile(0.75)])

   Answer

   With 5 values, positions for 0.25 and 0.75 are 1 and 3, so Q1 = 30 and Q3 = 60. Only 75 lies above 60.

   30.0 60.0
   4    75
   dtype: int64

2. 2 marks

   Write the output:

   s = pd.Series([4, 9, 4, 7, 9, 4])
print(s.mode())
print(s.mean(), s.median())

   Answer

   4 occurs three times, so it is the only mode. Mean = 37 / 6. Sorted: 4, 4, 4, 7, 9, 9; median = (4 + 7) / 2.

   0    4
   dtype: int64
   6.166666666666667 5.5

3. 2 marks

   Differentiate between count() and size with an example.

   Answer

   count() is a method that returns the number of non-NaN values; size is an attribute that returns the total number of elements including NaN. For a Series of 5, NaN, 7:

   2 3

4. 2 marks

   Write the output:

   df = pd.DataFrame({'A': [1, 2, 3], 'B': [10, 20, 30]})
print(df.sum())
print(df.sum(axis=1).tolist())

   Answer

   By default sums are column-wise; axis=1 sums each row.

   A     6
   B    60
   dtype: int64
   [11, 22, 33]

5. 2 marks

   A DataFrame sales has columns Region, Product and Amount. Write statements to display (a) total Amount per Region and (b) the number of sales per Product.

   Answer

   (a) print(sales.groupby('Region')['Amount'].sum())

   (b) print(sales.groupby('Product').size()) (or sales.groupby('Product')['Amount'].count())

6. 2 marks

   Write the output:

   df = pd.DataFrame({'House': ['Red', 'Blue', 'Red', 'Blue', 'Red'], 'Pts': [5, 8, 3, 6, 4]})
print(df.groupby('House')['Pts'].sum())

   Answer

   Blue = 8 + 6 and Red = 5 + 3 + 4. Group labels are sorted and become the index; the Series is named after the column.

   House
   Blue    14
   Red     12
   Name: Pts, dtype: int64

7. 2 marks

   Write the statement to sort DataFrame emp by Dept in ascending order and, within each department, by Salary in descending order.

   Answer

   emp = emp.sort_values(by=['Dept', 'Salary'], ascending=[True, False])

8. 2 marks

   What is the difference between dropna() and fillna()? Give one example of each.

   Answer

   dropna() removes rows (or columns with axis=1) that contain missing values, e.g. df = df.dropna().

   fillna() replaces missing values with a given value, e.g. df = df.fillna(0) or df['Marks'] = df['Marks'].fillna(df['Marks'].mean()).

9. 2 marks

   Write the output:

   s = pd.Series([8, None, 4, None])
print(s.isnull().sum())
print(s.fillna(0).sum())

   Answer

   Two values are missing. After filling with 0 the sum is 12, shown as a float because the Series became float64 when None was converted to NaN.

   2
   12.0

10. 2 marks

    Why does mode() return a Series and not a single value? How will you get one value?

    Answer

    Data can have more than one most-frequent value (for example 3 and 7 both appearing twice), so Pandas returns all modes as a Series sorted in ascending order. Use s.mode()[0] (or s.mode().iloc[0]) to take the first one.

11. 2 marks

    Name the two libraries to be installed to connect Pandas with MySQL, and write the statement that creates the connection to database library on the local computer for user root with password book123.

    Answer

    pymysql (driver) and sqlalchemy (provides create_engine).

    engine = create_engine('mysql+pymysql://root:book123@localhost:3306/library')

12. 2 marks

    Explain the three values of the if_exists parameter of to_sql().

    Answer

    • 'fail' (default): raise ValueError if the table already exists.
    • 'replace': drop the existing table and create a new one with the DataFrame's data.
    • 'append': insert the DataFrame's rows into the existing table.
13. 2 marks

    Write the output:

    s = pd.Series([40, 42, 38, 40])
print(s.var(), s.std())

    Answer

    Mean = 40. Squared differences: 0, 4, 4, 0, total 8. Pandas divides by n - 1 = 3, so variance = 8/3 ≈ 2.667, and std is its square root.

    2.6666666666666665 1.632993161855452

Long answer

1. 3 marks

   Explain the split-apply-combine idea behind groupby(). For the DataFrame sales (columns Quarter, Category, Item, Expenditure), write statements to print (a) total expenditure per category, (b) the average expenditure per quarter, and (c) the category with the highest total.

   Answer

   Split the rows into groups by a column's values, apply a function (sum, mean, ...) to each group, and combine the answers into one result whose index is the group labels.

   import pandas as pd
   sales = pd.read_csv('sales.csv')
   cat = sales.groupby('Category')['Expenditure'].sum()
   print(cat)
   print(sales.groupby('Quarter')['Expenditure'].mean())
   print('Highest:', cat.idxmax())

   Output

   Category
   Lab           121500
   Sports         69500
   Stationery     64000
   Name: Expenditure, dtype: int64
   Quarter
   Q1    28833.333333
   Q2    16666.666667
   Q3    18750.000000
   Q4    40500.000000
   Name: Expenditure, dtype: float64
   Highest: Lab

2. 5 marks

   Write a program to create a Series of 12 monthly electricity bills (in rupees) of a house and display: the mean, median, highest and lowest bill, the month of the highest bill, and the months whose bill is above the 75th percentile.

   Answer

   The months are used as the index, so idxmax() returns the month and the filter keeps month labels.

   import pandas as pd
   bills = pd.Series([1450, 1380, 1720, 2650, 3480, 3910, 3020, 2780, 2410, 1890, 1560, 1620],
                     index=['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                            'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])
   print('Mean:', round(bills.mean(), 2), 'Median:', bills.median())
   print('Highest:', bills.max(), 'in', bills.idxmax(), '| Lowest:', bills.min())
   q3 = bills.quantile(0.75)
   print('75th percentile:', q3)
   print(bills[bills > q3])

   Output

   Mean: 2322.5 Median: 2150.0
   Highest: 3910 in Jun | Lowest: 1380
   75th percentile: 2840.0
   May    3480
   Jun    3910
   Jul    3020
   dtype: int64

3. 3 marks

   A unit-test sheet has missing marks for absent students. Explain three ways of handling missing values in Pandas with code, and state when each is suitable.

   Answer

   1. Drop the rows (dropna()): when only a few rows are incomplete and they can be left out of the analysis.
   2. Fill with a fixed value (fillna(0)): when missing truly means zero, e.g. an absent student scored nothing.
   3. Fill with an estimate (fillna(df.mean())): when the value exists but was not recorded and an approximate value is acceptable.

   import pandas as pd
   import numpy as np
   ut = pd.DataFrame({'Maths': [18, np.nan, 22, 15], 'Science': [20, 17, np.nan, 19]},
                     index=['Aditi', 'Bharat', 'Chirag', 'Devika'])
   print(ut.dropna())
   print(ut.fillna(0))
   print(ut.fillna(ut.mean()).round(2))

   Output

           Maths  Science
   Aditi    18.0     20.0
   Devika   15.0     19.0
           Maths  Science
   Aditi    18.0     20.0
   Bharat    0.0     17.0
   Chirag   22.0      0.0
   Devika   15.0     19.0
           Maths  Science
   Aditi   18.00    20.00
   Bharat  18.33    17.00
   Chirag  22.00    18.67
   Devika  15.00    19.00

4. 5 marks

   Write a program that reads the student table (RollNo, Name, Section, Marks) of MySQL database school into a DataFrame, adds a column Grade ('A' for marks 80 and above, otherwise 'B'), and writes the result to a new MySQL table student_grade without the index. Explain each step.

   Answer

   1. create_engine() builds the connection from the connection string.
   2. read_sql_query() runs the SELECT and returns a DataFrame.
   3. A list comprehension over the Marks column builds the Grade column.
   4. to_sql() with if_exists='replace' creates (or recreates) the table; index=False keeps the 0, 1, 2 labels out of it.

   The final print reads the new table back to check it.

   import pandas as pd
   from sqlalchemy import create_engine
   engine = create_engine('mysql+pymysql://root:tiger@localhost:3306/school')
   df = pd.read_sql_query('SELECT * FROM student', engine)
   df['Grade'] = ['A' if m >= 80 else 'B' for m in df['Marks']]
   df.to_sql('student_grade', engine, if_exists='replace', index=False)
   print(pd.read_sql_query('SELECT * FROM student_grade', engine))

   Output

      RollNo    Name Section  Marks Grade
   0    1201   Aditi     12A     88     A
   1    1202  Bharat     12B     67     B
   2    1203  Chirag     12A     92     A
   3    1204  Devika     12C     75     B
   4    1205   Eshan     12B     59     B
   5    1206  Fatima     12C     81     A

5. 3 marks

   Write the output of the following code:

   df = pd.DataFrame({'Name': ['Ira', 'Om', 'Pia', 'Raj'], 'Sec': ['A', 'B', 'A', 'B'], 'Marks': [72, 85, 90, 64]})
print(df.sort_values(by='Marks', ascending=False).head(2))
print(df.groupby('Sec')['Marks'].mean())
print(df['Marks'].describe()[['min', 'max']])

   Answer

   Sorting keeps the original row labels (2 and 1). Section A's mean is (72 + 90) / 2 = 81 and B's is (85 + 64) / 2 = 74.5. describe() returns floats, so min and max print as 64.0 and 90.0.

     Name Sec  Marks
   2  Pia   A     90
   1   Om   B     85
   Sec
   A    81.0
   B    74.5
   Name: Marks, dtype: float64
   min    64.0
   max    90.0
   Name: Marks, dtype: float64

6. 3 marks

   Compare mean, median and mode as measures of the "typical" value, using the daily wages (in rupees) of seven workers: 450, 480, 450, 500, 470, 450, 2500. Write code to compute all three and comment on which best represents the group.

   Answer

   One very high wage (2500) pulls the mean up to about 757, which no worker actually earns near. The median (470) and the mode (450) ignore the extreme value, so they describe a typical worker better. The median is usually the best single measure for data with outliers.

   import pandas as pd
   wages = pd.Series([450, 480, 450, 500, 470, 450, 2500])
   print(round(wages.mean(), 2))
   print(wages.median())
   print(wages.mode()[0])

   Output

   757.14
   470.0
   450

7. 5 marks

   Write a program to create a DataFrame of six cricket players (Name, Team, Runs, Matches) using sample data. Display (a) the describe() summary, (b) players sorted by Runs (highest first), (c) team-wise total runs and number of players using agg(), (d) players whose Runs are above the 75th percentile.

   Answer

   describe() summarises the numeric columns Runs and Matches; sorting, agg() and a quantile filter answer the other parts.

   import pandas as pd
   ipl = pd.DataFrame({'Name': ['Kohli', 'Gill', 'Jaiswal', 'Rinku', 'Samson', 'Gaikwad'],
                       'Team': ['RCB', 'GT', 'RR', 'KKR', 'RR', 'CSK'],
                       'Runs': [741, 426, 435, 168, 531, 583],
                       'Matches': [15, 12, 16, 14, 16, 14]})
   print(ipl.describe())
   print(ipl.sort_values(by='Runs', ascending=False)[['Name', 'Runs']])
   print(ipl.groupby('Team')['Runs'].agg(['sum', 'count']))
   print(ipl[ipl['Runs'] > ipl['Runs'].quantile(0.75)]['Name'].tolist())

   Output

                Runs    Matches
   count    6.000000   6.000000
   mean   480.666667  14.500000
   std    191.631591   1.516575
   min    168.000000  12.000000
   25%    428.250000  14.000000
   50%    483.000000  14.500000
   75%    570.000000  15.750000
   max    741.000000  16.000000
         Name  Runs
   0    Kohli   741
   5  Gaikwad   583
   4   Samson   531
   2  Jaiswal   435
   1     Gill   426
   3    Rinku   168
         sum  count
   Team            
   CSK   583      1
   GT    426      1
   KKR   168      1
   RCB   741      1
   RR    966      2
   ['Kohli', 'Gaikwad']

Case-based

Practicals

import pandas as pd
runs = pd.Series([34, 12, 87, 45, 102, 8, 66, 59, 23, 91, 40, 77],
                 index=['I' + str(i) for i in range(1, 13)])
q3 = runs.quantile(0.75)
print('75th percentile:', q3)
above = runs[runs > q3]
print(above)
print('Innings above 75th percentile:', above.count())

75th percentile: 79.5
I3      87
I5     102
I10     91
dtype: int64
Innings above 75th percentile: 3

import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales[['Category', 'Item', 'Expenditure']])
total = sales.groupby('Category')['Expenditure'].sum()
print(total.sort_values(ascending=False))

     Category            Item  Expenditure
0  Stationery       Registers        18500
1      Sports    Cricket kits        42000
2         Lab       Chemicals        26000
3  Stationery     Chalk boxes         6500
4         Lab       Glassware        31500
5      Sports       Footballs        12000
6  Stationery   Printer paper        22000
7      Sports  Badminton sets        15500
8         Lab     Microscopes        64000
9  Stationery       Registers        17000
Category
Lab           121500
Sports         69500
Stationery     64000
Name: Expenditure, dtype: int64

import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales.groupby('Category')['Expenditure'].agg(['sum', 'mean', 'max', 'count']))
qc = sales.groupby(['Quarter', 'Category'])['Expenditure'].sum().reset_index()
print(qc)

               sum          mean    max  count
Category                                      
Lab         121500  40500.000000  64000      3
Sports       69500  23166.666667  42000      3
Stationery   64000  16000.000000  22000      4
  Quarter    Category  Expenditure
0      Q1         Lab        26000
1      Q1      Sports        42000
2      Q1  Stationery        18500
3      Q2         Lab        31500
4      Q2      Sports        12000
5      Q2  Stationery         6500
6      Q3      Sports        15500
7      Q3  Stationery        22000
8      Q4         Lab        64000
9      Q4  Stationery        17000

import pandas as pd
res = pd.DataFrame({'Name': ['Aditi', 'Bharat', 'Chirag', 'Devika', 'Eshan', 'Fatima'],
                    'Maths': [88, 67, 92, 75, 59, 81],
                    'Science': [79, 72, 95, 81, 64, 81],
                    'English': [91, 58, 84, 77, 70, 86]})
print(res.index.tolist(), res.columns.tolist())
print(res.dtypes)
print('Shape:', res.shape)
marks = res[['Maths', 'Science', 'English']]
print(pd.DataFrame({'Max': marks.max(), 'Min': marks.min(),
                    'Mean': marks.mean().round(2), 'Median': marks.median()}))
res['Total'] = marks.sum(axis=1)
print(res[['Name', 'Total']])
print('Topper:', res.loc[res['Total'].idxmax(), 'Name'])

[0, 1, 2, 3, 4, 5] ['Name', 'Maths', 'Science', 'English']
Name       object
Maths       int64
Science     int64
English     int64
dtype: object
Shape: (6, 4)
         Max  Min   Mean  Median
Maths     92   59  77.00    78.0
Science   95   64  78.67    80.0
English   91   58  77.67    80.5
     Name  Total
0   Aditi    258
1  Bharat    197
2  Chirag    271
3  Devika    233
4   Eshan    193
5  Fatima    248
Topper: Chirag

import pandas as pd
df = pd.DataFrame({'Name': ['Asha', 'Bilal', 'Charu', 'Dhruv', 'Ekta',
                            'Faiz', 'Gauri', 'Harsh', 'Indu', 'Jatin'],
                   'Maths': [56, 78, 91, 45, 67, 88, 72, 95, 60, 83],
                   'Science': [61, 74, 89, 52, 70, 81, 77, 92, 58, 85]})
print(df.describe())
print(df.quantile([0.25, 0.75], numeric_only=True))
print(df[df['Maths'] > df['Maths'].quantile(0.75)]['Name'].tolist())

           Maths    Science
count  10.000000  10.000000
mean   73.500000  73.900000
std    16.446884  13.551958
min    45.000000  52.000000
25%    61.750000  63.250000
50%    75.000000  75.500000
75%    86.750000  84.000000
max    95.000000  92.000000
      Maths  Science
0.25  61.75    63.25
0.75  86.75    84.00
['Charu', 'Faiz', 'Harsh']

import pandas as pd
df = pd.DataFrame({'Name': ['Kiran', 'Laila', 'Manav', 'Nidhi', 'Omar', 'Prachi', 'Rehan', 'Simran'],
                   'Section': ['B', 'A', 'A', 'C', 'B', 'C', 'A', 'B'],
                   'Percent': [81.5, 92.4, 67.0, 88.8, 74.2, 95.1, 79.9, 86.3]})
rank = df.sort_values(by='Percent', ascending=False).reset_index(drop=True)
rank.insert(0, 'Rank', range(1, len(rank) + 1))
print(rank)
print(rank.head(3))
print(df.sort_values(by=['Section', 'Percent'], ascending=[True, False]))

   Rank    Name Section  Percent
0     1  Prachi       C     95.1
1     2   Laila       A     92.4
2     3   Nidhi       C     88.8
3     4  Simran       B     86.3
4     5   Kiran       B     81.5
5     6   Rehan       A     79.9
6     7    Omar       B     74.2
7     8   Manav       A     67.0
   Rank    Name Section  Percent
0     1  Prachi       C     95.1
1     2   Laila       A     92.4
2     3   Nidhi       C     88.8
     Name Section  Percent
1   Laila       A     92.4
6   Rehan       A     79.9
2   Manav       A     67.0
7  Simran       B     86.3
0   Kiran       B     81.5
4    Omar       B     74.2
5  Prachi       C     95.1
3   Nidhi       C     88.8

import pandas as pd
import numpy as np
att = pd.DataFrame({'W1': [6, 5, np.nan, 6, 4],
                    'W2': [5, np.nan, np.nan, 6, 5],
                    'W3': [6, 6, 4, np.nan, 5],
                    'W4': [6, 5, 5, 6, np.nan]},
                   index=['Aryan', 'Bhavya', 'Charvi', 'Daksh', 'Eva'])
print(att.isnull().sum())
print('Total missing:', att.isnull().sum().sum())
print(att[att.isnull().any(axis=1)])
print(att.fillna(att.mean()).round(1))
print(att.dropna(thresh=3))

W1    1
W2    2
W3    1
W4    1
dtype: int64
Total missing: 5
         W1   W2   W3   W4
Bhavya  5.0  NaN  6.0  5.0
Charvi  NaN  NaN  4.0  5.0
Daksh   6.0  6.0  NaN  6.0
Eva     4.0  5.0  5.0  NaN
         W1   W2   W3   W4
Aryan   6.0  5.0  6.0  6.0
Bhavya  5.0  5.3  6.0  5.0
Charvi  5.2  5.3  4.0  5.0
Daksh   6.0  6.0  5.2  6.0
Eva     4.0  5.0  5.0  5.5
         W1   W2   W3   W4
Aryan   6.0  5.0  6.0  6.0
Bhavya  5.0  NaN  6.0  5.0
Daksh   6.0  6.0  NaN  6.0
Eva     4.0  5.0  5.0  NaN

import pandas as pd
sales = pd.read_csv('sales.csv')
print(sales[sales.duplicated(subset=['Category', 'Item'])])
clean = sales.drop_duplicates(subset=['Category', 'Item'])
print(clean[clean['Expenditure'] > clean['Expenditure'].mean()])
tot = clean.groupby('Category')['Expenditure'].sum().reset_index()
tot.to_csv('category_total.csv', index=False)
print(pd.read_csv('category_total.csv'))

  Quarter    Category       Item  Expenditure
9      Q4  Stationery  Registers        17000
  Quarter Category          Item  Expenditure
1      Q1   Sports  Cricket kits        42000
4      Q2      Lab     Glassware        31500
8      Q4      Lab   Microscopes        64000
     Category  Expenditure
0         Lab       121500
1      Sports        69500
2  Stationery        47000

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:tiger@localhost:3306/school')
df = pd.read_sql_query('SELECT * FROM student', engine)
print(df)
print(df.groupby('Section')['Marks'].agg(['max', 'mean']))
low = pd.read_sql_query('SELECT RollNo, Name, Marks FROM student WHERE Marks < 70', engine)
print(low)

   RollNo    Name Section  Marks
0    1201   Aditi     12A     88
1    1202  Bharat     12B     67
2    1203  Chirag     12A     92
3    1204  Devika     12C     75
4    1205   Eshan     12B     59
5    1206  Fatima     12C     81
         max  mean
Section           
12A       92  90.0
12B       67  63.0
12C       81  78.0
   RollNo    Name  Marks
0    1202  Bharat     67
1    1205   Eshan     59

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:tiger@localhost:3306/school')
hp = pd.DataFrame({'House': ['Shivaji', 'Tagore', 'Raman'],
                   'Captain': ['Arnav', 'Mehak', 'Yuvraj'],
                   'Points': [412, 389, 455]})
hp.to_sql('house_points', engine, if_exists='replace', index=False)
extra = pd.DataFrame({'House': ['Kalam'], 'Captain': ['Zara'], 'Points': [430]})
extra.to_sql('house_points', engine, if_exists='append', index=False)
print(pd.read_sql_query('SELECT * FROM house_points ORDER BY Points DESC', engine))

     House Captain  Points
0    Raman  Yuvraj     455
1    Kalam    Zara     430
2  Shivaji   Arnav     412
3   Tagore   Mehak     389

Common mistakes

• Writing df.mean() on a DataFrame with a Name column. Pandas 2.0 and later raise TypeError; write df.mean(numeric_only=True) or select the numeric columns first.

• Treating mode() as a single number. It returns a Series (there can be several modes); use s.mode()[0] when one value is needed.

• Confusing count() with size/len(). count() skips NaN, so it can be smaller than the number of rows.

• Expecting quantile(0.75) to be one of the data values. Pandas interpolates, so 86.75 is a valid answer for whole-number marks.

• Writing sales.groupby('Category').sum() when the table has text columns. Select the column first: sales.groupby('Category')['Expenditure'].sum().

• Searching for missing values with df['Marks'] == np.nan. NaN is never equal to anything; use df['Marks'].isnull().

• Calling df.sort_values('Total') or df.fillna(0) and expecting df to change. These return new DataFrames; assign the result back.

• Forgetting index=False in to_sql(), which adds an unwanted index column to the MySQL table, or running to_sql() twice with the default if_exists='fail' and getting ValueError.