Wednesday, April 11, 2012

Big Data, R and HANA: Analyze 200 Million Data Points and Later Visualize Using Google Maps

Technologies: SAP HANA, R, HTML5, D3, Google Maps, JQuery and JSON
For this fun exercise, I analyzed more than 200 million data points using SAP HANA and R and then brought in the aggregated results in HTML5 using D3, JSON and Google Maps APIs.  The 2008 airlines data is from the data expo and I have been using this entire data set (123 million rows and 29 columns) for quite sometime. See my other blogs

The results look beautiful:

Each airport icon is clickable and when clicked displays an info-window describing the key stats for the selected airport:
I then used D3 to display the aggregated result set in the modal window (light box):
D3 made it looks ridiculously simpler to generate a table from a JSON file. 
Unfortunately, I can't provide the live example due to the restrictions put in by Google Maps APIs and I am approaching my free API limits. 

Fun fact:  The Atlanta airport was the largest airport in 2008 on many dimensions: Total Flights Departed, Total Miles Flew, Total Destinations.  It also experienced lower average departure delay in 2008 than Chicago O'Hare. I always thought Chicago O'Hare is the largest US airport.

As always, I just needed 6 lines of R code including two lines of code to write data in JSON and CSV files:
################################################################################
airports.2008.hp.summary <- airports.2008.hp[major.airports,     
    list(AvgDepDelay=round(mean(DepDelay, na.rm=TRUE), digits=2),
    TotalMiles=prettyNum(sum(Distance, na.rm=TRUE), big.mark=","),
    TotalFlights=length(Month),
    TotalDestinations=length(unique(Dest)),
    URL=paste("http://www.fly", Origin, ".com",sep="")), 
                    by=list(Origin)][order(-TotalFlights)]
setkey(airports.2008.hp.summary, Origin)
#merge the two data tables
airports.2008.hp.summary <- major.airports[airports.2008.hp.summary, 
                                                     list(Airport=airport, 
                                                          AvgDepDelay, TotalMiles, TotalFlights, TotalDestinations, 
                                                          Address=paste(airport, city, state, sep=", "), 
                                                          Lat=lat, Lng=long, URL)][order(-TotalFlights)]

airports.2008.hp.summary.json <- getRowWiseJson(airports.2008.hp.summary)
writeLines(airports.2008.hp.summary.json, "airports.2008.hp.summary.json")                 
write.csv(airports.2008.hp.summary, "airports.2008.hp.summary.csv", row.names=FALSE)
##############################################################################

Happy Coding and remember the possibilities are endless!

2 comments:

  1. This post has a lot of importance to the people…I hope you can continue to inspire and post more of this…Thanks

    ReplyDelete
  2. Thanks for your post. I've been learning R for the past few weeks and finding it wonderful, especially the data.table package (which you are using here).

    You are joining the major.airports and airports.2008.hp.summary tables, and then overwriting the result to airports.2008.hp.summary. I think if you keep the existing airports.2008.hp.summary table and just add the new columns of matching rows from major.airports using the ":=" operator, you'll see a speed improvement. I tried an example and found it to be roughly 50 times faster (less than 2 seconds for a table with 90M rows, vs about 85 seconds using the overwrite method). Here is my example code:

    # http://stackoverflow.com/questions/11308754/add-multiple-columns-to-r-data-table-in-one-function-call

    library(data.table)

    fDT1<-function(n) data.table(x=rep(rep(c("a","b","c"),each=3),n), y=rep(c(1L,3L,6L),n), v=rep(1L:9L,n), key="x")
    DT2<-data.table(x=letters, z1=sample(1L:26L), z2=sample(27L:52L),key="x")

    n<-1e7L
    DT1<-fDT1(n)
    res1<-system.time(DT1<-DT2[DT1])[3]

    DT1<-fDT1(n)
    res2<-system.time(DT1[DT2,c("z1","z2"):=list(z1,z2),nomatch=0])[3]

    list(method_1=res1,method_2=res2,improvement=paste0(round(res1/res2,1),"X"))

    ReplyDelete