Pandas Data Analysis & Tabular Mechanics

This technical note explores the architectural mechanics and data manipulation patterns of tabular data processing in Python using Pandas, derived from reference implementations in Pandas.

1. Memory Model: Series vs DataFrame & BlockManager

At its core, Pandas builds on top of NumPy's contiguous memory buffers while introducing heterogeneous column data types and labeled alignment axes.

Code

DataFrame Structural Internals
├── Index (Row Labels) ------> ['Row_0', 'Row_1', 'Row_2', ...]
├── Columns (Col Labels) ----> ['Age', 'Salary', 'Department']
└── BlockManager (Column Storage)
    ├── IntBlock (2D NumPy array) -----> int64 data [Age, ...]
    ├── FloatBlock (2D NumPy array) ---> float64 data [Salary, ...]
    └── ObjectBlock (PyObject* pointers) -> string data [Department, ...]

1D `Series` vs 2D `DataFrame`

Series: A 1D homogeneous array paired with an immutable labeled index. Operations maintain index alignment across arithmetic computations.
DataFrame: A 2D tabular container backed by a BlockManager that groups columns of identical dtype into contiguous 2D NumPy arrays for memory efficiency.

2. Selection & Indexing: `.loc` vs `.iloc`

Pandas strictly separates label-based selection from integer position-based selection to prevent index type ambiguity.

Explicit Label Selection (`.loc`) vs Positional Selection (`.iloc`)

.loc[row_label, col_label]: Selects data by explicit row and column labels. Slicing with .loc is inclusive of both endpoints.
.iloc[row_index, col_index]: Selects data by zero-based integer index positions. Slicing with .iloc is exclusive of the upper bound.

Code

import pandas as pd

df = pd.DataFrame({
    'Population': [331, 1441, 1380],
    'GDP': [21.4, 14.7, 2.9]
}, index=['USA', 'China', 'India'])

# Label-based selection (inclusive endpoints)
usa_data = df.loc['USA', 'Population']  # 331

# Positional selection (exclusive upper bound)
first_two = df.iloc[0:2, 0]  # Rows 0..1, Column 0 -> USA and China Population

Compound Boolean Filtering

Logical filtering relies on bitwise operators (&, |, ~). Each individual sub-condition must be enclosed in parentheses to override Python's operator precedence:

Code

# Select countries with Population > 300M AND GDP > 10T
high_gdp_pop = df[(df['Population'] > 300) & (df['GDP'] > 10.0)]

3. Missing Data Handling & Imputation Strategies

Missing values in Pandas are represented by NaN (np.nan) for floating-point columns or None for object columns.

Code

# 1. Detection
missing_counts = df.isna().sum()

# 2. Row/Column Dropping with Thresh
# Drop rows having fewer than 5 non-null entries
clean_df = df.dropna(thresh=5)

# 3. Statistical Imputation
# Impute numerical columns with column median
df['Salary'] = df['Salary'].fillna(df['Salary'].median())

4. Database Joins & Relational Merges

Combining DataFrames follows standard relational algebra patterns via pd.merge():

Code

        Merge Types (pd.merge)
┌──────────────┬──────────────────────────────────┐
│ Join Type    │ Resulting Rows                   │
├──────────────┼──────────────────────────────────┤
│ Inner        │ Keys present in BOTH DataFrames  │
│ Left         │ All rows from Left + matched Right│
│ Right        │ All rows from Right + matched Left│
│ Outer (Full) │ All rows from BOTH DataFrames    │
└──────────────┴──────────────────────────────────┘

Code

df_merged = pd.merge(
    left_df,
    right_df,
    on='Country_Code',
    how='left',
    suffixes=('_left', '_right')
)

5. Split-Apply-Combine & Pivot Tables

The Split-Apply-Combine paradigm breaks a dataset into groups, applies aggregation functions, and combines results into a unified summary.

Code

# Single and multi-column aggregation
dept_summary = df.groupby('Department').agg(
    Avg_Salary=('Salary', 'mean'),
    Total_Bonus=('Bonus', 'sum'),
    Headcount=('Employee_ID', 'count')
)

# Multi-dimensional Pivot Table with subtotals
pivot = pd.pivot_table(
    df,
    values='Salary',
    index='Department',
    columns='Region',
    aggfunc='mean',
    fill_value=0,
    margins=True  # Adds ALL total rows & columns
)

6. Vectorized String Processing & Feature Extraction

Pandas provides vectorized .str accessor methods operating directly on object/string series using compiled C/Python regular expressions.

Extracting Structured Features via Regex

Code

# Extract episode counts and score ratings from raw string strings
# Example string: "Episodes: 24 | Rating: 8.95"
anime_df[['Episodes', 'Score']] = anime_df['Info'].str.extract(
    r'Episodes:\s*(\d+)\s*\|\s*Rating:\s*([\d.]+)'
)

# Cast extracted string groups to numerical types
anime_df['Episodes'] = pd.to_numeric(anime_df['Episodes'])
anime_df['Score'] = pd.to_numeric(anime_df['Score'])

7. Summary & References

🔗 GitHub Repository: github.com/nischalneupanee/Pandas
📦 Capstone Project: [[data-visualization-mastery]]
📖 Related Notes: [[python/all-about-numpy-array-computing]] and [[python/data-visualization-matplotlib]]

Pandas Data Analysis & Tabular Mechanics

This technical note explores the architectural mechanics and data manipulation patterns of tabular data processing in Python using Pandas, derived from reference implementations in Pandas.

1. Memory Model: Series vs DataFrame & BlockManager

At its core, Pandas builds on top of NumPy's contiguous memory buffers while introducing heterogeneous column data types and labeled alignment axes.

Code

DataFrame Structural Internals
├── Index (Row Labels) ------> ['Row_0', 'Row_1', 'Row_2', ...]
├── Columns (Col Labels) ----> ['Age', 'Salary', 'Department']
└── BlockManager (Column Storage)
    ├── IntBlock (2D NumPy array) -----> int64 data [Age, ...]
    ├── FloatBlock (2D NumPy array) ---> float64 data [Salary, ...]
    └── ObjectBlock (PyObject* pointers) -> string data [Department, ...]

1D `Series` vs 2D `DataFrame`

Series: A 1D homogeneous array paired with an immutable labeled index. Operations maintain index alignment across arithmetic computations.
DataFrame: A 2D tabular container backed by a BlockManager that groups columns of identical dtype into contiguous 2D NumPy arrays for memory efficiency.

2. Selection & Indexing: `.loc` vs `.iloc`

Pandas strictly separates label-based selection from integer position-based selection to prevent index type ambiguity.

Explicit Label Selection (`.loc`) vs Positional Selection (`.iloc`)

.loc[row_label, col_label]: Selects data by explicit row and column labels. Slicing with .loc is inclusive of both endpoints.
.iloc[row_index, col_index]: Selects data by zero-based integer index positions. Slicing with .iloc is exclusive of the upper bound.

Code

import pandas as pd

df = pd.DataFrame({
    'Population': [331, 1441, 1380],
    'GDP': [21.4, 14.7, 2.9]
}, index=['USA', 'China', 'India'])

# Label-based selection (inclusive endpoints)
usa_data = df.loc['USA', 'Population']  # 331

# Positional selection (exclusive upper bound)
first_two = df.iloc[0:2, 0]  # Rows 0..1, Column 0 -> USA and China Population

Compound Boolean Filtering

Logical filtering relies on bitwise operators (&, |, ~). Each individual sub-condition must be enclosed in parentheses to override Python's operator precedence:

Code

# Select countries with Population > 300M AND GDP > 10T
high_gdp_pop = df[(df['Population'] > 300) & (df['GDP'] > 10.0)]

3. Missing Data Handling & Imputation Strategies

Missing values in Pandas are represented by NaN (np.nan) for floating-point columns or None for object columns.

Code

# 1. Detection
missing_counts = df.isna().sum()

# 2. Row/Column Dropping with Thresh
# Drop rows having fewer than 5 non-null entries
clean_df = df.dropna(thresh=5)

# 3. Statistical Imputation
# Impute numerical columns with column median
df['Salary'] = df['Salary'].fillna(df['Salary'].median())

4. Database Joins & Relational Merges

Combining DataFrames follows standard relational algebra patterns via pd.merge():

Code

        Merge Types (pd.merge)
┌──────────────┬──────────────────────────────────┐
│ Join Type    │ Resulting Rows                   │
├──────────────┼──────────────────────────────────┤
│ Inner        │ Keys present in BOTH DataFrames  │
│ Left         │ All rows from Left + matched Right│
│ Right        │ All rows from Right + matched Left│
│ Outer (Full) │ All rows from BOTH DataFrames    │
└──────────────┴──────────────────────────────────┘

Code

df_merged = pd.merge(
    left_df,
    right_df,
    on='Country_Code',
    how='left',
    suffixes=('_left', '_right')
)

5. Split-Apply-Combine & Pivot Tables

The Split-Apply-Combine paradigm breaks a dataset into groups, applies aggregation functions, and combines results into a unified summary.

Code

# Single and multi-column aggregation
dept_summary = df.groupby('Department').agg(
    Avg_Salary=('Salary', 'mean'),
    Total_Bonus=('Bonus', 'sum'),
    Headcount=('Employee_ID', 'count')
)

# Multi-dimensional Pivot Table with subtotals
pivot = pd.pivot_table(
    df,
    values='Salary',
    index='Department',
    columns='Region',
    aggfunc='mean',
    fill_value=0,
    margins=True  # Adds ALL total rows & columns
)

6. Vectorized String Processing & Feature Extraction

Pandas provides vectorized .str accessor methods operating directly on object/string series using compiled C/Python regular expressions.

Extracting Structured Features via Regex

Code

# Extract episode counts and score ratings from raw string strings
# Example string: "Episodes: 24 | Rating: 8.95"
anime_df[['Episodes', 'Score']] = anime_df['Info'].str.extract(
    r'Episodes:\s*(\d+)\s*\|\s*Rating:\s*([\d.]+)'
)

# Cast extracted string groups to numerical types
anime_df['Episodes'] = pd.to_numeric(anime_df['Episodes'])
anime_df['Score'] = pd.to_numeric(anime_df['Score'])

7. Summary & References

🔗 GitHub Repository: github.com/nischalneupanee/Pandas
📦 Capstone Project: [[data-visualization-mastery]]
📖 Related Notes: [[python/all-about-numpy-array-computing]] and [[python/data-visualization-matplotlib]]

Pandas Data Analysis & Tabular Mechanics

Pandas Data Analysis & Tabular Mechanics

1. Memory Model: Series vs DataFrame & BlockManager

1D `Series` vs 2D `DataFrame`

2. Selection & Indexing: `.loc` vs `.iloc`

Explicit Label Selection (`.loc`) vs Positional Selection (`.iloc`)

Compound Boolean Filtering

3. Missing Data Handling & Imputation Strategies

4. Database Joins & Relational Merges

5. Split-Apply-Combine & Pivot Tables

6. Vectorized String Processing & Feature Extraction

Extracting Structured Features via Regex

7. Summary & References

References & Citation Links

Related Knowledge Notes

Data Visualization with Matplotlib & Pandas: Fundamentals & Practice

Machine Learning Model Evaluation Metrics: Precision, Recall & mAP

NumPy Architecture & High-Performance Array Computing Mechanics

Python Fundamentals & Language Mechanics: An Engineering Teardown

Subscribe to Journal Updates

Pandas Data Analysis & Tabular Mechanics

Pandas Data Analysis & Tabular Mechanics

1. Memory Model: Series vs DataFrame & BlockManager

1D `Series` vs 2D `DataFrame`

2. Selection & Indexing: `.loc` vs `.iloc`

Explicit Label Selection (`.loc`) vs Positional Selection (`.iloc`)

Compound Boolean Filtering

3. Missing Data Handling & Imputation Strategies

4. Database Joins & Relational Merges

5. Split-Apply-Combine & Pivot Tables

6. Vectorized String Processing & Feature Extraction

Extracting Structured Features via Regex

7. Summary & References

References & Citation Links

Related Knowledge Notes

Data Visualization with Matplotlib & Pandas: Fundamentals & Practice

Machine Learning Model Evaluation Metrics: Precision, Recall & mAP

NumPy Architecture & High-Performance Array Computing Mechanics

Python Fundamentals & Language Mechanics: An Engineering Teardown

Subscribe to Journal Updates

Pandas Data Analysis & Tabular Mechanics

1. Memory Model: Series vs DataFrame & BlockManager

1D Series vs 2D DataFrame

2. Selection & Indexing: .loc vs .iloc

Explicit Label Selection (.loc) vs Positional Selection (.iloc)

Compound Boolean Filtering

3. Missing Data Handling & Imputation Strategies

4. Database Joins & Relational Merges

5. Split-Apply-Combine & Pivot Tables

6. Vectorized String Processing & Feature Extraction

Extracting Structured Features via Regex

7. Summary & References

References & Citation Links

Related Knowledge Notes

Data Visualization with Matplotlib & Pandas: Fundamentals & Practice

Machine Learning Model Evaluation Metrics: Precision, Recall & mAP

NumPy Architecture & High-Performance Array Computing Mechanics

Python Fundamentals & Language Mechanics: An Engineering Teardown

Subscribe to Journal Updates

Pandas Data Analysis & Tabular Mechanics

1. Memory Model: Series vs DataFrame & BlockManager

1D Series vs 2D DataFrame

2. Selection & Indexing: .loc vs .iloc

Explicit Label Selection (.loc) vs Positional Selection (.iloc)

Compound Boolean Filtering

3. Missing Data Handling & Imputation Strategies

4. Database Joins & Relational Merges

5. Split-Apply-Combine & Pivot Tables

6. Vectorized String Processing & Feature Extraction

Extracting Structured Features via Regex

7. Summary & References

References & Citation Links

Related Knowledge Notes

Data Visualization with Matplotlib & Pandas: Fundamentals & Practice

Machine Learning Model Evaluation Metrics: Precision, Recall & mAP

NumPy Architecture & High-Performance Array Computing Mechanics

Python Fundamentals & Language Mechanics: An Engineering Teardown

Subscribe to Journal Updates

1D `Series` vs 2D `DataFrame`

2. Selection & Indexing: `.loc` vs `.iloc`

Explicit Label Selection (`.loc`) vs Positional Selection (`.iloc`)

1D `Series` vs 2D `DataFrame`

2. Selection & Indexing: `.loc` vs `.iloc`

Explicit Label Selection (`.loc`) vs Positional Selection (`.iloc`)