My First Python Program

Hello again! I’m back with a new post and this time it is about Python. I’ve dabbled in some MOOCs out there and recently completed the Introduction to Programming Nanodegree offered by Udacity. It was exciting, fun, and rewarding. I look forward to more MOOC classes in my future.

In my current role with a hotel company, I’m working with geospatial data much more than I have in the past. We have over 5000 hotels in the US and thus, perform analysis based on location. My task at hand is to join two separate data sets. One data set contains Postal Zip Codes but the other does not. The second data set does have Latitude and Longitude coordinates. So before I can join on Zip Codes, I must first populate the second data set with Zip Codes based on lat/lon values. This is where Python comes in.

After some googling, I discovered a Python library called Geocoder. This library provides geocoding services by leveraging various providers such as Google Maps, Bing, Mapquest, etc. One of its capabilities is the user can provide a lat/lon and it can return the Zip Code of that coordinate. Perfect!

So as test, I wrote the following program which submits a list of coordinates.

I have a CSV file that contains the lat/lon values. I pass this into Geocoder and it returns the Zip Code. The program prints each lat/lon and its Zip Code. Here’s a view of what it looks like when I run it in command line.

ScreenHunter_37 Dec. 23 09.50

In my test script, I’m only looking up 3 coordinates and you can see, it works! Yay!! My first Python program written for something in the real world. So what is next? Well, ultimately my task involves over 100k coordinates. Google’s API caps its free offerings so if I’d like to maintain this program, it is going to cost a little money to complete. Secondly, I need to adjust the program so that it writes back to a CSV file with three columns (lat/lon/zip) instead of just printing on command line. Let me know if you can help :) otherwise, back to Googling and Stack Overflow I go!

Share Button

Org Chart in Tableau

Since starting my new position, I’ve been talking about my work and department with my wife. I set out to create an organizational chart to give her more context when I speak about certain things. There are plenty of tools that facilitate org charts. Microsoft alone has Visio and Powerpoint. Heck, you could even use Excel or Word. But hey, I’m a Tableau user, so let make this more interesting.

Org charts in Tableau are not native. I searched the web and came across this thread on Tableau’s Ideas section. It would be interesting if Tableau added this as a Show Me feature. Perhaps they will someday. So I used this thread as a starting point on how to accomplish my goal.

The key to an org chart is connecting points. Let’s also not forget that the data structure is important when it comes to Tableau. Each data point needs two rows of data. You’re drawing a line to connect dots, so you need to have one row for the starting location (direct report) and one row for the end location (manager). Here’s how I set up my data:

ScreenHunter_01 Sep. 12 10.47

I have three individuals in this sample. Name1 is the top of the chart, so note the blank X, Y coordinates in the repeated row. This is because Name1 does not have a line to connect to its manager. I assigned each person an ID and mapped their manager ID. Only records in Order 1 have a Display Name and Display Title. This is used in the Label shelf so the records aren’t displayed twice. The hardest part of this is the X and Y coordinates. Depending on the size and branches of your org chart, you have to play around with their values. On a simple chart, you could probably automate the generation of these with Excel formulas.

The entire data file is stored here.

On to building the chart. Place the X values on the column shelf and the Y values on the row shelf. Go to Analysis and deselect Aggregate Measures. You should have something like this.

ScreenHunter_02 Sep. 12 11.06

Add the Y values to the rows again and enable dual axis with synchronized axis. On the Marks card, I set the first Y values to Line and the second to Square. Now you have something like this. It’s close but obviously needs a little more work.

ScreenHunter_06 Sep. 12 16.48

Each pair of records needs an unique ID. This ID will connect the hierarchy in the proper manner, instead of the mess above. We have this unique value as the ID column. Place the ID column in the Details shelf. From here you’re pretty much done. All that is left is formatting and labeling the values.

ScreenHunter_07 Sep. 12 16.53

Here’s my finished product. I placed this single sheet into a dashboard and set it to legal landscape. Click the image to navigate to the Tableau Public version if you wish to download the workbook.

Organizational Chart

Thanks for tuning in. Hopefully one day, I’ll be the at the top of this chart :).

Share Button


It has been a few months since I last posted. I wanted to take a quick minute to give an update. I took some time off from blogging as I began a job search. Like many others in the data blogging community, I’ve been very lucky to have turned my data passion into a career. I have moved to Choice Hotels International as a Tableau Developer and Analyst in the Business Intelligence Group. After 10 years with Discovery Communications, I decided it was time for a new challenge. I will miss many Discovery colleagues but have been making new friends at Choice. Discovery has so many talented individuals so I know everything I was part of there is in great hands.

It’s been a little intimidating moving industries. You take for granted the institutional knowledge that is acquired after 10 years of employment in one location. Now moving to the hospitality industry, I have so much to learn. RevPar, RevPar Index, ADR and the like are the foundations of hospitality reporting and analytics. It’s a been a fun ride so far and I look forward to much more.

So that is my quick update. I plan on being more active again now that things are settling down. Thanks for listening.

Share Button

Tableau in the office – episode 3

I’ve been revising some old dashboards at work that definitely are due for a refresh. One of the updates is to demonstrate Year over Year for a given metric. I wanted the calculation for current year to compare Jan 1 to date against previous year. But if the user selects to view YoY of previous years, I wanted to show full year difference, not Jan 1 to date. Below was my approach.

For sample data, I created an Excel file with random values per date from 1/1/2011 to 5/31/2015. You can download a copy of it here. To get my desired result, it will take one parameter and three calculated fields in the Filter shelf.

First, I created a Parameter for Year.

ScreenHunter_01 May. 20 16.07

Then I created a formula to only show the year selected in the Parameter and the year before it. This is a boolean expression and I place it on the Filter shelf, selecting the True result.

[Year] = year([Date]) or [Year] – 1 = year([Date])

In my scenario, I only want to show YoY of a year at a time. And since YoY is created with a table calculation for percent difference from previous, I needed a way to keep the result but hide the previous year in the view since it is blank. I used a simple index() calculation and placed this in the Filter shelf, selecting the value “2”.

Next, the following formula creates a calculated field which again is used in the Filter shelf. I’ve broken down the formula with the //comments.

//If a data point’s date is this year and in past months, keep it.
if [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) < datepart(‘month’, today())
then ‘keep’
//if a data point’s date is in this year and in current or future months, filter it.
elseif [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) > datepart(‘month’, today())
then ‘filter’
//if a data point’s date is in past years, keep it.
elseif [Year] < datepart(‘year’, today())
then ‘keep’
else ‘filter’

Place this calculated field in Filter shelf and select “keep”. Here are the three calculated fields in the Filter shelf. Any field I create strictly for filtering, I name with a “f.” prefix so I know its intended purpose.

ScreenHunter_02 May. 21 09.57

Now I place the Units measure in the Text shelf, and change the Sum(Units) to a Percent Difference table calculation.

ScreenHunter_03 May. 21 10.02

I also created a “label” field so the text changes depending on the Year parameter. This is to make it clear to the audience that YoY for current year is a Jan 1 to date comparison over previous year. But previous year to previous year minus 1 comparisons are Full Year over Full Year.

if [Year] = datepart(‘year’,today()) then “YoY” else “FYoFY” END

Add this “label” field to the Text shelf and lastly, add Year(Date) to the Text shelf too. Once all my text fields are in place, I edit the label as follows:

ScreenHunter_06 May. 21 10.09

The final result is below. Change the Parameter to view the differing YoY or FYoFY calculations. I included a table with the sample data.

Thanks for tuning in!

Share Button

Tableau server tip – Email Action

A quick and easy way to provide your user base the ability to email any questions about a given dashboard is to use a URL action. If you’re unfamiliar with actions, definitely check out this Knowledge Base article.

First, I decided to use an image of a question mark as a flag for the user. After a quick google image search, I found the one below that I liked. Download the image and save it to your …\My Tableau Repository\Shapes folder. I created a “Custom” sub-folder in this path to keep my custom shapes together.

ScreenHunter_01 Mar. 31 14.38

In your workbook, create a new sheet. The data source used is unimportant as you’re just going to create a viz with this shape in it.

Change the Marks to Shape, click the Shape pill, click More Shapes and choose the Custom folder (or what ever you named it) from the Select Shape Palette drop down. Click your shape and click OK.

ScreenHunter_02 May. 19 16.19

Now drop the omnipresent Number of Records measure into the Shape pill. You should now have a sheet that looks like this:

ScreenHunter_03 May. 19 16.23

You can enlarge the view by dragging along the sides of the sheet. Use the Size pill to make the shape larger or smaller as desired. I also format the sheet to remove Row and Column Dividers. In the Tooltip, replace existing text with something like “If you have questions about this data, please email:”.

Place this sheet anywhere in your dashboard. I prefer mine at the upper corner, right of the dashboard title.

ScreenHunter_04 May. 19 16.30

Create a URL Action. Name it your desired receiving email address. In the URL section, type: “mailto: email_address”

ScreenHunter_06 May. 19 16.36

Now when a user hovers over the image, the Tooltip will appear with the text and hyperlink. In my organization we use MS Outlook. Clicking the link launches a new email which contains your receiving email address already populated.

ScreenHunter_07 May. 19 16.40

Pretty simple trick. Hope you enjoyed it.


Share Button

Tableau Server Tip – Tabcmd

It’s been a little while since I have been able to post. But here I am back for a quick tip. I’m embarrassed to say that I only recently learned that Tabcmd (I pronounce it “Tab Command”) utilities can be installed and used from other machines, not just the one running your Tableau Server installation. If you’re unfamiliar with Tabcmd, these are a set of command line utilities that allow for automation and batch processing of tasks. This can be very handy when compiled into a BAT file and scheduled as a task.

Or just fun to play with, but be careful. There are delete commands that can be used. For the full list of Tabcmd options, check out the following link.

For you Alteryx users, Tabcmd can be implemented into your module. Interworks published a great article on how to take an output of a TDE and publish it to Tableau Server with Tabcmd. This is where being able to install Tabcmd on your machine shines. You don’t need to have Alteryx installed on your machine running Tableau Server. Check it out here.

Thanks for tuning in.

Share Button

March Madness Upsets

March Madness is one of my favorite sporting events. The excitement, the format, the emotion, the buzzer beaters, it encapsulates everything that is great about sports. So as I was watching the games these past two days, I started to think about all the upsets I have seen in my life. Naturally, a viz idea was born.

The below is summary of all March Madness upsets since 1985, the current format. I grabbed the data from this wikipedia page using Kimono, a great and simple tool to extract structured data from the web. Please note, the lowest seed considered an upset is 11. So no 10 vs. 7 or 9 vs. 8 match ups are present.


As for my personal brackets this year, the word of the day is #busted.

Share Button

You’re Welcome

This past week I was at a happy hour for a departing co-worker. While there I started talking to someone who I recently pointed her to a server viz for data. In our Post Production facility, we have an application called ScheduALL. It is a resource management system used by many media companies. The system has reporting capabilities via Crystal Reports. Our users generally provide volume/cost reports of network activity to others who do not have access to ScheduALL. Last year I had setup a variety of server vizzes for anyone to view network spend in our facility based on their network assignment. Our application runs on SQL Server and we have setup nightly jobs to create FACT tables of transaction data.

So back to the happy hour. The lady I was speaking this week had asked for assistance on how to generate these reports out of the system. This is a task where users run the data, export to Excel and create a pivot table of the results. The problem is our network reps are asked for this constantly. I instead sent her a link to a server viz that already contained the results and filters for more precise output. She now uses the tableau server viz instead of generating her own report. She also forwarded the link to her customer who was requesting the data.

I am not exaggerating when in her effusive Thank You to me, she said this new method of providing data to clients was “life changing.” Both her and her client were truly grateful for what I had provided with the power of Tableau Server. I love seeing newcomers to our world just be utterly wow’d.

So to her and everyone else who has thanked me for doing something I love, you’re welcome!

Share Button

Alteryx in the office – episode 2

I had a task the other week where I needed to load Excel data into a datamart. The problem was that the Excel data was not formatted like my destination table in the datamart. So I fired up Alteryx and started playing around.

The Excel sheet was structured with total quantity by week, like so:

ScreenHunter_01 Feb. 25 20.11

(sample data shown)

But I needed it to appear as follows:

ScreenHunter_02 Feb. 27 11.15

The goal is to have total distinct job_name rows equal the QTY value for the corresponding Week of. So 1/3/2011 would have a total of 117 rows.

My result was the following module.

ScreenHunter_03 Feb. 27 11.18

First, I bring in the data with an Input tool. Then I used a Formula tool to create a string field as “jobname”. The next part is where it gets fun. There’s a Generate Rows tool in the Preparation section. I set it up to create a new field, RowCount, initialized at value 1. You can then set the Condition Expression, which for me was RowCount<=[QTY]. The loop iteration is simply RowCount +1 in order to increment rows by 1 each time.

ScreenHunter_04 Feb. 27 11.21

This created rows that match the QTY value for each Week of. Next came the a Record ID tool followed by another Formula tool so that I could create distinct job_names. This is simply a concatenation of the original jobname field with Record ID. I also added a Quantity formula of 1 for quick summation purposes.

ScreenHunter_05 Feb. 27 11.36

A final Select tool was used so I only outputted my desired columns. You can download the sample input, Alteryx module, and sample output files here.

Hope you enjoyed it!

Share Button

Tableau in the office – episode 2

Time for another Tableau in the Office post. A quick viz that I worked on was for a department head that wanted to see how much a service had declined in recent years. The decline is due to advances in technology, therefore the service becoming obsolete. I took a look at the data (sample data used here) and created a simple line chart by plotting Quarter (Continuous) with Quantity.

ScreenHunter_01 Jan. 31 15.22

This is pretty straightforward. But I wanted to show the total percent decrease for 2014 Q4 since 2013 Q1. This is where I employed a Table Calculation. Table calcs in Tableau are extremely powerful. They can range for very simple to exceptionally complex. I try to read as much as I can about table calcs and encourage others as well.

I created a calculated field that used the LOOKUP function. This is a great way to isolate exactly what is needed as you can specify the offset. The formula is written as LOOKUP(expression, offset). So LOOKUP(SUM(QTY), FIRST() + 3) would return the fourth value in my partition.

Since I only needed the first and last values, I didn’t require an offset. Here’s how my formula looks:

ScreenHunter_02 Jan. 31 15.33

In pseudocode: (QTY at 2014 Q4 – QTY at 2013 Q1)/ QTY at 2013 Q1

I formatted the value as a percentage and placed this calculated field in the Detail shelf so I can use it in the chart.

ScreenHunter_03 Jan. 31 16.02

Right click on the last mark, select Annotate > Mark

ScreenHunter_04 Jan. 31 16.05

Edit the annotation as desired.

ScreenHunter_05 Jan. 31 16.06

Here’s my final viz. Simple and effective in illustrating the decrease of a service over the last two years.

ScreenHunter_06 Jan. 31 16.08

Thanks for tuning in!

Share Button