My Second Schema Revising

“Hmm …” he says, realizing he’ll want data viz for multiple elections and many candidates run in multiple elections … and sometimes they change parties …

  • Where should the year go?
  • Where the party at?

Oh, man … the number of Electoral College votes a state gets can change (based on population) as well …

And, aw, crap! Two states (Nebraska and Maine) don’t follow the stupid “winner take all” rule. Normally, I’d like that but it’s a pain in my butt now.

schemaRevised
Later changed “popVotes” table name to “results.” Considered having different results tables for different years but I think having affiliations and results structured the same will help me keep my code straight in my head.

Note how New York and Michigan lose votes with each census while Florida and Texas gain them (check out other examples in the screenshot below). The number of votes a state gets changes every ten years with the census so my electoralCollege table has columns for each first election of the decade so the following election years are grouped together.

  • 1992, 1996, 2000
  • 2004, 2008
  • 2012, 2016, 2020

Here’s what I made in Excel that I’ll save as a CSV.

ecExcel

It’s irritating how many different websites I have to visit to get these numbers, election results, etc.

News media get these results in real time so somebody has this information handy somewhere. Ugh.

It’s also irritating that, in an effort to find a faster way to convert the (sometimes PDF to) Excel to something usable in CSV I spend more time than if I just tediously copied and pasted. I do the same thing with my code. I think there must be a more elegant, faster way to do stuff when, nope, it’s only code, and it’s more simple and easy than I’m making it. Having said that, I’m unsure if this is the best way for this and other tables in this database to be organized, but here’s how I’m using the data for party affiliations.

affiliationsExcel

In the original PDF from the Federal Election Commission (FEC), it looked like this:

Evan McMullin (CCS, IDP, IND, BFA, NP, UN)

I haven’t thought too much yet about how I’ll query for party affiliation and morph that into my broad-stroke “socialist” and “conservative” big tents so I tried to make it as simple and malleable as possible. I’m very anxious to get started — this concept has been wanted to be born for a long, long time — so I’m allowing myself to rush a bit. I don’t mind having to fix it later if I must — it’ll be a fun learning experience (I hope).

It’s late in the day and I’m not sure if this is the best schema because I’m rushing a bit so I’ll need to review this later but this is what I’m going with to start.

electionDB2
Yep. Some of those commas shouldn’t be there. Noticed when I tried pasting into my Terminal.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s