Chapter 3 — Data Handling Using Pandas I

What is pandas?
Pandas is a Python library used for data handling and analysis. It provides fast, flexible data structures (Series and DataFrame) designed to work with structured/tabular data like CSV files, Excel sheets and SQL tables. Pandas builds on NumPy and integrates well with Matplotlib and Seaborn for visualization.

Core data structures

• Series — a one-dimensional labeled array (like a column). Example: pd.Series([10,20,30], index=['a','b','c']).
• DataFrame — a two-dimensional labeled table with rows and columns (like a spreadsheet). Example: pd.DataFrame({'Name':['A','B'],'Marks':[85,90]}).

Getting started

• Import: import pandas as pd
• Read CSV: df = pd.read_csv('data.csv')
• Basic view: df.head(), df.tail(), df.info(), df.describe()

Selecting data

• Column access: df['column_name']
• Row/column by labels: df.loc[2, 'column_name']
• Row/column by integer positions: df.iloc[0:5, 0:3]
• Filtering: df[df['marks'] > 50]

Handling missing data

• Detect: df.isnull().sum()
• Drop missing rows/columns: df.dropna()
• Fill missing values: df.fillna(value)

Basic operations and aggregation

• Summary stats: df.mean(), df.sum(), df.count()
• Group and aggregate: df.groupby('class')['marks'].mean()
• Sorting: df.sort_values(by='marks', ascending=False)
• Combine tables: pd.concat([df1, df2]), df1.merge(df2, on='id', how='inner')

Input/Output
Read and write common formats: CSV, Excel, JSON. Example export: df.to_csv('out.csv', index=False).

Why pandas is useful (real-life contexts)
Pandas simplifies data cleaning, transformation, analysis and prepares data for visualization or machine learning. It is widely used in business (sales analysis), education (marks and attendance), science (weather/stocks), and many other domains.

Short code example

import pandas as pd
# load data
df = pd.read_csv('students.csv')
# view and clean
print(df.head())
print(df.isnull().sum())
df['marks'] = df['marks'].fillna(df['marks'].mean())
# group and get average
print(df.groupby('class')['marks'].mean())
# save cleaned data
df.to_csv('students_clean.csv', index=False)

Definition: A Pandas Series is a one-dimensional labeled array capable of holding any data type (integers, floats, strings, Python objects, etc.). Each element has an associated index (labels). Think of it as a column in a spreadsheet or a 1‑D numpy array with labels.

Structure:

• Values: the actual data (numpy array under the hood).
• Index: labels for each value (can be numbers, strings, dates).
• dtype: data type of the values.

Creating a Series (examples):

import pandas as pd
s1 = pd.Series([10, 20, 30])                     # default index 0,1,2
s2 = pd.Series([10, 20, 30], index=['a','b','c']) # custom index
s3 = pd.Series({'a': 10, 'b': 20})               # from dict (keys -> index)

Indexing and selection:

• Label-based: s['a'] or s.loc['a']
• Position-based: s.iloc[0]
• Slicing: s[1:4] or label slices with .loc
• Boolean indexing: s[s > 50] returns values satisfying the condition

Vectorized operations: operations apply elementwise (fast because backed by numpy). Example: s + 10, s * 2, or combining Series with aligned indexes: s1 + s2 (indexes aligned, missing labels produce NaN).

Aggregations & statistics: common methods include sum(), mean(), median(), min(), max(), std(), describe() (summary).

Missing data: missing entries are represented by NaN. Useful methods: isnull(), dropna(), fillna(value).

Index alignment & reindexing: when doing arithmetic, Series align on index labels. Use s.reindex(new_index) to change or add labels (new positions get NaN).

Useful attributes and methods: index, values, dtype, head(), tail(), unique(), value_counts(), sort_values(), sort_index().

Series vs DataFrame: A Series is a single column (1‑D). A DataFrame is 2‑D (multiple Series sharing the same index).

Small code examples (handling & plotting):

# handling missing values
s = pd.Series([10, None, 30])
s_filled = s.fillna(0)

# basic aggregation
mean_val = s.mean()

# plotting (requires matplotlib)
s.plot(kind='line')

Pedagogical note: Emphasize labeled access (.loc) vs position (.iloc) and how operations align by index — this is central to avoiding mistakes when combining Series.

What is a DataFrame?
A DataFrame (pandas.DataFrame) is a two-dimensional, tabular data structure in Python's pandas library. It stores data in rows and columns with labels (index and column names). Think of it like a spreadsheet or SQL table: each column can have its own data type (numeric, string, boolean, datetime).

Main components

• Index: row labels (default 0..n-1 or custom labels)
• Columns: column labels (names)
• Values: the actual 2D array of data
• dtypes: data type of each column

Common ways to create a DataFrame
From a Python dict, list of dicts, list of lists, NumPy array, CSV (pd.read_csv), Excel (pd.read_excel), SQL query, etc.

Essential methods & attributes

• df.head(n), df.tail(n) — view first/last rows
• df.shape — (rows, columns)
• df.info() — summary (non-null counts and dtypes)
• df.describe() — summary statistics for numeric cols
• df.dtypes, df.columns, df.index
• df['col'] or df.col — select column (Series)
• df[['c1','c2']] — select multiple columns (DataFrame)
• df.loc[row_label, col_label] — label-based selection
• df.iloc[row_pos, col_pos] — position-based selection
• df[df['col'] > value] — boolean filtering
• df.groupby('col').agg({'num_col':'mean'}) — aggregation by groups
• df.isnull(), df.isnull().sum(), df.dropna(), df.fillna(value) — missing data
• df.sort_values(by='col'), df.drop(columns=['col']), df.rename(columns={'old':'new'})

Why DataFrame is useful (real-life view)
DataFrames let you load, inspect, clean, transform, analyze and visualize tabular data efficiently — from exam marks, sales records, patient data, weather logs to finance and survey responses.

Small code examples (illustrative)

# create from dict
import pandas as pd
data = {'Roll':[1,2,3], 'Name':['A','B','C'], 'Math':[78,85,90]}
df = pd.DataFrame(data)

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

# add column: total and percentage
df['Total'] = df[['Math']].sum(axis=1)
df['Percent'] = df['Total'] / 100 * 100  # if total marks = 100

# filter: students scoring >80 in Math
high = df[df['Math'] > 80]

# group & aggregate example (sales by region)
# sales_df.groupby('Region')['Amount'].sum()

What is I/O in Pandas?
Input/Output (I/O) means reading data from external sources into a Pandas DataFrame and writing DataFrame contents back to files or databases. Typical formats: CSV, Excel, JSON, SQL, HTML, and compressed files. Pandas provides high-level functions (pd.read_csv, pd.read_excel, pd.read_json, pd.read_sql, DataFrame.to_csv, to_excel, to_json, to_sql) that handle parsing, types, missing values, and performance options.

Basic workflow
1) Read: load raw data into DataFrame. 2) Inspect: df.head(), df.info(), df.describe(), df.isnull().sum(). 3) Clean/convert types: use dtype, parse_dates, converters, fillna/dropna. 4) Write: export cleaned data with options (index, header, encoding).

Key options and best practices

• Specify encoding (encoding='utf-8' or 'latin1') to avoid errors.
• Use parse_dates=['date_col'] to convert date strings into datetime dtype on load.
• Set dtype or use converters to ensure numeric columns load correctly and to save memory.
• Use usecols to load only required columns and nrows/skiprows for sampling or large files.
• For very large files, use iterator=True with chunksize or low_memory=False and process in chunks.
• Handle missing values during read: na_values=['', 'NA', 'n/a'] and keep_default_na options.
• When writing, use index=False to avoid saving the DataFrame index as a separate column unless required.
• For Excel, read/write multiple sheets using sheet_name parameter (can be name, index, or list).
• When interacting with SQL, use SQLAlchemy engine and pd.read_sql(sql, con) / df.to_sql(name, con, if_exists='replace', index=False).

Error handling and performance tips
If read_csv raises parsing errors, try specifying delimiter (sep), quoting, engine='python', or specifying column dtypes. For memory issues, convert large integer/float columns to smaller dtypes (pd.to_numeric with downcast) and parse dates only when needed.

Common inspection commands after reading
Use df.info() to check dtypes and memory, df.head() to preview, df.describe() for summary statistics, df.dtypes to see column types, and df.isnull().sum() to find missing values.

Overview
Indexing, selection and filtering are the basic operations used to access, pick and reduce data in pandas. A DataFrame is a 2‑dimensional table with rows (index labels) and columns. A Series is a single column (1‑D) with an index.

Indexing: labels vs positions
There are two main ways to select data:

• Label-based: use df.loc and df.at. These use the row/column labels (index names or column names).
• Position-based: use df.iloc and df.iat. These use integer positions (0,1,2...).

Examples:

# label-based
row = df.loc['R002']            # row with index label 'R002'
value = df.loc['R002', 'Marks']

# position-based
row2 = df.iloc[1]               # second row
value2 = df.iloc[1, 2]

# fast scalar access
score = df.at['R002', 'Marks']
score2 = df.iat[1, 2]

Selection
You can select columns or subsets:

• df['Col'] returns a Series (single column).
• df[['C1','C2']] returns a DataFrame with selected columns.
• Slicing rows by label: df.loc['R001':'R005'] (inclusive for labels).
• Slicing rows by position: df.iloc[0:5] (end excluded).

Filtering (Boolean indexing)
Filtering means selecting rows that meet conditions. Conditions produce boolean Series and can be used to index the DataFrame:

# single condition
high = df[df['Marks'] > 75]

# multiple conditions (use & and | with parentheses)
good = df[(df['Marks'] > 60) & (df['Attendance'] >= 80)]

# convenient methods
subset = df[df['State'].isin(['Karnataka', 'Kerala'])]
between = df[df['Age'].between(18, 25)]
# query string form
fast = df.query('Marks > 75 and Attendance >= 80')

Setting values and chained indexing
To change values use .loc to avoid chained-indexing issues:

# safe assignment
df.loc[df['Roll'] == 10, 'Marks'] = 95

Index operations
You can set or reset indices:

df2 = df.set_index('Roll')     # set column as index
df.reset_index(inplace=True)    # move index back to column

MultiIndex (brief)
A MultiIndex (hierarchical) has multiple levels (e.g., Year and Month). Access with tuples or cross-section methods like xs.

Good practices

• Prefer .loc/.iloc for clarity.
• Use parentheses when combining conditions.
• Check types and missing values before filtering.

Summary
Indexing lets you address rows/columns by label or position. Selection extracts columns or row slices. Filtering uses boolean expressions to pick rows that satisfy conditions. Together they let you inspect and prepare data for analysis.

What is missing data? Missing data (also called NA, NaN, or null) occurs when no value is stored for a variable in a dataset. In Pandas these appear as NaN (float), None (Python), or as placeholder values like -1 or "unknown".

Why handle missing data? Missing values can bias results, break algorithms, or produce wrong statistics (means, correlations). Proper handling preserves data quality for analysis and machine learning.

Missingness patterns (helps choose strategy):

• MCAR (Missing Completely At Random): missingness unrelated to data — safe to drop sometimes.
• MAR (Missing At Random): missingness related to observed data — imputation using other columns may work.
• MNAR (Missing Not At Random): missingness depends on unobserved values — hardest to handle and needs domain knowledge.

Detecting missing data (Pandas): use df.isnull(), df.isna(), df.notnull(), df.info(), df.isnull().sum(). Visual checks with plots are recommended.

Common handling methods (with Pandas functions):

• Remove rows/columns: df.dropna(axis=0 or 1, how='any' or 'all', thresh=..., subset=[...]). Use when missingness is small or whole column useless.
• Replace/Impute with constants: df.fillna(value) — e.g., 0 or 'unknown'.
• Statistical imputation: replace with column mean, median, or mode: df['col'].fillna(df['col'].mean()).
• Group-wise imputation: replace using group aggregates: df['col'] = df.groupby('group')['col'].transform(lambda x: x.fillna(x.mean())).
• Forward/Backward fill for ordered data: df.fillna(method='ffill') or method='bfill'.
• Interpolation (linear/time): df['col'].interpolate(method='linear' or 'time') — good for time series.
• Replace placeholders: first convert placeholders to NaN: df.replace({'unknown': np.nan, -1: np.nan}, inplace=True), then handle them.

Best practices:

• Always explore and visualise missingness before choosing a method.
• Prefer simple methods first (drop if few missing). Use imputation if dropping would bias results or lose too much data.
• Use domain knowledge: e.g., missing test scores might mean 'absent'.
• When imputing, consider preserving variance (multiple imputation or model-based imputation for advanced use).
• Document every replacement or deletion and, if possible, keep original data copy.

Example Pandas snippets:

# count missing per column
missing = df.isnull().sum()

# drop rows with any missing value
df.dropna(axis=0, how='any', inplace=True)

# fill numeric column with mean
df['age'] = df['age'].fillna(df['age'].mean())

# forward fill time series
df['temp'] = df['temp'].fillna(method='ffill')

# linear interpolate
df['value'] = df['value'].interpolate(method='linear')

# group-wise mean imputation
df['score'] = df.groupby('class')['score'].transform(lambda x: x.fillna(x.mean()))

Handling missing data properly ensures analyses are reliable and interpretable.

What is Data Wrangling and Transformation?
Data wrangling (also called data cleaning or data munging) is the process of inspecting, cleaning, restructuring and enriching raw data so it becomes suitable for analysis. Transformation refers to changing the shape, type or scale of data (e.g., converting strings to dates, normalizing numeric values, pivoting tables).

Typical workflow / steps

• Inspect: Understand data with df.info(), df.head(), df.describe() and check missing values and types.
• Clean: Handle missing values (df.dropna(), df.fillna()), remove duplicates (df.drop_duplicates()), correct typos (df.replace()).
• Convert types: Convert columns to correct types, e.g. pd.to_datetime(), df['col'] = df['col'].astype('int').
• Transform / reshape: Filter and sort (df[df.col > value], df.sort_values()), create new columns (df['new'] = ...), group and aggregate (df.groupby().agg()), reshape with pivot_table or melt.
• Encode & scale: Convert categorical variables to numeric (one-hot encoding via pd.get_dummies() or label encoding), scale numeric features (min-max or z-score).
• Validate & export: Re-check summaries and save cleaned data (df.to_csv()).

Common pandas operations (quick reference)

• Inspect: df.head(), df.info(), df.describe(), df.isnull().sum()
• Missing values: df.dropna(), df.fillna(value), df['col'].fillna(df['col'].median())
• Duplicates: df.drop_duplicates()
• Type conversion: pd.to_datetime(df['date']), df['col'].astype('int')
• String ops: df['name'].str.lower(), df['col'].str.replace()
• Combine tables: pd.concat([df1,df2]), pd.merge(df1,df2, on='key', how='inner')
• Reshape: df.pivot_table(index='A', columns='B', values='C', aggfunc='sum'), pd.melt(df)
• Group & aggregate: df.groupby('col').agg({'sales':'sum','qty':'mean'})
• Encoding: pd.get_dummies(df['category'])

Why it matters
Analytical models and visualizations require correct types, no obvious errors, and appropriate scales. Good wrangling reduces bias, prevents errors and makes insights reliable.

What this topic covers
Basic statistics and aggregation using Pandas teach how to compute summary numbers (central tendency, spread, counts) and how to combine rows to produce aggregate results (sums, means, counts) — useful for understanding and summarizing datasets.

Key statistical concepts

• Central tendency: mean (average), median (middle value), mode (most frequent).
• Dispersion: range, variance, standard deviation, interquartile range (IQR).
• Distribution summary: percentiles/quantiles and five-number summary (min, Q1, median, Q3, max).

Pandas objects and methods

• Series and DataFrame provide built-in summary methods: mean(), median(), mode(), std(), var(), min(), max(), sum(), count().
• describe() returns a compact summary (count, mean, std, min, 25%, 50%, 75%, max) for numeric columns.
• value_counts() counts occurrences of categorical values (use normalize=True for proportions).
• Aggregation (single or multiple functions) using agg() or aggregate() on a Series/DataFrame: e.g. df['score'].agg(['mean','std']).
• Grouping with groupby() to compute aggregates per group: df.groupby('class')['marks'].mean() or multiple aggregations: df.groupby('city').agg({'sales':'sum','profit':'mean'}).
• Pivot-like aggregation with pivot_table() to create cross-tab summaries: df.pivot_table(values='sales', index='region', columns='month', aggfunc='sum', fill_value=0).

Important parameters

• axis: axis=0 (default) aggregates down columns, axis=1 aggregates across columns (rows-wise).
• skipna (default True): ignore NaNs in calculations; use dropna / fillna if you need specific handling.
• numeric_only: include only numeric columns in some summary methods.

Example code patterns

# summary for numeric columns
print(df.describe())

# single-statistic for a column
mean_marks = df['marks'].mean()

# multiple aggregations on a column
df['marks'].agg(['mean','median','std'])

# groupwise aggregation
df.groupby('class')['marks'].agg(['count','mean','std'])

# custom aggregation per column
df.groupby('product').agg({'sales':'sum','discount':'mean'})

# pivot table
pd.pivot_table(df, values='sales', index='store', columns='month', aggfunc='sum', fill_value=0)

# counts of categories
df['response'].value_counts()

# relative frequencies
df['response'].value_counts(normalize=True)

Handling missing values before aggregation
Missing data can bias results. Options: df.dropna() to remove rows, or df.fillna(value) to impute. Many Pandas methods ignore NaNs by default (skipna=True).

Interpreting results
Use mean for symmetric numeric data, median for skewed distributions, and IQR or boxplots to detect outliers. Grouped aggregates reveal patterns across categories (e.g., which region has higher average sales).

What are text and categorical data?

Text data (string data) are free-form sequences of characters such as names, comments, addresses, product reviews, tweets. They are often unstructured and require cleaning and tokenization before analysis.

Categorical data represent variables that take values from a limited set of discrete categories. Categories can be nominal (no intrinsic order, e.g., gender, colour) or ordinal (ordered categories, e.g., education level, rating: low/medium/high).

How pandas handles them

• Text columns usually have dtype object or string. Pandas provides the .str accessor to perform vectorised string operations: lowercasing, trimming, contains, split, replace, length, extract with regex.
• Categorical columns can be converted to dtype category using astype('category') or pd.Categorical. category dtype stores a list of categories and optional order, reduces memory use, and enables efficient operations like .cat.codes, .cat.categories, and reordering.

Common tasks

• Cleaning text: trim whitespace, lowercasing, remove punctuation, correct common misspellings.
• Tokenization and simple text features: word counts, character counts, presence of keywords, extracting hashtags or mentions.
• Converting text to categorical: map repeated text values to categories; reduce cardinality by grouping rare values into an "Other" category.
• Encoding categories for modelling: label encoding (integer codes) for ordinal data, one-hot encoding (dummy variables) for nominal data using pd.get_dummies or pandas categorical methods.
• Summarising categories: value_counts, groupby + aggregation, proportion and percentage calculations.

Why these matter

Proper handling of text and categorical data is essential for accurate summaries, efficient storage, correct model inputs, and meaningful visualizations. Cleaning reduces noise, while appropriate encoding preserves important information (order for ordinal data, distinct flags for nominal data).

Overview: Utility functions and inspection in pandas are the set of methods used to load, examine and get a quick summary of a DataFrame so you understand its shape, types, missing values and basic statistics before further cleaning or analysis.

Common inspection methods (what they show):

• pd.read_csv(...) — read CSV into a DataFrame.
• df.head(n) / df.tail(n) — first/last n rows (quick peek).
• df.sample(n) — random sample of rows.
• df.shape — tuple (rows, columns).
• df.columns — list/Index of column names.
• df.index — row index range or labels.
• df.dtypes — data type of each column (int, float, object, datetime, etc.).
• df.info() — concise summary: index, dtypes, non-null counts and memory usage.
• df.describe() — summary statistics for numeric columns (count, mean, std, min, 25%, 50%, 75%, max); df.describe(include='all') includes non-numeric summaries.
• df.isnull().sum() — count of missing values per column.
• df.nunique() — number of unique values per column.
• df.value_counts() — frequency counts for a Series (useful for categorical columns).
• df.duplicated() / df.drop_duplicates() — find/remove duplicate rows.
• df.memory_usage(deep=True) — memory used by each column (helpful for optimization).

Why inspect first? Before cleaning or plotting, inspection tells you:

• How many rows/columns you have (df.shape)
• Which columns are numeric vs categorical (df.dtypes)
• Whether there are missing values and where (df.isnull().sum())
• Presence of duplicates or unexpected values (df.nunique(), value_counts)
• Basic distribution of numeric variables (df.describe())

Short example workflow (typical first steps):

1. Load: df = pd.read_csv('file.csv')
2. Peek: df.head() and df.sample(5)
3. Summary: df.info() and df.describe()
4. Missing/uniqueness: df.isnull().sum(), df.nunique()
5. Frequencies: df['col'].value_counts()

Typical code snippets (as HTML code examples):

# load and inspect
import pandas as pd

df = pd.read_csv('students.csv')
print(df.shape)          # (rows, cols)
print(df.columns)
print(df.dtypes)
print(df.head())

# quick summary
print(df.info())
print(df.describe())

# missing and duplicates
print(df.isnull().sum())
print(df.duplicated().sum())

Best practices:

• Always inspect data right after loading — it avoids surprises later.
• Use df.info() to spot columns loaded as object that should be numeric or datetime; convert them with pd.to_datetime() or df['col'] = df['col'].astype('int').
• Check missing value percentages before deciding how to impute or drop rows/columns.
• For large datasets use df.head(), df.sample() and df.info(memory_usage=True) to limit expensive operations.

How inspection informs next steps: If df.describe() shows extreme min/max or large std, you may need outlier handling. If df.isnull().sum() shows many nulls in a column, consider dropping or imputing. If dtypes are wrong, convert them.