Merge Inner - Pandas Step by Step

Build a key→value index from the right table, then walk left rows keeping only those whose key appears on both sides. With pandas, pd.merge(left, right, on='id') performs an inner join by default, dropping any row whose key is missing from either table.

By hand

Loop over right to build right_idx (id → score). Loop over left, and for each row whose id is in right_idx, append a (name, score) pair to joined.

                            naive.py
                        
Replay: real traced execution (multi-file project)

                            left = [
    {'id': 1, 'name': 'al'},
    {'id': 2, 'name': 'bo'},
    {'id': 3, 'name': 'cy'},
]
right = [
    {'id': 1, 'score': 80},
    {'id': 3, 'score': 70},
    {'id': 4, 'score': 90},
]
right_idx = {}
for r in right:
    right_idx[r['id']] = r['score']
joined = []
for l in left:
    if l['id'] in right_idx:
        joined.append((l['name'], right_idx[l['id']]))
print('RESULT:', joined)

                        
1{'id': 1, 'name': 'al'},1left = [2    {'id': 1, 'name': 'al'},3    {'id': 2, 'name': 'bo'},⋯
2{'id': 2, 'name': 'bo'},⋯2{'id': 1, 'name': 'al'},3{'id': 2, 'name': 'bo'},4{'id': 3, 'name': 'cy'},⋯
3{'id': 3, 'name': 'cy'},⋯3    {'id': 2, 'name': 'bo'},4    {'id': 3, 'name': 'cy'},5]⋯
4left ← [{'id': 1, 'name': 'al'}, {'id': 2, 'name': 'bo'}, {'id': 3, 'name': 'cy'}]1left = [2    {'id': 1, 'name': 'al'},⋯values this step[{'id': 1, 'name': 'al'}, {'id': 2, 'name': 'bo'}, {'id': 3, 'name': 'cy'}]left
5{'id': 1, 'score': 80},⋯6right = [7    {'id': 1, 'score': 80},8    {'id': 3, 'score': 70},⋯
6{'id': 3, 'score': 70},⋯7{'id': 1, 'score': 80},8{'id': 3, 'score': 70},9{'id': 4, 'score': 90},⋯
7{'id': 4, 'score': 90},⋯8    {'id': 3, 'score': 70},9    {'id': 4, 'score': 90},10]⋯
8right ← [{'id': 1, 'score': 80}, {'id': 3, 'score': 70}, {'id': 4, 'score': 90}]⋯5]6right = [7    {'id': 1, 'score': 80},⋯values this step[{'id': 1, 'score': 80}, {'id': 3, 'score': 70}, {'id': 4, 'score': 90}]right
9right_idx ← {}⋯10]11right_idx = {}12for r in right:⋯values this step{}right_idx
10r ← {'id': 1, 'score': 80}⋯11right_idx = {}12for r in right:13    right_idx[r['id']] = r['score']⋯values this step{'id': 1, 'score': 80}r
11right_idx ← {1: 80}⋯12for r in right:13    right_idx[r['id']] = r['score']14joined = []⋯values this step{} → {1: 80}right_idx
12r ← {'id': 3, 'score': 70}⋯11right_idx = {}12for r in right:13    right_idx[r['id']] = r['score']⋯values this step{'id': 1, 'score': 80} → {'id': 3, 'score': 70}r
13right_idx ← {1: 80, 3: 70}⋯12for r in right:13    right_idx[r['id']] = r['score']14joined = []⋯values this step{1: 80} → {1: 80, 3: 70}right_idx
14r ← {'id': 4, 'score': 90}⋯11right_idx = {}12for r in right:13    right_idx[r['id']] = r['score']⋯values this step{'id': 3, 'score': 70} → {'id': 4, 'score': 90}r
15right_idx ← {1: 80, 3: 70, 4: 90}⋯12for r in right:13    right_idx[r['id']] = r['score']14joined = []⋯values this step{1: 80, 3: 70} → {1: 80, 3: 70, 4: 90}right_idx
16for r in right:⋯11right_idx = {}12for r in right:13    right_idx[r['id']] = r['score']⋯
17joined ← []⋯13    right_idx[r['id']] = r['score']14joined = []15for l in left:⋯values this step[]joined
18l ← {'id': 1, 'name': 'al'}⋯14joined = []15for l in left:16    if l['id'] in right_idx:⋯values this step{'id': 1, 'name': 'al'}l
19if l['id'] in right_idx:⋯15for l in left:16    if l['id'] in right_idx:17        joined.append((l['name'], right_idx[l['id']]))⋯
20joined ← [('al', 80)]⋯16    if l['id'] in right_idx:17        joined.append((l['name'], right_idx[l['id']]))18print('RESULT:', joined)values this step[] → [('al', 80)]joined
21l ← {'id': 2, 'name': 'bo'}⋯14joined = []15for l in left:16    if l['id'] in right_idx:⋯values this step{'id': 1, 'name': 'al'} → {'id': 2, 'name': 'bo'}l
22if l['id'] in right_idx:⋯15for l in left:16    if l['id'] in right_idx:17        joined.append((l['name'], right_idx[l['id']]))⋯
23l ← {'id': 3, 'name': 'cy'}⋯14joined = []15for l in left:16    if l['id'] in right_idx:⋯values this step{'id': 2, 'name': 'bo'} → {'id': 3, 'name': 'cy'}l
24if l['id'] in right_idx:⋯15for l in left:16    if l['id'] in right_idx:17        joined.append((l['name'], right_idx[l['id']]))⋯
25joined ← [('al', 80), ('cy', 70)]⋯16    if l['id'] in right_idx:17        joined.append((l['name'], right_idx[l['id']]))18print('RESULT:', joined)values this step[('al', 80)] → [('al', 80), ('cy', 70)]joined
26for l in left:⋯14joined = []15for l in left:16    if l['id'] in right_idx:⋯
27stdout ← RESULT: [('al', 80), ('cy', 70)]⋯17        joined.append((l['name'], right_idx[l['id']]))18print('RESULT:', joined)values this stepRESULT: [('al', 80), ('cy', 70)]stdout

With pandas

pd.merge(left, right, on='id') matches rows by the 'id' column and keeps only pairs with a key present in both DataFrames. The snapshot shows the merged columns, joined ids, and per-column values.

                            library.py
                        

                            import pandas as pd
from dalib.display import set_display
set_display()

left = pd.DataFrame({'id': [1, 2, 3], 'name': ['al', 'bo', 'cy']})
right = pd.DataFrame({'id': [1, 3, 4], 'score': [80, 70, 90]})
merged = pd.merge(left, right, on='id')
print('columns:', merged.columns.tolist())
print('ids:', merged['id'].tolist())
print('names:', merged['name'].tolist())
print('scores:', merged['score'].tolist())
print('RESULT:', list(zip(merged['name'].tolist(), merged['score'].tolist())))

                        

columns: ['id', 'name', 'score']
ids: [1, 3]
names: ['al', 'cy']
scores: [80, 70]
RESULT: [('al', 80), ('cy', 70)]

Implementation notes

pd.merge defaults to how='inner' — only rows with a matching key on both sides appear in the result. Row with id=2 (left-only) and id=4 (right-only) are both dropped.
The on= argument names the shared column used as the join key. If the key column has different names in each table, use left_on='id_left' and right_on='id_right' instead.
Row order in the result follows the left table's key order by default.
Cross-reference: inner-join-by-key (python-data-basics) for the pure-Python dict-index version.