generally use pdf2text -layout to convert text to pdf in Linux command line then import the text file into R. For working with tables I use tabulizer to extract the tables in the browser though there also are native python and R bindings. You might also use pdftools on CRAN for importing PDFs but in my experience that requires more wrangling than creating a text file using pdf2text or tabulizer externally.
https://datascienceplus.com/extracting-tables-from-pdfs-in-r-using-the-tabulizer-package/
I discovered the tabula-py library, which is an alternative to converting PDFs externally to Python using pdftotext -layout from the poppler set of utilities. This might be a good alternative to having to install and run a seperate command line program.
import tabula
import pandas as pd
pdf="/home/andy/nov16-Enrollment/AlbanyED_nov16.pdf"
df=pd.concat(tabula.read_pdf(pdf,pages='all', stream=True))
df.query("STATUS=='Active'")
| COUNTY | ELECTION DIST | STATUS | DEM | REP | CON | GRE | WOR | IND | WEP | REF | OTH | BLANK | TOTAL | COUNTY ELECTION DIST | Unnamed: 0 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Albany | ALBANY 001001 | Active | 67 | 19 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 13 | 101 | NaN | NaN |
| 3 | Albany | ALBANY 001002 | Active | 286 | 20 | 3 | 0 | 1 | 19 | 0 | 0 | 0 | 45 | 374 | NaN | NaN |
| 6 | Albany | ALBANY 001003 | Active | 468 | 32 | 9 | 3 | 1 | 19 | 0 | 0 | 0 | 81 | 613 | NaN | NaN |
| 9 | Albany | ALBANY 001004 | Active | 449 | 25 | 6 | 1 | 4 | 15 | 1 | 0 | 1 | 74 | 576 | NaN | NaN |
| 12 | Albany | ALBANY 001005 | Active | 5 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 6 | NaN | NaN |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … | … |
| 7 | NaN | NaN | Active | 291 | 59 | 4 | 0 | 0 | 31 | 0 | 1 | 1 | 89 | 476 | Albany WATERVLIET 004004 | NaN |
| 10 | NaN | NaN | Active | 339 | 213 | 29 | 4 | 3 | 57 | 0 | 0 | 0 | 202 | 847 | Albany WESTERLO 000001 | NaN |
| 13 | NaN | NaN | Active | 348 | 119 | 17 | 1 | 5 | 43 | 0 | 0 | 0 | 160 | 693 | Albany WESTERLO 000002 | NaN |
| 16 | NaN | NaN | Active | 314 | 137 | 26 | 2 | 2 | 35 | 0 | 0 | 0 | 196 | 712 | Albany WESTERLO 000003 | NaN |
| 19 | NaN | NaN | Active | 91,658 | 35,821 | 2,991 | 525 | 608 | 9,612 | 49 | 14 | 198 | 41,926 | 183,402 | Albany County Total | NaN |
There also poppler bindings for python, which you can use after installing the poppler-cpp developer library. This should work but currently it’s not parsing things correctly.
from poppler import load_from_file, PageRenderer
pdf_document = load_from_file(pdfFile)
page_1 = pdf_document.create_page(0)
page_1_text = page_1.text()
import pandas as pd
from io import StringIO
df = pd.read_csv(StringIO(page_1_text),
skiprows=3,
sep='\s{2,}',
engine='python',
error_bad_lines=False)
df
| COUNTY ELECTION DIST | STATUS | DEM | REP | CON | GRE | WOR | IND | WEP REF OTH BLANK TOTAL | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Albany ALBANY 001001 | Active | 67.0 | 19.0 | 1.0 | 0.0 | 0.0 | 1.0 | 0.0 | 0.0 | 0.0 | 13.0 | 101.0 |
| Inactive | 5.0 | 2.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 2.0 | 9.0 | |
| Total | 72.0 | 21.0 | 1.0 | 0.0 | 0.0 | 1.0 | 0.0 | 0.0 | 0.0 | 15.0 | 110.0 | |
| Albany ALBANY 001002 | Active | 286.0 | 20.0 | 3.0 | 0.0 | 1.0 | 19.0 | 0.0 | 0.0 | 0.0 | 45.0 | 374.0 |
| Inactive | 27.0 | 8.0 | 0.0 | 0.0 | 0.0 | 3.0 | 0.0 | 0.0 | 0.0 | 12.0 | 50.0 | |
| Total | 313.0 | 28.0 | 3.0 | 0.0 | 1.0 | 22.0 | 0.0 | 0.0 | 0.0 | 57.0 | 424.0 | |
| Albany ALBANY 001003 | Active | 468.0 | 32.0 | 9.0 | 3.0 | 1.0 | 19.0 | 0.0 | 0.0 | 0.0 | 81.0 | 613.0 |
| Inactive | 56.0 | 5.0 | 0.0 | 0.0 | 0.0 | 2.0 | 0.0 | 0.0 | 0.0 | 19.0 | 82.0 | |
| Total | 524.0 | 37.0 | 9.0 | 3.0 | 1.0 | 21.0 | 0.0 | 0.0 | 0.0 | 100.0 | 695.0 | |
| Albany ALBANY 001004 | Active | 449.0 | 25.0 | 6.0 | 1.0 | 4.0 | 15.0 | 1.0 | 0.0 | 1.0 | 74.0 | 576.0 |
| Inactive | 64.0 | 0.0 | 1.0 | 0.0 | 0.0 | 4.0 | 0.0 | 0.0 | 0.0 | 13.0 | 82.0 | |
| Total | 513.0 | 25.0 | 7.0 | 1.0 | 4.0 | 19.0 | 1.0 | 0.0 | 1.0 | 87.0 | 658.0 | |
| Albany ALBANY 001005 | Active | 5.0 | 0.0 | 0.0 | 0.0 | 0.0 | 1.0 | 0.0 | 0.0 | 0.0 | 0.0 | 6.0 |
| Total | 5.0 | 0.0 | 0.0 | 0.0 | 0.0 | 1.0 | 0.0 | 0.0 | 0.0 | 0.0 | 6.0 | |
| Albany ALBANY 001006 | Active | 462.0 | 11.0 | 5.0 | 2.0 | 4.0 | 14.0 | 0.0 | 0.0 | 1.0 | 57.0 | 556.0 |
| Inactive | 63.0 | 3.0 | 0.0 | 0.0 | 0.0 | 5.0 | 0.0 | 0.0 | 1.0 | 12.0 | 84.0 | |
| Total | 525.0 | 14.0 | 5.0 | 2.0 | 4.0 | 19.0 | 0.0 | 0.0 | 2.0 | 69.0 | 640.0 | |
| Albany ALBANY 001007 | Active | 435.0 | 19.0 | 1.0 | 0.0 | 2.0 | 19.0 | 0.0 | 0.0 | 0.0 | 62.0 | 538.0 |
| Inactive | 60.0 | 3.0 | 0.0 | 0.0 | 2.0 | 3.0 | 0.0 | 0.0 | 0.0 | 7.0 | 75.0 | |
| Total | 495.0 | 22.0 | 1.0 | 0.0 | 4.0 | 22.0 | 0.0 | 0.0 | 0.0 | 69.0 | 613.0 | |
| Albany ALBANY 001008 | Active | 160.0 | 7.0 | 1.0 | 0.0 | 2.0 | 2.0 | 0.0 | 0.0 | 1.0 | 37.0 | 210.0 |
| Page 1 of 46 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
I’m making some progress on redoing the data structure to trim down the blog size to make it more efficient with years worth of space to grow. Rather than keep PDFs of all the maps I will have the blog seemlessly generate them on the fly. Doesn’t seem to be real CPU intensive to do that and if I see performance problems I can cache the fifty or hundred frequently requested maps. Most of the PDFs on the blog are image based, I don’t do Vector PDFs due to the size of the maps.
Taking out some obsolete code from the blog, some of which hasn’t been actively used or maintained in over a decade. I’m not going to keep the interactive 3D models as they’re kind of big and Q3D2JS plugin keeps breaking their format requiring changes to the blog code. Also the side by side history format is getting dumped as I don’t use it much in favor of the mapwarper.net website.
I’ve decided to monetize the blog with web advertisements but not the home page. This way people that are here primarily for maps or info about camping can get their information while my blog homepage is free of ads. I hate ads but with the cost of hosting going up, it should cover the cost of hosting and maybe give me a few bucks to cover my time creating maps.
I’ve come up with a framework in my head to make the switch over while not breaking too many things in the change over as I delete and upload the new versions of the data structure. The thing is I want to delete part of the old structure while uploading the new structure without taking all of the blog offline in the process. I should be able to do this but it takes a bit of planning as its a major undertaking, requiring a large amount of data to be re-uploaded in the process. mod-rewrite should get me most of the way there though in the transition – my goal is to break as little as possible or change links around.
I’m back to thinking about that again. I decided not to auto renew my blog but I still have to October 23rd to make up my mind and pay the $530 fee for the next three years. Indeed the cost is the big sticking point but also a recent email from the hosting company suggesting an up sell to an even more expensive contract due to the size of my blog – 36 gigabytes as they’re enforcing a 40 gig quota come November 1st as unlimited storage with their plus plan comes to an end. Somehow that rubs me the wrong way with the higher costs all around.
I’ve been considering my alternatives. One is to pull the plug on the blog come November. Another is to change web hosts. Or I could improve the efficiency of my data management system and or purge the oldest photos and maps which rarely get served and don’t add much value. The PDFs for maps take up a lot of space but get few downloads compared to the JPEG versions. Lossless compression could be increased, duplicated maps and scaled data deleted. I’ve also thought about embracing advertising, especially on the maps and archives pages which are mostly search engine traffic. Indeed if I were to embrace advertising it would be a revenue neutral proposition.
I discovered I accidentally uploaded the wrong file for the Albany County 2022 Election Results Reapportioned to the current EDs but it has been fixed. https://docs.google.com/spreadsheets/d/1f3p5vNnWtCnkZ38DZwt5GR74-aPauWSaoFSkspPqEiE/edit#gid=1481996295
Also, I added the 2023 Albany County Primary crunched numbers to my 2019-2023 archive, which includes all of those old results in their original version and “adjusted based on block-level Voting Age Population ” to currently in-effect election districts.
https://andyarthur.org/data/albany.county.election.results.2019.2022.zip
I’ve thought about going back to 2018 and before, but I’m not sure how useful those numbers are as demographics shift over the decade, and prior to November 2018 the State Board had enrollments in PDFs which can be tricker to parse then Excel files.
Usually when I want to research a New York organization I start at NY Open Government, which is a website put out by the NY Attorney General and brings together several open datasets.
One way to find people’s addresses is to find them in the voter file. Voter Ref contains the voter files for several states, which can be handy for looking up people’s addresses, date of birth, party registration.
You can confirm the latest information on people’s voting registration and address by using the state board of election’s voter lookup. You will need their county, date of birth and zip code which you can get from Voter Ref.
See Through NY has listings of many though not all government employees, which can be useful when you are trying to find information on government workers. No addresses here, but you can find salaries and who people work for in government. If you need bulk data, I wrote a scraping script.
Another good way to gleam people’s addresses and the candidates they search is NYS Campaign Finance Search. If you think somebody might work for a candidate or campaign, you can search the campaign expenses section.
The FEC Campaign Search includes a contributor’s address and reported employer, which can provide useful information.
Every county in New York State is required to post their tax rolls to their website. Tax rolls can be usually found by searching on Google: “XXXX County Tax Rolls” without the quotes. Not only can you find all of the properties owned by a person that way, you can find their address, assessed value and other information. Often the county tax rolls include information on tax exemptions, such as the Guilderland Solar Exemption and Veterans STAR Reduction, which can help you find people who have solar on their homes or are Veterans. I wrote a script to convert the PDFs into Excel spreadsheets.
If you need to search a whole county or the even the state, you will want to get the full roll from NYS GIS. You don’t need mapping or GIS software to use the Shapefiles — Microsoft Excel and OpenOffice can natively open .DBF which contain the data tables. NYS GIS offers selected countys tax-maps as a Shapefile or GeoPackage too.
NYS GIS Parcels Program: https://gis.ny.gov/parcels/
NYS GIS Tax Parcels Centroid Points: gis.ny.gov/gisdata/inventories/details.cfm?DSID=1300
In addition, most counties offer their tax maps as ArcGIS REST/Services that can be used in a GIS Program like QGIS. You can find them by searching on Google: “XXX County “REST/Services” parcel”.
How to download ArcGIS Rest/Services as KML Google Maps File: mappingsupport.com/p2/kmz_demo/How_to_download_arcgis_data_as_kmz.pdf
ArcPuller is a Great Way to Get this Data in R: andyarthur.org/youll-like-arcpullr.html
In addition, Joseph Elfelt maintains a list of many open government REST/Services:
PDF: https://mappingsupport.com/p/surf_gis/list-federal-state-county-
city-GIS-servers.pdf
txt file: https://mappingsupport.com/p/surf_gis/list-federal-state-county-city-GIS-servers.t
xt
csv file: https://mappingsupport.com/p/surf_gis/list-federal-state-county-city-GIS-servers.c
sv
An easier to read version can be found here: https://servers.cartobin.com/state/New%20York
Sometimes it’s useful to find what state contracts people have:
See how local governments like counties and cities send their money:
Many different data sets can be found on data.ny.gov
I thought about taking today and Friday off but with the front coming through, I decided it would be an awful cold and gray weekend. Plus going to work just is the default option, doesn’t require me to plan or spend money that I don’t have.
Partly cloudy and 54 degrees at the Elm Ave Park and Ride. ⛅ Calm wind. The dew point is 51 degrees.
I was surprised how many are on the Express Bus 🚍 downtown – six. This is particularly surprising as the later bus I take is less popular and many of the regular customers stopped riding during the pandemic and after Voorheesville service ended. Maybe this will help make the service more sustainable.
Found an old Broken USB cable to use as a belt cable 🔌 to keep my wallet tied to my pants 👖. Seems silly but if it keeps me from loosing my wallet, all the better. I worried a lot about that yesterday.
Been working on building a PANDAS 🐼 dataset with party enrollment to make graphs and other data analysis. The hardest thing was downloading and parsing the PDFs but even that isn’t rocket 🚀 science. Python does all the hard work.