Model pd.crosstab with margins=True, the quickest table of how two columns combine.
What to build
A program that counts combinations of two columns, with totals.
Requirements
- Each record is
A,B; an empty A or B means a missing value, and that record is not counted. Rows are the sorted distinct A values that were counted, columns the sorted distinct B values. - Print a header
A\B,C1,C2,...,All, then one line per A value with its counts and row total, then anAllline with the column totals and the grand total. Withnormalize, print every number as a fraction of the grand total with two decimals instead.
Input
Line 1: counts or normalize. Line 2: n. Then n records.
Output
The table.
Constraints
1 ≤ n ≤ 500; at least one record is complete.
Example
Input:
counts
6
Dhaka,tea
Dhaka,coffee
Paris,coffee
Dhaka,tea
,tea
Paris,
Output:
A\B,coffee,tea,All
Dhaka,1,2,3
Paris,1,0,1
All,2,2,4
Why it works
crosstab is a pivot table of counts: every complete record adds one to its cell, and the margins add up rows, columns and everything, so the All row and column always agree with the grand total.
Examples
counts 6 Dhaka,tea Dhaka,coffee Paris,coffee Dhaka,tea ,tea Paris,
A\B,coffee,tea,All Dhaka,1,2,3 Paris,1,0,1 All,2,2,4
normalize 4 x,p x,q y,p y,p
A\B,p,q,All x,0.25,0.25,0.50 y,0.50,0.00,0.50 All,0.75,0.25,1.00
Prompts inside input() are ignored when checking, so input("n: ") is fine.
Hints
Hint 1
Count every (row value, column value) pair; skip a pair if either value is missing.
Hint 2
The All column is each row's total, the All row each column's total, and the corner the grand total.
Solution
Show one correct solution (try it yourself first)
mode = input()
n = int(input())
counts = {}
for _ in range(n):
a, b = input().split(",")
if a and b:
counts[(a, b)] = counts.get((a, b), 0) + 1
rows = sorted({a for a, _ in counts})
cols = sorted({b for _, b in counts})
grand = sum(counts.values())
def show(x):
return f"{x / grand:.2f}" if mode == "normalize" else str(x)
print(",".join(["A\\B"] + cols + ["All"]))
for a in rows:
line = [counts.get((a, b), 0) for b in cols]
print(",".join([a] + [show(x) for x in line] + [show(sum(line))]))
totals = [sum(counts.get((a, b), 0) for a in rows) for b in cols]
print(",".join(["All"] + [show(x) for x in totals] + [show(grand)]))