McKinney Chapter 8 - Data Wrangling: Join, Combine, and Reshape

FINA 6333 for Spring 2025

Author

Richard Herron 

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import pandas_datareader as pdr
import yfinance as yf
%precision 4
pd.options.display.float_format = '{:.4f}'.format
# %config InlineBackend.figure_format = 'retina'

Introduction

Chapter 8 of McKinney (2022) introduces a few important pandas concepts:

  1. Joining or merging is combining 2+ data frames on 1+ indexes or columns into 1 data frame
  2. Reshaping is rearranging a data frame so it has fewer columns and more rows (wide to long) or more columns and fewer rows (long to wide)

Note: Indented block quotes are from McKinney (2022) unless otherwise indicated. The section numbers here differ from McKinney (2022) because we will only discuss some topics.

Hierarchical Indexing

We must learn about hierarchical indexing before we learn about combining and reshaping data. A hierarchical index has two or more levels. For example, we could index rows by ticker and date. Or we could index columns by variable and ticker. Hierarchical indexing helps us work with high-dimensional data in a low-dimensional form.

np.random.seed(42)
data = pd.Series(
    data=np.random.randn(9),
    index=[
        ['a', 'a', 'a', 'b', 'b', 'c', 'c', 'd', 'd'],
        [1, 2, 3, 1, 3, 1, 2, 2, 3]
    ]
)

data
a  1    0.4967
   2   -0.1383
   3    0.6477
b  1    1.5230
   3   -0.2342
c  1   -0.2341
   2    1.5792
d  2    0.7674
   3   -0.4695
dtype: float64

We can index this series to subset it.

data['b']
1    1.5230
3   -0.2342
dtype: float64
data.loc['b']
1    1.5230
3   -0.2342
dtype: float64
data['b':'c']
b  1    1.5230
   3   -0.2342
c  1   -0.2341
   2    1.5792
dtype: float64
data.loc['b':'c']
b  1    1.5230
   3   -0.2342
c  1   -0.2341
   2    1.5792
dtype: float64

We can subset on the index inner level, too. Here, the : slices all values in the outer index, and the 2 slices the three values with 2 indexes.

data.loc[:, 2]
a   -0.1383
c    1.5792
d    0.7674
dtype: float64

Here, data has a stacked or long format. We have multiple observations for each outer index (letters) with different inner indexes (numbers). We can un-stack data to convert the inner index level to columns. New, we have an unstacked or wide format.

data.unstack()
1 2 3
a 0.4967 -0.1383 0.6477
b 1.5230 NaN -0.2342
c -0.2341 1.5792 NaN
d NaN 0.7674 -0.4695

We can create a data frame with hierarchical indexes or multi-indexes on rows and columns.

frame = pd.DataFrame(
    data=np.arange(12).reshape((4, 3)),
    index=[['a', 'a', 'b', 'b'], [1, 2, 1, 2]],
    columns=[['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']]
)

frame
Ohio Colorado
Green Red Green
a 1 0 1 2
2 3 4 5
b 1 6 7 8
2 9 10 11

We can name these multi-indexes, but index names are not required.

frame.index.names = ['key1', 'key2']
frame.columns.names = ['state', 'color']

frame
state Ohio Colorado
color Green Red Green
key1 key2
a 1 0 1 2
2 3 4 5
b 1 6 7 8
2 9 10 11

Recall that df[val] selects the val column. Here, frame has a multi-index for the columns, so frame['Ohio'] selects all columns with Ohio as the outer index.

frame['Ohio']
color Green Red
key1 key2
a 1 0 1
2 3 4
b 1 6 7
2 9 10

We can pass a tuple if we only want one column.

frame[[('Ohio', 'Green')]]
state Ohio
color Green
key1 key2
a 1 0
2 3
b 1 6
2 9

We must do more work to slice the inner level of the column index.

frame.loc[:, (slice(None), 'Green')]
state Ohio Colorado
color Green Green
key1 key2
a 1 0 2
2 3 5
b 1 6 8
2 9 11

We can use pd.IndexSlice[:, 'Green'] an alternative to (slice(None), 'Green').

frame.loc[:, pd.IndexSlice[:, 'Green']]
state Ohio Colorado
color Green Green
key1 key2
a 1 0 2
2 3 5
b 1 6 8
2 9 11

Reordering and Sorting Levels

We can swap index levels with the .swaplevel() method. The default arguments are i=-2 and j=-1, which swap the two innermost index levels.

frame
state Ohio Colorado
color Green Red Green
key1 key2
a 1 0 1 2
2 3 4 5
b 1 6 7 8
2 9 10 11 
frame.swaplevel().sort_index()
state Ohio Colorado
color Green Red Green
key2 key1
1 a 0 1 2
b 6 7 8
2 a 3 4 5
b 9 10 11

We can use index names, too.

frame.swaplevel('key1', 'key2').sort_index()
state Ohio Colorado
color Green Red Green
key2 key1
1 a 0 1 2
b 6 7 8
2 a 3 4 5
b 9 10 11

Indexing with a DataFrame’s columns

We can convert a column into an index and an index into a column with the .set_index() and .reset_index() methods.

frame = pd.DataFrame({
    'a': range(7), 
    'b': range(7, 0, -1),
    'c': ['one', 'one', 'one', 'two', 'two','two', 'two'],
    'd': [0, 1, 2, 0, 1, 2, 3]
})

frame
a b c d
0 0 7 one 0
1 1 6 one 1
2 2 5 one 2
3 3 4 two 0
4 4 3 two 1
5 5 2 two 2
6 6 1 two 3

The .set_index() method converts columns to indexes and drops these columns by default.

frame2 = frame.set_index(['c', 'd'])

frame2
a b
c d
one 0 0 7
1 1 6
2 2 5
two 0 3 4
1 4 3
2 5 2
3 6 1

The .reset_index() method drops indexes, adds them as columns by default, and sets an integer index.

frame2.reset_index()
c d a b
0 one 0 0 7
1 one 1 1 6
2 one 2 2 5
3 two 0 3 4
4 two 1 4 3
5 two 2 5 2
6 two 3 6 1

Combining and Merging Datasets

pandas provides several methods and functions to combine and merge data. We can typically create the same output with several these methods or functions, but one may be more efficient.

When we want to combine data frames with similar indexes, we will tend to use the .join() method. The .join() can also combine three or more data frames.

Otherwise, we will use the .merge() method or pd.merge() function. The pd.merge() function is more flexible than the .join() method, so we will start with the pd.merge() function.

The pandas website provides helpful visualizations.

Database-Style DataFrame Joins

Merge or join operations combine datasets by linking rows using one or more keys. These operations are central to relational databases (e.g., SQL-based). The merge function in pandas is the main entry point for using these algorithms on your data.

df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
df2 = pd.DataFrame({'key': ['a', 'b', 'd'], 'data2': range(3)})
df1
key data1
0 b 0
1 b 1
2 a 2
3 c 3
4 a 4
5 a 5
6 b 6 
df2
key data2
0 a 0
1 b 1
2 d 2 
pd.merge(df1, df2)
key data1 data2
0 b 0 1
1 b 1 1
2 a 2 0
3 a 4 0
4 a 5 0
5 b 6 1

The default is how='inner', so pd.merge() inner joins left and right data frames by default, keeping only rows that appear in both. We can specify how='outer', so pd.merge() outer joins left and right data frames, keeping all rows that appear in either.

pd.merge(df1, df2, how='outer')
key data1 data2
0 a 2.0000 0.0000
1 a 4.0000 0.0000
2 a 5.0000 0.0000
3 b 0.0000 1.0000
4 b 1.0000 1.0000
5 b 6.0000 1.0000
6 c 3.0000 NaN
7 d NaN 2.0000

A how='left' merge keeps only rows that appear in the left data frame.

pd.merge(df1, df2, how='left')
key data1 data2
0 b 0 1.0000
1 b 1 1.0000
2 a 2 0.0000
3 c 3 NaN
4 a 4 0.0000
5 a 5 0.0000
6 b 6 1.0000

A how='right' merge keeps only rows that appear in the right data frame.

pd.merge(df1, df2, how='right')
key data1 data2
0 a 2.0000 0
1 a 4.0000 0
2 a 5.0000 0
3 b 0.0000 1
4 b 1.0000 1
5 b 6.0000 1
6 d NaN 2

By default, pd.merge() merges on any columns that appear in both data frames.

on : label or list Column or index level names to join on. These must be found in both DataFrames. If on is None and not merging on indexes then this defaults to the intersection of the columns in both DataFrames.

Here, key is the only common column between df1 and df2. We should specify on='key' to avoid unexpected results.

pd.merge(df1, df2, on='key')
key data1 data2
0 b 0 1
1 b 1 1
2 a 2 0
3 a 4 0
4 a 5 0
5 b 6 1

We must specify left_on and right_on if our left and right data frames do not have a common column.

df3 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
df4 = pd.DataFrame({'rkey': ['a', 'b', 'd'], 'data2': range(3)})
df3
lkey data1
0 b 0
1 b 1
2 a 2
3 c 3
4 a 4
5 a 5
6 b 6 
df4
rkey data2
0 a 0
1 b 1
2 d 2 
# pd.merge(df3, df4) # this code fails/errors because there are not common columns
# MergeError: No common columns to perform merge on. Merge options: left_on=None, right_on=None, left_index=False, right_index=False
pd.merge(df3, df4, left_on='lkey', right_on='rkey')
lkey data1 rkey data2
0 b 0 b 1
1 b 1 b 1
2 a 2 a 0
3 a 4 a 0
4 a 5 a 0
5 b 6 b 1

Here, pd.merge() drops row c from df3 and row d from df4 because pd.merge() inner joins by default. An inner join keeps the intersection of the left and right data frame keys. If we want to keep rows c and d, we can outer join df3 and df4 with how='outer'.

pd.merge(df3, df4, left_on='lkey', right_on='rkey', how='outer')
lkey data1 rkey data2
0 a 2.0000 a 0.0000
1 a 4.0000 a 0.0000
2 a 5.0000 a 0.0000
3 b 0.0000 b 1.0000
4 b 1.0000 b 1.0000
5 b 6.0000 b 1.0000
6 c 3.0000 NaN NaN
7 NaN NaN d 2.0000

Many-to-many merges have well-defined, though not necessarily intuitive, behavior.

df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'], 'data1': range(6)})
df2 = pd.DataFrame({'key': ['a', 'b', 'a', 'b', 'd'], 'data2': range(5)})
df1
key data1
0 b 0
1 b 1
2 a 2
3 c 3
4 a 4
5 b 5 
df2
key data2
0 a 0
1 b 1
2 a 2
3 b 3
4 d 4 
pd.merge(df1, df2, on='key')
key data1 data2
0 b 0 1
1 b 0 3
2 b 1 1
3 b 1 3
4 a 2 0
5 a 2 2
6 a 4 0
7 a 4 2
8 b 5 1
9 b 5 3

Many-to-many joins form the Cartesian product of the rows. Since there were three b rows in the left DataFrame and two in the right one, there are six b rows in the result. The join method only affects the distinct key values appearing in the result.

Be careful with many-to-many joins! In finance, we do not expect many-to-many joins because we expect at least one of the data frames to have unique observations. pandas will not warn us if we accidentally perform a many-to-many join instead of a one-to-one or many-to-one join.

# pd.merge(df1, df2, on='key', validate='1:1')
# MergeError: Merge keys are not unique in either left or right dataset; not a one-to-one merge

We can merge on more than one key. For example, we can merge two data sets on ticker-date pairs or industry-date pairs.

left = pd.DataFrame({'key1': ['foo', 'foo', 'bar'],
                     'key2': ['one', 'two', 'one'],
                     'lval': [1, 2, 3]})
right = pd.DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],
                      'key2': ['one', 'one', 'one', 'two'],
                      'rval': [4, 5, 6, 7]})
left
key1 key2 lval
0 foo one 1
1 foo two 2
2 bar one 3 
right
key1 key2 rval
0 foo one 4
1 foo one 5
2 bar one 6
3 bar two 7 
pd.merge(left, right, on=['key1', 'key2'], how='outer')
key1 key2 lval rval
0 bar one 3.0000 6.0000
1 bar two NaN 7.0000
2 foo one 1.0000 4.0000
3 foo one 1.0000 5.0000
4 foo two 2.0000 NaN

When column names overlap between the left and right data frames, pd.merge() appends _x and _y to the left and right versions of the overlapping column names.

pd.merge(left, right, on='key1')
key1 key2_x lval key2_y rval
0 foo one 1 one 4
1 foo one 1 one 5
2 foo two 2 one 4
3 foo two 2 one 5
4 bar one 3 one 6
5 bar one 3 two 7

I typically specify the suffixes argument to avoid confusion.

pd.merge(left, right, on='key1', suffixes=('_left', '_right'))
key1 key2_left lval key2_right rval
0 foo one 1 one 4
1 foo one 1 one 5
2 foo two 2 one 4
3 foo two 2 one 5
4 bar one 3 one 6
5 bar one 3 two 7

I read the pd.merge() docstring frequently! Table 8-2 summarizes the commonly used arguments for pd.merge().

  • left: DataFrame to be merged on the left side.
  • right: DataFrame to be merged on the right side.
  • how: One of ‘inner’, ‘outer’, ‘left’, or ‘right’; defaults to ‘inner’.
  • on: Column names to join on. Must be found in both DataFrame objects. If not specified and no other join keys given will use the intersection of the column names in left and right as the join keys.
  • left_on: Columns in left DataFrame to use as join keys.
  • right_on: Analogous to left_on for left DataFrame.
  • left_index: Use row index in left as its join key (or keys, if a MultiIndex).
  • right_index: Analogous to left_index.
  • sort: Sort merged data lexicographically by join keys; True by default (disable to get better performance in some cases on large datasets).
  • suffixes: Tuple of string values to append to column names in case of overlap; defaults to (’_x’, ’_y’) (e.g., if ‘data’ in both DataFrame objects, would appear as ‘data_x’ and ‘data_y’ in result).
  • copy: If False, avoid copying data into resulting data structure in some exceptional cases; by default always copies.
  • indicator: Adds a special column _merge that indicates the source of each row; values will be ‘left_only’, ‘right_only’, or ‘both’ based on the origin of the joined data in each row.

Merging on Index

If we want to use pd.merge() to join on row indexes, we can use the left_index and right_index arguments.

left1 = pd.DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c'], 'value': range(6)})
right1 = pd.DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])
left1
key value
0 a 0
1 b 1
2 a 2
3 a 3
4 b 4
5 c 5 
right1
group_val
a 3.5000
b 7.0000 
pd.merge(left1, right1, left_on='key', right_index=True, how='outer')
key value group_val
0 a 0 3.5000
2 a 2 3.5000
3 a 3 3.5000
1 b 1 7.0000
4 b 4 7.0000
5 c 5 NaN

The index arguments work for hierarchical indexes (multi indexes), too.

lefth = pd.DataFrame({'key1': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],
                      'key2': [2000, 2001, 2002, 2001, 2002],
                      'data': np.arange(5.)})
righth = pd.DataFrame(np.arange(12).reshape((6, 2)),
                      index=[['Nevada', 'Nevada', 'Ohio', 'Ohio', 'Ohio', 'Ohio'],
                             [2001, 2000, 2000, 2000, 2001, 2002]],
                      columns=['event1', 'event2'])
pd.merge(lefth, righth, left_on=['key1', 'key2'], right_index=True, how='outer')
key1 key2 data event1 event2
4 Nevada 2000 NaN 2.0000 3.0000
3 Nevada 2001 3.0000 0.0000 1.0000
4 Nevada 2002 4.0000 NaN NaN
0 Ohio 2000 0.0000 4.0000 5.0000
0 Ohio 2000 0.0000 6.0000 7.0000
1 Ohio 2001 1.0000 8.0000 9.0000
2 Ohio 2002 2.0000 10.0000 11.0000 
left2 = pd.DataFrame([[1., 2.], [3., 4.], [5., 6.]],
                     index=['a', 'c', 'e'],
                     columns=['Ohio', 'Nevada'])
right2 = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [13, 14]],
                      index=['b', 'c', 'd', 'e'],
                      columns=['Missouri', 'Alabama'])

If we use both indexes, pd.merge() will keep the index.

pd.merge(left2, right2, how='outer', left_index=True, right_index=True)
Ohio Nevada Missouri Alabama
a 1.0000 2.0000 NaN NaN
b NaN NaN 7.0000 8.0000
c 3.0000 4.0000 9.0000 10.0000
d NaN NaN 11.0000 12.0000
e 5.0000 6.0000 13.0000 14.0000

DataFrame has a convenient join instance for merging by index. It can also be used to combine together many DataFrame objects having the same or similar indexes but non-overlapping columns.

We can use the .join() method if both data frames have similar indexes.

left2
Ohio Nevada
a 1.0000 2.0000
c 3.0000 4.0000
e 5.0000 6.0000 
right2
Missouri Alabama
b 7.0000 8.0000
c 9.0000 10.0000
d 11.0000 12.0000
e 13.0000 14.0000 
left2.join(right2, how='outer')
Ohio Nevada Missouri Alabama
a 1.0000 2.0000 NaN NaN
b NaN NaN 7.0000 8.0000
c 3.0000 4.0000 9.0000 10.0000
d NaN NaN 11.0000 12.0000
e 5.0000 6.0000 13.0000 14.0000

The .join() method left joins by default. Because the .join() method uses indexes, it requires fewer arguments than .merge(). The .join() method can also accept a list of data frames.

another = pd.DataFrame(
    data=[[7., 8.], [9., 10.], [11., 12.], [16., 17.]],
    index=['a', 'c', 'e', 'f'],
    columns=['New York', 'Oregon']
)

another
New York Oregon
a 7.0000 8.0000
c 9.0000 10.0000
e 11.0000 12.0000
f 16.0000 17.0000 
left2.join([right2, another])
Ohio Nevada Missouri Alabama New York Oregon
a 1.0000 2.0000 NaN NaN 7.0000 8.0000
c 3.0000 4.0000 9.0000 10.0000 9.0000 10.0000
e 5.0000 6.0000 13.0000 14.0000 11.0000 12.0000 
left2.join([right2, another], how='outer')
Ohio Nevada Missouri Alabama New York Oregon
a 1.0000 2.0000 NaN NaN 7.0000 8.0000
c 3.0000 4.0000 9.0000 10.0000 9.0000 10.0000
e 5.0000 6.0000 13.0000 14.0000 11.0000 12.0000
b NaN NaN 7.0000 8.0000 NaN NaN
d NaN NaN 11.0000 12.0000 NaN NaN
f NaN NaN NaN NaN 16.0000 17.0000

Concatenating Along an Axis

The pd.concat() function provides a flexible way to combine data frames and series along an axis. I typically use pd.concat() to combine:

  1. A list of data frames with similar layouts
  2. A list of series because series do not have .join() or .merge() methods
s1 = pd.Series([0, 1], index=['a', 'b'])
s2 = pd.Series([2, 3, 4], index=['c', 'd', 'e'])
s3 = pd.Series([5, 6], index=['f', 'g'])
s1
a    0
b    1
dtype: int64
s2
c    2
d    3
e    4
dtype: int64
s3
f    5
g    6
dtype: int64
pd.concat([s1, s2, s3]) # implicit axis=0
a    0
b    1
c    2
d    3
e    4
f    5
g    6
dtype: int64
pd.concat([s1, s2, s3], axis=1) # explicit axis=1
0 1 2
a 0.0000 NaN NaN
b 1.0000 NaN NaN
c NaN 2.0000 NaN
d NaN 3.0000 NaN
e NaN 4.0000 NaN
f NaN NaN 5.0000
g NaN NaN 6.0000 
result = pd.concat([s1, s2, s3], keys=['one', 'two', 'three']) # implicit axis=0
result
one    a    0
       b    1
two    c    2
       d    3
       e    4
three  f    5
       g    6
dtype: int64
result.unstack(level=0)
one two three
a 0.0000 NaN NaN
b 1.0000 NaN NaN
c NaN 2.0000 NaN
d NaN 3.0000 NaN
e NaN 4.0000 NaN
f NaN NaN 5.0000
g NaN NaN 6.0000 
pd.concat([s1, s2, s3], axis=1, keys=['one', 'two', 'three']) # explicit axis=1
one two three
a 0.0000 NaN NaN
b 1.0000 NaN NaN
c NaN 2.0000 NaN
d NaN 3.0000 NaN
e NaN 4.0000 NaN
f NaN NaN 5.0000
g NaN NaN 6.0000 
df1 = pd.DataFrame(
    data=np.arange(6).reshape(3, 2),
    index=['a', 'b', 'c'],
    columns=['one', 'two']
)
df2 = pd.DataFrame(
    data=5 + np.arange(4).reshape(2, 2),
    index=['a', 'c'],
    columns=['three', 'four']
)
pd.concat([df1, df2], axis=1, keys=['level1', 'level2'])
level1 level2
one two three four
a 0 1 5.0000 6.0000
b 2 3 NaN NaN
c 4 5 7.0000 8.0000 
pd.concat([df1, df2], axis=1, keys=['level1', 'level2'], names=['upper', 'lower'])
upper level1 level2
lower one two three four
a 0 1 5.0000 6.0000
b 2 3 NaN NaN
c 4 5 7.0000 8.0000

Reshaping and Pivoting

Above, we briefly explore reshaping data with .stack() and .unstack(). Here, we more deeply explore reshaping data.

Reshaping with Hierarchical Indexing

Hierarchical indexes (multi-indexes) help reshape data.

There are two primary actions: - stack: This “rotates” or pivots from the columns in the data to the rows - unstack: This pivots from the rows into the columns

data = pd.DataFrame(np.arange(6).reshape((2, 3)),
                    index=pd.Index(['Ohio', 'Colorado'], name='state'),
                    columns=pd.Index(['one', 'two', 'three'],
                    name='number'))

data
number one two three
state
Ohio 0 1 2
Colorado 3 4 5 
result = data.stack()

result
state     number
Ohio      one       0
          two       1
          three     2
Colorado  one       3
          two       4
          three     5
dtype: int64
result.unstack()
number one two three
state
Ohio 0 1 2
Colorado 3 4 5 
s1 = pd.Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])
s2 = pd.Series([4, 5, 6], index=['c', 'd', 'e'])
data2 = pd.concat([s1, s2], keys=['one', 'two'])

data2
one  a    0
     b    1
     c    2
     d    3
two  c    4
     d    5
     e    6
dtype: int64

Un-stacking may introduce missing values because data frames are rectangular.

data2.unstack()
a b c d e
one 0.0000 1.0000 2.0000 3.0000 NaN
two NaN NaN 4.0000 5.0000 6.0000

Stacking drops these missing values by default. However, this behavior may change soon, so check your output!

data2.unstack().stack()
one  a   0.0000
     b   1.0000
     c   2.0000
     d   3.0000
two  c   4.0000
     d   5.0000
     e   6.0000
dtype: float64

McKinney provides two more subsections on reshaping data with the .pivot() and .melt() methods. Unlike, the stacking methods, the pivoting methods can aggregate data and do not require an index. We will skip these additional aggregation methods for now.

References

McKinney, Wes. 2022. Python for Data Analysis. 3rd ed. https://wesmckinney.com/book/; O’Reilly Media, Inc.