Finding a new home using Tableau & Excel
My soon-to-be employer recently funded a trip for me to find a place to stay once I completed my impending move to the Bay Area. Much to my annoyance, I realized that this cross-country flight — like half a dozen others in the last few months — would lead to me missing out on watching my beloved Manchester United in action. I decided to take out my frustration on the tiny in-flight entertainment screen and went about randomly tapping buttons with no real sense of purpose. Imagine my delight (and shock) when I stumbled upon NBC (since when did they have live TV channels on a flight?) to see Zlatan head the ball into the net from a Lingard cross!
I digress.
Where was I going with this? Oh, yes. Buoyed by being able to witness my team’s victory at 30,000 ft, I was excited about the home-finding trip. That, and the realization that I had just completed my Master’s degree had me all pumped up to do a solid job on finding the right house for me.
Finding the right house is quite a complex problem. Bi-Criteria Optimization in itself is a challenge when it comes to deciding what point on the trade-off curve best serves your purpose. Now imagine optimizing for 5 or 6 criteria. Prior to my trip I had identified most of these criteria but I didn’t give much thought as to how to find the place that would be best for me after accounting for all the data.
I spent most of my drive from SF to Cupertino pondering over this conundrum (pausing once or twice to sing along with Katy Perry). As the day progressed, some ideas began to take shape. By the end of my second day of the house-hunt, I had most of the data that I needed and sat down to perform my analysis.
I had two approaches in mind and I wanted to try both of them to see what kind of results I would end up with.
Tableau
My thought process to develop a visual was as follows: Decide on the 4 criteria most important to me and put this up on a scatter plot. For the 2 axes of the scatter plot, I had to pick two criteria that showed a range of continuous values. I ended up choosing ‘Travel time (min) at peak time’ and the ‘Monthly Rent + Utilities’ as the two axes. I then used a color scale for the marker points that showed the level of roommate-fit. And finally a circular marker for places that came with a private bath (don’t want to be late for work because someone else is having an unusually long shower) and cross-shaped markers for the ones that didn’t have one.
Based on this visual, which one of the places would you have picked?
Naturally, one would have to start at the lower left corner of the graph and imagine an expanding wave-front going outwards. I would like a house that’s closer to the point-source of the disturbance causing that wave (the lower left corner). But since having the right kind of roommates is important for me, I would be better off ignoring any of the red markers. At this point, I would have to start thinking of trade-offs between travel-time and rent and also the private-bath factor. Notice that there are 2 shades of green (the darker being the best) which further complicates the decision-making process. I was torn between the Scofield Dr & Norman Dr options. I’m curious to know if most people would have also ended up with the same two choices.
Time for a second opinion
The ever-dependable MS Excel
I only considered 4 main factors in the Tableau approach to simplify the problem. In excel, I decided to take into account all criteria. I assigned relative weights to each factor. I then scored each housing option on a scale of 0 to 3 (3 being best in class) for each of the factors and used conditional formatting on these numbers to display colors of red,orange, yellow and green for scores 0,1,2 and 3. A weighted score was calculated for each option.
The decision logic I wanted to employ here was to find the house with the highest weighted score but at the same time not scoring a zero on any individual factor.
The Norman Dr option came out on top whereas the Scofield Dr option could not get into second place.
That finally led me to go with Norman Dr.
Was that the best choice for me? Only time will tell.
