Calculating mean date by row

Question

I wish to obtain the mean date by row, where each row contains two dates. Eventually I found a way, posted below. However, the approach I used seems rather cumbersome. Is there a better way?

my.data = read.table(text = "
     OBS  MONTH1  DAY1  YEAR1  MONTH2  DAY2  YEAR2   STATE
       1       3     6   2012       3    10   2012       1
       2       3    10   2012       3    20   2012       1
       3       3    16   2012       3    30   2012       1
       4       3    20   2012       4     8   2012       1
       5       3    20   2012       4     9   2012       1
       6       3    20   2012       4    10   2012       1
       7       3    20   2012       4    11   2012       1
       8       4     4   2012       4     5   2012       1
       9       4     6   2012       4     6   2012       1
      10       4     6   2012       4     7   2012       1
", header = TRUE, stringsAsFactors = FALSE)
my.data

my.data$MY.DATE1 <- do.call(paste, list(my.data$MONTH1, my.data$DAY1, my.data$YEAR1))
my.data$MY.DATE2 <- do.call(paste, list(my.data$MONTH2, my.data$DAY2, my.data$YEAR2))

my.data$MY.DATE1 <- as.Date(my.data$MY.DATE1, format=c("%m %d %Y"))
my.data$MY.DATE2 <- as.Date(my.data$MY.DATE2, format=c("%m %d %Y"))
my.data

desired.result = read.table(text = "
   OBS MONTH1 DAY1 YEAR1 MONTH2 DAY2 YEAR2 STATE   MY.DATE1   MY.DATE2    mean.date
    1      3     6  2012      3   10  2012     1 2012-03-06 2012-03-10   2012-03-08
    2      3    10  2012      3   20  2012     1 2012-03-10 2012-03-20   2012-03-15
    3      3    16  2012      3   30  2012     1 2012-03-16 2012-03-30   2012-03-23
    4      3    20  2012      4    8  2012     1 2012-03-20 2012-04-08   2012-03-29
    5      3    20  2012      4    9  2012     1 2012-03-20 2012-04-09   2012-03-30
    6      3    20  2012      4   10  2012     1 2012-03-20 2012-04-10   2012-03-30
    7      3    20  2012      4   11  2012     1 2012-03-20 2012-04-11   2012-03-31
    8      4     4  2012      4    5  2012     1 2012-04-04 2012-04-05   2012-04-04
    9      4     6  2012      4    6  2012     1 2012-04-06 2012-04-06   2012-04-06
   10      4     6  2012      4    7  2012     1 2012-04-06 2012-04-07   2012-04-06
", header = TRUE, stringsAsFactors = FALSE)

Here is the approach that worked for me:

my.data$mean.date <- (my.data$MY.DATE1 + ((my.data$MY.DATE2 - my.data$MY.DATE1) / 2))
my.data

These approaches did not work:

my.data$mean.date <- mean(my.data$MY.DATE1, my.data$MY.DATE2)
my.data$mean.date <- mean(my.data$MY.DATE1, my.data$MY.DATE2, trim = 0)
my.data$mean.date <- mean(my.data$MY.DATE1, my.data$MY.DATE2, trim = 1)
my.data$mean.date <- mean(my.data$MY.DATE1, my.data$MY.DATE2, trim = 0.5)
my.data$mean.data <- apply(my.data, 1, function(x) {(x[9] + x[10]) / 2})

I think I am supposed to use the Ops.Date command, but have not found an example.

Thank you for any suggestions.

Answer 1

Keep things simple and use mean.Date in base R.

mean.Date(as.Date(c("01-01-2014", "01-07-2014"), format=c("%m-%d-%Y"))) 
[1] "2014-01-04"

Answer 2

Using the good advice of @ jaysunice3401, I came up with this. If you want to keep the original data, you can add remove = FALSE in the two lines with unite

library(dplyr)
library(tidyr)

my.data %>%
    unite(whatever1, matches("1"), sep = "-") %>%
    unite(whatever2, matches("2"), sep = "-") %>%
    mutate_each(funs(as.Date(., "%m-%d-%Y")), contains("whatever")) %>%
    rowwise %>%
    mutate(mean.date = mean.Date(c(whatever1, whatever2)))

#   OBS  whatever1  whatever2 STATE  mean.date
#1    1 2012-03-06 2012-03-10     1 2012-03-08
#2    2 2012-03-10 2012-03-20     1 2012-03-15
#3    3 2012-03-16 2012-03-30     1 2012-03-23
#4    4 2012-03-20 2012-04-08     1 2012-03-29
#5    5 2012-03-20 2012-04-09     1 2012-03-30
#6    6 2012-03-20 2012-04-10     1 2012-03-30
#7    7 2012-03-20 2012-04-11     1 2012-03-31
#8    8 2012-04-04 2012-04-05     1 2012-04-04
#9    9 2012-04-06 2012-04-06     1 2012-04-06
#10  10 2012-04-06 2012-04-07     1 2012-04-06