FiofanS FiofanS - 1 month ago 7
R Question

Create a table with mean duration times per years and types

I have the following dataframe:

library("lubridate")

df = data.frame(c("AAA","BBB","AAA"),
c("2012","2013","2012"),
c("2012-12-30 08:01:01","2013-12-30 09:05:02","2012-12-30 08:08:01"),
c("2012-12-30 09:01:00","2013-12-30 10:15:00","2012-12-30 08:11:01"))
colnames(df) = c("type","year","start","end")

df$duration = difftime(ymd_hms(df$end), ymd_hms(df$start),units="mins")


Now I want to create a table with mean durations so that it would look as follows:

2012 2013
AAA 31.49 0.00
BBB 0.00 69.97


Which functions should I use to create such table?

Update:

This is what I tried, but how can I add
year
?:

mean_duration_per_type_year = aggregate(duration~type,
data=df,
mean)

Answer

Here is what you can do, using the reshape2 package:

library(reshape2)
temp <- aggregate(df$duration, by = list(type = df$type, year = df$year), mean)
final <- dcast(temp, type~year)
final[is.na(final)] <- 0 # optional : replace the NA values with 0


final
#   type     2012     2013
# 1  AAA 31.49167  0.00000
# 2  BBB  0.00000 69.96667

Note that if you want the type to be the rownames, you could add these lines:

rownames(final) <- final$type
final$type <- NULL

final
#         2012     2013
# AAA 31.49167  0.00000
# BBB  0.00000 69.96667
Comments