Summary grid

[Peter Otten]

Jignesh Sutar wrote:

> Say I have list of data as given in the example code below, I want to find
> all the unique categories (alphabetic letters) and unique IDs (numbers)
> and then produce a summary grid as manually entered in the "results". How
> could I code this?
> 
> Many thanks in advance,
> Jignesh
> 
> 
> data= ["A.1", "A.2", "A.3", "B.1", "C.2", "C.3",  "D.4", "E.5", "E.6"]
> 
> cols=[]
> rows=[]
> for item in data:
>     i=item.split(".")
>     if i[0] not in cols: cols.append(i[0])
>     if i[1] not in rows: rows.append(i[1])
> 
> print cols
> print rows
> 
> results=
> [["Row/Col", "A", "B", "C", "D", "E"],
> [1, 1, 1, 0, 0, 0],
> [2, 1, 0, 1, 0, 0],
> [3, 1, 0, 1, 0, 0],
> [4, 0, 0, 0, 1, 0],
> [5, 0, 0, 0, 0, 1],
> [6, 0, 0, 0, 0, 1]]

$ cat pivot_jignesh.py
def add(x, y):
    return x + y


def pivot(
        data,
        accu=add,
        default=0, empty=0, origin=""):
    rows = {}
    columnkeys = set()
    for rowkey, columnkey, value in data:
        column = rows.setdefault(rowkey, {})
        column[columnkey] = accu(column.get(columnkey, default), value)
        columnkeys.add(columnkey)

    columnkeys = sorted(columnkeys)
    result = [
        [origin] + columnkeys
    ]
    for rowkey in sorted(rows):
        row = rows[rowkey]
        result.append([rowkey] + [row.get(ck, empty) for ck in columnkeys])
    return result


if __name__ == "__main__":
    import pprint

    data = ["A.1", "A.2", "A.3", "B.1", "C.2", "C.3",  "D.4", "E.5", "E.6"]
    data = (item.split(".") for item in data)
    data = ((int(row), column, 1) for column, row in data)

    pprint.pprint(pivot(data, origin="Row/Col"))
$ python pivot_jignesh.py 
[['Row/Col', 'A', 'B', 'C', 'D', 'E'],
 [1, 1, 1, 0, 0, 0],
 [2, 1, 0, 1, 0, 0],
 [3, 1, 0, 1, 0, 0],
 [4, 0, 0, 0, 1, 0],
 [5, 0, 0, 0, 0, 1],
 [6, 0, 0, 0, 0, 1]]
$