Skip to content

Instantly share code, notes, and snippets.

@mhawksey
Created January 26, 2012 11:20
  • Star 4 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save mhawksey/1682306 to your computer and use it in GitHub Desktop.
Read edge list from TAGS Spreadsheet calculate SNA data and POST back
library(igraph)
library(reshape)
library(plyr)
require(RJSONIO)
require(RCurl)
# Author: Martin Hawksey
# License: http://creativecommons.org/licenses/by/2.5/scotland/
# Google Spreadsheet key (must be published to the web)
key='0AqGkLMU9sHmLdGNYZDVyTWl1ZmtNbmFzWGlpUkt1Tmc'
# Sheet gid name
gid=105
# Publsih as service url
serviceUrl='https://docs.google.com/macros/exec?service=AKfycbwyuUVgwcK1ONFO22IrROC_hG0pGBBCQyN3lLUdxA'
# A secret set in the Script Editor of the spreadsheet to prevent unauthorised data entry
secret='FzWGlpUkt1Tmc'
# Read data from spreadsheet
ss = read.csv(paste('https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=', key ,'&single=true&gid=', gid, '&output=csv', sep=""), header = F)
#pass to igraph
g <- graph.data.frame(ss, directed = T)
# calculate some stats
betweenness_centrality <- betweenness(g,v=V(g),directed = TRUE)
eigenvector_centrality<-evcent(g)
pagerank<-page.rank(g)$vector
degree<-degree(g, v=V(g), mode = "total")
degree_in<-degree(g, v=V(g), mode = "in")
degree_out<-degree(g, v=V(g), mode = "out")
screen_name<-V(g)$name
# might want to get centralization stats http://igraph.wikidot.com/r-recipes (not included for now)
# bind into matrice
datagrid<-data.frame(I(screen_name),degree,degree_in,degree_out,betweenness_centrality,eigenvector_centrality[[1]],pagerank)
cc <- c("screen_name", "degree","degree_in","degree_out","betweenness_centrality","eigenvector_centrality","pagerank")
colnames(datagrid) <- cc
rownames(datagrid) <- screen_name
# convert to JSON
test <- data.matrix(datagrid)
dg = toJSON(data.matrix(datagrid))
dglabels = toJSON(rownames(datagrid))
#get top results from data.frame http://stackoverflow.com/a/3320420/1027723
datagrid.m <- melt(datagrid, id = 1)
a.screen_name <- cast(datagrid.m, screen_name ~ . | variable)
a.screen_name.max <- aaply(a.screen_name, 1, function(x) arrange(x, desc(`(all)`))[1:10,])
datagrid.screen_name.max <- adply(a.screen_name.max, 1)
toptens <- cbind(datagrid.screen_name.max[,2],datagrid.screen_name.max[,3],datagrid.screen_name.max[,1])
toptens = toJSON(toptens)
toptenslabels = toJSON(colnames(datagrid))
# write back to spreadsheet http://mashe.hawksey.info/2011/10/google-spreadsheets-as-a-database-insert-with-apps-script-form-postget-submit-method/
# SSL fix http://code.google.com/p/r-google-analytics/issues/detail?id=1#c3
options(RCurlOptions = list(capath = system.file("CurlSSL", "cacert.pem", package = "RCurl"), ssl.verifypeer = FALSE))
# post form
postForm(serviceUrl, "datagrid" = dg, "datagridlabels" = dglabels, "toptens" = toptens, "toptenslabels" = toptenslabels, "secret" = secret)
// Google Apps Script Snippet for POST handling data from R
function doPost(e){
// PART OF SCRIPT WHICH HANDLES DATA POSTED FROM R
// To use complete steps 1 -3 from TAGS - Advanced menu and File > Publish to the web..
// From this window select Share > Publish as service.. allowing anyone to invloke annonymously
// Download the R script from https://gist.github.com/1682306 and modify variables for the key, sheet, urls and common secret set below
// Run the R script and values will be entered into the spreadsheet
var secret ="FzWGlpUkt1Tmc"; //must match secret in R script
if (e.parameter.secret == secret){
var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));
var sheet = ss.getSheetByName("Vertices"); // read the existing data from Vertices sheet
var data = {}
var datagrid = Utilities.jsonParse(e.parameter.datagrid); // handling datagrid object made in R
var datagridlabels = Utilities.jsonParse(e.parameter.datagridlabels); // list of screen_names that match datagrid made in R
for (i in datagrid){
datagrid[i].screen_name = datagridlabels[i];
data[datagridlabels[i]]=datagrid[i]; // index objects with screennames
}
var toptens = Utilities.jsonParse(e.parameter.toptens); // collect toptens list made in R
var labels = Utilities.jsonParse(e.parameter.toptenslabels); //list of names of things calculated in R
var exdata = getRowsData(sheet); // read the existing data from Vertices sheet
for (i in exdata){ // add new data to existing data
if (data[exdata[i].screen_name] != undefined){
for (j in labels){
exdata[i][labels[j]] = data[exdata[i].screen_name][labels[j]];
}
}
}
setRowsData(sheet,exdata); // write individuals stats back to sheet
var sheet = ss.getSheetByName("SNA Metrics"); // add the top ten's to SNA Metrics sheet
var topsData = chunk(toptens,10);
// probably could have found better way of writting
sheet.getRange("A4:C13").setValues(topsData[0]);
sheet.getRange("A17:C26").setValues(topsData[1]);
sheet.getRange("A30:C39").setValues(topsData[2]);
sheet.getRange("A43:C52").setValues(topsData[3]);
sheet.getRange("A56:C65").setValues(topsData[4]);
sheet.getRange("A69:C78").setValues(topsData[5]);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment