CODEWITHSUNDEEP
rtime-seriesaggregate-functions
user3664020
user3664020

Reputation: 3020

Aggregate data to weekly level with every week starting from Monday

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.

Upvotes: 7

Views: 3839

Answers (3)

Bertil Baron
Bertil Baron

Reputation: 5003

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))

Upvotes: 2

cory
cory

Reputation: 6659

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)

Upvotes: 2

Akhil Nair
Akhil Nair

Reputation: 3274

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.

Upvotes: 2

Related Questions