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. Forleft, print every left row. Forouter, 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
inner id,name 3 1,Ana 2,Ben 3,Cy id,score 3 3,70 1,90 4,50
id,name,score 1,Ana,90 3,Cy,70
left id,name 3 1,Ana 2,Ben 3,Cy id,score 3 3,70 1,90 4,50
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))