Category Archives: Uncategorized

BLS Data in Excel Format

If you’ve ever tried to get the data out of the Bureau of Labor Statistics (BLS) you know that can be kind of a pain in the butt. You usually have to go through the wizards to get the data and then it only gives you one kind of data per table and you have to do a lot of tedious work to get that data into a format that is actually useful for additional work.

I finally got tired of doing this, so this weekend I put together a little program that takes BLS data and turns it into csv format, which can be opened in Excel.

Finally, I would really appreciate two things. First, please mention me (Matthias Shapiro) if you use the data for professional purposes, link back here to let people know where to get it. Second, if this is actually helpful data, please consider tossing $5 or so into my digital hat. I make maybe $50 per year from this blog, so anything to let me know this is worth doing is helpful to me.





Or you could buy a copy of my book Beautiful Visualization (disclaimer: I only wrote one chapter, but I call it “my book” anyway because that makes me feel important).

Download employment status (A Tables) (BLS link)

  • 1948 – Nov 2011
  • civilian population
  • labor force
  • participation rate
  • employed
  • employment-to-population ratio
  • unemployed
  • unemployment rate
  • not in labor force
  • persons who currently want a job
  • 1939-Nov 2011
  • payroll job counts for 150 industries/sub-industries
  • csv file (headers labeled “[state] – [field]” Example: “Alabama – Unemployed”
  • xlsx file (better headers, grouping states)
  • 1976 – Oct 2011
  • labor force by state
  • employed by state
  • unemployed by state
  • unemployment rate by state
Download state payrolls by industry (BLS Link)

Alternate Intro to the “Three Charts” Post

This was my original intro to the “Three Charts” post that I’ve just put up. Normally my rational brain gets the better of me and I delete this kind of crap before I post it. It is neither charitable nor professional. You’ve been warned.

Once again, the truth rolls its eyes and starts tying it’s laces while the lie is half-way around the world. There is a piece called “The Three Charts to E-Mail Your Right Wing Brother-In-Law” that is making the rounds and impressing many people who didn’t score high on the “critical thinking” portion of the test.

Whoever made this chart is an asshole. Straight up asshole. “Your Right Wing Brother-In-Law”? Really? I mean… I get it because no one who is related to this person by blood could ever be so stupid. Nice little undercurrent enforcing the concept of hereditary intelligence. Yeah… I heard a lot of that kind of shit when I visited the deep South and ran into the local racist assholes. Just enough plausible deniability to claim innocence, but deep down you know that, due to your pedigree, you’re just better than other people.

And someone who disagrees with you could never be your friend. I mean… you? Voluntarily associate with someone who doesn’t agree with you? Actually have friends who might challenge you on something? Heaven forbid. Disagreeing with you is something only the lowly and inferior do and you would never be around them of your own volition, so it has to be someone who is “in the family, but not part of the family”.

No, the right wing brother in law just mouths off without knowing the facts, so it is up to the great brainiac hope of the family to inject reality into the conversation. Is that a banana in your pocket or are you just fantasizing about winning an argument in real life without stuttering and turning red in the face?

And as salty icing on the moldy self-righteous cake why do YOU need to explain this to the “brother-in-law” specifically? It it because your wife or sister is too stupid or too meek to explain it to him herself? The weak-minded damsel will be saved by you, the powerful, witty, urbane, strong, righteous intellectual knight. And everyone will love you for being the big savior of the family conversation.

I find that creepy, dismissive and sexist. Everyone who giggles at it and re-shares it should be ashamed of themselves but won’t be because they aren’t introspective enough to stop stroking their own intellectual egos for the time it takes to feel shame.

See? This is why I usually delete that kind of stuff.

I’m Still Catching Up

I want to send out this apology. Policy analysis is not my job… I just do this for fun. That last post was meant to explain things I couldn’t explain over Twitter (to an audience of about 15 people who already discussing it).

Instead, I got almost as many comments and hits as I’ve gotten on everything else I’ve done here over the past 2 years. I’m trying to digest it all, but it’s crunch time at my “normal job”.

Thank you to everyone who has commented and engaged the topic. I am going to try to do a follow-up in the coming week that addresses more of the data.

Tim Carney’s Awesomeness Theory of Capitalism

I saw this when it came out and, as an aspiring entrepreneur, it really resonated with me on how entrepreneurs see the world.

For the people who really drive technology and push the world forward, it isn’t about the money (or, at least, it isn’t only about the money). The drive to “be awesome” is a powerful one… one that inspires a great deal of innovation.

I personally think that one of the great things about the internet isn’t simply access to data or information. It allows groups of people to show off their stuff to other people who care, to people who will think they are awesome. This force drives a huge amount of cool things like space tourism and genetics businesses like 23andMe . It’s also the driving force behind the DIY revolution that Johnny Chung Lee wrote about. For me, getting accolades from people I respect is bigger than a big payday. (In fact, if I somehow got a big payday in the millions of dollars, I’d probably change the trajectory of my career to a higher quotient of awesome-to-profit.)

Anyway, I keep wanting to refer to this clip, so I figured I should put it on my blog.

Vote For “Matthias” Y-Care Video

After months of not putting anything up, I finally made a new video. Created for the Y-Care National Debt video contest, it’s a fairly radical departure from my previous fare, but I think it’s entertaining. Please vote for me (Matthias Shapiro) at the Know-Y.org Y-Care video vote and help me win the $5,000 prize. (I have a very large scale visualization of the national debt that I’m working on, but I really need some capital to pull it off. This would be an awesome way to help me with that.)

Let me field a couple of questions about the production:

  • Do I really owe $45,000 of the National Debt? – No, you do not. I rounded down. Based on the latest estimate, there are 312 million people in the country and the US National Debt stands at $14.1 trillion, so you owe a shade under $45,200 at the time of writing. But it’s probably more than that by the time you read this.
  • Is that really $45,000 dollars in that bag? – No, it is not. I am not insane enough to carry around $45K in a bag and show it to people. Instead, that is $2,250 worth of one dollar bills with $20 bills on the top and bottom for a total of about $2700. For me, that is still a vast amount of capital. I ended up just taking it out of my savings, shooting the video and then quickly putting it back in.
  • Did you catch the guy who tried to steal the money? – Yes. We had sentries surrounding the area.
  • Wait a second… how can a baby owe $45,000 in debt? – The $45,000 number is a per-person estimate. If we were to do a “pre-household” estimate in which the mother and baby are counted together, it would be almost $123,000 per household. I went with the “per-person” estimate because I don’t have enough free cash to fake $123,000 in cash. If I used $20 bills to fake it, that would require $6,100 in ones and 122 $20 bills on the top and bottom, for a total of $8,540.

Think about that last bit for a second.

Most people don’t even have enough cash to fake the amount of money the federal government owes on their behalf.

The scope of the problem is truly staggering. I hope that my video has communicated it well. If you think that is the case, please vote for me (Matthias Shapiro) at the Know-Y.org Y-Care video contest.

2010 Election Live Blogging

12:30 – And… Colorado Senate gets weird. Ken Buck is currently beating Michael Bennet by a hair, but the popular theory at this point is that the Bennet counties (Denver, Arapahoe, Boulder, etc) have yet to report in 50% of their votes or more. As I write, Arapahoe county, which held 128,000+ votes for Obama, is at 10% reporting.

Based on some rough estimates, I’m a little skeptical. Mid-terms almost always result in lower turnout and this one is no exception. I’m looking at between 60 and 85% of the 2008 turnout. It will surprise no one that Republican turnout is slightly higher than Democratic turnout, compared to 2008.

If Arapahoe was only 10% counted, we’d be looking at a county that turned out 400-500% higher in this election than in 2008.

Using a slightly less silly example, if Denver is only 50% counted, we’re looking at a turnout 2% higher than 2008, rather than the 15-40% drops we’re seeing in all other counties.

Clearly this is not going to be settled tonight and I have to wake up in the morning. As such, good night and thank you for reading.

9:42 – I’ve moved mostly to Twitter at this point… follow me here:

.@fivethirtyeight says “G.O.P. Poised to Pick Up 54 to 75 House Seats”

RT @jaketapper: Top Dem emails me: “bloodbath.”

Sestak needs to win the uncounted votes by 20% to win PA-SEN. It’s over. Toomey wins PA.

8:50 – From Twitter: @fivethirtyeight: http://nyti.ms/bFwQdG Our model now projects a 64-65 seat gain for G.O.P.

8:46 – I’m calling South Carolina for Nikki Haley. She’s up 5% with only 19% left. That’s a big deficit to overcome.

8:45 – Brendan Loy ran a prediction contest: Here’s my predictions for Senate: Bold means I was right (so far)

Alaska Senate: Miller (R)
Arkansas Senate: Boozman (R)
California Senate: Boxer (D)
Colorado Senate: Buck (R)
Connecticut Senate: Blumenthal (D)
Delaware Senate: Coons (D)
Florida Senate: Rubio (R)
Illinois Senate: Kirk (R)
Indiana Senate: Coats (R)
Kentucky Senate: Paul (R)
Nevada Senate: Angle (R)
New Hampshire Senate: Ayotte (R)
North Dakota Senate: Hoeven (R)
Pennsylvania Senate: Toomey (R)
Washington Senate: Murray (D)
West Virginia Senate: Manchin (D)
Wisconsin Senate: Johnson (R)

I’m not looking so hot on Colorado, Pennsylvania is a toss-up at the moment, and Illinois is close. But I’m not wrong on anything yet. 🙂

8:30 – Looking at Governors, Florida looks like a Rick Scott (R) win, South Carolina is still running neck and neck, Republicans have Michigan, Pennsylvania, Wisconsin… overall Republicans are doing fairly well in the governors races.

7:50 – From Twitter:

My personal race to watch: South Carolina gov: Nicky Haley vs. Vincent Sheheen… neck and neck

Nikki Haley pulling away! 50% – 48%

7:37 – From Twitter: @andylevy: .@SpeakerPelosi Have you decided what your new twitter name will be yet?

7:25 – Looks like things are starting to pan out more or less as expected by pollsters. 50-55 seats heading the Republican’s way and NONE of the long-shots have panned out.

6:55 – The margins at the moment are looking pretty strange. Todd Young just won Indiana 9 by 10+ points when he was projected by 3. This is similar all over the place, with most congressional districts going 3-4% one way or another. So far, it’s mostly toward Republicans, but (and this is a very early prognostication), it seems like the election results are polarized.

6:30 – Let’s take a look at Florida Congressional districts. There were 4 races that were takeover possibilities, districts 2, 8, 24, 22. District 2 looks like it’s heading toward a solid Democratic win, but the others have Republicans out-performing by 3-7%.

6:20 – OK… now we’re starting to get a better picture. Rubio (Senate) and Scott (Gov) are outperforming the polls in Florida, although Rubio is doing better than Scott. Don’t know what exactly that means, but Rubio is up 5% higher than he polled.

6:10 – Just starting to punch numbers into my spreadsheets. So far, Republicans are underperforming the Senate polls by 1-2%. In Indiana, they’re matching the expectations on House seats with about 20-30% in.

5:12 – my apologies for being so late… as election results come in, I discovered I don’t get CNN any longer. Long story short, this discovery resulted in an emergency trip to the Comcast service location to get the digital converter box. I’ll be up and running as soon as I can. My apologies for the delay.