CODEWITHSUNDEEP
rdate
Akula135
Akula135

Reputation: 3

Converting days into weeks in R

I want to convert days into weeks with all the values from that week summed up

Right now I have the following df

          Date  x
1   2018-02-23 15
2   2018-03-26  4
3   2018-03-29  3
4   2018-03-30  6
5   2018-04-03  5
6   2018-04-04 12
7   2018-04-05  7
8   2018-04-06  5
9   2018-04-07  5
10  2018-04-09 13
11  2018-04-10  8
12  2018-04-11  2

ETC.

The x in this df stands for amount of items sent on a certain day. There are days in this df where there are no items beeing transported. This df has a total of 688 tuples.

What I would like to see it:

           Date   x
1   Week 8 2018  19
2   Week 9 2018  26
3   Week 10 2018  33

ETC.

Can someone help me out?

Upvotes: 0

Views: 970

Answers (4)

GKi
GKi

Reputation: 39667

You can use aggregate and get the weeks with format %V:

aggregate(df$x, list(Date=format(df$Date, "%V %Y")), sum)
#     Date  x
#1 08 2018 15
#2 13 2018 13
#3 14 2018 34
#4 15 2018 23

Or with Week (Thanks to @sindri-baldur for the comment):

aggregate(df$x, list(Date=sub("^0?", "Week ", format(df$Date, "%V %Y"))), sum)
#aggregate(df$x, list(Date=format(df$Date, "Week %-V %Y")), sum) #Alternative
#          Date  x
#1 Week 13 2018 13
#2 Week 14 2018 34
#3 Week 15 2018 23
#4  Week 8 2018 15

Data:

df <- read.table(header=TRUE, text="          Date  x
1   2018-02-23 15
2   2018-03-26  4
3   2018-03-29  3
4   2018-03-30  6
5   2018-04-03  5
6   2018-04-04 12
7   2018-04-05  7
8   2018-04-06  5
9   2018-04-07  5
10  2018-04-09 13
11  2018-04-10  8
12  2018-04-11  2")
df$Date <- as.Date(df$Date)

Upvotes: 1

Akula135
Akula135

Reputation: 3

@akrun This almost worked. Right now I get 52 rows out of 3 years of data:

   week total_per_week
1     1            246
2     2            491
3     3            458
4     4            556
5     5            454
6     6            594
7     7            592
8     8            496
9     9            567
10   10            615

Upvotes: 0

akrun
akrun

Reputation: 887148

Using collapse

library(collapse)
library(lubridate)
library(magrittr)
df %>% 
   ftransform(week = week(ymd(Date))) %>% 
   fgroup_by(week) %>% 
   fsummarise(total_per_week = fsum(x))
#  week total_per_week
#1    8             15
#2   13             13
#3   14             34
#4   15             23

data

df <- structure(list(Date = c("2018-02-23", "2018-03-26", "2018-03-29", 
"2018-03-30", "2018-04-03", "2018-04-04", "2018-04-05", "2018-04-06", 
"2018-04-07", "2018-04-09", "2018-04-10", "2018-04-11"), x = c(15L, 
4L, 3L, 6L, 5L, 12L, 7L, 5L, 5L, 13L, 8L, 2L)), class = "data.frame", 
row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"))

Upvotes: 0

A.Chrlt
A.Chrlt

Reputation: 316

library(lubridate)
library(tidyverse)

## Random data
df <- data.frame(date=seq.Date(from = as.Date("2018-01-01"), to=as.Date("2018-12-31"), by = "day"),x=runif(n=365,min=0,max=25))


## Aggregating by week
df2 <- df %>%
mutate(week = lubridate::week(ymd(date))) %>%
  group_by(week) %>%
  summarise(total_per_week = sum(x))

Upvotes: 0

Related Questions