At Qri, we’ve been organizing a volunteer effort to manually geocode records in a 5000-row dataset. Each record is a mini research project. Some have a discrete address, others are found through local knowledge or google sleuthing. In this blog, we’ll cover why we had to call on the crowd to accomplish this task, and share some of the tools and techniques we employ to keep things organized.

JavaScript is not available.

We need volunteers to manually look up projects and add lat/lon coordinates. If interested I will assign you a community district to research. Each has several dozen and should take a couple of hours to geocode. Can you help? @qri_io #crowdsourcing pic.twitter.com/ksb9rySqmg

The Data White Whale: Mapping New York City Capital Projects

I recently had the opportunity to scrape and do some simple analysis of a fascinating dataset, NYC Capital Project Detail Data. In this dataset are details of the billions of dollars my city is spending on capital improvements. This includes water and sewer mains, park improvements, building construction and renovation, etc. For a city this large, it’s a lot to take in (there are over 5000 projects happening simultaneously at the moment and the source PDFs are published as 5 volumes), but this dataset, and the PDFs it was scraped from, represent the best comprehensive project-level data available to the public.

One big problem: The projects aren’t mappable. Not yet anyway. The information system these projects are tracked in does not keep track of spatial data (a point, line, or polygon geometry). Rather, projects are associated with one or more of New York’s 59 community districts, giving some idea of the applicable geographic benefits, but not allowing for precise “mapping of the budget”. Furthermore, some projects are deemed to have borough-wide or citywide geographic impact (think computer systems, fleets of vehicles, or other less construction-y investments).

“Everything happens somewhere” is an old saying in GIS circles, along with “all data are spatial”. In the prior blog post about scraping the data and the possibilities it could unlock, I attempted to manually assign point geometries to every project I could research in a single community district. To make this dataset spatial, I literally added two more columns, latitudeand longitude , and then literally googled keywords in the project descriptions to see if I could figure out where a project was happening. This is easy for libraries and public housing projects with discrete names, not so much with sewer projects that may have obscure acronyms or other jargon-y descriptions (But now I know what “guniting” is)

The result is this bubble map showing the location and approximate total cost (dollars spent + next 5 years of planned spend) for a few dozen projects:

This is a fascinating overview of projects that the system of record says will benefit my local community. All it took was a couple of hours of research, looking for clues in the project descriptions to figure out where they were located. Google, Google Maps, Community News Sites, Blogs, and whatever else I could pull up on the internet were the tools of the trade.

So… what would it take to do this for the whole city? We’re not talking millions of rows. There’s only 5000 projects, and only 4000 or so of them are non-citywide. Once we have the entire dataset geocoded, this opens up the door to more thorough analysis, along with a proper web application for anyone to search and explore every capital project. Someday…

Divide & C̶o̶n̶q̶u̶e̶r̶ Geocode

The new dataset contains a column community_boards_served, making it easy to filter for each of the 59 districts. Community districts are a known geography in NYC, and are a simple way to assign a subset of projects to a volunteer. I did two myself, so now all I need is 57 more civic data nerds to repeat the process!

Airtable — Data Editing

Airtable is an extremely useful way to make little filtered views of a larger dataset. It takes some clicking, but after uploading the original dataset and adding two numeric columns for the latitude and longitude, I just need to make a view for each volunteer to work through.

On the left we have a big list of views, one for each community district, and a few more to track overall progress. When a volunteer claims a district, it’s easy for them to drop into the airtable, find their view, and get to work!

One caveat: Everyone working in this dataset has “Editor” access, which could be chaotic. It’s not really a controlled environment, and in theory users could mess up other people’s records, modify fields from the source data, etc. It hasn’t been a problem at all yet, as the task at hand is pretty simple and people are only really editing two fields for each row. I don’t share the link for this airtable publicly, it is only shared with people who have committed to working on the project.

Google Docs — Instructions

We use a google doc for instructions. This effort doesn’t have a website (yet!), so a google doc serves as easy-to-edit and easy-to-share content. I have it sitting ready in my bookmarks bar, and the instant someone expresses interest in helping out, I fire off this URL.

Inside are a quickly digestible background of the project, and a step-by-step guide for communicating with the organizer (me) and geocoding. We even have a notes section with tips from other volunteers that may come in handy.

Bumping some great tools to use when looking for latlngs. - https://t.co/yWaP2Ui5NA for facilities owned by agency- https://t.co/oCbCb0qFpX for pin pointing park improvements- https://t.co/eqj05605iF for any DEP sewage inf. such as pumps, treatment, and CSOs pic.twitter.com/b8iopmEom6

Google Sheets- Assignment Tracking

I could have just as easily used another Airtable for this (and probably will). For now, I use a sheet to keep track of who has claimed or been assigned to each community district. It’s a bit chaotic, but I use background color to track what is in progress/done, and can get a good idea of how many districts are yet to be claimed. This sheet is also easily embeddable into the instructions doc, and lets new volunteers know what is still available and how much is left to go.

Twitter, Linkedin, Facebook, Medium — Recruiting

The original blog post about the data scraping included a call to action at the end that dreamed big about a crowdsourced effort to map the whole dataset. This one will include the same. Aside from that, we regularly post on social media asking for volunteers to take off a bite-size chunk of the dataset. (We even discussed this effort in a recent community call and put it on YouTube)

I like to frame it as a great way to get involved in a community data project without much skill or time commitment. Literally anyone can look up these projects. A bit of local knowledge of NYC helps, but isn’t required.

It’s also a lot of fun to look up projects. You can learn a lot about various entities that improve the infrastructure (I am talking about Business Improvement Districts (BIDs) and Local Development Corporations (LDCs) as well as myriad cultural institutions that get capital funding.

Look at all those capital projects in Queens Community District 7, mapped by @zhi_keng. We need volunteers to geocode projects in other parts of the city. If you're interested, let me know and you can claim a district! @qri_io pic.twitter.com/lMO4bzJOyt

qri.cloud — Archival and Publishing

Managing data in airtable can be chaotic, especially with many cooks in the kitchen. Qri is a version control system for datasets, and is well-suited for creating snapshots of the data from time to time.

I’ve created a node.js script that pulls the data from the airtable API, transforms it into a CSV and saves it to disk, then executes a commit on the qri dataset chriswhong/nyc_capital_project_data_geom. If the commit is successful (meaning there was some change from the last version), it then pushes the dataset to qri.cloud, which you can see here. The dataset, along with its version history, is published and available to all.

You can download it, pull it if you have Qri Desktop, or connect to it via our API. You can also explore older versions and will soon be able to get a report of what changed from version to version. It’s easy and free to start publishing your own data on qri.cloud. If you have a CSV that’s tired of being hidden from the world on your computer, consider publishing it with Qri.

Next Steps

At the time of writing, we have just over 1,000 projects geocoded! This is tremendous progress for this low-tech distributed effort we’ve thrown together over the past week. The recipe we’ve outlined here appears to be working out, and we’re always looking for additional hands. If you’d like to help out, take a look at the instructions and reach out!

Crowdsourcing Civic Data — A Playbook of Tools and Techniques was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

First, take a look at the article:

Crowded Subways? Yes, in Neighborhoods Where People Have to Go to Work

The article refers to “percent of usual” ridership in several places which was our attempt to come up with a normalized measurement for comparing the recovery of individual stations, boroughs, and the system as a whole. It’s important to understand how we calculated the “usual” ridership which we compared October ridership to.

What is usual, anyway?

For our analysis, “usual” was the average monthly ridership for a given station in 2019. We add up all of the subway entries for a given station for the entire year and divide by 12. Why an average? In earlier iterations of this analysis we would compare a station/month to the same station/month during the previous year, but this led to some anomalies as there were stations with skewed ridership due to construction and other abnormal events. Using an average gives us a more consistent and reliable denominator for calculating the percentage.

“Even as Manhattan stations remain eerily empty, a surge of commuters in other boroughs has pushed overall ridership to 30 percent of normal levels.”

What are the numbers behind this 30% figure? 45.1 million / 143.0 million = .3155 (systemwide entries in October 2020 / average monthly entries in 2019)

We can use the same math to calculate the percent of usual for each borough:

We can see that though Manhattan has the largest numbers, percentage-wise it is the only borough that is below the citywide average (31.55%). Brooklyn and Queens approached 40% of normal ridership, and the Bronx had the largest resurgence of activity at 45%.

“Even as stations in Manhattan that were once the busiest in the city remain eerily quiet — with as few as one-fifth of typical passenger levels — ridership at some stops in the other boroughs has surpassed 50 percent of pre-pandemic levels.”

When we do the same calculation as above for individual stations, we can see a broad range of percentages. Here are the top 15 and bottom 15, but most are in the middle somewhere:

A quick histogram of the percentages shows the distribution more clearly.

Borough Breakdown

Creating the same histogram for each of the four boroughs served by the subway shows clearly distinct patterns. In the Bronx we see a much smaller range pushed toward the higher end of the scale around the 40% mark. In Manhattan the pattern is more erratic, but skews to the lower percentages. We can also see that all three of the outliers are stations in Queens.

“In the months since, the system’s revival has been similarly skewed: Ridership has returned to about 40 percent of normal in Brooklyn and 45 percent in the Bronx. In Manhattan, ridership is still just 25 percent of what it was before the outbreak.”

Here’s the same data in the table above, as a chart, showing the four boroughs served by the subway along with the citywide percentage.

Finally, a map allows us to see individual stations and observe the overall patterns. The stations in Manhattan with low ridership are mostly clustered in midtown and below, and we can see the small selection of stations throughout the city where ridership has surpassed 50% of usual.

Thanks for reading!

The code used in this analysis is available on github.

The dataset, NYC Turnstile Daily Counts 2020 is available on qri.cloud.

Analyzing the Recovery of the NYC Subway during COVID-19 was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

Update: 4:19pm EST on December 4th. The errors have been corrected, and all all numbers and visuals in this blog post have been updated. An update to the dataset has also been pushed to qri.cloud, and an update to the scraper code has been pushed to github.

If you follow the exciting world of municipal budgets, you may know that New York City’s operating budget is close to $90 Billion for the current fiscal year. This is fairly cut and dry, money is raised from taxes and other sources, and spent on salaries, rent, and other operating costs. Repeat annually.

The capital budget, however, is a more elusive beast. Capital projects may span years or even decades, and the enormity of the numbers and the complexity of the process make it quite difficult to figure out where the money goes, and when.

A Quick Primer on Capital Budget Information

If you want to inspect the capital budget, here’s the official capital budget document for Fiscal Year 2021 approved by the city council and mayor. It’s 604 pages long, and is mostly screen dumps from a government computer system. If you scan these pages, which are already cryptic and confusing, you may see some specific project-related descriptions for major projects like bridges, but most of the lines have something like:

CONSTRUCTION, RECONSTRUCTION, ACQUISITION OR INSTALLATION OF A NON-CITY OWNED PHYSICAL PUBLIC BETTERMENT OR IMPROVEMENT WITH A CITY PURPOSE, WHICH WOULD BE CLASSIFIED AS A CAPITAL ASSET UNDER GENERALLY ACCEPTED ACCOUNTING PRINCIPLES FOR MUNICIPALITIES; FOR THE GRAND STREET SETTLEMENT.

Sounds pretty vague, right? This is because the Capital Budget deals in appropriations to “budget lines”, not projects. These can be thought of as buckets of money reserved for a specific type of use. To get down to the project level, where money turns into real things that humans need or use, we must look to the Capital Commitment Plan.

The Capital Commitment Plan is a 4000 page document of more computer system screen dumps, and is so large, it has to be published in 4 parts. Take a look at one of the pdfs over at https://www1.nyc.gov/site/omb/publications/fy20-accp.page, and you will find screens that list projects under each budget line, along with cryptic information about milestones, planned completion dates, and expected costs over time.

So, we’ve got thousands of pages of PDFs to pore over, or we can go look at high-level numbers, but how am I as a citizen supposed to figure out what is happening in my neighborhood? How is an analyst supposed to figure out if these funds are being allocated in a fair and equitable way? There’s hope in a new set of publications that the Office of Management and Budget recently started publishing.

A New Hope: Capital Project Detail Data PDFs

Recently, a new set of budget publications appeared on the OMB website, immediately following the capital commitment plan PDFs. They are known as Capital Project Detail Data, and are a wonderful improvement on making all of the above information more digestible by normal humans.

They include a nicer layout with a single page of detailed information for every capital project in the city. Here’s the page for the carousel improvement in Prospect Park, Brooklyn.

From this page, we can see:

• They have spent $90,000 on this project so far
• They plan to spend $78k in FY 2020, and $422k in 2021
• The total spent +planned is $590k, which is higher than the original budget of $500k
• There’s actually an error in this PDF where the community districts are prefixed with the wrong borough code. It should be “3” for Brooklyn.
• This project will serve Brooklyn Community Districts 6, 7, 8, 9, and 14 (aka 306, 307, 309, and 314 in the data)

We also have milestones with original vs current start and end dates, to get an idea of the timeliness/delays of the project. There are also some goodies in here that are not included in any of the other PDFs, such as “Project Location” and “Scope Summary”, all of which help to move this project out of paper and into the real world of things that New Yorkers need and use.

PDFs Are Where Data Goes to Die

This is an old saying in the circles I run in (civic open data nerd circles, to be precise). There’s a ton of data in these pdfs that’s been turned into page layouts for human eyes, but in the process is made non-machine-readable, so we cannot use it for analysis purposes. Wouldn’t it be great to do a simple sum() on the total spend on all projects in my community district, or to group all projects in Brooklyn by their category and get a simple run-down of how we’re spending all this money?

Better yet, can we map where these projects are happening and see where we’re spending all this money?

OMB has actually published two datasets that represent the Capital Project Detail Data PDFs Capital Project Detail Data — Dollars, and Capital Project Detail Data — Milestones.

A noble effort and an excellent commitment to transparency, but unfortunately at the moment they are both stale and incomplete. Specifically, I wanted to do analysis of the community_districts_served for all these projects, so had to take matters into my own hands. The data from these pdfs can be scraped!

Scraping the Data

I won’t bore you with the details (the code is on github), but we scraped the Capital Project Detail Data PDFs and published the data on qri.cloud, so anyone can have access to granular NYC Capital Project Data. We also published two tables:

• NYC Capital Project Detail Data | Dataset Published on qri.cloud
• NYC Capital Project Detail Data - Milestones | Dataset Published on qri.cloud

The meat is in the first table, where we have one row for each of the 5,200 discrete capital projects currently happening in New York City! The second table contains the milestones, which have a many-to-one relationship with projects.

Some top-level highlights:

• The sum of combined_total for all rows is $105.8B ($40.9B prior actual spending/$64.9B planned spending)
• While many projects are assignable to one the five boroughs, we see that the largest chunk of this spending ($36.4 B) is for citywide projects . We can also see a detailed borough breakdown of the non-citywide projects.

Citywide vs. Boroughwide vs. Local Projects

Below the borough level, things get trickier. As we saw in the PDFs, a single project can serve multiple community districts (for example, it looks like most of the projects in Prospect Park are listed as serving all of the surrounding districts. The data also includes some nonexistent community district ids meant to describe projects with borough-wide benefit. Community districts ids usually contain a single digit for the borough and two more for the district number, so Bronx Community District 4 = ‘204’. The data include district ids such as 200 and 299 which are inferred to mean the benefits are borough-wide (someone please correct me if I am wrong on this and 00 and 99 have different implications).

To get a basic understanding of how many projects have “local” benefits, I did some simple grouping, treating projects with at least one real community district listed as local, all others are considered borough-wide.

We can see that the ratio varies quite a bit from borough to borough, with Brooklyn having nearly double the amount of local spending for boroughwide projects. Collectively, about 48% of all borough-specific project spending is local under this definition.

Local Projects by Category

Now, the moment you’ve been waiting for and the reason you came to read this blog post. With community district and categories assigned to each project, we can map out where local projects for each category are happening around the city! A few caveats and notes before we dive in:

• The categories shown come from the ten_year_plan_category field in the dataset
• Projects attributable to more than one community district are represented more than once in the calculations used to generate the maps
• All of the spending shown below represents only non-citywide and non-boroughwide, or about 40% of the $105.8B total spending outlined in the dataset

Drumroll, please 🥁…

Right off the bat, several categories stand out as having wide coverage around the city:

• Large, major, and regional park reconstruction
• Neighborhood parks and playgrounds
• Essential reconstruction of facilities
• Garages and facilities

Parks, as we might expect, are spread throughout the city and need plenty of capital investment for improvements. “Facilities” is vague, and there are may other categories that also describe facilities, so it’s not really clear what we’re seeing here. Each tiny map on this grid could probably be its own blog post. As a viewer, I immediately want to drill down into each category and see the individual projects. An interactive UI for exploring Capital Spending would go a long way, and I’d love to build one someday (again)!

Mapping a Single Community District’s Local Projects

Another useful application of this rich, project-level data is discrete mapping of individual projects. Of course, we don’t have latitude/longitude coordinates in the data, but we can’t let that stop us.

I manually looked up each of the 46 projects that serve my home community district, Brooklyn 6, assigning a point coordinate to each one. The location, combined with the total spending and project description makes for a fascinating visual of what’s going on with the in my area ($480.9M of actual + planned spending).

The big ones include water and sewer reconstruction along with reconstruction of the drawbridge over the Gowanus Canal. It’s also interesting to see the taxpayers contribute to the cruise terminal. The projects in Prospect Park generally list all of the surrounding districts as served, so we can see some interesting projects like renovations to the carousel, a comfort station, and the LeFrak Center (I thought this was complete but I guess it’s still an active project)

What does $3.7M for ADA accessibility at Brooklyn’s smallest library branch (Pacific Street) entail?

Why does HVAC replacement at the Carroll Gardens Library cost $4M?

What does $15.9M of ball field improvements in Red Hook actually look like?

This dataset probably raises more questions than it answers, but I think that’s the point. Now we can see, sum, map, chart, and otherwise have more visibility and informed discussions about how we spend our capital dollars.

Next Steps

Mapping capital spending has been a white whale of mine for a while (I actually worked on this during my time at the Department of City Planning but the project never saw the light of day). Obviously, we need to assign latitude/longitude coordinates to the other 4000 or so capital projects that serve local community districts, so we can expand the map above to the whole city. On top of that, there’s so much rich data about each project that static visuals don’t do it justice. An interactive tool where we can combine the mappable projects with the citywide projects and allow the user to “surf the budget” is in order, and would go a long way towards making this information useful and actionable to community groups, politicians, and anyone else with a stake in how we invest in our city.

If you’d like to help out with this effort, we’d love to hear from you. Of course, we’ll be managing the data in qri, our cutting-edge version control system for datasets, so every contribution will be trackable and public for the world to see.

Thanks for reading! Let us know what you found exploring the maps on this blog post by giving us a yell on twitter.

Data scraping code (node/javascript)
Code for the analysis and graphics used in this post (RStudio)

Mapping Billions in NYC Capital Spending was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

tl;dr just give me the data please: https://qri.cloud/nyc-transit-data/citibike_daily_counts_2020

The Status Quo

Citibike does a great job publishing archival trip data on their website at https://citibikenyc/system-data. When you click “Download Citi Bike trip history data”, you land on the index page of an S3 bucket, where a zip file for each month of data awaits you. The archives go back to 2013!

So what’s in the data? Here’s a glimpse of what’s inside the dataset for October 2020 (101MB zipped, 427MB unzipped!). This is already becoming a substantial disk space commitment if you want to look at multiple months worth of data.

We can see that each row is a single trip, with the duration, start time, end time, start station details (and location), end station details, etc. There’s even a birth year and gender. There’s also a bike id, meaning it is possible to track a single bike in its travels around the city. Someday it might be fun to animate a bike’s journey, similar to the way I did with Taxis back in 2014, but I digress.

The Code

I chose to do this aggregation in R Studio, but it could just as easily have been done in Python/Jupyter or even Javascript. The full code is on github if you want to take a look.

First, I manually downloaded each of the monthly zips for 2020 and unzipped them. This can be scripted too, but I already had them on disk from an earlier analysis.

Next, I wrote a function called processMonth which, as the name suggests:

• reads a single months’s csv
• pulls out the calendar date from the trip start timestamp
• creates a lookup table for each unique station
• counts the trip starts for each day/station
• counts the trip ends for each day/station
• joins the start counts, end counts, and station info

processMonth <- function(month) {
  raw <- read_csv(paste("/Users/chriswhong/Sites/citibike-daily/raw/", month, '-citibike-tripdata.csv', sep=''))
  
  # clean names, extract the date from the timestamp
  daily <- clean_names(raw) %>% mutate(date = as.Date(starttime))
  
  startstations <- distinct(daily, start_station_id, .keep_all = TRUE) %>%
    select(start_station_id, start_station_name, start_station_longitude, start_station_latitude) %>%
    rename(station_id = start_station_id, station_name = start_station_name, station_longitude=start_station_longitude, station_latitude = start_station_latitude)
  
  endstations <- distinct(daily, end_station_id, .keep_all = TRUE) %>%
    select(end_station_id, end_station_name, end_station_longitude, end_station_latitude) %>%
    rename(station_id = end_station_id, station_name = end_station_name, station_longitude=end_station_longitude, station_latitude = end_station_latitude)
  
  station_lookup <- bind_rows(startstations, endstations) %>% distinct(station_id, .keep_all = TRUE)
  
  
  # count daily trip starts for each station/date
  daily_starts <- group_by(daily, date, start_station_id) %>%
    summarize(startcount = n()) %>%
    rename(station_id = start_station_id)
  
  # count daily trip ends for each station/date
  daily_ends <- group_by(daily, date, end_station_id) %>%
    summarize(endcount = n()) %>%
    rename(station_id = end_station_id)
  
  # full join them so we don't lose any where there were starts but not ends and vice versa
  combined <- full_join(daily_starts, daily_ends, by = c('date', 'station_id'))
  
  with_station_info <- left_join(combined, station_lookup, by='station_id')
  
  return with_station_info
  
}

Once I had this function working correctly for one month, all I had to do was run it for each month, combine the results together, and a new CSV is born!

dailies <- list()

months <- c('202001', '202002', '202003', '202004', '202005', '202006', '202007', '202008', '202009', '202010')
for (i in months) {
  dailies[[i]] <- processMonth(i)
}

# combine all of the months into a single table
for (i in seq_along(months)) {
  if (i == 1) {
    combined <- dailies[[months[1]]]
  } else {
    combined <- bind_rows(combined, dailies[[months[i]]])
  }
}

# write to CSV
write_csv(combined, '/Users/chriswhong/Sites/citibike-daily/citibike-daily-counts-2020.csv')

Here’s what citibike-daily-counts-2020.csvlooks like (now just 20MB!):

At the top of the dataset are all the aggregates for January 1, 2020, each row is a station with a count of starts, count of ends, a human-friendly station name, and lat/lon coordinates for mapping.

The 2020 dataset has been pushed to qri.cloud, where it’s hosted for all to find. If you find it useful, we’d love to hear about it!

Citibike Daily Counts 2020 | Dataset Published on qri.cloud

A Quick Chart

The original prompt for the creation of this dataset came from a Qri user and fellow urban data analyst, Ben Oldenburg. He’s been making use of the 2020 NYC Subway Turnstile Counts dataset in some projects, and wondered if a similar “daily aggregates” dataset existed for Citibike. The answer was no, so we decided to make one.

This is the chart we were after, a quick visual of the effects of the arrival of coronavirus on Citibike traffic in March, followed by what looks like a recovery. We would need to compare this to previous years or ask a citibike expert (I am not one) to get a better feel for “normal”, but the curve is clearly quite different from what we see in the subway data.

Is this dataset helpful to you? Is anything missing? Do you *really* want us to make the same dataset for 2013–2019 and Jersey City? Give us a yell on twitter or drop into our Discord and let us know what features you’d find helpful. Thanks for reading!

Building a Daily Aggregate Dataset from Raw Citibike Trip Data was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

November 20th is President-Elect Joe Biden’s 78th Birthday.

When he becomes President on January 20, 2021, he will have had the most birthdays of any President assuming the office. To celebrate, here’s a rundown of our 46 Presidents* and their birthdays. Happy Birthday, Mr. President-Elect!

Useless Trivia:

• Only two Presidents share a birthday (11/2): Warren G. Harding and James Polk. *Grover Cleveland was both the 22nd and 24th President. I’m not sure that counts.
• We’ve yet to have a President born on the following dates of any given month (3rd, 17th, 21st, 25th, 26th & 31st).
• October & November are tied for the most popular presidential birth month (6 Presidents born in each month).
• May is the least popular presidential birth month with only 2 (Truman and Kennedy).

What’s Written in the Stars (& Stripes)

If you’re into Astrological signs, Joe Biden’s ascension to the Presidency puts Scorpios ahead (breaking the tie with Aquarius) in the lead among US Presidents at 6:

Scorpio (6): John Adams, James K. Polk, James A. Garfield, Theodore Roosevelt ,Warren G. Harding, Joseph Biden

Aquarius (5): William Henry Harrison, Abraham Lincoln, William McKinley, Franklin Roosevelt, Ronald Reagan

Taurus (4): James Monroe, James Buchanan, Ulysses S. Grant, Harry S Truman

Capricorn (4): Millard Fillmore, Andrew Johnson, Woodrow Wilson, Richard Nixon

Libra (4): Rutherford B. Hayes, Chester Arthur, Dwight Eisenhower, Jimmy Carter

Pisces (4): George Washington, James Madison, Andrew Jackson, Grover Cleveland

Leo (4): Benjamin Harrison, Herbert Hoover, Bill Clinton, Barack Obama

Cancer (4): John Quincy Adams, Calvin Coolidge, Gerald Ford, George W. Bush

Gemini (3): John F. Kennedy, George H.W. Bush, Donald Trump

Sagittarius (3): Martin Van Buren, Zachary Taylor, Franklin Pierce

Aries (2): Thomas Jefferson, John Tyler

Virgo (2): William Howard Taft, Lyndon B. Johnson

Get the data!

The data is free for you to take and play with. Consider adding more interesting data (age on Inauguration day, party affiliation, VP(s) and their birthdays, and on and on….), or generating more useless (or perhaps useful) trivia with what’s already in there!

Birthdays & Astrological Signs of US Presidents | Dataset Published on qri.cloud

Happy Birthday, Mr. President-Elect was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

This post is a follow-up to my first piece exploring the disparities of power and representation across U.S. states in the Electoral College and the U.S. Senate. The stars of that post were the cases of Wyoming v. California, and Vermont v. Texas (pairings intended to show the most dramatic disparities). In this post, I thought I’d zoom out a bit and offer a view on the full landscape of states.

“The Virtue of a Proportional Response”

First, I’d like to acknowledge that I understand the U.S. Senate never was proportional, was not designed to be proportional, will likely never be proportional, and perhaps should not be proportional. I offer this analysis principally to explore an interesting question (how far off are various states’ representation in the US senate today from a proportional arrangement?), and to educate myself and others on the current state of affairs.

To that end, I built the table below showing each U.S. state and the percentage of representation/power it has today compared to what each might under a perfectly proportional system.

If a proportional allocation of power is the goal, then 100% would be the goal for each state. States below 100% are ‘underrepresented’ today, and states above 100% are ‘overrepresented.’

For example, North Dakota has 860% of the representation or power it ‘should’ have in the Senate (Should is in quotes, again presuming proportionality is the goal). It is 760% over-represented.

Here’s how I calculated those percentages:

Proportional Ratio: Under a perfectly proportional system, America’s 327 million residents would be represented by 100 senators at a ratio of 3.27M per Senator.

Present-Day Ratio: I calculated this ratio (population per Senator) by dividing each state’s current population by its count of Senators (2).

I then divided each state’s Present-Day Ratio by its Proportional Ratio and built this table. If a state is proportionally well-represented today, that ratio should be close to 1, or 100%. States below 100% are ‘under-represented’ (in red), states above 100% are over-represented (in green).

Finally, I graphed the results.

Plot the Dots

The graph below shows U.S. states along the X-axis, and their relative percentages of their populations’ under or over-representation in the Senate along the Y-axis. If a state is below the green dotted line, set at the now familiar 100%, it is underrepresented, and if the state is above it, it is overrepresented.

The graph above tells a dramatic story about the states in the upper right-hand corner, but it leaves the false impression that the states crowded near the dotted green line (from California to Kentucky) are in the same ‘representation ballpark.’

I assure you, they are not.

The Population Price (Per Senator)

Here’s essentially the same information presented above, but more simply. Below is a graph sorted by each state’s ratio of its population per Senator. I simply took each state’s population and divided it by two. I also coded each bar (red, blue, purple or grey) with the political party breakdown of its Senate delegation in the 117th Congress (the next Congress).

If we allow for the idea of paying a ‘population price’ per Senator, the states above the dotted green line pay too much (their populations are above the average of 6.55M of perfectly proportional system), the states below the dotted green line are getting a deal on representation.

It’s worth noting that the first (and most populous) nine states from the left (California, Texas, Florida, New York, Pennsylvania, Illinois, Ohio, Georgia, & North Carolina) represent over half of the total US population. Yes, 50% of the population are represented by just 18% of the Senators.

From this data alone it’d be inaccurate to conclude that ‘blue’ states are disadvantaged in the Senate and red states are advantaged, although that’s probably true today. First, a state’s Senate delegation is only one way to define its ‘redness’ or ‘blueness,’ and the binary nature of the two colors betray the competitiveness of any given race in any given year. Second, Congressional (House) delegations are more nuanced (ignoring the warping that happens via gerrymandering) and are determined 3 times as often (every two years vs. every six years). There are more reasons. I’m just saying, you can’t ONLY use this analysis to say one party or the other suffers more or less because of the structural design of the Senate.

Solutions

I don’t have them. Sorry. My only goal here was to crunch the data (linked below) in an effort to educate myself and anyone else about the existing disparities in representation. For now, there seems to be enough work just convincing folks that this is a problem (in my circles anyway). You may think Vermont’s 46x advantage over Texas in the Senate is not a problem, but at least now you know the scale of the advantage.

I’ll close out this two-part series of Senate bashing by directing you to some articles that explore this issue further. Some even lay out interesting scenarios for resolving the problems that arise from the wacky system we’ve designed for ourselves.

• The Path to Give California 12 Senators, and Vermont Just One
• The Senate is a much bigger problem than the Electoral College
• Pack the Union: A Proposal to Admit New States for the Purpose of Amending the Constitution to Ensure Equal Representation
• Small states are getting a much bigger say in who gets on Supreme Court
• The Electoral College is the Tail. The Senate is the Dog.

Get the data

Surely there are other interesting factors aside from gross population (GDP contributions by state, income data, age distribution, population density, etc.) you can use to say something interesting about our states and how they’re represented.

I encourage you to play with the data I’ve made available and run some comparisons of your own. I’ve published the dataset I built & used on qri cloud — a platform for publishing and discovering open data projects:

Electoral College & Senate Disparities | Dataset Published on qri.cloud

You’re free to download my data, subscribe to updates (as I publish new versions), or continue the conversation there by filing an issue.

You’re probably underrepresented in the U.S. Senate. was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

As another presidential election comes and goes, calls for Electoral College reform, too, come and go. The Electoral College is an easy, popular, and worthy punching bag… to me anyway.

In recent weeks, I’ve engaged in vigorous discussions with friends about the Electoral College. I tried to make the case that it is 1) antiquated 2) undemocratic & unfair and 3) racist. I’ve also made the far simpler case: the electoral college distorts the will of voters.

Isn’t that obvious? Isn’t that, by definition, true? Isn’t that… really easy to prove? I was frustrated by the resistance I got to accepting that as fact. So, I pivoted:

“At least it’s not as undemocratic as the U.S. Senate!”

As most Americans ought to know, each state is delegated electors based on its count of seats in the House of Representatives (allocated based on population, with each house district representing roughly 700,000 people — kind of democratic!), and its count of seats in the Senate (two per state, regardless of population—democratic for states, but not for people!).

Because there are 100 electors apportioned based on the count of Senate seats, and 438 electors apportioned based on the count of House seats (Washington, D.C. gets three electors despite not having Senators, and only one, non-voting Congressperson), the electoral college outcome is more heavily weighted by votes that come from a proportional-by-population system (House). The winner-take-all nature of the way electoral votes are won on in the presidential election essentially erases the value this proportionality provides. This is another issue worthy of scrutiny, but not the target of this humble blog post.

The Electoral College is the tail.

Let’s compare the power our most and least populous states, California and Wyoming respectively, have in the electoral college.

• California’s 55 electoral votes are determined by the voters among its population of 39,512,223, giving us a ratio of 718,404 people per electoral vote.
• Wyoming’s 3 electoral votes are determined by the voters among its population of 578,759, giving us a ratio of 192,920 people per electoral vote.

Wyoming voters have 3.7 times the power Californians have in the Electoral College.

When you average out the entire US population per electoral vote and compare each state, California voters have 85% of the power they would under a perfectly proportional electoral college, and Wyoming voters today have 317% of the power they would have under the same system.

A 3.7x disparity is alarming enough to justify reform. However, my beef is less with the Electoral College than it is with the U.S. Senate.

The Senate is the dog.

The undemocratic nature of the Electoral College does its damage one day every four years (obviously ignoring how it shapes campaigns, candidates, priorities, messaging, policy agendas etc., which we really shouldn’t do...). The undemocratic nature of the Senate, however, is at work every day of every year, or 1459 days every four years.

While the House of Representatives was designed to be proportional, the Senate was designed to be “equal”:

“The equality of representation in the Senate is another point, which, being evidently the result of compromise between the opposite pretensions of the large and the small States, does not call for much discussion. If indeed it be right, that among a people thoroughly incorporated into one nation, every district ought to have a PROPORTIONAL share in the government, and that among independent and sovereign States, bound together by a simple league, the parties, however unequal in size, ought to have an EQUAL share in the common councils, it does not appear to be without some reason that in a compound republic, partaking both of the national and federal character, the government ought to be founded on a mixture of the principles of proportional and equal representation.”

— James Madison, The Federalist Papers №62

The distortion of the public will generated by that design, however, has only grown over time as populations boom unevenly across our states over the last century.

In a country with 327 million residents (half of whom live in just 9 states), each U.S. Senator would represent 3.275 million residents in a perfectly proportional system. To show how far we’re off from that ratio, let’s look at California and Wyoming again.

• California’s ratio of population (39.5M) to U.S. Senator (2) is over 19.7 million.
• Wyoming’s is 289,380.

Under a perfectly proportional system…

• Californians are 12.06% of the U.S. population. They ‘should’ have 12.06 Senators representing them instead of 2.
• Californians today have 17% of the Senate representation they ‘should’ have in a perfectly proportional system.
• Wyomingites are 0.18% of the U.S. population. They ‘should’ have 0.18 Senators representing them instead of 2.
• Wyomingites today have over 1,100% (11x) of the representation they ‘should’ have in a perfectly proportional system.

Put it together: Wyoming voters today have over 68x times the power Californians have in the U.S. Senate.

In terms of disparities and warping the public will, the Electoral College is the tail, the Senate is the dog.

The dog wags the tail.

So, what about the other states?

I ran this same analysis (comparing their power disparities under the current Electoral College and Senate arrangement versus a more proportional one, and published the data on Airtable & Qri Cloud.

Here’s how to interpret the key columns:

• Any state with a percentage below/above 100% in the “Percent of EC Power they Should Have” column is under/over-represented in the Electoral College.
• Any state with a percentage below/above 100% in the “Percent of the Senate Power they Should have” column is under/over-represented in the U.S. Senate.

What about Vermont v. Texas?

Successfully arguing for Electoral College and Senate reform hits an obvious wall when you fail to make the case bi-partisan. So, let’s do a quick analysis comparing Vermont (blue state, 49th in population) and Texas (red state, 2nd in population):

The California vs. Wyoming case is quite comparable to Texas v. Vermont

• Texas voters have 80% of the power in the Electoral College they ‘should’ have and 23% of the power in the Senate they ‘should’ have.
• Vermont voters have 294% of the power in the Electoral College they ‘should’ have and 1,050% of the power in the Senate they ‘should’ have.

Vermonters have 3.7x the voting power in the Electoral College as Texans do, but 46.5x the voting power in the Senate!

If you’d like to get your hands dirty with the data I used, I strongly recommend going to the version on Qri Cloud, because there you’ll also have access to column descriptions, useful metadata, readme info, and structure/schema data, and any new versions, should I choose to update it (all of which ride together when you pull the dataset!).

Electoral College & Senate Disparities | Dataset Published on qri.cloud

The Electoral College is the Tail. The Senate is the Dog. was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

Qri (pronounced “query”) is a version control system for datasets. Our software is designed to make datasets more robust, documented, portable, and trustworthy, in ways that file-based data and traditional databases cannot. When you create a Qri Dataset, each version is signed, hashed, timestamped, and immutable… a snapshot of the data that carries crucial metadata with it and is easy to move around.

So Google Sheets is great for editing data, and Qri is great for archiving and sharing data… I think maybe they should talk!

The Setup

I found a Mashed article about the best pizza place in each U.S. state and figured it would make a wonderful 50-row dataset in Qri, but I wanted the convenience of Google Sheets to manually modify the data. I copied the state and pizza place name from the text on the website and created a simple table with two columns: state and pizza_joint . I then added a website column that would need to be manually filled in. Here’s the sheet. If you want to help fill in the data, request editor access!

For demonstration purposes, I’ve written a node.js script that downloads the sheet as a CSV, then uses that CSV to run qri save on the Qri dataset chriswhong/best-pizza-place-in-every-state. Finally, the Qri dataset is published to qri.cloud where anyone can see the version history and pull/download the latest version of the data!

The dataset now has a home on the web at https://qri.cloud/chriswhong/best-pizza-place-in-every-state. Qri.cloud is not just hosting my data, it’s making it discoverable in search engines and providing access to the readme, metadata, and schema information that lives along with my table.

This dataset, and all of the different incremental versions are now available for pulling and downloading on qri.cloud. You can even follow the dataset to get an email notification each time I update it. If the dataset becomes more popular, I’ll have an incentive to make sure it’s more complete, and maybe add columns that will be helpful to other users.

Towards a Google Sheets Connector

This example is a proof of concept for moving data between Google Sheets and Qri, and lays some of the groundwork for a proper connector between the two platforms. What could this look like? One idea is to use Qri Desktop as the jumping off point, allowing the user to quickly create a new dataset linked to a Google Sheet, and commit new versions with a click.

Another idea is to have the integration UI live in the Google Sheets UI (or as a browser plugin) which watches the sheet for changes and allows for a quick commit of a new Qri version.

What would you want in an Google Sheets/ Qri integration? Give us a yell on twitter or drop into our Discord and let us know what features you’d find helpful. Thanks for reading!

Archiving your Google Sheets Data with Qri was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

Qri (pronounced “query”) is a version control system for datasets. Our software is designed to make datasets more robust, documented, portable, and trustworthy, in ways that file-based data and traditional databases cannot. When you create a Qri Dataset, each version is signed, hashed, timestamped, and immutable… a snapshot of the data that carries crucial metadata with it and is easy to move around.

So Airtable is great for editing data, and Qri is great for archiving and sharing data… I think maybe they should talk!

The Setup

I actually use Airtable in the real world as a way to easily log water chemistry data for my reef aquarium. A few times a month I am measuring pH, alkalinity, salinity, nitrates, phosphates, etc, to make sure everything is within acceptable ranges. Here’s what the Airtable looks like:

For demonstration purposes, I’ve written a node.js script that pulls the data from the Airtable API, transforms it to a CSV, then uses that CSV to run qri save on the Qri dataset chriswhong/reef-tank-water-testing. Finally, the Qri dataset is published to qri.cloud where anyone can see the version history and pull/download the latest version of the data!

The dataset now has a home on the web at https://qri.cloud/chriswhong/reef-tank-water-testing. Qri.cloud is not just hosting my data, it’s making it discoverable in search engines and providing access to the readme, metadata, and schema information that lives along with my table.

Each version is immutable, so if I try to go back and fudge the numbers to impress my fish tank friends, you’ll know how the table really looked in October 2020. :). You can even follow the dataset to get an email notification each time I update it.

Towards an Airtable Connector

This example is a proof of concept for moving data between Airtable and Qri, and lays some of the groundwork for a proper connector between the two platforms. What could this look like? One idea is to use Qri Desktop as the jumping off point, allowing the user to quickly create a new dataset linked to an Airtable, and commit new versions with a click.

Another idea is to have the integration UI live in the Airtable UI (or as a browser plugin) which watches the Airtable for changes and allows for a quick commit of a new Qri version.

What would you want in an Airtable / Qri integration? Give us a yell on twitter or drop into our Discord and let us know what features you’d find helpful. Thanks for reading!

Archiving your Airtable Data with Qri was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.

On the surface it seems simple, but for most businesses it’s vital to understand what’s happening month to month. How are our ads performing? How many customers did we gain? How loaded is our pipeline? And on and on.

Spreadsheets at the workplace

Spreadsheets are an invaluable tool at work and help us work with data on a daily basis and provide a human level interaction with it. Nearly every business depends on it, but managing templates and keeping track of files and revisions quickly gets cumbersome. Once those problems are dealt with, we still need to digest that data and get some insights from it.

To deal with it I’ve got a couple suggestions:

• Use versioning software to track changes instead of keeping track of filenames
• Use some basic analysis tools to understand how the data is changing over time at a glance

This becomes more important as the business grows and your operations become more complex. Eventually you may have to hire someone to do the reporting. And once you grow into it, tooling becomes your best friend to keep scaling with minimal overhead.

Stop doing `Final_Rev_3_Final_Finalest.xlsx`

Most popular spreadsheet tools now offer some sort of version control to highlight changes in a table so it’s easy to understand how data changed from version to version. Google Sheets allows you to browse changes by automatically generated snapshots or by explicitly named versions.

Now that we use versioning of our data for more clarity and less error prone workflows, we need to gain some insights from the data. While in-depth reports, read-throughs and detailed analyses are mandatory to make big decisions, we often want to quickly understand what is happening without spending hours or days sifting through the spreadsheet.

Understanding change

Spreadsheets have magnificent tooling to generate some quick reports and plot data. Coupled with version control this means that we can easily glance at a summary of a table just by selecting the appropriate version. Yet, it still doesn’t highlight changes between the versions, and we have to keep clicking back and forth between the versions looking at the same graph and data points any number of times. This often results in us missing the bigger picture as we are so focused on the fine grained details. All we wanted to know is what’s new this month.

There’s a whole subset of analytics that deals with understanding data at a glance, namely descriptive statistics are a great starting point to quickly summarize some data columns.

Without getting into too much detail and math, the purpose of this is to show you some basic numbers on some data so you can understand what is going on and decide whether to dive deeper into it or move on. With the world revolving around numbers, a quick example would be viewing the sum, average, count and distribution of values.

I took some visitor stats from some of my pages to make a quick demonstration.

Here’s a quick summary of active users for August:

• 31 rows (for 31 days)
• Sum: 743
• Average: 23.97
• Std. deviation: 8.04
• Median: 23
• Top 10% days (90th percentile): 34
• Bottom 10% days (10th percentile): 13

Quick graph showing active users by day and distribution of days by count.

Now the same for September:

• 30 rows (for 30 days)
• Sum: 879
• Average: 29.3
• Std. deviation: 13.26
• Median: 31.5
• Top 10% days (90th percentile): 43.3
• Bottom 10% days (10th percentile): 10.9

As you see, both give some very quick and meaningful info for the respective months. But how did we do with the months relative to each other?

A cursory view of the raw numbers shows some kind of improvement, but that’s neither quantitative, nor rigorous. We need more.

Setting the descriptive stats side by side offers plenty of insight without looking at the above two reports:

From the first graph we can see a ~20% increase in traffic from August to September. On a daily basis we performed ~5 active users better on average, but we also had a lot more oscillation. In general, all metrics lean towards the right and show slight to moderate growth, though our worst days in September are worse than the worst days in August.

There’s no need to look at either of the tables individually or parse through their charts. Furthermore not even the comparison bar chart is required to understand the change, the simple delta chart tells plenty.

The purpose of the above analysis is not to say you can learn more by taking a shallower dive into the data, only that you can learn a lot at a glance and thus make more decisions in less time. This becomes increasingly more useful as there are more and more types of reports you are generating and consuming.

Phew, okay. So I got my answer, but that required some work.

A Need for Better Tooling — A Data Differ

Software developers have one tool at their disposal that I don’t (yet) have as a data analyst — a differ, or an interface that shows me the differences (what changed) from one version of a file (in my world, a dataset) to the next.

The good news is, I work at Qri to try and improve the world of data with better tooling. We already have version control as a core principle in evolving a dataset over time.

Recently we put descriptive statistics and diffing onto our near term roadmap to further aid day to day workflows with datasets. We’re kicking around a few ideas on what that differ (or change report), would look like, how it’d be used, what it should tell you, etc., and we’re looking for feedback. Here’s what we’ve dreamt up so far:

A Data Differ Wires:

As you might recognize, this format is inspired by software code diff reports. However, there are MANY reasons data is different from code, and thus deserves its own considerations:

• Data files are MUCH bigger than code.
• All code can be shown as text and be reasonably interpreted. Data is often best displayed with a visualization (bar charts, graphs, etc), or in columns and rows (a spreadsheet!).
• Data can be organized & sorted without truly ‘changing’
• …and more.

These variations add complexity to the discussion on what comparison tools (differ) should do and look like.

Are there more considerations for a data differ? We’re inviting all data analysts, data engineers, data scientists & statisticians to weigh in.

How would you use a data differ? What would you want it to tell you about how a one file differs from another? How granular or high-level do you get when inspecting changes?

This post will be one in a series on this emerging feature, so please follow up!

Learn more by doing less with spreadsheets was originally published in qri.io on Medium, where people are continuing the conversation by highlighting and responding to this story.