My Previous Life - Analysis of Trouble Tickets

This is part of my #1YearOfViz series! Check out the archive here: http://bourbonandbrains.blogspot.com/p/one-year-of-dataviz.html

Before we get started this week I wanted to say that I haven't spent a whole lot of time on anything but I did publish this little quickie viz from last week (hey they can't all be winners right?!). I spent a good part of last week working on upgrading my dad's computer and hanging out with my niece... so while the viz got published last week it's taken me a while to sit down and write a blog post for it! I'm sure you can understand!

Me reading to the Niece (Regan)

It's really because after almost 5 years I finally took down my "ticketing" form that I used for people in my prior job as a sys admin at the University of Kentucky to submit trouble tickets (if possible). Of course there were TONS of people who couldn't submit a ticket online, due to connection issues or whatever, so they ended up calling in. These represent I'd guess about 3/4 of the total amount of tickets myself and my partner in crime would get through the course of our time there.

I worked at the University of Kentucky (UK) for 19 years in total... I literally worked there my entire professional career. I worked there longer than I didn't work there (I started when I was 18). It really became a second home to me. I was let go back in October as part of a "Reduction in Force" during a departmental merger. A lot of people would think I'd be SUPER mad about it but honestly this has given me the kick-in-the-butt I've always needed to pursue my career as a data analyst; which is something I found myself heavily involved with in my last 4-5 years there.

Click image for full comic!

A lot of people think I should be mad about the whole situation... but I'm not mad. Honestly I'm just sad (see comic to the left!) about the whole thing. I just felt very invested in the future of the University only to find out they weren't terribly invested in the future of me. Isn't that crappy?! Shouldn't I be mad about that!? The problem is WHO to be mad at... the people who actually let me go are nice people who really weren't given any other choice. They had to make some hard decisions and I fully expect they'll retire after the full transition period is done.

Over my time at the University I spearheaded several programs that I felt really made a difference both in our impact towards students and on things like the environment. One of the largest (and EASILY most painful) projects I worked on was the transition from personal printers, which at the time consisted of about 35% of my work-time, to large-scale networked printers via our UK contract with Ricoh for Managed Print Services.

Towards the end the large majority of requests came for adding/removing users, file problems... just hum-drum admin stuff. Which is one of the reasons I started poking around with Tableau in the first place actually! So yay boring work! I limited the time in this viz to only the time I was actually at work (even though the form itself stayed up until very recently).




Let's break down the numbers real quick:

• 1,882 Tickets submitted from 11/16/2011 - 9/29/2016
• 912 Distinct Days Tickets Submitted
• Approximately 2 tickets per day
• Avg Days to Complete = 7
• This number was driven up by a few VERY longstanding tickets that we couldn't do anything to speed up
• Median Days to Complete = 1
• I'm super-proud of this obviously!
• Avg Priority Rank (self-ranked by the way!) = 6.912 (out of 10)
• Median Priority Rank = 7
• Served 336 distinct usernames
• Worked on 666 distinct computer names
• Covering 22 departments across 19 different buildings

First here's a quick little look at tickets by priority:

Now let's get into it and look at Users and Departments:

Finally let's examine trends by department:

As always hit me up in the comments below or on Twitter @wjking0 if you have any questions!

EPA Critical Violations (Last 3 Years)

This is part of my #1YearOfViz series! Check out the archive here: http://bourbonandbrains.blogspot.com/p/one-year-of-dataviz.html

I know I promised everyone a Casey Neistat viz this week but I'm hoping to give that one a little more love and do some fancy-dancy things with it.... so it's getting postponed a while!

Instead I'm bringing you another things near and dear to my heart... EPA violations! I was thinking about pollution and I decided to swing by the EPA website to see if they had information worth scrapping or looking at.

That's when I saw it... not only did they have EVERY SINGLE BIT OF DATA IMAGINABLE for every facility that reports to the EPA... they had them all ALREADY GEOCODED.

ARE YOU EVEN KIDDING ME RIGHT NOW EPA!? I LOVE YOU! (But not as much as cats or pizza... )

So I tried to export all 500,000+ records but they have some limits on their queries. Another amazing thing is that if you try a query that returns over their query limit they have SUGGESTED FILTERS that pop up on the side for ways to filter the data down to actually pull results! I decided to only pull "Significant Violations" for this particular viz. Seriously, whoever designed their search system... I owe you SO many donuts! I'm still looking for the pure raw data to pull down for a more in-depth dive (I'm guessing it's located on data.gov but I haven't gone searching for it really hard yet) into all this but I decided in the meantime to pull down all the locations nationwide which have had a "critical problem".

I really didn't want any of my family to wake up to
 be the Toxic Avenger!

The first thing I noticed was that there were not many "sanctions" really being delved out. I came across this as I was looking at this list of court cases brought by the EPA against companies/individuals. For those of you in WV you obviously heard about the American Industries scandal as chemicals were leaked into water intakes for drinking water around the state. My own family was involved in this so it's pretty fresh in my mind. Did you know that the President of Freedom Industries only got a few months in prison for that? Does that seem like a lot to you? Another person I read about dumped sewage into a much smaller river and ended up getting 3 years of prison time. I was hoping to scrape all the prison terms and such but it's not formatted equally enough for me to get it at this time. Again, I hope that I'll be able to get some of this down the road.

Here's the data on states and sanctioning vs inspections, click on either the state or the number of quarters of non-compliance (or both) to reshape the data to show average inspections, EPA actions, and number of records:

Me when I find out how many places DON'T
get punished for consistent EPA violations. =/

Now keep in mind these are ONLY facilities which have a "Significant Violation"... still the average number of EPA actions even for places with 11 quarters of non-compliance (we can assume that 12 quarters could possibly be an error... or the frightening truth). Eleven quarters means that at SOME POINT they were compliant in the last 3 years... of places with 9-11 quarters of non-compliance (2-2.75 years) this comprises 22.21% of all significant violation records. Of those with 9-11 quarters of non-compliance the average "Formal Action Count" is 1.025 out of an average of 3.057 inspections.

This last dashboard allows you to zoom in on the area you (or your loved-ones) might live to look around at facilities near them that have had critical EPA violations and read more in-depth about the facilities themselves. You can search by facility name, state, and city. Clicking on the dot will load up the EPA page in a new window and you can read up for yourself! Edit: I tried to get the viz to load the page INSIDE the dashboard but both http and https versions weren't working once I actually saved to Tableau Public so I figured this was the best compromise since I still wanted the individual facilities to be viewable.

I grew up in a place known as the Chemical Valley (highlighted in this graphic I exported from Tableau below) so I care about the existence of the EPA, they keep the people safe from corporate interests in a very real way. Prior to crackdowns in EPA regulation there were places close to where I grew up that had population cancer rates OVER 85%... thanks to the EPA those rates are significantly lower today. Just something to think about when people talk about doing away with the EPA in favor of "self regulation" (ie no regulation). =/ As always hit me up on twitter @wjking0 and I hope to create a little more in-depth dive with this data a little later on.

Boozey Kentucky Locations Viz

This is part of my #1YearOfViz series! Check out the archive here: http://bourbonandbrains.blogspot.com/p/one-year-of-dataviz.html

First off I wanted to say that I had a BLAST as one of the first ever presenters at the Lexington Tableau Users Group! I did a presentation about Public Data for Public Good. I'll be doing a subsequent blog post once I get the video a little better produced and I'll post that here on the blog as well! In the meantime here's a couple pictures from it and a very nice tweet after I got done!

.@wjking0 Killed it tonight. Great presentation that makes me want to dig into public data and get my geek on! pic.twitter.com/SUPcOlVcuA

— Alex Dixon (@ealexdixon) January 11, 2017

I'm hoping to do this again soon with some other organizations (particularly some newspaper groups) and really spread the love of public data and the #1YearOfViz around!

Let's get into this week's viz. I scraped the Kentucky Alcohol Beverage Control (ABC) database of liquor licenses. I was really hoping there would be some interesting data to crunch out of it and was kind of let down. I think one of the most interesting realizations that came out of this data was that Kroger was the largest liquor license holder in the state at 300 licenses!

As with the typical vizzes I publish like this EVERYTHING is clickable and sortable. The only section that doesn't reformat the data is the color coding of the liquor license types. If you'd like to sort by those please click up on the numerical values above the color pallet to do so. Hop on in and find out who owns the most liquor licenses in your zip code!

As always please hit me up on twitter @wjking0 if you have any questions or comments!

Also be sure to check out next week as I'm working on something special based on my fave YouTuber Casey Neistat!

Veterans Affairs Database of Military Graves

This is part of my #1YearOfViz series! Check out the archive here: http://bourbonandbrains.blogspot.com/p/one-year-of-dataviz.html

I hope this week's viz doesn't come across as too morbid, You'll also find none of my typical gifs in this post as I wanted to keep it as respectful as possible. I was looking through data.gov the other day for a Kentucky-centric piece of data to viz when I found the Kentucky record from the VA of military burial sites and it seemed to be out of a larger dataset. Sure enough, I found the entire dataset which includes full names, birth dates, death dates, wars fought in, branch and occasionally rank of the individuals!

This is really neat for me as I've had several family members serve including both grandfathers who fought in World War II. As a matter of fact I recently got to go to LA to hang out with one of my best friends and we got to tour my Grandfather Davis' old battleship the USS Iowa! Here's a pic:

Me in front of my Grandpa's old ship the USS Iowa - Photo courtesy of Casey Miller

I having birthdays and death dates I was hoping I could do some more in-depth calculations on ages at time of death but the problem is that you can't specifically know based on the data that an individual died IN that particular conflict. You can make some pretty strong inferences to see how wars in particular pull down the median ages of people who pass away at different points. Let's go ahead and look at that dashboard now. You can click on a branch of military (the % of ALL records are on the right) and the ages/dates will reformat on the left (Median ages of people who passed away certain years). You can also just enter in a particular branch or rank on the left hand side if you'd like to filter the whole dashboard that way as well.

As you can see periods of war, particularly the first and second World Wars caused great dips in the median ages of veterans that passed away on those years. These pronunciations become much more distinct if you limit the ages of those who pass away to 35 and younger. The data as it's shown above represents ages 16-62 (the mandatory retirement age for military personnel barring special circumstances). In this next dash however you can see some gaps in the VA's data. Particularly between the years of 2001-2005... there's only a very small spattering of people represented in those years compared to the rest. Again you can reformat either side of the data by clicking or selecting parts from the opposite side. I'd suggest using a box select on the left side and selecting an individual war on the right. You can expand the "War" column on the right to allow for people who served in multiple wars to be selected. Generally speaking they are in chronological order but occasionally not. Some people, for instance, had WWI listed AFTER WWII... but let me assure you I did a TON of data cleanup on this... and I had to stop somewhere so ordering all the dimensions was my stopping point!

Finally this last one is a map of all the known military burial sites listed by the VA. I imagined there would be more than 183 but several have VERY large sections dedicated to soldiers. If you know that you have a family member or friend that is buried in one of these locations you can do searches. If you limit it down to a single person per location it will actually give you the full details of the location of the grave in the tooltip if you hover over the location with your mouse or finger. I hope that can be useful for some of you out there to find your loved ones.

Lastly let me say thank you to all those who have served... who lived and died for the freedom for people like me to be able to find public data and publish it.

As always hit me up in the comments below or on twitter @wjking0 if you have any questions or concerns.