The open data movement has made more and more data available for academics to download and use for their research. But how can we map this data? This workshop will take you through the process of acquiring data from the Los Angeles Open Data portal and visualizing it on Tableau for further analysis.
1.3 million records! Let’s filter it down to something more manageable.
Now add the filter to narrow down the data to one month:
Export the data
Cleaning up those coordinates
Open the downloaded data in Excel. Scroll to the right until you see the Location column.
Hmm, that’s strange, the latitude and longitude columns are in the same column! Most GIS plaforms will not like this. Let’s clean this up.
First, find and replace the brackets.
Select the Location column
Bring up the find and replace tool (ctrl-h)
For “Find what”, enter an open bracket “(“
Click Replace All
Repeat for the closing bracket.
Split the column into two:
Choose, delimited, check the “Comma” box, and finish.
Rename the column headers to Latitude and Longitude
Let’s map it!
Open Tableau Public. Click “Text file” and open the arrest data you just downloaded from the LA Data Portal.
Create a worksheet, click on “Sheet 1” at the bottom of the window. Under “measures,” double click on Lat, then double click on Lon.
Drag ReportID into the Marks box:
Switch to the Pan tool (or type F on your keyboard) and zoom into Los Angeles.
Drag Sex Code into the Color box:
Add another worksheet.
Double click on Descent Code under Dimensions.
Drag Number of Records from Measures to the count column (bunch of Abc’s).
Go to Show Me (top right) and select the bubble chart:
Add another worksheet.
Double click on Charge Description under Dimensions.
Drag Number of Records into the Abc column.
Expand Show Me (top right) and select the bar graph.
Click on Charge Description next to Rows (top).
Select Sort… from the drop down menu.
Sort by Field, Descending.
Drag Sex Code to the Color box.
Now create a new dashboard.
Change Size to Automatic.
Drag each sheet into the dashboard:
Click on Sheet 1 to highlight it, and enable the filter (it should turn white):
Repeat for the other two sheets so that each sheet is linked to one another.
Click on different elements on the charts and notice how each sheet changes as you do so.
Select the Radial Selection from the map tools.
Click and drag different areas of the map. How does that inform the other elements of your dashboard, and what are some discoveries you find interesting?
In response to COVID-19 the Sandbox is providing weekly open office hours for research consulting and questions.
Open Data and Tableau
Acquiring data
The open data movement has made more and more data available for academics to download and use for their research. But how can we map this data? This workshop will take you through the process of acquiring data from the Los Angeles Open Data portal and visualizing it on Tableau for further analysis.
The Los Angeles Open Data Portal
Go to the LA Data Portal, and search for arrest data:
Inspect the data
1.3 million records! Let’s filter it down to something more manageable.
Now add the filter to narrow down the data to one month:
Export the data
Cleaning up those coordinates
Open the downloaded data in Excel. Scroll to the right until you see the Location column.
Hmm, that’s strange, the latitude and longitude columns are in the same column! Most GIS plaforms will not like this. Let’s clean this up.
First, find and replace the brackets.
Repeat for the closing bracket.
Split the column into two:
Choose, delimited, check the “Comma” box, and finish.
Rename the column headers to Latitude and Longitude
Let’s map it!
Open Tableau Public. Click “Text file” and open the arrest data you just downloaded from the LA Data Portal.
Create a worksheet, click on “Sheet 1” at the bottom of the window. Under “measures,” double click on Lat, then double click on Lon.
Drag ReportID into the Marks box:
Switch to the Pan tool (or type F on your keyboard) and zoom into Los Angeles.
Drag Sex Code into the Color box:
Add another worksheet.
Double click on Descent Code under Dimensions.
Drag Number of Records from Measures to the count column (bunch of Abc’s).
Go to Show Me (top right) and select the bubble chart:
Add another worksheet.
Double click on Charge Description under Dimensions.
Drag Number of Records into the Abc column.
Expand Show Me (top right) and select the bar graph.
Click on Charge Description next to Rows (top).
Select Sort… from the drop down menu.
Sort by Field, Descending.
Drag Sex Code to the Color box.
Now create a new dashboard.
Change Size to Automatic.
Drag each sheet into the dashboard:
Click on Sheet 1 to highlight it, and enable the filter (it should turn white):
Repeat for the other two sheets so that each sheet is linked to one another.
Click on different elements on the charts and notice how each sheet changes as you do so.
Select the Radial Selection from the map tools.
Click and drag different areas of the map. How does that inform the other elements of your dashboard, and what are some discoveries you find interesting?