pandas: A checked many-to-one merge

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 (or NaN), and both or left_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

Example 1 · Input
4
o1,c1,10
o2,c2,20
o3,c1,5
o4,c9,7
2
c1,Ana
c2,Ben
Expected 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
Example 2 · Input
1
o1,c1,10
2
c1,Ana
c1,Ann
Expected output
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)}")

Lesson: Merging and joining DataFrames →