Merging and Reshaping

• pd.merge(left, right, on='key', how='inner') works like a SQL JOIN
• Matches rows by key values, not by position or index label

orders.merge(customers, on='cust_id', how='left')

all orders kept, even when the customer is missing

• Find unmatched rows: how='left', indicator=True, then _merge == 'left_only'

• df.join(other): joins on the index, default left
• pd.merge: joins on shared column names, default inner

• Overlapping non-key columns get _x, _y by default
• Pass suffixes=('_l','_r') for readable names

• validate='one_to_one', 'one_to_many', 'many_to_one' raises MergeError if uniqueness fails
• indicator=True adds _merge: left_only, right_only, both

• pd.concat([a, b]) stacks rows (axis=0)
• Columns align by name
• Column in one frame only → NaN in the other rows

import pandas as pd
a = pd.DataFrame({'x': [1, 2]})
b = pd.DataFrame({'x': [3], 'y': [9]})
print(pd.concat([a, b], keys=['a', 'b']))

keys= adds a source level to the index

• ignore_index=True → clean 0..n-1 index
• Without it indexes repeat: loc[0] returns several rows
• keys=['a','b'] → MultiIndex of source + old index

• concat or merge? concat stacks or aligns by label; merge matches key values
• Repeated index? Each frame keeps its own labels
• One-frame column? Kept; other rows get NaN

• pd.melt turns the listed value_vars columns into rows
• id_vars stay as columns; names go to var_name, cells to value_name

long = pd.melt(df, id_vars=['id'],
    value_vars=['q1', 'q2'],
    var_name='quarter',
    value_name='sales')

one row per id and quarter

• df.pivot(index='id', columns='quarter', values='sales') makes long into wide
• Reverse a melt with pivot, when pairs are unique

• df.stack(): innermost column level → row index (wide to long)
• s.unstack(level=-1): index level → columns (long to wide)

• MultiIndex = index with several levels
• Build: set_index(['a','b']) or groupby(['a','b'])
• Read: loc[('a1','b1')] for one row, xs('b1', level='b') for one level

s = df.set_index(['a','b'])['v']
s.unstack()
s.unstack(fill_value=0)
s.loc[('a1','b1')]
s.xs('b1', level='b')

df has columns a, b, v

• unstack() creates NaN for missing combinations; fill_value=0 fills them
• pandas 3: stack() keeps NaN; older versions dropped all-NaN rows
• Slicing a MultiIndex range needs sort_index() first

• how= inner | left | right | outer
• validate='1:1' raises on bad keys; indicator=True adds _merge
• Key names differ: left_on= + right_on=; df.join(o) joins on the index

r = a.merge(b, on='k', how='left',
            suffixes=('_l','_r'),
            validate='1:1', indicator=True)
r[r['_merge'] == 'left_only']
a.merge(b, left_on='a_id', right_on='b_id')

how='left' keeps unmatched rows so left_only can find them

• Which tool? merge or join by key, concat to glue, pivot_table to aggregate, unstack to move a level
• Checks after a merge? row count, _merge counts, validate=