manipulating dates in R

dates.r

# HELPER FUNCTIONS
as.week_of_month <- function(d) {
  day <-format(d,"%d")
  val <- ((as(day,"numeric")-1)/7)+1
  val <- as(val,"integer")
  val <- as(val,"character")
}


# then to convert to a date use
dates <- strptime(row.names(x),"%B %d, %Y")

# and to extract formatted parts of a date use
week <- format(strptime(row.names(x),"%B %d, %Y"),"%U") #US convention, not UK convention
day <- format(strptime(row.names(x),"%B %d, %Y"),"%a")
month <- format(strptime(row.names(x),"%B %d, %Y"),"%b")

# x is a data frame 
# and row.names(x) is a date format such as
#  [1] "January 5, 2009"    "January 6, 2009"    "January 7, 2009"    "January 8, 2009"    "January 9, 2009"    "January 12, 2009"   

x <- read.csv("inputs/call_volumes.csv", row.names=1)


# DAY OF WEEK
# append day (Mon, TUe, Wed, Thu,Fri) to each daily total
daily <- data.frame(total=rowSums(x), day = format(strptime(row.names(x),"%B %d, %Y"),"%a"))
# summarize to day counts, totals and avg in two steps
day_of_week <- data.frame(count=summary(daily$day), total=rowsum(daily$total, daily$day))
day_of_week <- data.frame(day_of_week, avg=day_of_week$total/day_of_week$count)

# WEEK OF YEAR
weekly <- data.frame(total=rowSums(x), week = format(strptime(row.names(x),"%B %d, %Y"),"%U"))
week_of_year <- data.frame(count=summary(weekly$week), total=rowsum(weekly$total, weekly$week))
week_of_year <- data.frame(week_of_year, avg=week_of_year$total/week_of_year$count)

# MONTH OF YEAR
monthly <- data.frame(total=rowSums(x), month = format(strptime(row.names(x),"%B %d, %Y"),"%b"))
month_of_year <- data.frame(count=summary(monthly$month), total=rowsum(monthly$total, monthly$month))
month_of_year <- data.frame(month_of_year, avg=month_of_year$total/month_of_year$count)

# DAY OF MONTH
day_monthly <- data.frame(total=rowSums(x), day_of_month = format(strptime(row.names(x),"%B %d, %Y"),"%d"))
day_of_month <- data.frame(count=summary(day_monthly$day_of_month), total=rowsum(day_monthly$total, day_monthly$day_of_month))
day_of_month <- data.frame(day_of_month, avg=day_of_month$total/day_of_month$count)

# WEEK OF MONTH
week_monthly <- data.frame(total=rowSums(x), week_of_month = as.week_of_month(strptime(row.names(x),"%B %d, %Y")))
week_of_month <- data.frame(count=summary(week_monthly$week_of_month), total=rowsum(week_monthly$total, week_monthly$week_of_month))
week_of_month <- data.frame(week_of_month, avg=week_of_month$total/week_of_month$count)

# UPDATE DAILY TOTALS WITH EFFECTS
# use match to get averages for all observations => day_of_week$avg[match(daily$day, row.names(day_of_week))]
# now add day averages to daily
daily <- data.frame(daily,avg=day_of_week$avg[match(daily$day, row.names(day_of_week))])


# d1 <- data.frame(total=rowSums(x), day = format(strptime(row.names(x),"%B %d, %Y"),"%a"), week = format(strptime(row.names(x),"%B %d, %Y"),"%U"), month = format(strptime(row.names(x),"%B %d, %Y"),"%b"), day_of_month = format(strptime(row.names(x),"%B %d, %Y"),"%d"),week_of_month = as.week_of_month(strptime(row.names(x),"%B %d, %Y")))


# CONFORM dates to strf i.e two digit years strftime=> (strptime(row.names(daily),"%B %d, %Y"),"%B %d, %Y")
FORMATTED_DATES <- strftime(strptime(row.names(daily),"%B %d, %Y"),"%B %d, %Y")

# HOLIDAY EFFECTS

get_holiday_dates <- function(holiday) {
	h <- strptime(holiday, "%B %d, %Y")
	
	offset <- 7 * 24 * 60 * 60 # 7 days in secs
	before <- h -offset
	after <- h +offset
	
	c(strftime(before, "%B %d, %Y"),strftime(after, "%B %d, %Y"))
}

hols <- match(get_holiday_dates("December 28, 2009"), FORMATTED_DATES)
dec_28_09 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("October 13, 2009"), FORMATTED_DATES)
oct_13_09 = mean(daily$total[hols],na.rm = T)
 
hols <- match(get_holiday_dates("January 4, 2010"), FORMATTED_DATES)
jan_4_10 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("April 6, 2010"), FORMATTED_DATES)
apr_6_10 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("May 25, 2010"), FORMATTED_DATES)
may_25_10 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("June 25, 2010"), FORMATTED_DATES)
jun_25_10 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("July 2, 2010"), FORMATTED_DATES)
jul_2_10 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("September 7, 2010"), FORMATTED_DATES)
sep_7_10 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("January 5, 2009"), FORMATTED_DATES)
jan_5_09 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("April 14, 2009"), FORMATTED_DATES)
apr_14_09 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("May 19, 2009"), FORMATTED_DATES)
may_19_09 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("June 25, 2009"), FORMATTED_DATES)
jun_25_09 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("July 2, 2009"), FORMATTED_DATES)
jul_2_09 = mean(daily$total[hols],na.rm = T)

hols <- match(get_holiday_dates("September 8, 2009"), FORMATTED_DATES)
sep_8_09 = mean(daily$total[hols],na.rm = T)



holiday_avgs = c(dec_28_09,oct_13_09,jan_4_10,apr_6_10,may_25_10,jun_25_10,jul_2_10,sep_7_10,jan_5_09,apr_14_09,may_19_09,jun_25_09,jul_2_09,sep_8_09)

holidays = c("December 28, 2009","October 13, 2009","January 4, 2010","April 6, 2010","May 25, 2010","June 25, 2010","July 2, 2010","September 7, 2010","January 5, 2009","April 14, 2009","May 19, 2009","June 25, 2009","July 2, 2009","September 8, 2009")

hols <- data.frame(avg = holiday_avgs, row.names=holidays)
daily$total[match(row.names(hols),row.names(daily))] <- hols$avg