Creating a smoothed activity plot

hourly-activity-percentages-odesk.R

library(RPostgreSQL)
library(sqldf)
library(KernSmooth)

# Connect to the database
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname='odw', host='localhost', user='panos', password='password', port='12345')

# Retrieve the data from the database. 
# Query columns: active_contractors, total_contractors, percent_activity, local_dayoftheweek, local_hour, country
# We will be plotting the percent_activity vs local_dayoftheweek+local_hour,
df <- dbGetQuery(con, 'select * from odw.ipeirotis.visualization_local_activity')

# Merge local_dayoftheweek and local_hour into a single date variable, ranging from 0 to 7
df$date <- df$local_dayoftheweek + df$local_hour/24.0

# Create a 2-column data frame that will be used by the 2-d kernel smoothing function
samp <- df[,c("date","percent_activity")]

# Apply 2-d kernel smoothing. In the future, we can add an automated estimator for estimating the proper bandwidth values for smoothing
model <- bkde2D(samp, bandwidth=c(0.05,0.01), gridsize=c(500, 500), truncate=TRUE)

# Plot the result of the 2-d kernel smoothing
filled.contour(model$x1, model$x2, model$fhat, xlim=range(c(0,6.95)), ylim=range(c(0,0.1)), nlevels=50, col=terrain.colors(52, alpha =1.0), xlab = "Day of the week / Hour of day", ylab = "Percentage of Active Contractors", main="Activity of oDesk Contractors During the Week")