Ever wondered how to get UK petition data in a format suitable to produce your own visualizations in Tableau? In this blog post I will walk you through the steps of just that!
In this weeks DS project, we were tasked with finding a petition of interest from the UK government petition website, (Petitions - UK Government and Parliament), downloading it in the form of a JSON file, parsing it out in Alteryx (before turning the flow into a macro) and enriching it with external data of our choice to produce a tableau dashboard.
I decided to choose the petition “End child food poverty – no child should be going hungry”, on the back of the amazing work Marcus Rashford (The Manchester United and England footballer has done). Details of the petition can be found here:
End child food poverty – no child should be going hungry - Petitions (parliament.uk)
OK Let’s go!
Download the data as a JSON format by heading to the bottom of the webpage and clicking on “Get petition data”
The window will open with a page of lots of text, right click anywhere on the text and choose “Save as” – you can now save this as a JSON file to wherever you choose.
Open up the file in Alteryx using an Input tool, making sure the configuration is set to “Unicode UTF-8” in the Code Page setting. Save this workflow at this point before continuing as this will be our “main workflow” file that we will insert our macro into later on.
Copy the input tool over to a new workflow, then right click and choose the “Convert to Macro Input” option. The data should look like what we have in the results window below, 2 fields of “JSON_Name” and “JSON_ValueString”. In this petitions case we have 3,183 rows of data, but we now need to extract all the meaningful information we need in the macro, and ensure the macro is repeatable to perform the same extraction on any petition that we insert.
I just wanted to include the UK constituency data from the petition (but there is data in each petition that includes signatures from overseas countries if that was of interest to you). In order to do this, a simple filter tool, filtering down to rows just containing the constituency information will do the job:
Next, a Text to columns tool can be inserted to split our our JSON_Name field using _ as the delimiter and setting the number of columns to 3, before inserting a select tool to delete the first 2 new columns split (as we don’t need these), and I re-named column 3 to “Name” using a Select Tool.
A data cleansing tool can be used at this point to remove the numbers and punctuation from our newly created field in order to leave “constituencyname”, “constituencyonscode”, “constituencymp” and “constituencysignaturecount” as the 4 rows for each constituency (notice that a new constituency is listed after every 4 rows)
In order to prepare the data to be cross-tabbed into a more usable format, we are going to have to first create a record ID for each row using the Record ID tool, and use a formula that will increase the record ID number by 1 after every 4 rows (in order to match the same record ID for all our rows for a specific constituency). The formula CEIL([RecordID]/4) will work here which is basically dividing the row number by 4 and rounding up to the nearest whole number.
Next we can insert a crosstab tool to change the shape of our data, by making our “constituencymp”, “constituencyname”, “constituencyonscode” and “constituencysiganturecount” into their own fields. By grouping by our modified record ID in the last step, changing the name field to our new column headers and our values as the JSON_ValueString field we can get our data into this format
I also wanted to include some population data for each constituency in my visualization, and I was able to source this in a .csv file from Parliamentary constituency postcodes (doogal.co.uk). This meant inserting a join into the work flow at this point, and joining my current flow and the csv file on a common field, in this case “ONS code”. A quick but of cleaning with the embedded select tool in the join and you can now insert a macro output tool.
Lastly, if you want to customize the icon for your macro before inserting it back into the main workflow, you can do this by clicking on view from the options at the top of your window and then view interface designer. If you then click on the properties cog icon within the interface designer, you can use an image of your choice!
Always remember to save your macro before going back over to the main workflow(Alteryx will default to saving this as a .yxmc file).
At this point, its back over to the main workflow that we saved earlier, right click on the canvas and then insert macro. Connect this to the input we have from earlier, and run the flow, and there we have it!
We can validate the macro works for any other petition we might want to visualize by connecting another JSON petition file and running the macro against this input. This saves us building out the same workflow again if we wanted to work with data from any other petitions. Notice I still have my 650 constituencies but the signature numbers are different in the validation step below
The data is now ready for tableau. After importing the data, one of the things I realised I wanted to do was to create a filled map for each constituency, so I joined to a shape file within tableau by joining the constituency code from my Alteryx output to the Pcon17cd field within the shape file. The shape file can be sourced from
The result of my visualization can be seen below and the interactive viz can be found at DS22 Petition Project - Simon Evans | Tableau Public
Hopefully this blog post was helpful!