Orders are merged onto customers with pd.merge(orders, customers, on="customer", how="left", validate="many_to_one", indicator=True). Model the check, the merge and the indicator column.
What to build
A program that validates and merges orders with customers and audits the matches.
Requirements
- If any customer id appears more than once in the customers table, print only
error: customer keys are not unique. - Otherwise print
order,customer,amount,name,_merge, then one line per order in input order: its three values, the customer's name (orNaN), andbothorleft_only. - Finally print
matched K of N.
Input
Line 1: n, then n order lines order,customer,amount. Then m, then m customer lines customer,name.
Output
One line, or n + 2 lines.
Constraints
1 ≤ n, m ≤ 100.
Example
Input:
4
o1,c1,10
o2,c2,20
o3,c1,5
o4,c9,7
2
c1,Ana
c2,Ben
Output:
order,customer,amount,name,_merge
o1,c1,10,Ana,both
o2,c2,20,Ben,both
o3,c1,5,Ana,both
o4,c9,7,NaN,left_only
matched 3 of 4
Why it works
A many-to-one merge is only safe when each right key is unique, otherwise orders would be duplicated; the indicator column then shows exactly which orders found no customer.
Examples
4 o1,c1,10 o2,c2,20 o3,c1,5 o4,c9,7 2 c1,Ana c2,Ben
order,customer,amount,name,_merge o1,c1,10,Ana,both o2,c2,20,Ben,both o3,c1,5,Ana,both o4,c9,7,NaN,left_only matched 3 of 4
1 o1,c1,10 2 c1,Ana c1,Ann
error: customer keys are not unique
Prompts inside input() are ignored when checking, so input("n: ") is fine.
Hints
Hint 1
First check the customer ids: if any repeats, print the error and stop.
Hint 2
Look each order's customer up in a dictionary; a missing one gives NaN and left_only.
Hint 3
Count the rows marked both.
Solution
Show one correct solution (try it yourself first)
orders = [input().split(",") for _ in range(int(input()))]
pairs = [input().split(",") for _ in range(int(input()))]
ids = [c for c, _ in pairs]
if len(set(ids)) != len(ids):
print("error: customer keys are not unique")
else:
names = dict(pairs)
matched = 0
print("order,customer,amount,name,_merge")
for o in orders:
if o[1] in names:
matched += 1
print(",".join(o + [names[o[1]], "both"]))
else:
print(",".join(o + ["NaN", "left_only"]))
print(f"matched {matched} of {len(orders)}")