Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.