pandas: Revenue and share by category

Merge sales with a catalogue, group by category and compute each category's share of revenue, reporting the unmatched sales.

What to build

A program that reports revenue and share per category.

Requirements

  • Revenue of a sale is quantity × price. Sales of products missing from the catalogue are left out and counted.
  • Print CATEGORY REVENUE SHARE% for each category with sales, revenue to two decimals and share to one decimal, from the largest revenue to the smallest and by name on ties; then print unmatched N.

Input

Line 1: n, then n catalogue lines PRODUCT,CATEGORY,PRICE. Then m, then m sales lines PRODUCT,QTY.

Output

One line per category, then one line.

Constraints

1 ≤ n, m ≤ 1000; product names are unique in the catalogue; at least one sale matches and the matched revenue is over 0.

Example

Input:

3
pen,office,1.5
desk,furniture,120
lamp,furniture,30
5
pen,10
desk,1
lamp,2
pen,4
mug,3

Output:

furniture 180.00 89.6%
office 21.00 10.4%
unmatched 1

Why it works

A left merge keeps every sale, the missing catalogue entries mark the unmatched ones, and dividing each category's revenue by the matched total gives shares that add up to 100%.

Examples

Example 1 · Input
3
pen,office,1.5
desk,furniture,120
lamp,furniture,30
5
pen,10
desk,1
lamp,2
pen,4
mug,3
Expected output
furniture 180.00 89.6%
office 21.00 10.4%
unmatched 1
Example 2 · Input
1
a,x,2
1
a,1
Expected output
x 2.00 100.0%
unmatched 0

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

Hints

Hint 1

Build a dictionary from product to (category, price).

Hint 2

Skip and count sales whose product is not in the catalogue.

Hint 3

Sort by revenue from largest to smallest, then by category name.

Solution

Show one correct solution (try it yourself first)
n = int(input())
catalogue = {}
for _ in range(n):
    product, category, price = input().split(",")
    catalogue[product] = (category, float(price))
m = int(input())
revenue, unmatched = {}, 0
for _ in range(m):
    product, q = input().split(",")
    if product not in catalogue:
        unmatched += 1
        continue
    category, price = catalogue[product]
    revenue[category] = revenue.get(category, 0) + int(q) * price
total = sum(revenue.values())
for c in sorted(revenue, key=lambda c: (-revenue[c], c)):
    print(f"{c} {revenue[c]:.2f} {100 * revenue[c] / total:.1f}%")
print("unmatched", unmatched)

Lesson: A complete analysis with pandas →