Kate's Quantified Bike Commute

Kate Hotler
on May 29, 2014

Hi there! My name’s Kate, and I started as a User Experience Designer at Tableau back in March of this year. I’ve been commuting by bike and bus since I moved to Seattle in 2009.

Beginning to work at a company whose mission is to help people see and understand their data, I figured it would be interesting to collect my own data, and figure out what to do with it later. I had used Strava to record my bike commuting intermittently in the past, but had let it go. But with a new commute and a new routine in a new community, I thought maybe if I could see it, see the miles accumulating, I might feel more motivated to bike. I started out back in March with the new job, in the mornings, and it was a really enjoyable way to get to work: I got there feeling cheerful and energetic, pumping plenty of oxygen to my brain. The other thing I realized quickly was that it was the fastest way to get to work; faster than the twobuses, faster even than driving, even with Car2Go. So very quickly, I was hooked on the morning commute IN to work in Fremont. With full fenders, boots, and occasionally Rainlegs, Seattle's gray, drizzly weather rarely deterred me or cramped my style.

The evening however, was a different matter. No matter how I sliced it, my commute directly home was a bit of flat followed by a monster hill, and many of the routes upward were besieged by evening traffic.

My favorite route/MO became to bike downtown and then to bike up to North Capitol Hill more gradually,which felt familiar to me from commutes when I had lived and worked in different parts of town. This also split the climb into two pieces. Often I'd stop in the central part of the Capitol Hill neighborhood; sit and read in the park, go to my favorite bookstore, or meet a friend for happy hour or dinner, and then ride up the last stretch of hill to home at the end of my evening (which itself feels like a luxury right now, being an extroverted single person with no children.)

Sometimes though, I'd ride in, but then the combination of a long day and a big hill would deter me from riding home. Feeling tired and a little guilty, I'd just leave my bike at work, but the next morning, I'd inevitably be kicking myself, when no bike at the ready to sail down to work on meant no sunny wind in my face, more time and money spent. I inevitably arrived to work more stressed on those days.

With the positive culture of biking and my desire to foster more positive connections at work, in April I decided to captain an all-women'sbike team here at Tableau for the Cascade Bicycle Club's Commute Challenge for the month of May, which is National Bike Month. The response was so positive from my female colleagues that we formed 3 teams, (among many more representing Tableau for the month) squished together to make one 28-woman team called the Quantifying Quaintrelles. Our focus wasn't on logging tons of miles, racing, or shaving ounces off our bikes' weight, but rather just helping more people feel comfortable trying out bike commuting and look/feel good doing it. I wanted to be able to set a great example as captain, not only in terms of logging miles, but also in diligently collecting data and using it to make these vizzes, learning enough to pass on processes and files to teammates, so they could easily reproduce something similar by the end of the month.

The meditative euphoria of biking on a gorgeous spring morning is a factor in my choosing to ride that I wanted to make sure wasn't lost in looking at so much quantitative data. So in mid-April, I decided to
start taking one Instagram photo per ride.

As I’ve just made biking part of my lifestyle and my primary way of getting around, it has increasingly become a rich, authentic source of community for me, both at work and more broadly. Tableau team happy hours, lunch rides, in-house tune-ups, support with bike storage and internal online chats and forums help us encourage each other and offer helpful commuter tidbits. Weekends and evenings, I've been inspired by the leadership of women like Madi Carlson and Merlin Rainwater, riding with Critical Lass and on rides like the Silly Hilly to check out what's happening with Seattle Neighborhood Greenways, checking out Cascade’s BikeMonth Policy Rides & Happy Hours, and attending women’s cycling wisdom events at REI and Hub & Bespoke. There have been parties for captains and riders, film screenings, and plenty of chances to make new friends. and a fashion show! This is my kind of civic duty!

Serendipity happens too. One gorgeous evening on my way home, I ran across a motley crew of 60 riders en route to Magnuson Park for an impromptu dance party on the water. Going with them was the most unbridled, serendipitous, kid-like fun I've had in ages. Turns out they ride someplace new every week!

All this has gotten me more excited to use data to help advocate for cycling and inform decision-making, and I’m not the only one! The Oregon Department of Transportation is going to start using aggregated Strava data to determine sites potential neighborhood greenway facilities. This is great data-driven decision-making and a wonderful start. However, I fear that such a use of that data might paint a skewed picture, given that greenways need to be suitable for riders of all ages and abilities, while the Strava app has a much narrower, much sportier demographic. In case such a use comes to Seattle, I feel compelled to contribute some more relaxed and less hilly gps tracks than the average Strava user.

I’m also very interested in working local organizations like Cascade and Washington Bikes, and using Tableau to visualize ridership data, depicting ridership over the Fremont Bridge collected with Seattle’s first Bike Counter, and to blend that data with other publicly available sources to aid in creative decision-making for projects like the Westlake Cycle Track.

As the month has progressed, it has been nice to be able to see my progress. I have the necessary pluck to bike straight home more often, and when I do, it’s faster than it has been before. While some of Strava’s competitive, spandex-clad masculine imagery has been a turn-off for me at times, I do still enjoy competing with myself, the program’s visualizations are fun, and having a Private Strava Club for Tableau Employees to keep sharing with each other after Bike Month ends.

As with so many other areas of life, the more you do a thing, the better you get at it. That's hard to remember about bike commuting sometimes when trying to decide whether to ride. But I find that the more I’ve biked up hills, the less trying it feels, and the more enjoyable it gets. The more I ride in the rain, the less it bothers me. The same seems to be proving true as I have used this project to learn Tableau: the more I play with my data, the more I appreciate what’s possible, and see how much opportunity we have to make it even better.

This is the first Viz I’m publishing on Tableau Public. I’d love feedback from the community about how it could be even better, what other metrics would be interesting, and how I could make the process more automated and scalable for a larger group of bike commuters. Check future comments for project updates and future iterations!

Special thanks to colleagues Robert Kosara, Jewel Lorée, Isaac Obezo, Jeff Pettiross, Amanda Pype, Kenneth Trease, and the Quantifying Quaintrelles for your support and guidance in putting together this project: I couldn’t have done it without you.  I’m also honored to be the second person to get to create a piece for the this blog using Tableau 8.2 for the Mac (in beta) using StoryPoints.


The code that was used to convert the raw gpx files into the csv that tableau public can read has been published here, if you want to try this out yourself (this gives you the raw data for the maps. The instagram stuff is up to you. :))


awesome post, kate! i commute daily in chicago, or at least when it's above 40F. i'm using strava, too, but i'm not sure how to get the data out of the app and onto my computer. how did you do that? i'd like to make my own vizzes.

Hello Kate, Thanks for sharing such an inspiring viz and narrative. You are leading the way for how to gather and analyze bike commuting data, and have fun too. I am wondering if for your next iteration of this if you might include an element of economics such as how much $$ you saved by not using a large motor vehicle to travel the same distance. As we grapple with ways, and reasons, to use one less car, I and many others would appreciate your insights.

Keep up the amazing work, and ride on!

Hi MP Lee,
So glad you liked it, and so glad you asked! To get my data out of Strava and into Tableau, I created two separate data sources, and then used them together to make my vizzes. Little plug for Strava here to say that the vizzes they give one the site are interesting and helpful: I just wanted to play more, expand, and use my own data to learn Tableau.

First, I logged into my Strava account and downloaded my raw, completely unaggregated data. One you're logged in via desktop, click on your photo in the top right corner, go to Settings, and the orange button will be on the bottom right. It will send you an email link to download it once your file is ready. This gives you a packaged folder of .gpx files, one for every ride you've ever logged. From there, I called on my developer buddy and greenways advocate Kenneth Trease (@kptrease) to make a python script that would convert the gpx files (GPS format that's kind of like an xml file), into one mega tab-separated CSV file, which Tableau could gobble up. If you'd like to do the same without having to write new code, check out Kenneth's, linked to Github in these comments.

Next, for each ride, I painstakingly hand-copied the summary-level ride data into a Google Spreadsheet https://docs.google.com/spreadsheet/ccc?key=0AgGAZJEhfbicdC0xai1TYVVXQzN... in a format I thought Tableau would be able to consume easily (It did great, except for the date/timestamp info: rookie mistake. Learn more about dates in tableau here: http://onlinehelp.tableausoftware.com/current/pro/online/en-us/help.htm#... and here http://onlinehelp.tableausoftware.com/v8.2/pro/online/mac/en-us/help.htm...). I had originally planned to scrape it out using the fabulous https://import.io/, but it didn't like the cookies in Strava, so I faltered. To make this process more scalable/reproducible by others, I'd like to figure out a bit of javascript magic to periodically pull the data. Do you (or others) know about such things? After I had the google doc, I just downloaded it as an Excel file and brought it into tableau along with my .csv that way.

Good luck! I'd love to see what vizzes you might make! If you download my .twbx, you could swap in your data, and use that as a starting point.
Being in Chicago, you might be interested in the Divvy BikeShare Data Challenge too! https://www.divvybikes.com/datachallenge If you want to check out the dataset that is available for download there, I'd love to play! I want to learn how to bin lat's and long's to make a heat map with all of those traces: that might be v2.

Let's be friends on Strava and Twitter too, if you like. http://www.strava.com/athletes/436224 https://twitter.com/katelovesux
Cheers, -Kate

Thanks for the kudos, Chris! This would be really interesting to calculate, but would be a question one could approach on various levels.

Personally, I don't own a car, so there's the difference in price between just owning and maintaining a car, and the cost of parking it near our Fremont, Seattle office. Alternately, folks who bike a lot but don't want to have to bike for every single commute can look at hybrid commuting options that include a bus ride (paid monthly unlimited, or per ride), a taxi or "Uber"-type option, or a Car2Go. I've also had co-workers who live more than 10 miles from the office, so they drive in most of the way with their bike in/on the car, and then bike in the rest of the way for the exercise, enjoyment, and savings on parking.

There are also costs associated with commuting by bike, but they're pretty minimal, and mostly elective. I'm lucky enough to work in a building with secure bike parking and shower facilities, but great facilities/services like the ViaBike Cycle Club in downtown Seattle http://www.cascade.org/2013/07/a-look-inside-via6 would likely still result in a net financial savings for a lot of people.

Some folks might consider relinquishing a gym membership in favor of bike commuting as an additional potential cost savings. I do a lot of social dancing and yoga to complement my bike activities, so it's not true for me, but I'm sure it many others! There's also the time one saves by not sitting in traffic and going to the gym as separately scheduled chunks of time on the weekly calendar.