# How can I effectively summarize and visualize time series of employee activities?

I am managing many people entering data into a database. I have a log of user, date, time, table, and action that each person makes:

records <- data.frame(user = c('bob', 'bob', 'jane', 'jane', 'bob', 'bob', 'bob', 'jane', 'jane', 'bob'),
date = c("2010-06-24", "2010-06-28", "2010-06-29", "2010-06-30", "2010-07-01", "2010-07-02", "2010-07-05", "2010-07-06", "2010-07-07", "2010-07-09"),
time = c("01:40:08", "01:40:18", "01:40:28", "01:40:37", "01:40:44", "01:40:52", "01:40:59", "01:56:26", "02:16:37", "03:55:06"),
table = c(rep('table1',5), rep('table2',5)),
action = c('create', 'create', 'create', 'update', 'create', 'update', 'update', 'create', 'create', 'create'))


For a non-trivial example, the actual records dataframe with 10,000 entries can be downloaded as an .Rdata file here, and then:

load('records.Rdata')
library(ggplot2)
qplot(date, table, data = records, color = user, geom='jitter')


How can I visualize, overall and for each table:

1. the amount of time each person works per week
2. the type and number or frequency of actions that they made.

?

-
@David this is not just a visualization problem, you may be interested in the discussion of split-apply-combine strategies facilitated by Hadley's plyr package had.co.nz/plyr/plyr-intro-090510.pdf – Abe Feb 10 '11 at 20:37
@David how do you compute the "amount"? Is it the sum of actions? We would need the duration of each single action if you want the amount of time... – teucer Feb 10 '11 at 20:38
@teucer I was thinking about the number of create/update/check actions entered per unit time for each table between the first login and last action. Some tables might have 1-2 creates/week, while others may have as many as 100 creates in a day or even more checks/day. – David Feb 10 '11 at 20:42
@David could you provide the duration of each logging? – teucer Feb 10 '11 at 20:49
@David I guess when you say first login and last action, these are per day(?). I can easily compute the number of actions per user per week, but, with the given data, it is difficult to translate this in amount of time... – teucer Feb 10 '11 at 20:56
show 2 more comments

Below the code to plot the numbers of actions per week/per user:

load("records.Rdata")
library(ggplot2)
records$posdate <- as.POSIXlt(records$date,format="%Y-%m-%d")
records$week <- as.numeric(format(records$posdate,"%W")) #changed from previous hack!
numberActions <- by(records$action,records[,c("user","week")],function(x) length(x[x!="Login"])) numberActions <- melt(t(numberActions[1:7,]),"week") colnames(numberActions)[2] <- "user" ggplot(numberActions,aes(week,value,group=user,col=user))+geom_line()+geom_point(size=3)  I hope this helps. EDIT: For the second part you can use plyr: numberActions <- ddply(records,c("user","week"),function(x) table(x$action))
numberActions <- melt(numberActions,c("user","week"))
numberActions <- numberActions[numberActions$value!=0 & numberActions$variable!="Login",]


and then the usual ggplot...

-
 thanks for walking me through the data manipulation, but how can I retain the variables 'action' and 'table' so that I can use something like facet_grid(. ~ action) or facet_grid(. ~ action*table)? – David Feb 10 '11 at 22:01 @David, rr <-records[records\$action!="Login",] ddply(rr,~user+table+action+week,nrow) – mpiktas Feb 11 '11 at 8:41