Advanced Pandas functions for data manipulation and transformation in a Python/data science context.

Overview
Merge, Join and Concatenate
Binning with Pandas
Pivot, Melt and Crosstab
Reference Tables
Category Diagrams

1. Overview

This course covers advanced Pandas functions for data manipulation and transformation in a Python/data science context. Main topics:

Combining DataFrames: merge, join, concat
Discretizing continuous data: cut, qcut (binning)
Reshaping DataFrames: pivot, melt, crosstab

import pandas as pd
import numpy as np

# Check installed version
print(pd.__version__)

2. Merge, Join and Concatenate

2.1 Fundamental Differences

All three functions combine DataFrames, but their behaviors differ substantially:

Criterion	`merge`	`join`	`concat`
Combination basis	Common columns or indices	Index (key or index)	Row or column axis
Syntax type	Module-level function	Instance method (dot syntax)	Module-level function
Default join	Inner join	Left join on index	No join — simple concatenation
Use case	Combine rows sharing data	Efficient join on index	Stack DataFrames of the same structure
SQL analogy	`JOIN`	`LEFT JOIN`	`UNION ALL`

2.2 `merge` Function

The merge function performs relational database-style joins, similar to SQL operations.

Single-column merge

import pandas as pd

df1 = pd.DataFrame({'ID': [1, 2, 3], 'Name': ['Alice', 'Bob', 'Charlie']})
df2 = pd.DataFrame({'ID': [2, 3, 4], 'Age': [25, 30, 35]})

# Single-column merge on ID column
merged_df = pd.merge(df1, df2, on='ID')
print(merged_df)
# Result: only rows with common ID (2 and 3)
#    ID     Name  Age
# 0   2      Bob   25
# 1   3  Charlie   30

Multiple-column merge

df3 = pd.DataFrame({'ID': [1, 2, 3], 'Category': ['A', 'B', 'A']})
df4 = pd.DataFrame({'ID': [2, 3, 4], 'Category': ['B', 'A', 'C'], 'Value': [100, 200, 300]})

# Multiple-column merge on ID and Category
merged_df_multi = pd.merge(df3, df4, on=['ID', 'Category'])
print(merged_df_multi)
# Result: one row with ID=2 and Category='B'
#    ID Category  Value
# 0   2        B    100

Join Types with `merge`

# Inner join (default)
pd.merge(df1, df2, on='ID', how='inner')

# Left join
pd.merge(df1, df2, on='ID', how='left')

# Right join
pd.merge(df1, df2, on='ID', how='right')

# Outer join
pd.merge(df1, df2, on='ID', how='outer')

# Merge on columns with different names
pd.merge(df1, df2, left_on='ID_left', right_on='ID_right')

# Merge on index
pd.merge(df1, df2, left_index=True, right_index=True)

Key pd.merge() Parameters:

Parameter	Description	Default
`left`	First DataFrame	—
`right`	Second DataFrame	—
`how`	Join type: `'inner'`, `'left'`, `'right'`, `'outer'`, `'cross'`	`'inner'`
`on`	Common column(s) to join on	`None`
`left_on`	Left DataFrame column(s)	`None`
`right_on`	Right DataFrame column(s)	`None`
`left_index`	Use left DataFrame index	`False`
`right_index`	Use right DataFrame index	`False`
`suffixes`	Suffixes for duplicate columns	`('_x', '_y')`
`indicator`	Add `_merge` column indicating source	`False`

2.3 `join` Function

The join method combines two DataFrames based on their indices. It is an instance method called directly on a DataFrame.

import pandas as pd

# DataFrame 1: index 1, 2, 3
df_left = pd.DataFrame(
    {'Name': ['Alice', 'Bob', 'Charlie'], 'Score': [85, 90, 78]},
    index=[1, 2, 3]
)

# DataFrame 2: index 2, 3, 4
df_right = pd.DataFrame(
    {'Age': [25, 30, 35]},
    index=[2, 3, 4]
)

# Inner join: intersection of indices (2 and 3)
inner_result = df_left.join(df_right, how='inner')
print(inner_result)
#       Name  Score  Age
# 2      Bob     90   25
# 3  Charlie     78   30

# Outer join: union of all indices (1, 2, 3, 4)
outer_result = df_left.join(df_right, how='outer')
print(outer_result)
#       Name  Score   Age
# 1    Alice   85.0   NaN
# 2      Bob   90.0  25.0
# 3  Charlie   78.0  30.0
# 4      NaN    NaN  35.0

# Left join: all rows of left DataFrame (indices 1, 2, 3)
left_result = df_left.join(df_right, how='left')

# Right join: all rows of right DataFrame (indices 2, 3, 4)
right_result = df_left.join(df_right, how='right')

2.4 `concat` Function

pd.concat() concatenates two or more DataFrames along a specified axis (rows or columns). No join logic — data is simply “stacked”.

The `ignore_index` Parameter

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [5, 6], 'B': [7, 8]})

# ignore_index=True: reset index (0, 1, 2, 3)
result_reset = pd.concat([df1, df2], axis=0, ignore_index=True)
print(result_reset)
#    A  B
# 0  1  3
# 1  2  4
# 2  5  7
# 3  6  8

# ignore_index=False (default): preserve original indices (0, 1, 0, 1)
result_preserved = pd.concat([df1, df2], axis=0, ignore_index=False)
print(result_preserved)
#    A  B
# 0  1  3
# 1  2  4
# 0  5  7
# 1  6  8

# Column concatenation (axis=1)
result_cols = pd.concat([df1, df2], axis=1)
print(result_cols)
#    A  B  A  B
# 0  1  3  5  7
# 1  2  4  6  8

Key pd.concat() Parameters:

Parameter	Description	Default
`objs`	List of DataFrames or Series to concatenate	—
`axis`	Concatenation axis: `0` (rows) or `1` (columns)	`0`
`ignore_index`	Reset result index	`False`
`keys`	Add hierarchical labels (MultiIndex)	`None`
`join`	`'inner'` or `'outer'` for non-common columns	`'outer'`
`sort`	Sort non-concatenation columns	`False`

3. Binning with Pandas

Binning (or discretization) is the process of grouping continuous or numerical variables into discrete intervals. This converts numerical data into categorical data.

3.1 Types of Binning

flowchart TD
    A[Binning in Pandas] --> B[Equal Width Binning\nFixed-width intervals]
    A --> C[Equal Frequency Binning\nSimilar number of points per bin]
    B --> D["pd.cut()"]
    C --> E["pd.qcut()"]
    D --> D1[Example: age groups\n20-40, 40-60, 60-80]
    E --> E1[Example: exam scores\nquartiles Q1, Q2, Q3, Q4]
    D1 --> D2[Possibly non-uniform distribution\nEqual-width bins]
    E1 --> E2[Balanced distribution\nSame number of points per bin]

Criterion	`cut` (Equal Width)	`qcut` (Equal Frequency)
Principle	Equal-width intervals	Bins with similar number of points
Based on	Value range	Quantiles
Use case	Data with uniform distribution	Data with variable distribution
Risk	Empty bins if distribution is skewed	Bins with duplicated boundary values

3.2 `cut` Function

The cut function performs equal width binning — it divides the value range into fixed-width intervals.

import pandas as pd

# Age data
ages = [22, 25, 30, 35, 42, 50, 55, 65, 70, 75]

# Define bin boundaries
bins = [20, 40, 60, 80]
labels = ['Young', 'Adult', 'Senior']

# Simple cut application
age_bins = pd.cut(ages, bins=bins, labels=labels)

# Create DataFrame for visualization
df = pd.DataFrame({'Age': ages, 'Group': age_bins})
print(df)
#    Age  Group
# 0   22  Young
# 1   25  Young
# 2   30  Young
# ...

# Cut with number of bins (automatic division)
auto_bins = pd.cut(ages, bins=4)
print(auto_bins)

Custom Cut with Advanced Parameters

custom_bins = pd.cut(
    ages,
    bins=[20, 40, 60, 80],
    labels=['Young', 'Adult', 'Senior'],
    right=True,           # Include right boundary
    include_lowest=True,  # Include minimum value
)

# Statistics per bin
df['Group'].value_counts()

# Return bin edges too
result, bin_edges = pd.cut(ages, bins=3, retbins=True)
print("Bin edges:", bin_edges)

pd.cut() Parameters:

Parameter	Description	Default
`x`	Array or data sequence to discretize	—
`bins`	Number of bins (int) or sequence of boundaries	—
`labels`	Labels for bins (same length as number of bins)	`None`
`right`	Bins closed on right (`True`) or left (`False`)	`True`
`include_lowest`	Include the lower boundary of the first bin	`False`
`duplicates`	Handling of duplicate boundaries: `'raise'`, `'drop'`	`'raise'`
`retbins`	Return bin edges	`False`
`precision`	Precision for label display	`3`

3.3 `qcut` Function

The qcut function performs equal frequency binning — it creates bins where each bin contains approximately the same number of data points, based on quantiles.

import pandas as pd

# Exam score data
scores = [65, 70, 72, 75, 78, 80, 82, 85, 88, 90, 92, 95, 97, 99]

# qcut with 4 quantiles (quartiles)
quartiles = pd.qcut(scores, q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])
df = pd.DataFrame({'Score': scores, 'Quartile': quartiles})
print(df)

# Custom quantiles
custom_quantiles = pd.qcut(
    scores,
    q=[0, 0.25, 0.50, 0.75, 1.0],
    labels=['Low', 'Mid-Low', 'Mid-High', 'High']
)

# Get bin edges
result, bin_edges = pd.qcut(scores, q=4, retbins=True)
print("Quantile edges:", bin_edges)

pd.qcut() Parameters:

Parameter	Description	Default
`x`	Array or data sequence	—
`q`	Number of quantiles (int) or sequence of quantiles	—
`labels`	Labels for bins or `False` for integer labels	`None`
`retbins`	Return bin edges	`False`
`precision`	Precision for label storage and display	`3`
`duplicates`	Handling of duplicate bin edges: `'raise'`, `'drop'`	`'raise'`

4. Pivot, Melt and Crosstab

4.1 `pivot` Function

The pivot function reshapes a DataFrame by pivoting based on specified column values. It transforms data from long format to wide format.

flowchart LR
    subgraph "Long Format"
        L["Date | Product | Sales
        2023-01 | A     | 100
        2023-01 | B     | 200
        2023-02 | A     | 150
        2023-02 | B     | 250"]
    end
    subgraph "Wide Format (after pivot)"
        W["Product | A   | B
        2023-01  | 100 | 200
        2023-02  | 150 | 250"]
    end
    L --"pivot(index='Date',\ncolumns='Product',\nvalues='Sales')"-->W

import pandas as pd

# Long format DataFrame (sales by date and product)
sales_df = pd.DataFrame({
    'Date': ['2023-01', '2023-01', '2023-02', '2023-02'],
    'Product': ['A', 'B', 'A', 'B'],
    'Sales': [100, 200, 150, 250]
})

# Pivot: transform to wide format
pivot_result = sales_df.pivot(
    index='Date',
    columns='Product',
    values='Sales'
)
print(pivot_result)
# Product       A    B
# Date
# 2023-01     100  200
# 2023-02     150  250

DataFrame.pivot() Parameters:

Parameter	Description
`index`	Column whose unique values become the pivoted DataFrame’s index
`columns`	Column whose unique values become the column headers
`values`	Column whose values fill the cells of the pivoted DataFrame

Note: pivot() fails if there are duplicate values for an index/column combination. Use pivot_table() in that case, which supports aggregation functions.

# pivot_table: supports aggregations and duplicate values
pivot_table_result = sales_df.pivot_table(
    index='Date',
    columns='Product',
    values='Sales',
    aggfunc='sum',   # 'mean', 'count', 'min', 'max', etc.
    fill_value=0     # Replace NaN with 0
)

4.2 `melt` Function

The melt function performs the inverse operation of pivot — it transforms a DataFrame from wide format to long format.

flowchart LR
    subgraph "Wide Format"
        W["Participant | Q1 | Q2 | Q3
        Alice        | 4  | 5  | 3
        Bob          | 3  | 4  | 5"]
    end
    subgraph "Long Format (after melt)"
        L["Participant | Question | Response
        Alice        | Q1       | 4
        Alice        | Q2       | 5
        Alice        | Q3       | 3
        Bob          | Q1       | 3
        Bob          | Q2       | 4
        Bob          | Q3       | 5"]
    end
    W --"melt(id_vars='Participant',\nvar_name='Question',\nvalue_name='Response')"-->L

import pandas as pd

# Wide format DataFrame (survey data)
survey_df = pd.DataFrame({
    'Participant': ['Alice', 'Bob', 'Charlie'],
    'Q1': [4, 3, 5],
    'Q2': [5, 4, 3],
    'Q3': [3, 5, 4]
})

# Method 1: pd.melt() — standalone function
melted_1 = pd.melt(
    survey_df,
    id_vars=['Participant'],         # Columns to keep as identifiers
    value_vars=['Q1', 'Q2', 'Q3'],   # Columns to transform
    var_name='Question',             # Name of the variable column
    value_name='Response'            # Name of the value column
)

# Method 2: DataFrame.melt() — instance method
melted_2 = survey_df.melt(
    id_vars=['Participant'],
    var_name='Question',
    value_name='Response'
)

print(melted_1)
#   Participant Question  Response
# 0       Alice       Q1         4
# 1         Bob       Q1         3
# 2     Charlie       Q1         5
# 3       Alice       Q2         5
# ...

melt() Parameters:

Parameter	Description	Default
`id_vars`	Column(s) to use as identifier variables (kept)	`None`
`value_vars`	Column(s) to transform to long format	All except `id_vars`
`var_name`	Name of the column for old headers	`'variable'`
`value_name`	Name of the column for values	`'value'`
`col_level`	Column MultiIndex level to use	`None`

4.3 `crosstab` Function

The crosstab function creates cross-tabulations to analyze relationships between two or more categorical variables.

import pandas as pd

survey_df = pd.DataFrame({
    'Gender': ['M', 'F', 'M', 'F', 'M', 'F', 'M', 'F'],
    'Education': ['BA', 'Master', 'BA', 'Master', 'Master', 'BA', 'Master', 'BA'],
    'Satisfaction': ['High', 'Low', 'High', 'High', 'Low', 'High', 'High', 'Low']
})

# Basic crosstab
ct = pd.crosstab(
    index=survey_df['Gender'],
    columns=survey_df['Education']
)
print(ct)
# Education  BA  Master
# Gender
# F           2       2
# M           2       2

# With subtotals (margins)
ct_margins = pd.crosstab(
    index=survey_df['Gender'],
    columns=survey_df['Education'],
    margins=True,
    margins_name='Total'
)
print(ct_margins)
# Education  BA  Master  Total
# Gender
# F           2       2      4
# M           2       2      4
# Total       4       4      8

# Normalization (proportions)
ct_normalized = pd.crosstab(
    index=survey_df['Gender'],
    columns=survey_df['Education'],
    normalize='all'   # 'all', 'index', 'columns'
)

# Crosstab with values and aggregation function
ct_values = pd.crosstab(
    index=survey_df['Gender'],
    columns=survey_df['Education'],
    values=survey_df['Satisfaction'].map({'High': 1, 'Low': 0}),
    aggfunc='mean'
)

pd.crosstab() Parameters:

Parameter	Description	Default
`index`	Factors that become the cross-tabulation rows	—
`columns`	Factors that become the columns	—
`values`	Values to aggregate (requires `aggfunc`)	`None`
`aggfunc`	Aggregation function (e.g., `'sum'`, `'mean'`, `'count'`)	`None`
`margins`	Include subtotals	`False`
`margins_name`	Name of the subtotals row/column	`'All'`
`normalize`	Normalize by division: `'all'`, `'index'`, `'columns'`	`False`
`dropna`	Exclude columns where all entries are NaN	`True`

5. Reference Tables

5.1 Complete Reference — Combination Functions

Function	Syntax	Type	Default Join	Based On
`pd.merge()`	`pd.merge(df1, df2, on='col', how='inner')`	Module-level	`inner`	Columns or index
`DataFrame.join()`	`df1.join(df2, how='left')`	Instance method	`left`	Index
`pd.concat()`	`pd.concat([df1, df2], axis=0)`	Module-level	`outer` (columns)	No join

5.2 Complete Reference — Join Types

Type	Description	SQL Equivalent	Resulting Rows
`inner`	Only common rows	`INNER JOIN`	Intersection
`left`	All rows from left DataFrame	`LEFT JOIN`	All from left + right matches
`right`	All rows from right DataFrame	`RIGHT JOIN`	All from right + left matches
`outer`	All rows from both DataFrames	`FULL OUTER JOIN`	Union with NaN for missing
`cross`	Cartesian product	`CROSS JOIN`	All possible combinations

5.3 Complete Reference — Reshaping Functions

Function	Direction	Use Case	Constraint
`pivot()`	Long → Wide	Transform DataFrame structure	No index/column duplicates
`pivot_table()`	Long → Wide	Like pivot + aggregation	Supports duplicates
`melt()`	Wide → Long	Normalize columns to rows	`id_vars` must exist
`stack()`	Wide → Long	Stack columns into index	Columns to MultiIndex
`unstack()`	Long → Wide	Unstack index level to columns	Index must be MultiIndex
`crosstab()`	—	Frequency cross-tabulation	Categorical variables

5.4 Complete Reference — Binning Functions

Function	Type	bins Parameter	Bin Distribution
`pd.cut()`	Equal width	`bins=int` or boundary sequence	Equal width, variable frequency
`pd.qcut()`	Equal frequency	`q=int` (quantiles) or sequence	Equal frequency, variable width

6. Category Diagrams

Function Categories

mindmap
  root((Pandas\nFunctions))
    Combination
      merge
        Single column
        Multiple columns
        Inner/Left/Right/Outer
      join
        Inner join
        Outer join
        Left join
        Right join
      concat
        axis=0 rows
        axis=1 columns
        ignore_index
    Discretization
      cut
        Equal width binning
        Fixed boundaries
        Custom labels
      qcut
        Equal frequency binning
        Quantile-based
        Balanced distribution
    Reshaping
      pivot
        Long to Wide
        index / columns / values
      melt
        Wide to Long
        id_vars / var_name
      crosstab
        Cross-tabulation
        Margins and subtotals
        Normalization

Search Terms

pandas · functions · python · foundations · data · analysis · engineering · analytics · function · merge · reference · join · binning · types · crosstab · cut · melt · pivot

Pandas Functions

Table of Contents

1. Overview

2. Merge, Join and Concatenate

2.1 Fundamental Differences

2.2 `merge` Function

Single-column merge

Multiple-column merge

Join Types with `merge`

2.3 `join` Function

2.4 `concat` Function

The `ignore_index` Parameter

3. Binning with Pandas

3.1 Types of Binning

3.2 `cut` Function

Custom Cut with Advanced Parameters

3.3 `qcut` Function

4. Pivot, Melt and Crosstab

4.1 `pivot` Function

4.2 `melt` Function

4.3 `crosstab` Function

5. Reference Tables

5.1 Complete Reference — Combination Functions

5.2 Complete Reference — Join Types

5.3 Complete Reference — Reshaping Functions

5.4 Complete Reference — Binning Functions

6. Category Diagrams

Function Categories

Search Terms

More Python Foundations & Data Analysis courses

Python Data Essentials: Python Introduction

Python Data Essentials: Programming Fundamentals

Up and Running with Pandas

Cleaning Data with Pandas

Normalize Data for Analysis with Pandas

Plotting Data with Pandas

Interested in this course?

Table of Contents

1. Overview

2. Merge, Join and Concatenate

2.1 Fundamental Differences

2.2 merge Function

Single-column merge

Multiple-column merge

Join Types with merge

2.3 join Function

2.4 concat Function

The ignore_index Parameter

3. Binning with Pandas

3.1 Types of Binning

3.2 cut Function

Custom Cut with Advanced Parameters

3.3 qcut Function

4. Pivot, Melt and Crosstab

4.1 pivot Function

4.2 melt Function

4.3 crosstab Function

5. Reference Tables

5.1 Complete Reference — Combination Functions

5.2 Complete Reference — Join Types

5.3 Complete Reference — Reshaping Functions

5.4 Complete Reference — Binning Functions

6. Category Diagrams

Function Categories

Search Terms

More Python Foundations & Data Analysis courses

Python Data Essentials: Python Introduction

Python Data Essentials: Programming Fundamentals

Up and Running with Pandas

Cleaning Data with Pandas

Normalize Data for Analysis with Pandas

Plotting Data with Pandas

Interested in this course?

2.2 `merge` Function

Join Types with `merge`

2.3 `join` Function

2.4 `concat` Function

The `ignore_index` Parameter

3.2 `cut` Function

3.3 `qcut` Function

4.1 `pivot` Function

4.2 `melt` Function

4.3 `crosstab` Function