DataFrame Transformations and Reshaping

Notebook de Jupyter, 6 min de lectura.pandas/pandas_transformations.ipynb

En esta página

Element-wise Operations: map, apply, transform
map - Series Only, Element-wise
apply - Flexible, Works on Series and DataFrames
transform - Same Shape Output, Group-Aware
Comparison: When to Use Each
Grouping Operations
groupby - Split-Apply-Combine
agg - Multiple Aggregations
transform with groupby - Broadcasting Group Results
apply with groupby - Flexible Group Operations
Summary: agg vs transform vs apply with groupby
The assign Method
Reshaping Data: melt and pivot
Wide vs Long Format
melt - Wide to Long
pivot and pivot_table - Long to Wide
pivot_table - Handles Duplicates with Aggregation
pivot_table vs groupby + agg
Summary

This notebook covers advanced pandas operations for transforming, aggregating, and reshaping data.

Prerequisites: Familiarity with basic DataFrame operations (indexing, filtering, sorting) covered in DataFrame Operations.

Topics covered:

Element-wise operations: map, apply, transform
Grouping operations: groupby, agg, group-wise transform and apply
Reshaping: melt, pivot, pivot_table

import pandas as pd
import numpy as np

# Sample dataset for demonstrations
np.random.seed(42)
df = pd.DataFrame({
    'student': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve', 'Frank'],
    'group': ['A', 'A', 'B', 'B', 'C', 'C'],
    'math': [85, 90, 78, 92, 88, 76],
    'physics': [80, 85, 82, 88, 90, 70],
    'chemistry': [75, 88, 80, 95, 85, 72]
})
df

	student	group	math	physics	chemistry
0	Alice	A	85	80	75
1	Bob	A	90	85	88
2	Charlie	B	78	82	80
3	Diana	B	92	88	95
4	Eve	C	88	90	85
5	Frank	C	76	70	72

Element-wise Operations: `map`, `apply`, `transform`

`map` - Series Only, Element-wise

map is used exclusively on Series to apply a function, dictionary, or another Series element-by-element.

Use cases:

Replacing values using a dictionary or function
Simple element-wise transformations on a single column

# map with a function
df['math'].map(lambda x: x + 5)  # Add 5 to each math grade

0    90
1    95
2    83
3    97
4    93
5    81
Name: math, dtype: int64

# map with a dictionary - useful for categorical replacements
group_names = {'A': 'Alpha', 'B': 'Beta', 'C': 'Gamma'}
df['group'].map(group_names)

0    Alpha
1    Alpha
2     Beta
3     Beta
4    Gamma
5    Gamma
Name: group, dtype: object

# map returns NaN for values not in the dictionary
incomplete_map = {'A': 'Alpha', 'B': 'Beta'}  # Missing 'C'
df['group'].map(incomplete_map)  # 'C' becomes NaN

0    Alpha
1    Alpha
2     Beta
3     Beta
4      NaN
5      NaN
Name: group, dtype: object

Note: For dictionary mapping where you want to keep original values for missing keys, use replace() instead of map().

# replace keeps original values for missing keys
df['group'].replace({'A': 'Alpha', 'B': 'Beta'})  # 'C' stays as 'C'

0    Alpha
1    Alpha
2     Beta
3     Beta
4        C
5        C
Name: group, dtype: object

`apply` - Flexible, Works on Series and DataFrames

apply is more versatile than map:

On a Series: applies a function element-wise (similar to map)
On a DataFrame: applies a function along an axis (rows or columns)

# apply on Series - similar to map
df['math'].apply(lambda x: x + 5)

0    90
1    95
2    83
3    97
4    93
5    81
Name: math, dtype: int64

# apply on DataFrame - operates on entire columns (axis=0, default)
df[['math', 'physics', 'chemistry']].apply(lambda col: col.max() - col.min())

math         16
physics      20
chemistry    23
dtype: int64

# apply on DataFrame - operates on entire rows (axis=1)
df[['math', 'physics', 'chemistry']].apply(lambda row: row.mean(), axis=1)

0    80.000000
1    87.666667
2    80.000000
3    91.666667
4    87.666667
5    72.666667
dtype: float64

Key insight: When using apply on a DataFrame, the function receives an entire Series (a column or a row), not individual values.

# Demonstrating what apply receives
def show_input(x):
    print(f"Type: {type(x).__name__}, Shape: {x.shape}")
    print(x)
    print("---")
    return x.sum()

df[['math', 'physics']].apply(show_input)  # Each column is passed as a Series

Type: Series, Shape: (6,)
0    85
1    90
2    78
3    92
4    88
5    76
Name: math, dtype: int64
---
Type: Series, Shape: (6,)
0    80
1    85
2    82
3    88
4    90
5    70
Name: physics, dtype: int64
---

math       509
physics    495
dtype: int64

`transform` - Same Shape Output, Group-Aware

transform always returns a result with the same shape as the input. This is crucial for:

Broadcasting group-level calculations back to individual rows
Ensuring alignment with the original DataFrame

# transform on a Series - element-wise, same as map/apply
df['math'].transform(lambda x: x + 5)

0    90
1    95
2    83
3    97
4    93
5    81
Name: math, dtype: int64

# transform on DataFrame columns
df[['math', 'physics', 'chemistry']].transform(lambda x: (x - x.mean()) / x.std())

	math	physics	chemistry
0	0.025545	-0.349727	-0.877208
1	0.791910	0.349727	0.643286
2	-1.047364	-0.069945	-0.292403
3	1.098455	0.769400	1.462013
4	0.485364	1.049182	0.292403
5	-1.353910	-1.748637	-1.228091

Comparison: When to Use Each

Method	Works On	Input to Function	Output Shape	Best For
`map`	Series only	Single value	Same as input	Value replacement, simple transforms
`apply`	Series & DataFrame	Value (Series) or Series (DataFrame)	Can differ	Flexible operations, aggregations
`transform`	Series & DataFrame	Series	Must match input	Normalization, group broadcasting

# Equivalent operations - all add 5 to math grades
print("map:      ", df['math'].map(lambda x: x + 5).tolist())
print("apply:    ", df['math'].apply(lambda x: x + 5).tolist())
print("transform:", df['math'].transform(lambda x: x + 5).tolist())

map:       [90, 95, 83, 97, 93, 81]
apply:     [90, 95, 83, 97, 93, 81]
transform: [90, 95, 83, 97, 93, 81]

# Only map works with dictionaries directly
df['group'].map({'A': 'Alpha', 'B': 'Beta', 'C': 'Gamma'})
# df['group'].apply({'A': 'Alpha'})  # Would raise an error
# df['group'].transform({'A': 'Alpha'})  # Would raise an error

0    Alpha
1    Alpha
2     Beta
3     Beta
4    Gamma
5    Gamma
Name: group, dtype: object

# Only apply can reduce dimensions (return different shape)
df[['math', 'physics', 'chemistry']].apply(lambda x: x.sum())  # Returns a Series with 3 values
# df[['math', 'physics', 'chemistry']].transform(lambda x: x.sum())  # Would broadcast the sum to all rows

math         509
physics      495
chemistry    495
dtype: int64

Grouping Operations

`groupby` - Split-Apply-Combine

The groupby operation follows a split-apply-combine pattern:

Split the data into groups based on one or more keys
Apply a function to each group independently
Combine the results into a new data structure

# Creating a groupby object
grouped = df.groupby('group')
print(type(grouped))
print(f"Number of groups: {grouped.ngroups}")
print(f"Groups: {grouped.groups}")

<class 'pandas.core.groupby.generic.DataFrameGroupBy'>
Number of groups: 3
Groups: {'A': [0, 1], 'B': [2, 3], 'C': [4, 5]}

# Iterating over groups
for name, group_df in grouped:
    print(f"Group: {name}")
    print(group_df)
    print()

Group: A
  student group  math  physics  chemistry
0   Alice     A    85       80         75
1     Bob     A    90       85         88

Group: B
   student group  math  physics  chemistry
2  Charlie     B    78       82         80
3    Diana     B    92       88         95

Group: C
  student group  math  physics  chemistry
4     Eve     C    88       90         85
5   Frank     C    76       70         72

# Get a specific group
grouped.get_group('A')

	student	group	math	physics	chemistry
0	Alice	A	85	80	75
1	Bob	A	90	85	88

# Basic aggregation
df.groupby('group')['math'].mean()

group
A    87.5
B    85.0
C    82.0
Name: math, dtype: float64

# Multiple columns
df.groupby('group')[['math', 'physics', 'chemistry']].mean()

	math	physics	chemistry
group
A	87.5	82.5	81.5
B	85.0	85.0	87.5
C	82.0	80.0	78.5

`agg` - Multiple Aggregations

The agg (or aggregate) method allows applying multiple aggregation functions at once, and even different functions to different columns.

# Single aggregation (equivalent to .mean())
df.groupby('group')['math'].agg('mean')

group
A    87.5
B    85.0
C    82.0
Name: math, dtype: float64

# Multiple aggregations on one column
df.groupby('group')['math'].agg(['mean', 'std', 'min', 'max'])

	mean	std	min	max
group
A	87.5	3.535534	85	90
B	85.0	9.899495	78	92
C	82.0	8.485281	76	88

# Multiple aggregations on multiple columns
df.groupby('group')[['math', 'physics']].agg(['mean', 'std'])

	math		physics
	mean	std	mean	std
group
A	87.5	3.535534	82.5	3.535534
B	85.0	9.899495	85.0	4.242641
C	82.0	8.485281	80.0	14.142136

# Different aggregations for different columns using a dictionary
df.groupby('group').agg({
    'math': 'mean',
    'physics': ['min', 'max'],
    'chemistry': 'std'
})

	math	physics		chemistry
	mean	min	max	std
group
A	87.5	80	85	9.192388
B	85.0	82	88	10.606602
C	82.0	70	90	9.192388

# Named aggregations (cleaner column names)
df.groupby('group').agg(
    math_avg=('math', 'mean'),
    physics_range=('physics', lambda x: x.max() - x.min()),
    chemistry_std=('chemistry', 'std')
)

	math_avg	physics_range	chemistry_std
group
A	87.5	5	9.192388
B	85.0	6	10.606602
C	82.0	20	9.192388

`transform` with `groupby` - Broadcasting Group Results

The real power of transform shines when combined with groupby. It broadcasts group-level calculations back to each row, maintaining the original DataFrame's shape.

# Problem: Add a column with each student's group average
# Using apply - returns aggregated result (3 rows)
df.groupby('group')['math'].apply(lambda x: x.mean())

group
A    87.5
B    85.0
C    82.0
Name: math, dtype: float64

# Using transform - broadcasts back to original shape (6 rows)
df.groupby('group')['math'].transform('mean')

0    87.5
1    87.5
2    85.0
3    85.0
4    82.0
5    82.0
Name: math, dtype: float64

# Adding group mean as a new column
df_with_group_mean = df.assign(
    group_math_avg=df.groupby('group')['math'].transform('mean')
)
df_with_group_mean

	student	group	math	physics	chemistry	group_math_avg
0	Alice	A	85	80	75	87.5
1	Bob	A	90	85	88	87.5
2	Charlie	B	78	82	80	85.0
3	Diana	B	92	88	95	85.0
4	Eve	C	88	90	85	82.0
5	Frank	C	76	70	72	82.0

# Normalize within each group (z-score per group)
df.assign(
    math_normalized=df.groupby('group')['math'].transform(
        lambda x: (x - x.mean()) / x.std()
    )
)

	student	group	math	physics	chemistry	math_normalized
0	Alice	A	85	80	75	-0.707107
1	Bob	A	90	85	88	0.707107
2	Charlie	B	78	82	80	-0.707107
3	Diana	B	92	88	95	0.707107
4	Eve	C	88	90	85	0.707107
5	Frank	C	76	70	72	-0.707107

# Rank within each group
df.assign(
    math_rank_in_group=df.groupby('group')['math'].transform('rank', ascending=False)
)

	student	group	math	physics	chemistry	math_rank_in_group
0	Alice	A	85	80	75	2.0
1	Bob	A	90	85	88	1.0
2	Charlie	B	78	82	80	2.0
3	Diana	B	92	88	95	1.0
4	Eve	C	88	90	85	1.0
5	Frank	C	76	70	72	2.0

`apply` with `groupby` - Flexible Group Operations

apply on a grouped object is the most flexible option. The function receives the entire group as a DataFrame and can return:

A scalar (aggregation)
A Series (transformation)
A DataFrame (complex transformations)

# Custom function that returns a scalar per group
def grade_spread(group):
    return group['math'].max() - group['math'].min()

df.groupby('group').apply(grade_spread, include_groups=False)

group
A     5
B    14
C    12
dtype: int64

# Custom function that returns a DataFrame (e.g., top student per group)
def top_student(group):
    return group.nlargest(1, 'math')

df.groupby('group').apply(top_student, include_groups=False)

		student	math	physics	chemistry
group
A	1	Bob	90	85	88
B	3	Diana	92	88	95
C	4	Eve	88	90	85

Summary: `agg` vs `transform` vs `apply` with groupby

Method	Output Shape	Use Case
`agg`	One row per group	Summary statistics, multiple aggregations
`transform`	Same as input	Broadcasting group stats back to rows
`apply`	Flexible	Complex operations, custom logic

The `assign` Method

assign creates new columns (or overwrites existing ones) and returns a new DataFrame. It's particularly useful for method chaining.

# Traditional way to add a column
df_copy = df.copy()
df_copy['average'] = (df_copy['math'] + df_copy['physics'] + df_copy['chemistry']) / 3
df_copy

	student	group	math	physics	chemistry	average
0	Alice	A	85	80	75	80.000000
1	Bob	A	90	85	88	87.666667
2	Charlie	B	78	82	80	80.000000
3	Diana	B	92	88	95	91.666667
4	Eve	C	88	90	85	87.666667
5	Frank	C	76	70	72	72.666667

# Using assign (doesn't modify original)
df.assign(
    average=(df['math'] + df['physics'] + df['chemistry']) / 3
)

	student	group	math	physics	chemistry	average
0	Alice	A	85	80	75	80.000000
1	Bob	A	90	85	88	87.666667
2	Charlie	B	78	82	80	80.000000
3	Diana	B	92	88	95	91.666667
4	Eve	C	88	90	85	87.666667
5	Frank	C	76	70	72	72.666667

# assign with lambda - references the DataFrame being transformed
df.assign(
    average=lambda x: (x['math'] + x['physics'] + x['chemistry']) / 3
)

	student	group	math	physics	chemistry	average
0	Alice	A	85	80	75	80.000000
1	Bob	A	90	85	88	87.666667
2	Charlie	B	78	82	80	80.000000
3	Diana	B	92	88	95	91.666667
4	Eve	C	88	90	85	87.666667
5	Frank	C	76	70	72	72.666667

# Multiple columns at once, with dependencies between them
df.assign(
    average=lambda x: (x['math'] + x['physics'] + x['chemistry']) / 3,
    passed=lambda x: x['average'] >= 80,  # Uses the average column just created!
    group_avg=lambda x: x.groupby('group')['average'].transform('mean')
)

	student	group	math	physics	chemistry	average	passed	group_avg
0	Alice	A	85	80	75	80.000000	True	83.833333
1	Bob	A	90	85	88	87.666667	True	83.833333
2	Charlie	B	78	82	80	80.000000	True	85.833333
3	Diana	B	92	88	95	91.666667	True	85.833333
4	Eve	C	88	90	85	87.666667	True	80.166667
5	Frank	C	76	70	72	72.666667	False	80.166667

Why use assign?

Supports method chaining (functional style)
Doesn't modify the original DataFrame
Lambda functions can reference columns created in the same assign call

# Method chaining example
(
    df
    .assign(average=lambda x: x[['math', 'physics', 'chemistry']].mean(axis=1))
    .query('average >= 80')
    .sort_values('average', ascending=False)
)

	student	group	math	physics	chemistry	average
3	Diana	B	92	88	95	91.666667
4	Eve	C	88	90	85	87.666667
1	Bob	A	90	85	88	87.666667
0	Alice	A	85	80	75	80.000000
2	Charlie	B	78	82	80	80.000000

Reshaping Data: `melt` and `pivot`

Wide vs Long Format

Data can be organized in two main formats:

Wide format: Each variable has its own column (e.g., math, physics, chemistry as separate columns)
Long format: Variables are stacked into two columns: one for the variable name, one for the value

Different analyses require different formats. melt converts wide to long, pivot converts long to wide.

# Our data is currently in wide format
df

	student	group	math	physics	chemistry
0	Alice	A	85	80	75
1	Bob	A	90	85	88
2	Charlie	B	78	82	80
3	Diana	B	92	88	95
4	Eve	C	88	90	85
5	Frank	C	76	70	72

`melt` - Wide to Long

melt "unpivots" a DataFrame from wide to long format.

# Convert to long format
df_long = df.melt(
    id_vars=['student', 'group'],      # Columns to keep as identifiers
    value_vars=['math', 'physics', 'chemistry'],  # Columns to unpivot
    var_name='subject',                # Name for the variable column
    value_name='grade'                 # Name for the value column
)
df_long

	student	group	subject	grade
0	Alice	A	math	85
1	Bob	A	math	90
2	Charlie	B	math	78
3	Diana	B	math	92
4	Eve	C	math	88
5	Frank	C	math	76
6	Alice	A	physics	80
7	Bob	A	physics	85
8	Charlie	B	physics	82
9	Diana	B	physics	88
10	Eve	C	physics	90
11	Frank	C	physics	70
12	Alice	A	chemistry	75
13	Bob	A	chemistry	88
14	Charlie	B	chemistry	80
15	Diana	B	chemistry	95
16	Eve	C	chemistry	85
17	Frank	C	chemistry	72

# If value_vars is omitted, all columns not in id_vars are melted
df.melt(id_vars=['student', 'group'], var_name='subject', value_name='grade')

	student	group	subject	grade
0	Alice	A	math	85
1	Bob	A	math	90
2	Charlie	B	math	78
3	Diana	B	math	92
4	Eve	C	math	88
5	Frank	C	math	76
6	Alice	A	physics	80
7	Bob	A	physics	85
8	Charlie	B	physics	82
9	Diana	B	physics	88
10	Eve	C	physics	90
11	Frank	C	physics	70
12	Alice	A	chemistry	75
13	Bob	A	chemistry	88
14	Charlie	B	chemistry	80
15	Diana	B	chemistry	95
16	Eve	C	chemistry	85
17	Frank	C	chemistry	72

When is long format useful?

Aggregating across categories (e.g., average grade per subject)
Many visualization libraries prefer long format
Easier to filter by category

# Average grade per subject (easier in long format)
df_long.groupby('subject')['grade'].mean()

subject
chemistry    82.500000
math         84.833333
physics      82.500000
Name: grade, dtype: float64

# Average grade per group and subject
df_long.groupby(['group', 'subject'])['grade'].mean()

group  subject  
A      chemistry    81.5
       math         87.5
       physics      82.5
B      chemistry    87.5
       math         85.0
       physics      85.0
C      chemistry    78.5
       math         82.0
       physics      80.0
Name: grade, dtype: float64

`pivot` and `pivot_table` - Long to Wide

pivot reshapes data from long to wide format. pivot_table is similar but handles duplicate entries by aggregating them.

# pivot - convert back to wide format
df_wide = df_long.pivot(
    index=['student', 'group'],  # Columns to use as row identifiers
    columns='subject',            # Column whose values become new columns
    values='grade'                # Values to fill the new columns
)
df_wide

	subject	chemistry	math	physics
student	group
Alice	A	75	85	80
Bob	A	88	90	85
Charlie	B	80	78	82
Diana	B	95	92	88
Eve	C	85	88	90
Frank	C	72	76	70

# Clean up the result
df_wide.reset_index().rename_axis(None, axis=1)

	student	group	chemistry	math	physics
0	Alice	A	75	85	80
1	Bob	A	88	90	85
2	Charlie	B	80	78	82
3	Diana	B	95	92	88
4	Eve	C	85	88	90
5	Frank	C	72	76	70

pivot fails with duplicate entries:

# If we try to pivot grouped data, it fails because there are multiple values per cell
try:
    df_long.pivot(index='group', columns='subject', values='grade')
except ValueError as e:
    print(f"Error: {e}")

Error: Index contains duplicate entries, cannot reshape

`pivot_table` - Handles Duplicates with Aggregation

# pivot_table aggregates duplicates (default is mean)
df_long.pivot_table(
    index='group',
    columns='subject',
    values='grade',
    aggfunc='mean'
)

subject	chemistry	math	physics
group
A	81.5	87.5	82.5
B	87.5	85.0	85.0
C	78.5	82.0	80.0

# Multiple aggregations
df_long.pivot_table(
    index='group',
    columns='subject',
    values='grade',
    aggfunc=['mean', 'std']
)

	mean			std
subject	chemistry	math	physics	chemistry	math	physics
group
A	81.5	87.5	82.5	9.192388	3.535534	3.535534
B	87.5	85.0	85.0	10.606602	9.899495	4.242641
C	78.5	82.0	80.0	9.192388	8.485281	14.142136

# Add margins (totals)
df_long.pivot_table(
    index='group',
    columns='subject',
    values='grade',
    aggfunc='mean',
    margins=True,
    margins_name='Overall'
)

subject	chemistry	math	physics	Overall
group
A	81.5	87.500000	82.5	83.833333
B	87.5	85.000000	85.0	85.833333
C	78.5	82.000000	80.0	80.166667
Overall	82.5	84.833333	82.5	83.277778

`pivot_table` vs `groupby` + `agg`

pivot_table is essentially a groupby followed by reshaping. These are equivalent:

# Using pivot_table
pt = df_long.pivot_table(index='group', columns='subject', values='grade', aggfunc='mean')
pt

subject	chemistry	math	physics
group
A	81.5	87.5	82.5
B	87.5	85.0	85.0
C	78.5	82.0	80.0

# Equivalent using groupby
gb = df_long.groupby(['group', 'subject'])['grade'].mean().unstack()
gb

subject	chemistry	math	physics
group
A	81.5	87.5	82.5
B	87.5	85.0	85.0
C	78.5	82.0	80.0

Summary

Operation	Purpose	Key Behavior
`map`	Element-wise on Series	Dictionary/function mapping
`apply`	Flexible transformation	Works on Series, rows, or columns
`transform`	Same-shape transformation	Essential for group broadcasting
`groupby`	Split-apply-combine	Foundation for group operations
`agg`	Multiple aggregations	Reduce groups to summary values
`assign`	Add columns fluently	Method chaining, immutable
`melt`	Wide → Long	Unpivot columns to rows
`pivot`	Long → Wide (unique)	Reshape without aggregation
`pivot_table`	Long → Wide (duplicates)	Reshape with aggregation

Element-wise Operations: map, apply, transform

map - Series Only, Element-wise

apply - Flexible, Works on Series and DataFrames

transform - Same Shape Output, Group-Aware

Comparison: When to Use Each

Grouping Operations

groupby - Split-Apply-Combine

agg - Multiple Aggregations

transform with groupby - Broadcasting Group Results

apply with groupby - Flexible Group Operations

Summary: agg vs transform vs apply with groupby

The assign Method

Reshaping Data: melt and pivot