If you haven’t already seen the news, we’ve announced the first two themes of this year’s Iron Viz feeder contests. The first contest, which starts next Monday is going to have a Sports theme. This was problematic for me because I don’t know ANYTHING about sports. Aside from one really poor season of track and field in 7th grade, I’ve never really played sports. I was always more of a band/drama/dance kind of person.
But I was on a team. I was on a team that practiced harder and longer than most sports teams at my school did. I was on a team that was driven and consumed by a desire to be champions. I was on a team that was not only about physical coordination, but also teamwork, synchronicity, musicality, and artistry. I did marching band.
Marching band taught me about teamwork and perseverance. It helped an awkward, skinny teenage nerd like myself feel coordinated and graceful. And the year that I was drum major (that’s the person who conducts the band), my arms rivaled Michelle Obama’s. Marching band was also how I first learned the rules of football and basketball; when I was drum major, I had to know when to queue up certain songs, so I had to learn to follow the games.
Competitive high school band is one thing, but there is actually a “professional” side to this sport, Drum Corps International. Every summer, groups of incredibly talented high school and college students battle it out in competitions all over the United States as a part of DCI. If you are still skeptical about the legitimacy of marching band as a sport, consider that they actually played DCI championships on ESPN. So, yeah, it’s totally a sport. And it’s awesome. And I made a dashboard of it that includes videos so that you can fully see how awesome it is.
You can click on the logo of a corps to view their score history, some facts about them and see an example of one of their performances. On the bottom half of the viz, you can click a corps on the bottom left bar chart to filter the bottom right to just their winning shows. And you can click on the bars in the Winning Shows chart to view those in the youtube player. I got all the data from Corpsreps.com, which I scraped using, you guessed it, import.io. Here it is:
As you can see, the Blue Devils pretty much dominate everything, and for good reason. They are incredible. Former Blue Devils coached and wrote the show for our high school marching band one year and we won our division in the state championships. This is going to look pretty mediocre if you’ve watched some of the amazing performances in my dashboard above, but trust me, it was super cool. I was marching tenor saxophone in this performance and it was the most fun I had in all of high school.
I might post some tutorial-like stuff about this dashboard later. If there’s anything you are curious about with how I got the data or made the viz, feel free to leave me a comment or drop me a line on Twitter. I will definitely be following this up with a viz about another nerdy, sport-ish thing I participated in, Winterguard, aka “The Sport of the Arts.” Because dancing around with swords is DEFINITELY a sport.
Here’s a dataset I’ve always wanted to play with but never had time to fully explore: Reddit Top 2.5 Million posts.
An awesome Redditor compiled this list together. I’ve always wanted to use it to figure out when the best time/day to post for maximum karma is. Give it a go and reap the orange benefits.
One of the things I was most excited for with the launch of Tableau 8.1 was the new DATEPARSE function. I just knew that someday this magical function would make my life easier. Today was that day!
I’m getting ready to build a visualization based on the playlist of my favorite local radio station, KEXP. They have recorded every song they have played since 2001. It’s pretty amazing. I scraped the playlist for all the songs they played in 2013 (over 300k!) using my favorite web scraping tool, import.io.
From the playlist table scraper I built, time was included in the table, but not date. However, the date for each row can be derived from the source page URL, which always contains the date and hour. Here’s what the data looks like:
I thought about how I would try to extract the date from that string in Excel, some combination of MID, DATE, and other functions) but then I realized that this is the PERFECT use case for using DATEPARSE. To start, I needed to extract just the date part of the URL string. I did that using the MID string function. I haven’t used string or date calculations very much, so I did them as two separate calculations at first, just so I could check that it was working as I expected to. The MID calculation takes the string that you specify as the first argument, in this case my source URL, and takes out a chunk of text starting on the character number that you specify in the second argument. There is an optional third argument, in which you specify how many characters you want to select, but in this case the length of characters varies, so by leaving this third argument out, I’m basically saying “Grab everything to the right of the 30th character.”
I put this calculation on the rows shelf just to do a sanity check and make sure the strings came out as I expected them to.
Next is the fun part! DATEPARSE is super easy to use and incredibly flexible. The first argument, you specify what the format is. In this case, it’s ”yyyy/MM/dd/hha”, where y is year, M is month (not to be confused with m which is minute!), d is day, h is hour, and a specifies “period” or AM vs PM. You can find a reference for unicode date symbols here. The great thing about DATEPARSE is that even though my months are only one digit and my days can be one or two digits, it still totally understood what I was doing. Here’s what the calculation looked like:
And here is another sanity check with the date string pulled from the URL on the left and the actual parsed date on the right!
Yay! It works! Since I know that it works now, I made it more efficient by combining these two calculations into one.
And now I can do all kinds of fun stuff with the dates! In conclusion, DATEPARSE freaking rules and doing all your datetime formulas in Excel is totally outdated.
Hey all you single ladies (and guys)!
On this very day last year, I landed my first Viz of the Day: Dateless in Seattle. I held Tableau Public training this morning, and as a fun treat for Valentine’s Day, I thought I would rebuild this viz. The original was build in Tableau 7, and it is obvious. I did some more Tableau 8 style formatting. I also had a stroke of genius in the middle of the training and added a new feature: if you click on a zip code, you’ll get a link to find a good date spot. This searches Yelp for “romantic restaurants” in that zip code. Although, if you don’t have a reservation by now, you probably are in pretty bad shape…
Here’s the viz we built in training this morning. It was a quick build, so pardon my lack of polish. Happy Valentine’s Day!
Some super awesome person cataloged how many times every game was played and win/loss ratios for The Price is Right. The data goes up to season 28, which was 1999, which makes sense considering the look of the page and the TERRIBLE formatting in the tables. I’ve tried bringing the data into excel, but excel likes to think the win/loss ratios are dates and it kinda messes up everything. This is my reaction when I think about what I’d have to do to get this data in a usable:
If you do something with it, please try to stay true to the bright colors and retro font styling of The Price is Right. That’s really why I wanted to use the data, because the viz could be so colorful! Seriously, if you make something that looks awesome I’ll pretty much be like this guy:
How many people die in your favorite movies? Get the data here.
Sexual satisfaction/Frequency vs GDP by Country.
Please make things prettier than this:
as Reported to the United States Department of Education Pursuant to Equity in Athletics Disclosure Act.
The data includes:
- Revenue in millions
- Expense in millions
There are over 10k rows here. Definitely a lot that you can work with! Now go make something awesome. Ready, set….hike!
I often come across awesome datasets that I don’t have time to viz. I wanted a place to collect all the interesting datasets I found so I’ve created a new category for my blog: The Data Feed. Any time I see an fascinating dataset, table on a website, or anything like that, I’ll share it with you guys! Just be sure to let me know if you make something rad with it!
To get it kicked off, Divvy, a bike sharing service in Chicago, released a dataset of their data including:
- Trip start day and time
- Trip end day and time
- Trip start station
- Trip end station
- Rider type (Member or 24-Hour Pass User)
- If a Member trip, it will also include Member’s gender and year of birth
The data already looks really clean and easy to use. They even have latlong for all the different stations! They are running a data visualization contest with this data, so if you are in the Chicago area, you can win some cool stuff. Click here to get the data and view the contest rules.
I was working on a scatter plot and trying to determine which shape made it easiest to read. I switched to little x’s and thought it was so cute. It looked just like cross-stitch! It made me think, it wouldn’t be that hard to replicate cross-stitch in Tableau, right?
So here it is:
It was actually pretty easy to do. It only took me like 20 minutes or so, and most of that was just drawing it.
I set the width/height of the cells so that they were basically squares. Then, I started coloring in cells. Pretty self-explanatory.
Step 2: Make sure everything has a coordinate.
I numbered all the rows and columns so that there was basically a coordinate system for each cell.
Step 3: Replace cell colors with labels.
Did you know you could do a find and replace based on formatting? It’s true! I searched for all the colors I used and replaced them with a code: blk, blu, or red.
Step 4: Reshape.
The way the data is shaped right now couldn’t go directly into Tableau. I used the Tableau Reshaper tool to turn each colored cell into a color and a pair of coordinates.
Step 5: Bring it into Tableau
Put your x values on columns, y values on rows, color on color, and change the shape to x’s. BAM! You now have cross-stitch. Because of the way I numbered the y-values, it ended up being upside down at first. I reversed the y-axis and that was it! Done!
Would this ever be useful? Probably not really. I’ve read online that a lot of people design cross-stitch patterns in Excel, though, and this may be a good way to quickly change colors and decide on what you want the final product to look like. Mostly, it was just fun for me because I generally think of myself as a crafty person but I’m terrible at doing cross-stitch in real life.