Every MP tweet and Facebook update about Grenfell during one year http://birminghameastside.com/2018/06/14/grenfell-mp-mentions-social-media/
Coinciding with the one year anniversary of the Grenfell Tower fire on 14 June, Birmingham Eastside decided to find out which MPs have continued to talk about the topic throughout the past 12 months on Twitter and Facebook.
Get the data
The data was collated with Crowdtangle and inluded all the tweets and Facebook updates made by MPs between June 2017 and June 2018 mentioning "Grenfell" in their messages or linked content.
The scraping provided these 4 files below:
- CSV: Grenfell mentions by Conservative MPs on Facebook
- CSV: Grenfell mentions by Labour MPs on Facebook
- CSV: Grenfell mentions by all MPs on Facebook
- CSV: Grenfell mentions by all MPs on Twitter
To visualise the data, it had first to be cleaned. Excel formulas and pivot tables were used to put the data together and remove duplicates.
To match the party of the MPs from the Facebook database who only apeared in the Parliament list, the Wikipedia List of MPs elected in the United Kingdom general election, 2017 was used.
In the second case, names had to be cleaned, as most of the MPs add other words like "MP" or their party at the end of the profile name. The following is an example of a formula used to clean the "MP" bit at the end of the profile name:
=IF(ISERR(FIND(" MP",A2)),A2,REPLACE(A2,FIND(" MP",A2),3,""))
After cleaning, some analysis needed to be done to know when and by who the mentions were made the most.
The month and the year of the message were extracted with their respective MONTH and YEAR formulas and put toghether with a CONCATENATE formula to create a new column grouped and ordered by each of the 12 months.
Also, we were interested in the mention of Grenfell in relation to debates like materials in use in tower blocks in general. That is why the formula =SEARCH("cladding",A2) was used to find out a 10% of the messages also mentioned the word "cladding".
Because of our West Midlands focus, we matched these region's MPs to their constituencies using, again a VLOOKUP formula.
The clean data with the new calculated variables can be downloaded from this CSV.
We used the survey template from Flourish studio to create the interactive visualisation.
In this visualisation the user can group, colour and compare by party, date and platform. Hovering over the dots, which represent each tweet or Facebook update, details of the MP, the party, the day and the message appear.