migurski

#!/usr/bin/env python3
import itertools
import typing
import math

import cairo
import shapely.affinity
import shapely.geometry

DISTANCE_CUTOFF = 2  # max jump length to bridge with a curve

migurski

"""Convert input CSV to OpenElections CSV

Adapt large tabular sources like this Lenawee County example (1) converted from PDFs (2)
to format required by OpenElections (3) intepreting categories of votes, candidates, and
offices along the way.

1) https://docs.google.com/spreadsheets/d/1iOEqLFcwuA3J_HXUtEWIZUirA7J1_NdsnVUnc8z0TZY/edit?gid=2129694166#gid=2129694166
2) https://github.com/openelections/openelections-sources-mi/blob/master/2024/general/Gogebic%20MI%20Official%20Statement%20of%20Votes%20Cast%20with%20Certification%2011.5.2024.pdf
3) https://github.com/openelections/openelections-data-mi/issues/69

migurski

"""Coalesce Excel sheets that appear to be excerpts of larger sheets

Requires pylightxl: https://pylightxl.readthedocs.io/en/latest/

PDF sources like this Gogebic County example (1) can be converted by AWS Textract
into usable Excel workbooks with multiple sheets (2). This scripts makes a best-effort
attempt to stitch together large tables broken up across multiple pages based on
row counts, column counts, and cell values representing precincts. The results still
require a lot of manual interpretation to be turned into useful OpenElections data (3).

migurski

migurski

import datetime
import math
import mercantile
import pytz
import suncalc

def date(yyyy, mm, dd, hh):
    dt_naive = datetime.datetime(yyyy, mm, dd, int(hh), int((60 * hh) % 60))
    dt_local = dt_naive.astimezone(pytz.timezone('America/Los_Angeles'))
    dt_utc = dt_local.astimezone(pytz.utc)

migurski

#!/usr/bin/env python3
import datetime
import json
import os
import sys
import gzip

import ics
import requests
import pytz

migurski

INTRODUCTION
============
This archive was generated at the request of the following user:
- @username at the time the archive was generated: migurski
- Account ID: 2790981

The easiest way to navigate your archive is to open the HTML renderer in a desktop web browser by double clicking the “Your archive” file included in the main folder once the archive is unzipped.

Note that the HTML renderer only works if the archive is less than 50GB. Also note that the HTML renderer only includes a subset of the data included in the archive. To see all the data included in the archive, please navigate the JSON files located in the “data” folder.

migurski

migurski

SELECT 
  categories.main AS main_category,
  names['common'][1]['value'] AS common_name,
  confidence,
  ST_AsText(ST_GeomFromBinary(geometry)) AS wkt,
  confidence,
  CASE WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((0.905301687393953 47.895747559191,3.79669831260605 47.895747559191,3.79669831260605 49.79817987169,0.905301687393953 49.79817987169,0.905301687393953 47.895747559191))')) THEN 'Paris'
    WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((75.7827016873939 27.3583247590675,78.674098312606 27.3583247590675,78.674098312606 29.8959350440702,75.7827016873939 29.8959350440702,75.7827016873939 27.3583247590675))')) THEN 'New Delhi'
    WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((138.325301687394 34.5021508791562,141.216698312606 34.5021508791562,141.216698312606 36.8505684661713,138.325301687394 36.8505684661713,138.325301687394 34.5021508791562))')) THEN 'Tokyo'
    WHEN ST_Within(ST_GeomFromBinary(geometry), ST_Geom

migurski

import re
import sys
import numpy
import shapely.geometry
import shapely.ops
import bezier

moveto_pat = re.compile(r'^M(?P<x>-?\d+(\.\d+(e-?\d+)?)?),(?P<y>-?\d+(\.\d+(e-?\d+)?)?)(\s+(?P<tail>.+))?$')
lineto_pat = re.compile(r'^L(?P<x>-?\d+(\.\d+(e-?\d+)?)?),(?P<y>-?\d+(\.\d+(e-?\d+)?)?)(\s+(?P<tail>.+))?$')
curveto_pat = re.compile(r'''