""" Flatten a San Diego batch canvass workbook into one long CSV: file, tabulator, batch, type, contest, choice, votes. """ import csv, sys, warnings import openpyxl warnings.filterwarnings('ignore') src, out = sys.argv[1], sys.argv[2] wb = openpyxl.load_workbook(src, read_only=True) odd = {} with open(out, 'w', newline='') as fh: w = csv.writer(fh) w.writerow(['sheet', 'contest', 'tabulator', 'batch', 'ballot_type', 'choice', 'votes']) for ws in wb.worksheets[1:]: contest, hdr = '', None for row in ws.iter_rows(values_only=True): row = list(row) if row[0] == '' and row[1]: contest, hdr = str(row[1]).strip(), None continue if row[0] == 'Tabulator': hdr = row continue if hdr is None or row[0] in (None, ''): if row[0] not in (None, '') or any(c not in (None, '') for c in row[1:]): odd.setdefault(str(row[:4]), 0) odd[str(row[:4])] += 1 continue for j in range(8, len(hdr)): if hdr[j] not in (None, ''): w.writerow([ws.title, contest, row[0], row[3], row[4], hdr[j], row[j]]) print(len(odd), 'odd row kinds') for k, v in sorted(odd.items(), key=lambda x: -x[1])[:15]: print(v, k)