Geocoding: Tableau and R IntegrationAll the power of R + Tableau BI Toolsjose Luis RodriguezBlockedUnblockFollowFollowingJun 12, 2018Who is this feature intended for?This feature is primarily targeted for users who are already have some experience working with R.
These users will find the integration beneficial for several reasons:Ability to do statistical analysis on Tableau data.
Full access to any R package or function that has been installed in R.
Use tableau visualization capabilities to further analyze and understand data without having to manipulate data in R.
Users must have access to an R server/engine to access R functions from Tableau, also can be run on the same computer (localhost).
R Engine in TableauR functions and models can now be used in Tableau by creating new calculated fields that dynamically invoke the R engine and pass values to R.
The results are then returned back to Tableau for use by the Tableau visualization engine.
Tableau + RRserve: Acts as a socket server (TCP/IP or local sockets) which allows binary requests to be sent to R.
ggmap: A package for spatial visualization with Google Maps and OpenStreetMap.
R Packages InstallationFirst we need to open R and install the Rserve package to send code from tableau to R and ggmap to make requests to the google maps API.
# Code to install the packagesinstall.
packages(Rserve);install.
packages(ggmap);Loading Packages and Starting R ServerIn the same R script after installing the R packages successfully.
You need to load Rserve package with the following command.
Then after the package is loaded we need to start the Rserve with thefollowing command.
# Load R library and start the R enginelibrary(Rserve)Rserve()The Code: R and Tableau IntegrationThis is how your R setup script would look like.
In this stage we are just starting the R serve to integrate Tableau with R.
It is important to make sure that the package ggmap to get the geocodes is installed.
Rserve – Binary R server – RForge.
netRserve is a TCP/IP server which allows other programs to use facilities of R (see www.
r-project.
org) from various…www.
rforge.
net# Here we are checking if the package is installedif(!require("Rserve")){ # If the package is not in the system then it will be install install.
packages("Rserve", dependencies = TRUE) # Here we are loading the package library("Rserve")}dkahle/ggmapggmap – a package for plotting maps in R with ggplot2github.
com# Here we are checking if the package is installedif(!require("ggmap")){ # If the package is not in the system then it will be install install.
packages("ggmap", dependencies = TRUE) # Here we are loading the package library("ggmap")}Start the R serverThat command knows how to find Rserve, how to setup the environment and how to start it, regardless of your platform.
# Start RserveRserve()Geolocation from AddressIn many cases we have access to data with location information ie.
Address, City, County, Zip Code, Country.
To visualize this data with more detail we need to use geolocation.
In order to get the actual geolocation coordinates (Longitude, Latitude) for the location data, this requires a calculation.
Using the R package ggmaps we can easily generate the geolocation coordinates.
Testing the Tableau and R ConnectionAfter starting the Rserve in R we need to test if tableau can talk to R.
For this case the Rserve runs on the same computer.
Hence we dont have to change the default settings.
We just need to test the connection, a successful connection notification window should pop-up.
Tableau Special Calculated fieldsCalculated field: Full AddressFirst we to concatenate the address into a single string.
To do this we need to create a calculated field.
using the tableau formula feature:Calculated Field Name: Full Address# Tableau Formula:[Address] + "," + [City] + "," + [State]Geocode functionNow that we have a complete address with the proper format.
We need to create another tableau calculated field that uses R code to get the geocodes from the Google maps API.
Calculated Field Name: Geocode# Tableau Formula:SCRIPT_STR("library('ggmap');geo <- geocode(.
arg1, output = 'latlon');geo$latlon <- do.
call(paste,c(geo[c('lat','lon')],sep=','));geo$latlon",ATTR([Full Address]))Calculated field: latitudeCalculated Field Name: lat# Tableau Formula:left([Geocode],find([Geocode], ",")-1)Now we need to change the geocodes from the lat calculated field to float numerical values and create a new latitude field.
Calculated Field Name: latitude# Tableau Formula:float([lat])Calculated field: longitudeCalculated Field Name: lon# Tableau Formula:right([Geocode], len([Geocode])-find([Geocode], ","))Now we need to change the geocodes for the lon calculated field to float numerical values and create a new longitude field.
Calculated Field Name: longitude# Tableau Formula:float([lon])Assigning geographical rolesAssigning “Geographic Roles” to latitude and longitude allow us to plot latitude and longitude in Tableau as if they were in the original data set.
Setting Geographic Role for Latitude and Longitude fieldsMapping Address to Geographic CoordinatesAdd the calculated Latitude field to the workbook.
Disregard any connection error Tableau is trying to run the R script but is missing the full address field.
Now add the Longitude field to the workbook.
The connection error will appear again.
Now add the Full Address calculated field to the workbook.
Tableau should start computing and mapping the addresses to the map.
Full Address to Geographical LocationFinally the resulting map contains the input addresses and their location according to the calculated geolocation coordinate.
.