3. Descriptive Stats, GroupBy & CSV I/O

CBSE Syllabus Alignment • Subject Code 065
Descriptive statistics: max, min, count, sum, mean, median, mode, quantile, std, var.
Data aggregation, groupby(), sorting by values and index.
Handling missing values: isna(), notna(), dropna(), fillna().
Importing and exporting data between CSV files and DataFrames (read_csv, to_csv).

1. Descriptive Statistics on DataFrames

Pandas provides a rich collection of mathematical and statistical aggregation functions. By default, statistical operations compute column-wise summaries (axis=0).

Given sample DataFrame sales_df:

python

import pandas as pd
import numpy as np

data = {
    'Quarter': ['Q1', 'Q2', 'Q3', 'Q4'],
    'North': [45000, 52000, 61000, 75000],
    'South': [38000, np.nan, 49000, 62000],
    'West':  [29000, 31000, 42000, 48000]
}
df = pd.DataFrame(data)

Method	Description	`axis=0` (Default, Column-Wise)	`axis=1` (Row-Wise)
`df.count()`	Number of non-null elements	Counts non-nulls in each column	Counts non-nulls in each row
`df.sum()`	Sum of all values	Total sum per region column	Total quarterly sales across regions
`df.mean()`	Arithmetic average ( $\bar{x}$ )	Average sales for North, South, West	Average sales in each quarter
`df.median()`	50th percentile / middle value	Median of each column	Median of each row
`df.min()` / `max()`	Minimum and Maximum values	Min/Max per column	Min/Max per row
`df.std()` / `var()`	Sample standard deviation & variance	Spread of sales across quarters	Spread across regions
`df.mode()`	Most frequently occurring value	Returns mode DataFrame	Returns row modes
`df.quantile(0.75)`	75th percentile cutoff value	75% cutoff for each column	75% cutoff for each row

python

# Column-wise mean (default axis=0):
print(df[['North', 'West']].mean())
# North    58250.0
# West     37500.0
# dtype: float64

# Row-wise sum across regions for each quarter:
df['Total_Sales'] = df[['North', 'South', 'West']].sum(axis=1)

The `describe()` Function:

Returns a quick statistical summary DataFrame for all numeric columns:

python

print(df.describe())
# Displays: count, mean, std, min, 25%, 50%, 75%, max

2. Sorting Data: `sort_values()` and `sort_index()`

1. Sorting by Column Values

python

# Sort students by IP_Score in descending order:
df_sorted = df.sort_values(by='North', ascending=False)

# Sort by multiple columns:
df_multi_sort = df.sort_values(by=['North', 'West'], ascending=[True, False])

2. Sorting by Index Labels

python

# Sort rows by index alphabetically:
df_index_sorted = df.sort_index(axis=0, ascending=True)

# Sort column names alphabetically:
df_cols_sorted = df.sort_index(axis=1, ascending=True)

3. Data Aggregation with `groupby()`

The groupby() method allows you to group data by categorical values and compute aggregate metrics over subsets.

python

employees = pd.DataFrame({
    'Dept': ['CS', 'IT', 'CS', 'Accounts', 'IT', 'Accounts'],
    'Name': ['Amit', 'Sunita', 'Pooja', 'Ravi', 'Sneha', 'Manoj'],
    'Salary': [75000, 82000, 68000, 54000, 91000, 48000],
    'Experience': [5, 7, 3, 4, 9, 2]
})

# 1. Calculate Average Salary per Department:
dept_avg = employees.groupby('Dept')['Salary'].mean()
print(dept_avg)
# Output:
# Dept
# Accounts    51000.0
# CS          71500.0
# IT          86500.0
# Name: Salary, dtype: float64

# 2. Multiple aggregates per group:
dept_summary = employees.groupby('Dept').agg({
    'Salary': ['mean', 'max'],
    'Name': 'count'
})

4. Handling Missing Data (`NaN`)

Missing data is represented in Pandas by NaN (Not a Number) or None.

Step 1: Detecting Missing Data (`isna` / `notna`)

python

# Boolean DataFrame of missing values:
print(df.isna())        # or df.isnull()

# Total missing values per column:
print(df.isna().sum())
# Quarter    0
# North      0
# South      1
# West       0
# dtype: int64

Step 2: Dropping Missing Values (`dropna`)

python

# Drop any row containing at least one NaN:
clean_rows = df.dropna(axis=0, how='any')

# Drop row only if ALL elements in that row are NaN:
clean_all = df.dropna(axis=0, how='all')

# Drop row if NaN is in a specific subset of columns:
clean_subset = df.dropna(subset=['South'])

Step 3: Filling Missing Values (`fillna`)

python

# Fill all NaNs with scalar 0:
df_filled = df.fillna(0)

# Fill missing South sales with the column mean:
df['South'].fillna(df['South'].mean(), inplace=True)

5. CSV File I/O: `read_csv()` and `to_csv()`

Comma-Separated Values (CSV) are standard plain-text files used to import and export tabular datasets.

mermaid

graph LR
    A["cbse_students.csv<br/>(Disk File)"] -- pd.read_csv() --> B["Pandas DataFrame<br/>(RAM Memory)"]
    B -- df.to_csv() --> C["processed_report.csv<br/>(Exported File)"]

1. Reading CSV into a DataFrame (`pd.read_csv`)

python

import pandas as pd

# Standard CSV read:
df = pd.read_csv('students.csv')

# Specifying custom delimiter (e.g., semicolon or tab):
df_tab = pd.read_csv('data.tsv', sep='\t')

# Using a specific column as the row index:
df_indexed = pd.read_csv('students.csv', index_col='RollNo')

# Skipping unwanted top rows:
df_skip = pd.read_csv('students.csv', skiprows=2)

# Reading a file with NO header row:
df_no_head = pd.read_csv('raw_data.csv', header=None, names=['ID', 'Score', 'Status'])

2. Exporting DataFrame to CSV (`df.to_csv`)

python

# Export DataFrame to CSV:
df.to_csv('final_results.csv')

# Export WITHOUT writing the row index labels:
df.to_csv('final_results_clean.csv', index=False)

# Export with custom delimiter and custom header suppression:
df.to_csv('custom_output.txt', sep='|', header=True, index=False)

TIP

CBSE Practical Exam Best Practice: Always remember to set index=False when saving a DataFrame to CSV if you do not want an extra unnamed index column 0, 1, 2... generated in the CSV file!

Active Recall & Syntax Review

⚡ Pandas & SQL Query Explorer

Interactive Pandas Method & SQL Function Matchmaker

Search and filter across the complete CBSE IP 065 library of Pandas Methods and MySQL Functions. Inspect syntax, parameters, return types, and typical exam pitfalls.

df.loc[ ]Pandas

DataFrame Slicing & Indexing➔Series or DataFrame

💻 Syntax & Usage:df.loc[row_label_start : row_label_end, [col1, col2]]

⚙️ Mechanism & Purpose:Label-based data selection and slicing. Accesses a group of rows and columns by labels or a boolean array.

⚠️ CBSE Board Trap:Both start AND end index labels are strictly included in the output slice.

df.iloc[ ]Pandas

DataFrame Slicing & Indexing➔Series or DataFrame

💻 Syntax & Usage:df.iloc[row_pos_start : row_pos_end, col_pos_start : col_pos_end]

⚙️ Mechanism & Purpose:Integer position-based selection. Purely 0-based positional indexing by integer locations.

⚠️ CBSE Board Trap:The stop index integer is strictly EXCLUDED (Python slice convention [start, stop)).

df.dropna( )Pandas

Missing Data & Cleaning➔Cleaned DataFrame

💻 Syntax & Usage:df.dropna(axis=0, how='any', subset=None, inplace=False)

⚙️ Mechanism & Purpose:Removes missing values (NaN / None) along rows (axis=0) or columns (axis=1).

⚠️ CBSE Board Trap:Does NOT modify original DataFrame unless inplace=True is explicitly set!

df.fillna( )Pandas

Missing Data & Cleaning➔Filled DataFrame

💻 Syntax & Usage:df.fillna(value, inplace=False)

⚙️ Mechanism & Purpose:Replaces all missing/NaN values with a specified scalar constant or dictionary of column defaults.

⚠️ CBSE Board Trap:Omitting inplace=True leaves the original DataFrame with NaNs unchanged.

df.groupby( )Pandas

Data Aggregation & GroupBy➔DataFrameGroupBy Object

💻 Syntax & Usage:df.groupby('Column')['Target'].mean()

⚙️ Mechanism & Purpose:Splits DataFrame into groups based on unique values in a column, applies aggregation, and combines results.

⚠️ CBSE Board Trap:Calling groupby() alone does not produce a DataFrame; you must chain an aggregation function like .sum() or .mean().

pd.read_csv( )Pandas

CSV I/O & File Handling➔DataFrame

💻 Syntax & Usage:pd.read_csv('filename.csv', sep=',', header=0)

⚙️ Mechanism & Purpose:Reads a comma-separated values (CSV) text file into a 2D Pandas DataFrame structure.

⚠️ CBSE Board Trap:Ensure file path is accurate. If no header row exists in the CSV, pass header=None.

SUBSTRING() / MID()SQL

SQL String Functions➔String (VARCHAR)

💻 Syntax & Usage:SUBSTRING(str, pos, len) -- or MID(str, pos, len)

⚙️ Mechanism & Purpose:Extracts a substring of length len starting at character index pos.

⚠️ CBSE Board Trap:MySQL is strictly 1-BASED! Position 1 is the very first character, not index 0.

INSTR()SQL

SQL String Functions➔Integer Position (1-based)

💻 Syntax & Usage:INSTR(string, substring_to_find)

⚙️ Mechanism & Purpose:Returns the 1-based index position of the first occurrence of substring in string. Returns 0 if not found.

⚠️ CBSE Board Trap:Argument order is INSTR(source_str, search_str), NOT the reverse! Returns 0 (not -1 or NULL) if match fails.

ROUND()SQL

SQL Math Functions➔Numeric / Decimal

💻 Syntax & Usage:ROUND(number, decimal_places)

⚙️ Mechanism & Purpose:Rounds a number to a specified number of decimal places (or to nearest integer if places omitted).

⚠️ CBSE Board Trap:ROUND(15.678, -1) rounds to the nearest tens place, yielding 20.

MOD()SQL

SQL Math Functions➔Integer Remainder

💻 Syntax & Usage:MOD(dividend, divisor) -- or N % M

⚙️ Mechanism & Purpose:Calculates the remainder of dividend divided by divisor.

⚠️ CBSE Board Trap:MOD(25, 7) yields 4 (since 25 = 7*3 + 4).

MONTHNAME() / DAYNAME()SQL

SQL Date & Time Functions➔String Month/Day Name

💻 Syntax & Usage:MONTHNAME('YYYY-MM-DD'), DAYNAME('YYYY-MM-DD')

⚙️ Mechanism & Purpose:Returns the full English name of the month (e.g. 'September') or weekday (e.g. 'Saturday').

⚠️ CBSE Board Trap:Date format string MUST be 'YYYY-MM-DD'. Using DD-MM-YYYY causes incorrect output or NULL.

COUNT(*)SQL

SQL Aggregate Functions➔Integer Total Rows

💻 Syntax & Usage:SELECT COUNT(*) FROM TableName [WHERE condition];

⚙️ Mechanism & Purpose:Counts the total number of rows meeting the condition, including rows containing NULLs in columns.

⚠️ CBSE Board Trap:COUNT(*) counts all rows; COUNT(column_name) ignores NULL rows in that specific column.

💥 Myth vs Computing Reality

Information Practices Misconception Buster

Click any common student misconception to see why intuition fails in CBSE IP board output questions.

❌ Myth 1

"COUNT(*) and COUNT(column_name) always return the exact same integer in SQL."

▼

❌ Myth 2

"Pandas df.loc[1:3] and df.iloc[1:3] select the exact same rows."

▼

❌ Myth 3

"In MySQL, SUBSTRING('INFORMATICS', 3, 4) starts at index 3 with 0-based indexing."

▼

❌ Myth 4

"The WHERE clause can be used to filter aggregate values like WHERE AVG(Marks) > 80."

▼

❌ Myth 5

"Adding two Pandas Series with different indices throws a ValueError."

▼

❌ Myth 6

"A Hub and a Switch transmit network packets to target computers in the identical way."

▼

3. Descriptive Stats, GroupBy & CSV I/O ​

1. Descriptive Statistics on DataFrames ​

The describe() Function: ​

2. Sorting Data: sort_values() and sort_index() ​

1. Sorting by Column Values ​

2. Sorting by Index Labels ​

3. Data Aggregation with groupby() ​

4. Handling Missing Data (NaN) ​

Step 1: Detecting Missing Data (isna / notna) ​

Step 2: Dropping Missing Values (dropna) ​

Step 3: Filling Missing Values (fillna) ​

5. CSV File I/O: read_csv() and to_csv() ​

1. Reading CSV into a DataFrame (pd.read_csv) ​

2. Exporting DataFrame to CSV (df.to_csv) ​

Active Recall & Syntax Review ​

Interactive Pandas Method & SQL Function Matchmaker

Information Practices Misconception Buster

3. Descriptive Stats, GroupBy & CSV I/O

1. Descriptive Statistics on DataFrames

The `describe()` Function:

2. Sorting Data: `sort_values()` and `sort_index()`

1. Sorting by Column Values

2. Sorting by Index Labels

3. Data Aggregation with `groupby()`

4. Handling Missing Data (`NaN`)

Step 1: Detecting Missing Data (`isna` / `notna`)

Step 2: Dropping Missing Values (`dropna`)

Step 3: Filling Missing Values (`fillna`)

5. CSV File I/O: `read_csv()` and `to_csv()`

1. Reading CSV into a DataFrame (`pd.read_csv`)

2. Exporting DataFrame to CSV (`df.to_csv`)

Active Recall & Syntax Review