Lecture 03

These skills are essential for effective data analysis, collaboration, and handling data in real-world projects.

Chapter 4: Pandas

Online Textbook Material

• What is Pandas
• How to install and import Pandas
• Basic Pandas Workflow
• Some Basic Operations using Pandas

A fundamental data science tool for Python.

An open-source library providing high-performance, easy-to-use data structures and data analysis tools. Built on top of NumPy, it is essential for structured (tabular) data.

Key Data Structures

Why Pandas?

check_circle Easy Missing Data Handling: Built-in mechanisms for cleaning and handling incomplete datasets.

check_circle Powerful Aggregations: Flexible split-apply-combine operations for easy data grouping.

check_circle Quick Operations: High-performance filtering, sorting, merging, and reshaping.

check_circle Robust I/O Support: Read/write seamlessly across CSV, Excel, SQL, and JSON formats.

The Origin of "Pandas" (Derived from "Python Data Analysis Library")

Started in 2008 by Wes McKinney to deliver a high-performance, flexible tool capable of executing advanced quantitative analysis on financial datasets.

1. Load

2. Inspect

3. Clean

4. Transform

5. Analyze

The Data Workbench

Pandas is the core environment for managing tabular data, allowing seamless transition from raw files to clean datasets.

Supported Formats

• CSV files & spreadsheets
• Scraped web tables & logs
• Database query results

First four commands

import pandas as pd

df = pd.read_csv("scores.csv")

df.head()

df.info()

3. MAP

Apply a function to every row, possibly creating more or fewer columns

Original Dataset

Mapped (Split Columns)

Alternative Variations

Some variations allow one row to generate multiple rows in the output (commonly referred to as "flatmap").

By 'A'

6. UNION / INTERSECTION / DIFFERENCE

Set operations – only if the two tables have identical attributes/columns

Key Behavioral Insights (These operations treat tables like sets of rows.)

Intersection (common rows) → keeps rows that appear in both tables.

Set Difference EXCEPT/MINUS→ keeps rows that appear in one table but not the other.

Note: Results can change depending on whether an ID column is included. Two rows with the same data but different IDs may be treated as different rows.

01 / Terminal System Install Open terminal and run either command:

$ conda install pandas

# OR

$ pip install pandas

02 / Jupyter Notebook Setup Run inside notebook cells. The ! prefix executes as terminal shell:

!pip install pandas

03 / Python

Import & Verify

Import as pd (standard alias) and print version to verify:

import pandas as pd

print(pd.__version__)

1

Loading data: CSV, Excel, databases

2

Inspecting data: First few rows, column names, data types

3

Cleaning data

Handle missing values • Fix data types • Remove duplicates

4

Filtering & sorting

Select rows/columns • Apply conditions

5

Aggregation: Mean, median, count, group by categories

Class Colab: https://colab.research.google.com/drive/1i2IJTAodNi3RVYuoGM5INU4bHOiN-eBU?usp=sharing

Dataset: https://drive.google.com/file/d/1au5qJnRR57pOeAt4aefzxwsmG02JyaR_/view?usp=sharing

Visual Concept

Key Idea:

• Series is the fundamental building block.

• DataFrame is a collection of Series sharing an index.

01 / The Series

1D Labeled Array

• Represents a single column or variable
• Consists of index + data values
• Example: [72, 85, 90, 88]

Common Operations:

sum(), count(), unique()

02 / The DataFrame

2D Labeled Table

• Holds the entire tabular dataset
• Structured as rows and columns
• Each column is treated as a Series

Data Access:

Columns accessed by name, rows accessed by index (less common).

Reading Data

# Read CSV file

df = pd.read_csv("data.csv")

# Read Excel file (XLS / XLSX)

df = pd.read_excel("data.xlsx")

# Read text file (whitespace-separated)

df = pd.read_csv("data.txt", sep="\s")

# Read JSON file

df = pd.read_json("data.json")

Key Idea: Pandas can load data from multiple file formats using the file path as input.

Outputting Data

# Write DataFrame to CSV

df.to_csv("out.csv", index=False)

# Write DataFrame to Excel

df.to_excel("out.xlsx", index=False)

# Write text file (space-separated)

df.to_csv("out.txt", sep=" ")

# Write DataFrame to JSON

df.to_json("out.json")

​

Key Idea: Use built-in to_*() methods on Series or DataFrames to export data smoothly.

Pandas Pipeline

Read➔Write

Row Inspection

# Display First 5 rows

df.head()

# Display Last 3 rows

df.tail(n=3)

Specify the number of rows through the n argument (the default is 5).

Schema & Metadata

# Data types

df.dtypes

# Column names

df.columns

# Overview of columns & types

df.info()

Stats & Dimensions

# Summary statistics

df.describe()

Prints count, mean, std, range, and quartiles.

# Shape (rows, columns)

df.shape

df.shape[0]: Rows only

df.shape[1]: Columns only

Selecting Data

​

# Column Selection (Series / DataFrame)

df["Age"] or df[["Name", "City"]]

# Row Selection by Index

df.iloc[0] # first row

df.iloc[1:3] # rows 1-2

# Row Selection by Condition

df[df["Age"] > 30]

Access Pattern:

dataframe["column_name"] → accesses a column

Modifying & Arithmetic

​

# Create New Columns

df["new_col"] = df["col1"] + df["col2"]

df["new_col"] = df["col1"] - df["col2"]

# Modify an Existing Column

df["Age"] = df["Age"] + 1

Key Idea:

Pandas supports vectorized operations; mathematical applications automatically apply to the entire column.

Applying Functions Directly to a DataFrame

Column-Wise Operation

You can apply a function or method to a DataFrame column, and pandas will operate on the values in that column.

SYNTAX PATTERN

dataframe[column_name].function()

​

1. Column Filtering

Select specific columns by name using basic selection:

df[['col1']] or df[['col1', 'col2']]

Key Benefits:

• Feature selection for modeling
• Focus on relevant variables
• Reduce memory by dropping unused columns

2. Row Filtering

Select rows that satisfy a condition using Boolean Indexing:

df[df['col'] > value]

Key Benefits:

• Filter values based on conditions
• Analyze specific subsets of data
• Clean data (remove outliers, duplicates)
• Remove unwanted or invalid rows

1. Column Filtering

Extract specific columns by passing one or more column names. This helps focus on relevant features and reduces memory usage.

# Create sample DataFrame

df = pd.DataFrame({

'name': ['Alice', 'Bob', 'Charlie'],

'age': [25, 30, 35],

'city': ['NY', 'LA', 'Chicago']

})

# Select specific columns

df_cols = df[['name', 'age']]

2. Row Filtering (Boolean Indexing)

Select rows by passing a condition inside square brackets. Pandas keeps the rows where the condition is True.

Syntax: df[boolean_condition]

Example: df[df['Age'] > 29]

Next Step

What about multiple conditions?

1. Frequency Counts

Count unique values in categorical data. Returns a Series sorted descending by default.

df["column"].value_counts()

Scenario 1

On Entire DataFrame (Numeric Columns Only)

df.mean() # Mean of all numeric columns

df.max() # Max of all numeric columns

Scenario 2

On a Single Column (Series)

df['Price'].sum() # Sum of 'Price' column

df['Age'].std() # Standard deviation of 'Age'

Scenario 3Next Topic

On Grouped Data

df.groupby('Category')['Price'].mean()

Interactive Example

import pandas as pd

# Sample DataFrame

data = {'Price': [10, 15, 20], 'Quantity': [3, 2, 5]}

df = pd.DataFrame(data)

# Direct aggregations

print("Sum of columns:")

print(df.sum())

# Output: Price 45, Quantity 10

print("Mean of 'Price':")

print(df['Price'].mean())

# Output: 15.0

df[df[condition]][column].statistics_function()

Example Find the Mean of 'Number' where Age > 25

split → apply → combine

df.groupby('group_col')['target_col'].mean()

EXAMPLE: AVERAGE SCORE PER MAJOR

df.groupby('Major')['Score'].mean()

df.groupby('group_col')['target_col'].mean()

1. Single Column

Group rows based on unique values in a single column.

df.groupby('column_name')

Question: average score for each major

df.groupby('major')['score'].mean()

2. Multiple Columns

Group by multiple hierarchy levels using a list of columns.

df.groupby(['col1', 'col2'])

Question: average score for each major-year pair

df.groupby(['major','year'])['score'].mean()

3. Group + Aggregate

MOST COMMON PATTERN

[col] extracts column before aggregating.

df.groupby('cat')['val'].mean() → one col

df.groupby('cat').mean() → all numeric

Question: average score & hours per major

df.groupby('major')[['score','hours']].mean()

Remember: groupby() groups rows first; column selection and aggregation happen afterwards for optimal performance.

Group By - More Complicated Examples (2)

INPUT DATAFRAME (DF)

RESULT OUTPUT

The Task

Find the maximum Values for each Category, but only include rows where Values are less than 25.

Execution Steps

• Step 1: Filter rows where `Values` < 25
• Step 2: Group by `Category` and calculate the maximum `Values`

SYNTAX PATTERN

loc : Label-Based

Selects data by explicit labels or names. Slicing is inclusive (both bounds are kept).

# df.loc[row_label, col_label]

df.loc[1, 'Age']

# Access rows with labels from index 2 to 4 and columns 'A' and 'B'

df.loc[2:4, ['A', 'B']]

iloc : Position-Based

Selects data by integer positions. Slicing is end-exclusive (upper bound excluded).

# df.iloc[row_index, col_index]

df.iloc[1, 1]

​

# Access rows from index 2 to 4 and columns at positions 0 and 1

df.iloc[2:5, [0, 1]]

Merge: combining tables safely

Real datasets are often spread across multiple files. Merge by a shared key.

SYNTAX PATTERN & MERGE CHECK

merged = scores.merge(roster, on="student_id", how="inner")

# Check after every merge

merged.shape

merged["student_id"].is_unique

Important Tip

Always check row counts after merging. Duplicated keys can quietly create extra rows.

01 Pandas dataframes can be visualized using Matplotlib.

02 Plotting dataframes is a useful way to see results and extract quick patterns.

03

Install Matplotlib using conda or pip package manager.

pip/conda install matplotlib

04 Visualization helps in understanding the dataframe content efficiently.

01

A powerful Python library tailored for complex data tasks like analysis and cleaning.

02

Simplifies data representation, making complex data structures easier to understand.

03

Efficiently cleans messy datasets to ensure readability, structure, and relevance.

04

Increases developer productivity by significantly minimizing the amount of code required.

05

Offers highly extensive features built specifically for seamless and robust data analysis.

Official Documentation

• pandas Documentation�pandas-docs/stable/index.html
• Input / Output API�pandas-docs/stable/reference/io.html
• DataFrame Reference�pandas-docs/stable/reference/frame.html
• Series Reference�pandas-docs/stable/reference/series.html
• Plotting Functions�pandas-docs/stable/reference/plotting.html

External & Learning Resources

• GitHub Tutorial (Tirendaz Academy)�https://github.com/TirendazAcademy/PANDAS-TUTORIAL/blob/main/01-What-is-Pandas.ipynb
• Kevin Markham Video Playlist�https://www.youtube.com/playlist?list=PL5-da3qGB5ICCsgW1MxlZ0Hq8LL5U3u9y
• Books (Handbook & Data Analysis)�by Jake VanderPlas & Wes McKinney
• Comparison with SQL�pandas-docs/stable/comparison_with_sql.html
• CheatSheet: DataCamp PDF�PandasPythonForDataScience.pdf

The End