pandas: Inner, left and outer merges

pd.merge(left, right, on="id", how=...) keeps different rows for each kind of join. Model inner, left and outer merges of two tables with unique keys.

What to build

A program that merges two tables on their id column.

Requirements

  • Print the header: id, then the left table's other columns, then the right table's other columns.
  • For inner, print the left rows whose id is in the right table, in left order. For left, print every left row. For outer, print every id from either table, sorted as text.
  • Each row is the id, the left values and the right values; a value from a table that lacks the id is NaN.

Input

Line 1: inner, left or outer. Then the left table: header (first column id), n, n rows. Then the right table in the same form.

Output

The header and the merged rows.

Constraints

0 ≤ n, m ≤ 50; ids are unique within each table; apart from id, the two tables share no column names.

Example

Input:

inner
id,name
3
1,Ana
2,Ben
3,Cy
id,score
3
3,70
1,90
4,50

Output:

id,name,score
1,Ana,90
3,Cy,70

Why it works

Each kind of join is a rule about which keys survive: the intersection, all left keys, or the union. Looking each key up in a dictionary built from the right table pairs the matching rows.

Examples

Example 1 · Input
inner
id,name
3
1,Ana
2,Ben
3,Cy
id,score
3
3,70
1,90
4,50
Expected output
id,name,score
1,Ana,90
3,Cy,70
Example 2 · Input
left
id,name
3
1,Ana
2,Ben
3,Cy
id,score
3
3,70
1,90
4,50
Expected output
id,name,score
1,Ana,90
2,Ben,NaN
3,Cy,70

Prompts inside input() are ignored when checking, so input("n: ") is fine.

Hints

Hint 1

Index the right table by id with a dictionary.

Hint 2

inner and left follow the left table's order; outer uses the sorted union of keys.

Hint 3

Missing values are printed as NaN.

Solution

Show one correct solution (try it yourself first)
how = input()
lh = input().split(",")
left = {}
order = []
for _ in range(int(input())):
    r = input().split(",")
    left[r[0]] = r[1:]
    order.append(r[0])
rh = input().split(",")
right = {}
for _ in range(int(input())):
    r = input().split(",")
    right[r[0]] = r[1:]
print(",".join(lh + rh[1:]))
if how == "inner":
    keys = [k for k in order if k in right]
elif how == "left":
    keys = order
else:
    keys = sorted(set(left) | set(right))
for k in keys:
    lv = left.get(k, ["NaN"] * (len(lh) - 1))
    rv = right.get(k, ["NaN"] * (len(rh) - 1))
    print(",".join([k] + lv + rv))

Lesson: Merging and joining DataFrames →