Instantly share code, notes, and snippets.
Created
January 26, 2012 11:20
Star
You must be signed in to star a gist
Read edge list from TAGS Spreadsheet calculate SNA data and POST back
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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