Hotel Wifi: IRE_Conference

Password: GrayTV1

Today’s Presentation

Download Tableau Public

Tableau Download: “CNTL” + click for a New Tab

We’ll configure this in the second part of the presentation, but you can start the download now. It is a 400 mb download that will take about 1.5 GB on your hard drive.

Also, you will need to create a Tableau Public account.

Storytelling with Local GDP Data

This section will analyze the storytelling possibilities with the Gross Domestic Product by Metropolitan Area data.

GDP Release: “CNTL” + click for a New Tab

Describe the data set

Gross domestic product (GDP) by metropolitan area is the sub-state counterpart of the Nation’s gross domestic product (GDP), the Bureau’s featured and most comprehensive measure of U.S. economic activity. GDP by metropolitan area is derived as the sum of the GDP originating in all the industries in the metropolitan area.

Contributions to growth are an industry’s contribution to the state’s overall percent change in real GDP. The contributions are additive and can be summed to the state’s overall percent change. The statistics of GDP by metropolitan area released today are consistent with statistics of GDP by state.

GDP at chained volume measure is adjusted for the effect of inflation to give a measure of ‘real GDP’.

Data limitations

This data series lags significantly. We are working with 2017 data, the most recent available, which was released September 18, 2018.

But it is still the best you can get and you can’t beat the price.

Step 1: Retrieve the Data

Click here to download the Excel tables: “CNTL” + click for a New Tab

• Let’s see what we have.
  • Three tables of data.
    
  • 382 metropolitan areas.
    
  • 13 industrial sectors.
    
    
    Walk through the tables:

Table 1: Basic GDP growth by metro, 2012-2017, and its rank nationally.
NYC is #1, Sebring, Fla. is #383. Houston is #7, Fayetteville, AR is #93.

Table 2: Inflation adjusted GDP, chained to 2009.
This is cool because it has the percentage change growth, 2015/2016, and ranks it. So Odessa, Texas makes the top of the list with 12.1 percent growth, a rate nearly double that of China in 2017. Don’t believe me? Look it up: https://data.worldbank.org/country/china

Houston’s growth was flat. Ranked #314 And Fayetteville makes #57 on the list, with 3.4% growth.

Question: What is the benchmark metric?

We will do the map exercise with Table 2 in a bit. But we have to look at Table 3.

Table 3: This is where we get the industry data.
Contributions to Percent Change in Real Gross Domestic Product (GDP) by Metropolitan Area, 2017)

Note the first column is the percentage change growth we saw in the Table 2. So that’s a handy reference.

Data limitations - weirdness

You may see a notation (D) in some cells. The footnote says: “(D) Not shown to avoid disclosure of confidential information, but the estimates for this item are included in the totals.” What that means is this particularly industry is pretty small or has just a few dominant actors, so disclosure of the information would reveal confidential business information. Which is no fun whatsoever.

Data Cleaning

The Google sheet you have has been cleaned and modified so it will play well with Tableau. Basically, the BEA spreadsheet was cleaned to remove merged cells, states were split off into a new column, a data dictionary was created to keep track of our changes and new headers were created. Here is a 10-minute video on how to do this yourself, which you can watch later:

Video on Excel Data Cleaning: “CNTL” + click for a New Tab

And here is the end result in a Google Sheet. Add this sheet to your Google Drive or download it as a spreadsheet on your computer.

Cleaned Data: “CNTL” + click for a New Tab

Step 2: Basic Interactive Chart

For this part, we will use Tableau Public to produce charts and a map. Download the app if you haven’t already

https://public.tableau.com/en-us/s/

You will need to create a Tableau Public account.

Launch the app.

Tableau accepts many data sources. Under “To a Server” there’s an option for Google Sheets. Or if you have downloaded the cleaned spreadsheet to your computer, you can link to it via Excel.

Cleaned Data: “CNTL” + click for a New Tab

With the Google Sheets link, select Table3Cleaned and drag it to the pane that says “Drag sheets here.”

Clean the data with the “Data Interpreter.” Click the box is right above the sheet listing.

Now, look at the data.

“Metro Area” and “State” should be the only things in text: Metro area is a blue “ABC.” That’s a dimension. The state is a blue Globe. That’s a geographic field. The rest should be with a green # sign. Those are measures. The difference is crucial in Tableau.

We’re ready. Click on “Sheet 1” in lower lefthand corner.

Welcome to a Tableau Worksheet

1. Drag the “Metro Area”" dimension to Columns
   
2. Drag the “Percent Change in real GDP” measure to Rows

• You should have an image like this:
  
  

* This is your basic graph of GDP growth by metro area in from 2016 to 2017.

Let’s sort it, high to low.

To do this, hover your mouse over the Y axis and a small arrow icon will emerge. Click it and it sorts your sheet. There is also a sorting tool in the menu bar.

Filter to Arkansas Metro Areas

+ To filter, click on the down arrow on blue pill in columns, "Metro Area." Click "Show Filter." A list of all 382 metro areas appears in a box on the sheet.

• Configure the filter by clicking a down arrow to the right of “Metro Area.” Select the option “Multiple Values Dropdown.”
  
  
  
  
  
  

• Deselect “All” so nothing is checked. Then search for the following and individually select:

  • Fayetteville-Springdale-Rogers
  • Fort Smith
    
  • Hot Springs
    
  • Jonesboro
    
  • Little Rock-North Little Rock-Conway
    
  • Pine Bluff
    
  • U.S. metropolitan areas
    
    

You now have a GDP growth chart for the metro areas in Arkansas with a U.S. comparison. You can do the same with your state later on.

Pro Tip: Duplicate and Iterate. Duplicate the Arkansas sheet by left clicking on the sheet tab. Once copied, adjust the filter for your state or city. More on Duplicate and Iterate later.

• Tweak the chart.

  • Drag “Percent Change in real GDP” to Color in the Marks Column near the worksheet. You can select a custom color palette by clicking on colors.

  • Double Click on the heading, “Sheet 1” and type in a headline. I typed “Northwest Arkansas Beats U.S. in 2017 GDP Growth Source: Bureau of Economic Analysis.” Use the formatting tools to center.

  • Drag “Percent Change in Real GDP” to the “Label” icon in the Marks box. This is to the left of the chart. Numbers will appear above the bars on the chart.

  • Format the numbers and legend. Look at the Marks card. Find the green pill representing the label “Sum(Percent Change in Real GDP).” Click the down arrow in this label. Click Format.

  • Format the numbers, continued: The Format pane appears to the left. Under “Default,” select “Numbers.” Then select “Number - Custom.” Select two decimal places and put a % sign in the suffix.

  • Format the Y axis legend. Left click on the Y axis to see menu. Select “Format.” See the formatting pane on left: select “Axis.” On “Scale,” select “Numbers.” Then select “Number - Custom.” Select no decimals and put a % sign in the suffix.

  • Drag the “Metro Area” Filter and then the Color legend to just below the X axis.

  • Drag right border out to expand the chart. Mouse over right edge to find the graphic.

  • Rename the tab “Sheet 1” to “Arkansas GDP”

• Your chart should look like this:

Tableau Chart: “CNTL” + click for a New Tab

—

Step 3: Basic Map

We’ll use the same data for a map. First, we have to convert the Dimension “Metro Area” to geographic data, or we need to “Geocode” this dimension.

• Geocode Data
  • Click on “State_Metro” down arrow
    
  • Select “Geographic Role” and then Select “CBSA/MSA”
    • this converts the text of Metro Area into data for mapping.

Notice in Measures that Latitude and Longitude measures are now created.

Follow These Steps In This Order:

• Click on “new worksheet” tab at lower left. It creates Sheet 2.
  
• Drag Longitude to Columns, Latitude to Rows
  
• In upper right “Show Me” menu, click on the map icon. A grayed out world map appears.
  
• Drag “State_Metro” to the sheet. A U.S. map appears with blue dots.

• Filter “Metro Area” blue pill in the Marks formatting box next to the sheet. Click “Show Filter” and format the filter as “multiple values dropdown” as we had in the past.
• Deselect “All” so nothing is checked. Then select:
  
• Fayetteville-Springdale-Rogers
  
• Fort Smith, AR-OK
  
• Hot Springs
  
• Jonesboro
  
• Little Rock-North Little Rock-Conway
  
• Pine Bluff

• Change “Automatic” in Marks Card. Click down arrow and select “Map.” The blue metro areas will appear on the map.
  
• Drag from Measures “Percent Change in real GDP” to the worksheet.
  
• Bring up the data labels. Drag “Percent Change in real GDP” to Label box on Marks card.
  
• Add Metro area names. Drag “Metro Area” to Label on Marks card. Click the Options box to allow labels to overlap
  
• Format labels, drag search box, legend to bottom, write a headline

• If you are good with this tutorial, then create a chart and map for your local community. Chart the rate of industry-specific sector growth by chooings one or more of the following:
  • Arts, entertainment, recreation, accommodation, and food services
    
  • Construction
    
  • Durable-goods manufacturing
    
  • Educational services, health care, and social assistance
    
  • Finance, insurance, real estate, rental, and leasing
    
  • Government
    
  • Information
    
  • Professional and business services
  • Trade
    
  • Transportation and utilities
    
    
    
    

Use Tableau story for multiple perspectives. Here’s an example of what you can do: “CNTL” + click for a New Tab

Story Function
The story function allows you to compile all of these separate sheet into one visual, interactive narrative. Click book icon in lower right to create a story. Add your various sheets to a story, creating one interactive graphic with multiple panels.

Duplicate and Iterate.
I built these multiple views by first formatting the first Arkansas sheet. Duplicate by left clicking on the sheet tab. Once copied, adjust the filter for your state or city. You can zero in on specific items and build specific data visualizations for industries or cities.
Get the embed code from Tableau Public and add it to your blog or website.

Now, go experiment and figure out some other visualization!

Thank you.