Connecting Tableau to NYC Open Data API using the Web Data Connector
Open DataNYC is public data source that has a variety of interesting tidbits about NYC. If you’re like me, you might have gone to the website and downloaded an a dataset in .csv or .xlsx format and loaded it into Tableau.
But there is a better way! Since NYC Open Data uses the Socrata API, you can directly connect the the dataset, skipping the need to download the data and need to repeat the process to update the data source when new information becomes available (Note — I made a similar tutorial for R). Let’s get started:
Step 1: Open Tableau and get the Web Data Connector ready.
Fire up Tableau and click on “Web Data Connector”:
In Tableau Public it’s on the left hand side when Tableau first opens up under “To a Server”
In Tableau Desktop it’s you’ll have to click “More” under “To a Server”
Then click “Web Data Connector”
Step 2: Find the NYC Open data (Socrata) web connector
You’ll then be greeted with Tableaus internet browser and it will show you the Web Data connector landing page. Copy and paste the following link into a web browser (Google Chrome):
https://tableau.github.io/webdataconnector/community/
This will take you to a page where “web connecters” have been created by the community and ready for use. Scroll down and look for “Socrata”
Step 3: Copy the web connector URL
Next we have to get the Socrata URL. Hover over “Socrata” and click “Copy Link Address”
Step 4: Load the URL into Tableau’s web browser and press “enter”:
Step 5: Give the data connector the information about the data.
This will take you to the landing page of the data connector. This is where you will input Open DataNYC information.
Notice, there are four inputs:
Socrata Site — This is the link to the data.
Dataset ID — The ID of the datasets.
Where Clause — Gives you the opportunity to use a “where” statement and filter rows.
Limit — Allows you to set a specific number of rows. This is very helpful when you have a massive dataset such as 311 data.
To get this information:
- Go ahead and find the NYC OpenData that you are interested in. We’re going to use NYC Civil Service List (Active) but this tutorial will work for any NYC OpenData.
- For he Socrata Site: field simply input the following: https://nycopendata.socrata.com/
3. Next, we’re going find the Dataset ID. This information is located in the URL of the web page. It should be everything after the last “/” of the URL. For example the NYC Civil Service List (Active) data, the Dataset ID would be “vx8i-nprf”.
4. We’re going to leave the Where Clause: blank because we want the full dataset but this is where you could apply a basic query.
5. We will set keep the Limit: field to the default “10000” for this exercise, but you can set it to whatever you like. Keep in mind, the higher this number the longer it will take to load and if you leave it blank, some datasets may take forever.
Your Web Data Connector input page should look like this:
Then simply click “Get Data” and there you have it, data connected via the Web Data Connect. No need to download datasets to refresh in the future, just simply click the “Update dataset” in the data source tab and you’re good to go!
Have at it and please let me know if this worked for you!
