pandas: A pivot table with aggregation

Model pivot_table with a choice of aggregation and a fill value for empty cells.

What to build

A program that builds a grid from long rows.

Requirements

  • Each long row is ROW COLUMN VALUE. Cells are aggregated with mean, sum, count or max. Print a header line ROWLABEL,C1,C2,... (sorted column keys), then one line per sorted row key with its cells, numbers to two decimals (count as a whole number).
  • A cell with no rows is NaN, or the fill value when one is given.

Input

Line 1: the aggregation and optionally fill=X. Line 2: the name of the row label. Line 3: n. Then n long rows.

Output

The grid.

Constraints

1 ≤ n ≤ 300; values are numbers.

Example

Input:

mean
name
6
Ada maths 90
Ada art 70
Rahim maths 80
Rahim art 84
Mei maths 95
Rahim art 86

Output:

name,art,maths
Ada,70.00,90.00
Mei,NaN,95.00
Rahim,85.00,80.00

Why it works

Each cell gathers the rows that share its row and column key and reduces them to one number, which is why duplicates need an aggregation, and a cell nothing falls into has no value until a fill value gives it one.

Examples

Example 1 · Input
mean
name
6
Ada maths 90
Ada art 70
Rahim maths 80
Rahim art 84
Mei maths 95
Rahim art 86
Expected output
name,art,maths
Ada,70.00,90.00
Mei,NaN,95.00
Rahim,85.00,80.00
Example 2 · Input
sum fill=0
region
4
north jan 10
south jan 5
north feb 2.5
north jan 1
Expected output
region,feb,jan
north,2.50,11.00
south,0,5.00

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

Hints

Hint 1

Collect every value that falls in each (row, column) cell, then aggregate each cell.

Hint 2

Rows and columns are sorted; an empty cell is NaN unless a fill value is given.

Solution

Show one correct solution (try it yourself first)
spec = input().split()
agg = spec[0]
fill = spec[1][5:] if len(spec) > 1 else "NaN"
label = input()
n = int(input())
cells = {}
for _ in range(n):
    r, c, v = input().split()
    cells.setdefault((r, c), []).append(float(v))
row_keys = sorted({r for r, _ in cells})
col_keys = sorted({c for _, c in cells})


def reduce(values):
    if agg == "count":
        return str(len(values))
    if agg == "sum":
        result = sum(values)
    elif agg == "max":
        result = max(values)
    else:
        result = sum(values) / len(values)
    return f"{result:.2f}"


print(",".join([label] + col_keys))
for r in row_keys:
    out = [reduce(cells[(r, c)]) if (r, c) in cells else fill for c in col_keys]
    print(",".join([r] + out))

Lesson: Reshaping: pivot_table, melt and crosstab →