pandas: crosstab with margins

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 an All line with the column totals and the grand total. With normalize, 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

Example 1 · Input
counts
6
Dhaka,tea
Dhaka,coffee
Paris,coffee
Dhaka,tea
,tea
Paris,
Expected output
A\B,coffee,tea,All
Dhaka,1,2,3
Paris,1,0,1
All,2,2,4
Example 2 · Input
normalize
4
x,p
x,q
y,p
y,p
Expected output
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)]))

Lesson: Reshaping: pivot_table, melt and crosstab →