OUseful.Info, the blog…

I couldn’t but notice the chatter about Iain Duncan Smith claiming he’d have no problem “living on 53 pounds a dayweek“, which made me wonder not only how many meal catered events he attends each week (and how many of his scheduled meeting also have complementary tea and biscuits (a bellweather of the extent of cuts in many institutions…;-), but also how he fares on the expenses stakes…

For the last couple of years, details about MPs’ expense claims have been published via the Independent Parliamentary Standards Authority (IPSA) website. The data seems to be most easily grabbed as CSV files containing all MPs’ claims for a parliamentary session (or tax year?) – eg 2012/13 or 2011/2012. As you might expect, that means the files are relatively large – 20MB (~100,000 rows) for 12/13, or just over 40MB (~190k rows) for 2011/12.

Files of this size are fine if you’re happy working with files of this size (?!), but can be a pain if you aren’t… So here are a couple of ways getting the data into a more manageable form, starting from raw data files that look something like this…

The file is made up of a series of rows, one per expense entry, with common columns. If we loaded this data into a spreadsheet application such as Excel or Google Spreadsheets, we’d see a single sheet containing however many tens of thousands of rows of data. Assuming that the application could cope with loading such a large amount of data of course… which it might not be able to do…which means we may need to make the data file a bit more manageable, somehow…

Let’s start with grabbing data relating to Iain Duncan Smith’s expense claims. On a Linux box, or a Mac, this is easy enough from a Terminal command line. (On Windows, something like cygwin should provide you with equivalents of some of the more useful unix tools.) For example, the grep command let’s us pull just the rows that contain the phrase Iain Duncan Smith:

grep "Iain Duncan Smith" DataDownload_2012.csv > IDS_expenses_2012.csv

This reads along the lines of: search through the file “DataDownload_2012.csv” looking for rows that contain “Iain Duncan Smith”, then copy those rows and only those rows into the file “IDS_expenses_2012.csv”

For what it’s worth, I’ve posted IDS’ expenses data from 2011 and 2012 to a couple of Google Fusion Tables: 2011/12, 2012/13

Another way of wrangling the data into a state we can start to play with it is to load it into RStudio, where we can start applying magical R incantations to it.

mpExpenses2012 = read.csv("~/Downloads/DataDownload_2012.csv")

We can then generate a subset of the data containing just IDS’ data:

ids2012=subset(mpExpenses2012, MP.s.Name=="Iain Duncan Smith")

We can also generate a combined data set of IDS’ expense claims from both 2011/12 and 2012/13, for example:

mpExpenses2011 = read.csv("~/Downloads/DataDownload_2011.csv")
ids2011=subset(mpExpenses2011, MP.s.Name=="Iain Duncan Smith")

ids11and12=rbind(ids2011,ids2012)

However, all is not well…

On loading the 2011 data into R, 158320 observations are loaded in. The actual number of rows (including the header – so one more row than the number of “observations”) can be given by running a line count (from the terminal/command line) over the original file:

wc -l DataDownload_2011.csv
187447 DataDownload_2011.csv

That is, 187447 rows…

If we try to pull out a list of MPs’ names using the levels command:

mpNames=data.frame(name=levels(mpExpenses2011$MP.s.Name))

we find that as well as the expected MPs names, there’s some “bad” data:

(What we expect to see in the name column is a list of MP names, one unique name per row.)

This is, of course, the way of the world. Folk who publish data rarely if ever, provide a demonstration of how to actually open it cleanly into an application (typically because data publishers think that once they have published the data, it’s bound to be all right and doesn’t need checking. This is, however, rarely true…although, for the 2012/13 data, there are 99071 loaded observations against 99072 (including 1 header) rows in the download file, which does seem to be correct).

What we should do now, of course, is go into a an ETL (or at least, TL) debug mode on the 2011 data and try to figure out what’s going wrong with the simple import… or we just work with the data we have and try to work around the dodgy rows…

…or we limit ourselves to the 2012 data, which does seem okay…

So if we do that, what other sorts of investigation come to mind?

One thing that came to mind after skimming the data…

was a “rail travel fares according to MPs’ expenses” lookup table.

So for example, we might start by creating a subset of the data based on expenses categorised as “Travel” and then look to see what sorts of trvel classification falls within that Category:

travel=droplevels(subset(mpExpenses2012,Category=="Travel"))
levels(travel$Expense.Type)

Here’s what we get:

[1] "Car Hire"                       "Car Hire Fuel"                  "Car Hire Fuel MP"              
 [4] "Car Hire Fuel MP Staff"         "Car Hire Insurance MP Staff"    "Car Hire MP"                   
 [7] "Car Hire MP Staff"              "Congest. Zone/Toll Seas Ticket" "Congestion Zone/Toll"          
[10] "Congestion Zone/Toll Dependant" "Congestion Zone/Toll MP"        "Congestion Zone/Toll MP Staff" 
[13] "Food & Drink"                   "Food & Drink @ Parliament"      "Food & Drink @ Parlmnt OFF Est"
[16] "Food & Drink MP"                "Food & Drink MP Staff"          "Hotel Late Sitting"            
[19] "Hotel Late Sitting > 1.00"      "Hotel London Area MP Staff"     "Hotel NOT London Area (Travel)"
[22] "Hotel NOT London Area MP Staff" "Hotel Outside UK"               "Own Bicycle MP"                
[25] "Own Car Dependant"              "Own Car MP"                     "Own Car MP Staff"              
[28] "Own Vehicle Bicycle"            "Own Vehicle Bicycle MP Staff"   "Own Vehicle Car"               
[31] "Own Vehicle Car Dependant"      "Own Vehicle Car MP Staff"       "Own Vehicle Mot Cycle MP Staff"
[34] "Parking"                        "Parking Dependant"              "Parking MP Staff"              
[37] "Parking Season Ticket"          "Public Tr AIR"                  "Public Tr AIR Dependant"       
[40] "Public Tr AIR MP Staff"         "Public Tr BUS"                  "Public Tr BUS MP Staff"        
[43] "Public Tr COACH"                "Public Tr COACH MP Staff"       "Public Tr FERRY"               
[46] "Public Tr FERRY MP Staff"       "Public Tr OTHER"                "Public Tr OTHER Dependant"     
[49] "Public Tr OTHER MP Staff"       "Public Tr RAIL - RTN"           "Public Tr RAIL - SGL"          
[52] "Public Tr RAIL Dependant - RTN" "Public Tr RAIL Dependant - SGL" "Public Tr RAIL Foreign"        
[55] "Public Tr RAIL MP Staff - RTN"  "Public Tr RAIL MP Staff - SGL"  "Public Tr RAIL Other"          
[58] "Public Tr RAIL Other Dependant" "Public Tr RAIL Other MP Staff"  "Public Tr RAIL Railcard"       
[61] "Public Tr RAIL Railcd MP Staff" "Public Tr RAIL Sleeper Suppl"   "Public Tr Season Ticket"       
[64] "Public Tr UND"                  "Public Tr UND Dependant"        "Public Tr UND MP Staff"        
[67] "Public Tr Underground MP"       "Taxi"                           "Taxi After Late Sitting"       
[70] "Taxi after Late Sitting 11pm"   "Taxi Dependant"                 "Taxi MP"                       
[73] "Taxi MP Staff"                  "Taxi Working Late After 9pm"    "Taxi Working Late Before 9pm"

We might further pull out just the rows relating to rail travel (almost 10,000 rows from the 2012/13 dataset):

rail=droplevels(subset(travel,grepl("Rail",Expense.Type,ignore.case=TRUE)))

and then we might start looking to see who’s travelling First vs. who’s travelling Standard, as well as building up a database of rail fares between locations as claimed on expenses. But that’s for another day…

Following a chat with @andypryke, I thought I’d try out a simple bit of feature detection around approximated follower acquisition charts (e.g. Estimated Follower Accession Charts for Twitter) to see if I could detect dates around which there were spikes in follower acquisition.

So for example, here’s the follower acquistion chart for Seem Malhotra:

We see a spike in follower count about 440 days ago, with an increased daily follower acquisition rate thereafter. WHat happened 440 days or so ago? We can easily look this up on something like Wolfram Alpha (query on /440 days ago/) or directly in R:

as.Date(Sys.time())-440
[1] "2011-12-20"

So what happened in December 2011? A quick search on /”Seema Malhotra” December 2011/ turns up the news that she won a by-election on 16 December 2011. The spike in followers matches the by-election date well, and the increased rate in daily follower acquisition since then is presumably related to the fact that Seema Malhotra is now an MP.

So what’s the new line on the chart (the black, stepped line along the bottom)? It’s actually a 5 point moving average of the first difference in follower count over time (that is, sort of a smoothed version of a crude approximation to the gradient of the follower acquisition curve; the firstdiff curve is normalised by finding the difference in accumulated follower count between consecutive time samples divided the number of days between samples. So it’s a sort of gradient rather than first difference. If the samples were all a day apart, it would be a first difference…). I also filter the line to only show days on which there was a “significant jump” in follower count, arbitrarily set at a 5 sample moving average of more than 50 new followers per day. Note that scaling of the moving average values too – the numerical y-axis scale is 1:1 for the cumulative follower number, but 10x the moving average value. The numerical value labels that annotate the line chart correspond to the number of days ago (relative to the date the chart was generated) that the peak corresponds to. For any chart critics out there – this is a “working chart”, rather than a polished presentation graphic;-)

#Process Twitter user data file
processUsersData=function(data){
  data$tz=as.POSIXct(data$created_at)
  data$days=as.integer(difftime(Sys.time(),data$tz,units='days'))
  data=data[rev(rownames(data)),]
  data$acc=1:length(data$days)
  data$recency=cummin(data$days)
  data$frfo=data$friends_count/data$followers_count
  data$stfo=data$statuses_count/data$followers_count
  data$foperday=data$followers_count/data$days
  data$stperday=data$statuses_count/data$days
  data$fost=data$followers_count/(1+data$statuses_count)
  
  data
}

#The TTR library includes various moving average functions
require(TTR)

differ_a=function(d){
  d=processUsersData(d)

  #Find the users who are used to approximate the accession date
  d2=subset(d,days==recency)
  #Dedupe these rows (need to check if I grab the first of the last...)
  d3=d2[!duplicated(d2$recency),]

  #First difference
  d3$accdiff=c(0,diff(d3$acc))
  d3$daysdiff=c(0,-diff(d3$days))
  d3$firstdiff=d3$accdiff/d3$daysdiff

  #First difference smoothed over 5 values - note we do dodgy things against time here - just look for signal!
  d3$SMA5=SMA(d3$firstdiff,5)

  #Second difference
  d3$fdd=c(0,diff(d3$firstdiff))
  d3$seconddiff=d3$fdd/d3$daysdiff
  
  d3
}

#An example plotter - sm is the user data
g= ggplot(processUsersData(sm))
g=g+geom_point(aes(x=-days,y=acc),size=1) #The black acc/days dots
g=g+geom_point(aes(x=-recency,y=acc),col='red',size=1) #The red acc/days  acquisition date estimate dots
g=g+geom_line(data=differ_a(sm),aes(x=-days,y=10*SMA5)) #The firstdiff moving average line
g=g+geom_text(data=subset(differ_a(sm),SMA5>50),aes(x=-days,y=10*SMA5,label=days),size=3) #Feature label
g=g+ggtitle("Seema Malhotra") #Chart title

Here’s Chris Pincher:

This account got hit about 79 days ago (December 15th 2012) – we need to ignore the width of the moving average curve and just focus on the leading edge, as a zoom into the chart, with a barchart depicting firstdiff replacing the first diff moving average line, demonstrates.

#Got a rogue datapoint in there somehow?
ggplot(subset(processUsersData(cpmp),days<5000))
g=g+geom_point(aes(x=-days,y=acc),size=1)
g=g+geom_point(aes(x=-recency,y=acc),col='red',size=1)
g=g+geom_bar(data=subset(differ_a(cpmp),days50 & days<5000),aes(x=-days,y=firstdiff,label=days),size=3)
g=g+ggtitle("Chris Pincher")+xlim(-200,-25)

The spam followers that were signed up to the account look like they were created in batches several months prior to what I presume was an attack? COuld this have been in response to his Speaking Out about the Collapse of Drive Assist on Thursday, December 13th, 2012, his Huffpo post on the 11th, or his vote against the Human Rights Act as reported on the 5th?

Who else has an odd follower acquisition chart? How about Aidan Burley?

219 days ago – 28th July 2012…

I guess that caused something of a Twitter storm, and a resulting growth in his follower count… Diane Abbott’s racist tweet row from December 2012 also grew her twitter following… Top tip for follower acquisition, there;-)

Nadine Dorries’ outspoken comments in May 2012 around David Cameron’s party leadership, and then same sex marriage, was good for her Twitter follower count, which received another push when she joined I’m a Celebrity and was suspended from the Parliamentary Conservative party.

Showing your emotions in Parliament looks like a handy trick too…Here’s a spike around about October 20th, 2011…

(There also looks to be a gradient change around 200 days ago maybe? The second diff calculations might pull this out?)

Chris Bryant’s speech on the phone hacking saga in July 2011 showed that publicly well-received parliamentary speeches can be good for numbers too; not surprisingly, the phone hacking scandal was also good for Tom Watson’s follower count around the end of July 2011. Election victories can be good too: Andy Sawford got a jump in followers when he was announced as a PPC (10th August 2012) and then when he won his seat (November 7th 2012); Ben Bradshaw’s numbers also jumped around the time of his May 2010 election victory, as did Lynne Featherstone’s, particularly with her appointment to a government position. Jesse Norman appeared to get a bump after the Prime Minister confronted him on July 11th 2012; Nick de Bois saw a leap in followers following the riots in his constituency in early August 2011, and the riots also seem to have bumped David Lammy’s and Diane Abbott’s numbers up.

A tragedy on September 17th looks like it may have pushed Peter Hain’s numbers, but he was in the news a reasonable amount around that time – maybe getting your name in the press for several days in a row is good for Twitter follower counts? Steve Rotherham also benefited from another recalled tragedy, the Hillsborough distaster, when, in October 2011, he called the ex-Sun’s editor out over it’s original coverage; he seems to have received another boost in followers when he lead a debate on internet trolls in September 2012.

Personal misfortune didn’t do Michael Fabricant any harm – his speeding conviction colourful Twitter baiting in October 2012 caused his follower count to fly and achieve an elevated rate of daily growth it’s maintained ever since.

A Dispatches special on ticket touts got a bounce in followers for Sharon Hodgson, who was sponsoring a private member’s bill on ticket touts at the time; winning a social media award seemed to do Kevin Brennan a favour in terms of his daily follower acquisition rate, as this ramp increase around the start of December 2010 shows:

So there we have it; political life as seen through the lens of Twitter follower acquisition bursts:-)

But what now? I guess one thing to do would be to have a go at estimating the daily growth rates of the various twittering MPs, and see if thy have any bearing to things like ministerial (or Shadow Minister) responsiblity? Where rates seem to change (sustained kinks in the curve), it might be worth looking to see whether we can identify any signs of changes in tweeting behaviour – or maybe news stories that come to associate the MP with Twitter in some way?