merge / join / concat

import pandas as pd
import numpy as np

# 1) Sample data
users = pd.DataFrame({
    'user_id': [1, 2, 3, 4],
    'name':    ['Mara', 'Sam', 'Alex', 'Kim'],
    'country': ['AU', 'AU', 'US', 'GB'],
})

orders = pd.DataFrame({
    'order_id': [10, 11, 12, 13, 14],
    'user_id':  [1, 1, 2, 3, 99],            # 99 is orphan
    'amount':   [50, 30, 75, 20, 5],
})

# 2) Inner join — keep only matched rows
pd.merge(users, orders, on='user_id', how='inner')
#    user_id  name  country  order_id  amount
# 0        1  Mara     AU       10       50
# 1        1  Mara     AU       11       30
# 2        2  Sam      AU       12       75
# 3        3  Alex     US       13       20
# Mara appears twice (two orders); Kim absent (no orders); order 14 dropped.

# 3) Left join — keep ALL rows from the left
pd.merge(users, orders, on='user_id', how='left')
# Kim shows with NaN columns. Orphan order 14 dropped.

# 4) Right join — keep ALL rows from the right
pd.merge(users, orders, on='user_id', how='right')
# Order 14 appears with NaN user fields.

# 5) Outer join — keep everything; missing fields = NaN
pd.merge(users, orders, on='user_id', how='outer', indicator=True)
# '_merge' column shows: 'both', 'left_only', 'right_only' — handy for auditing.

# 6) Anti-join (rows in left without a match)
merged = users.merge(orders, on='user_id', how='left', indicator=True)
left_only = merged[merged['_merge'] == 'left_only'].drop(columns=['_merge'])

# 7) Multi-key merges
pd.merge(left, right, on=['country', 'product_id'])
pd.merge(left, right, left_on='uid', right_on='user_id')
pd.merge(left, right, left_index=True, right_index=True)        # by index

# 8) Column name collisions — use suffixes
left  = pd.DataFrame({'id': [1, 2], 'value': [10, 20]})
right = pd.DataFrame({'id': [1, 2], 'value': [100, 200]})
pd.merge(left, right, on='id', suffixes=('_left', '_right'))
# id  value_left  value_right
# 1   10          100
# 2   20          200

# 9) Validate — catch unexpected cardinalities EARLY
pd.merge(users, orders, on='user_id', how='left', validate='one_to_many')
# Raises if user_id is NOT unique in users OR if a single user has zero orders impossible
# Validate options:
#   'one_to_one'   — both sides unique
#   'one_to_many'  — left unique
#   'many_to_one'  — right unique
#   'many_to_many' — no constraint
# This catches row-explosion bugs at merge time.

# 10) df.join — index-based shortcut
left  = pd.DataFrame({'a': [1, 2, 3]}, index=['x', 'y', 'z'])
right = pd.DataFrame({'b': [10, 20]},   index=['x', 'y'])
left.join(right, how='left')
# Equivalent to merge with left_index=right_index=True. More concise for index joins.

# 11) concat — stack DataFrames
pd.concat([df1, df2], axis=0, ignore_index=True)      # rows (UNION)
pd.concat([df1, df2], axis=1)                          # columns side-by-side
pd.concat([df1, df2], keys=['a', 'b'])                 # MultiIndex of source

# Schemas align by COLUMN NAME. Missing columns become NaN unless join='inner'.
pd.concat([df1, df2], join='inner')                    # only shared columns

# 12) Merge-asof — time-ordered tolerance match
left = pd.DataFrame({
    'time': pd.to_datetime(['09:00', '09:30', '10:00']),
    'event': ['login', 'view', 'click'],
})
right = pd.DataFrame({
    'time': pd.to_datetime(['08:45', '09:25', '09:50']),
    'price': [100, 102, 105],
})
pd.merge_asof(left.sort_values('time'), right.sort_values('time'),
                            on='time', direction='backward', tolerance=pd.Timedelta(minutes=10))
# Matches each left row to the nearest PRIOR right row within 10 min.
# Perfect for time-series joins (trades vs quotes, events vs config snapshots).

# 13) Cross join — every combination
pd.merge(df1, df2, how='cross')
# product of rows — careful, grows quadratically

# 14) Performance tips
# • Set the merge key as the index BEFORE merging — avoids hash table rebuild
# • Sort both sides on the key — pandas can use sort-merge join
# • Convert keys to categorical for repeated string joins (saves memory)
# • Use Polars / DuckDB for huge merges — they outperform pandas by orders of magnitude

# 15) Common bugs
# • Forgetting validate= → silent row explosion when both sides have duplicates
# • Merging on mismatched dtypes (str vs int) → no matches, both columns retained
# • NaN keys never match — clean nulls before merge or use indicator='_merge'
# • Default suffixes ('_x', '_y') → ambiguous in downstream code; name them
# • Left join then assuming non-null in right cols — check or .fillna() afterwards
# • Index resets — pd.merge resets index; if you need the original index, save it first
# • Concat with axis=0 but mismatched dtypes (int vs float) → dtype upcasts; may surprise
# • Joining on float columns → precision issues; round or cast to a stable type first
# • Trying merge_asof without sorted keys → ValueError

import pandas as pd
merged = users.merge(orders, on='user_id', how='left')
big = pd.concat([part_a, part_b], ignore_index=True)
rows = pd.concat([df1, df2], axis=1)