Redmine is a great tool to organize tasks. It has many built in functions like spent time report but also there are many limitations. Thanks to the package redmineR we can access to Redmine data and use the power of R to analyze and visualize it.
In this little introductory tutorial I will focus only on spent time data but I’d like to highlight that there are many more analytical potential on the redmineR package. Check the documentation for more details (help(package = “redmineR”)).
First of all we have to connect to our Redmine database with the API key. In this example I use a dummy Redmine site with a random data which has been prepared for this tutorial. My code is fully reproducible so the URL and the API key is valid! (Just please do not delete it )
library(devtools) # install_github("openanalytics/redmineR") library(redmineR) Sys.setenv("REDMINE_URL" = "http://testredminer.m.redmine.org/") Sys.setenv("REDMINE_TOKEN" = "0bd65b60431ef3917af1e263cbd44246e1458a32")
Basic data preparation:
Once we connect I just download all the spent time data which is available on the server and store it as an R data frame object.
activity_list <- redmine_time_entries() head(activity_list)
# id project user activity hours comments spent_on created_on updated_on #1 39 2, Mobile app development Agnes Yellow 3, development 3 2018-07-13 2018-07-13T21:23:51Z 2018-07-13T21:23:51Z #2 38 2, Mobile app development Agnes Yellow 3, development 4 2018-07-13 2018-07-13T21:22:30Z 2018-07-13T21:22:30Z #3 35 2, Mobile app development Agnes Yellow 3, development 3 2018-07-13 2018-07-13T21:20:41Z 2018-07-13T21:20:41Z #4 20 2, Mobile app development James White 3, development 8 2018-07-13 2018-07-13T19:52:55Z 2018-07-13T19:59:59Z #5 12 2, Mobile app development Jenny Blue 3, development 5 2018-07-13 2018-07-13T19:51:56Z 2018-07-13T19:51:56Z #6 11 2, Mobile app development Tom Black 3, development 9 2018-07-13 2018-07-13T19:48:37Z 2018-07-13T19:48:37Z
As we can see the are lists on the data frame which wont be so optimal for us later when need to transform the data a little bit. So I unlist the column 'user' to get access to usernames:
activity_list$user <- unlist(lapply(activity_list$user, `[[`, 2))
I filter the data to a specific week. When data manipulation is required I use the functions of package dplyr. I pick the days between '2018-07-09' and '2018-07-13'.
activity_list <- activity_list %>% filter(spent_on >= "2018-07-09" & spent_on <= "2018-07-13")
Analysis of users daily performance:
At first I group the data by users and days and after it I just summarize how many working hours the users did per day.
daily_performance_db <- activity_list %>% group_by(user, spent_on) %>% summarise(actual_worked_hours = sum(hours))
I assume that the expected working hours per day is 8. If I divide the actual worked hours by the expected working hours and I exact 1 out of it finally the result can be a type of performance indicator for the managers.
daily_performance_db <- daily_performance_db %>% mutate(expected_working_hours = 8, performance_pc = (actual_worked_hours / expected_working_hours) - 1) head(daily_performance_db)
# A tibble: 6 x 5 # Groups: user  # user spent_on actual_worked_hours expected_working_hours performance_pc #
#1 Agnes Yellow 2018-07-09 10 8 0.25 #2 Agnes Yellow 2018-07-10 6 8 -0.25 #3 Agnes Yellow 2018-07-11 9.5 8 0.188 #4 Agnes Yellow 2018-07-12 10 8 0.25 #5 Agnes Yellow 2018-07-13 10 8 0.25 #6 James White 2018-07-09 10 8 0.25
With the package ggplot2 we can visualize the result:
library(ggplot2) library(scales) ggplot(daily_performance_db, aes(x = spent_on, y = performance_pc, fill = user)) + geom_bar(stat = "identity", position = position_dodge()) + scale_y_continuous(labels = scales::percent)
According to our chart the workload is not the same between days. For example Jenny did an overtime on Monday but in the next few days she only worked 4 hours.
Analysis of users weekly performance:
With the analogy of the previous example we can also calculate the weekly performance. We just don't need the 'spent on' column as a grouping variable anymore.
weekly_performance_db <- activity_list %>% group_by(user) %>% summarise(actual_worked_hours = sum(hours)) %>% mutate(expected_working_hours = 40, performance_pc = (actual_worked_hours / expected_working_hours) - 1)
Now just determine a tolerance level which indicates us the "outliers". In this example I use +10% as a maximum and -10% as a minimum level. So if somebody works more or less than 10 percent as we expect we mark him or her.
t_level = c(0.1, -0.1) weekly_performance_db <- weekly_performance_db %>% mutate(performance = ifelse(between(performance_pc, min(t_level), max(t_level)), "expected", NA), performance = ifelse(performance_pc < min(t_level), "underperformance", performance), performance = ifelse(performance_pc > max(t_level), "outperformance", performance)) weekly_performance_db
# A tibble: 4 x 5 # user actual_worked_hours expected_working_hours performance_pc performance #
#1 Agnes Yellow 45.5 40 0.137 outperformance #2 James White 40.8 40 0.02 expected #3 Jenny Blue 28 40 -0.3 underperformance #4 Tom Black 34.5 40 -0.137 underperformance
As we see only James performed as we expect. Agnes outperformed a little bit so there is a danger of born-out or overload. In the other hand Jenny and Tom worked less hours than the expectation maybe the task allocation between our collages is not proper.
This tutorial helped us to prove that there is an analytical potential on the package redmineR so I highly recommend to dig deeper into it.