Data science: Build a cross tabulation

A cross tabulation shows a total for every combination of two keys, with totals for each row and column.

What to build

A program that builds a table of sales by region and product.

Requirements

  • Print a header region,PRODUCT,...,total with the products in alphabetical order.
  • Then one line per region, in alphabetical order: the region, its total for each product (0 if none) and its row total. Finally a line total,... with each product's total and the grand total.

Input

Line 1: n. Then n lines REGION PRODUCT AMOUNT (whole numbers).

Output

The header, one line per region, then the totals line.

Constraints

1 ≤ n ≤ 10000.

Example

Input:

5
North tea 120
South tea 90
North coffee 200
South coffee 150
East tea 60

Output:

region,coffee,tea,total
East,0,60,60
North,200,120,320
South,150,90,240
total,350,270,620

Why it works

A composite key holds one running total per region and product, and every margin of the table is a sum over those totals, so the row totals, column totals and grand total always agree.

Examples

Example 1 · Input
5
North tea 120
South tea 90
North coffee 200
South coffee 150
East tea 60
Expected output
region,coffee,tea,total
East,0,60,60
North,200,120,320
South,150,90,240
total,350,270,620
Example 2 · Input
2
A x 5
A x 7
Expected output
region,x,total
A,12,12
total,12,12

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

Hints

Hint 1

Keep totals in a dictionary keyed by (region, product), and the region and product names in two sets.

Hint 2

Row totals, column totals and the grand total are sums over the same dictionary.

Solution

Show one correct solution (try it yourself first)
n = int(input())
totals = {}
regions, products = set(), set()
for _ in range(n):
    region, product, amount = input().split()
    totals[(region, product)] = totals.get((region, product), 0) + int(amount)
    regions.add(region)
    products.add(product)

products = sorted(products)
print(",".join(["region"] + products + ["total"]))
for region in sorted(regions):
    cells = [totals.get((region, p), 0) for p in products]
    print(",".join([region] + [str(c) for c in cells] + [str(sum(cells))]))
column = [sum(totals.get((r, p), 0) for r in regions) for p in products]
print(",".join(["total"] + [str(c) for c in column] + [str(sum(column))]))

Lesson: Grouping and aggregation →