Aggregate data to weekly level with every week starting from Monday

7
votes

I have a data frame like,

2015-01-30     1       Fri
2015-01-30     2       Sat
2015-02-01     3       Sun
2015-02-02     1       Mon
2015-02-03     1       Tue
2015-02-04     1       Wed 
2015-02-05     1       Thu
2015-02-06     1       Fri
2015-02-07     1       Sat
2015-02-08     1       Sun

I want to aggregaate it to weekly level such that every week starts from "monday" and ends in "sunday". So, in the aggregated data for above, first week should end on 2015-02-01.

output should look like something for above

firstweek    6  
secondweek   7

I tried this,

data <- as.xts(data$value,order.by=as.Date(data$interval))
weekly <- apply.weekly(data,sum)

But here in the final result, every week is starting from Sunday.

3
rtime-seriesaggregate-functions
Maybe aggregate(V2 ~ cumsum(V3 == "Mon"), df, sum)? – David Arenburg
This link may help you as well stackoverflow.com/questions/13944838/… – akrun
Check strptime; %V weeks start on mondays. – Henrik

3 Answers

2
votes

This should work. I've called the dataframe m and named the columns possibly different to yours. 

library(plyr) # install.packages("plyr")

colnames(m) = c("Date", "count","Day")
start  = as.Date("2015-01-26")
m$Week <- floor(unclass(as.Date(m$Date) - as.Date(start)) / 7) + 1
m$Week = as.numeric(m$Week)
m %>% group_by(Week) %>% summarise(count = sum(count))

The library plyr is great for data manipulation, but it's just a rough hack to get the week number in.

2
votes

Convert to date and use the %W format to get a week number...

df <- read.csv(textConnection("2015-01-30,     1,       Fri,
2015-01-30,     2,       Sat,
2015-02-01,     3,       Sun,
2015-02-02,     1,       Mon,
2015-02-03,     1,       Tue,
2015-02-04,     1,       Wed,
2015-02-05,     1,       Thu,
2015-02-06,     1,       Fri,
2015-02-07,     1,       Sat,
2015-02-08,     1,       Sun"), header=F, stringsAsFactors=F)
names(df) <- c("date", "something", "day")
df$date <- as.Date(df$date, format="%Y-%m-%d")
df$week <- format(df$date, "%W")
aggregate(df$something, list(df$week), sum)
2
votes

Wit dplyr and lubridate is this really easy thanks to the function isoweek

my.df <- read.table(header=FALSE, text=
  '2015-01-30     1       Fri
   2015-01-30     2       Sat
   2015-02-01     3       Sun
   2015-02-02     1       Mon
   2015-02-03     1       Tue
   2015-02-04     1       Wed 
   2015-02-05     1       Thu
   2015-02-06     1       Fri
   2015-02-07     1       Sat
   2015-02-08     1       Sun')
 my.df %>% mutate(week = isoweek(V1)) %>% group_by(week) %>% summarise(sum(V2))

or a bit shorter

my.df %>% group_by(isoweek(V1)) %>% summarise(sum(V2))