2

I try to create a weekly cumulative result from this daily data with detail below

date    A   B   C   D   E   F   G   H
16-Jan-22   227 441 3593    2467    9   6   31  2196
17-Jan-22   224 353 3555    2162    31  5   39  2388
18-Jan-22   181 144 2734    2916    0   0   14  1753
19-Jan-22   95  433 3610    3084    42  19  10  2862
20-Jan-22   141 222 3693    3149    183 19  23  2176
21-Jan-22   247 426 3455    4016    68  0   1   2759
22-Jan-22   413 931 4435    4922    184 2   39  3993
23-Jan-22   389 1340    5433    5071    200 48  27  4495
24-Jan-22   281 940 6875    5009    343 47  71  3713
25-Jan-22   314 454 5167    4555    127 1   68  3554
26-Jan-22   315 973 5789    3809    203 1   105 4456
27-Jan-22   269 1217    6776    4578    227 91  17  5373
28-Jan-22   248 1320    5942    3569    271 91  156 4260
29-Jan-22   155 1406    6771    4328    426 44  109 4566
Nad Pat
  • 3,129
  • 3
  • 10
  • 20
Thoriq Al Ayyubi
  • 21
  • 1
  • refer https://stackoverflow.com/questions/60475358/convert-daily-data-into-weekly-data-in-r https://stackoverflow.com/questions/16442396/convert-daily-to-weekly-monthly-data-with-r/16446250 – Nad Pat Feb 11 '22 at 07:37
  • if my data have another colomn, does it work? I mean with a multiple data like date city A city B etc – Thoriq Al Ayyubi Feb 11 '22 at 07:38

2 Answers2

0

Solution using data.table and lubridate

library(lubridate)
library(data.table)

setDT(df)

df[, lapply(.SD, sum), by = isoweek(dmy(date))]

#    isoweek    A    B     C     D    E   F   G     H
# 1:       2  227  441  3593  2467    9   6  31  2196
# 2:       3 1690 3849 26915 25320  708  93 153 20426
# 3:       4 1582 6310 37320 25848 1597 275 526 25922
Merijn van Tilborg
  • 5,452
  • 1
  • 7
  • 22
0

I wanted to provide a solution using the tidyverse principles.

You will need to use the group_by and summarize formulas and this very useful across() function.

#recreate data in tribble
df <- tribble(
~"date",    ~"A",   ~"B",   ~"C",   ~"D",   ~"E",   ~"F",   ~"G",   ~H,
"16-Jan-22",227, 441, 3593,    2467,    9,   6,   31,  2196,
"17-Jan-22",224, 353, 3555,    2162,    31,  5,   39,  2388,
"18-Jan-22",181, 144, 2734,    2916,    0,   0,   14,  1753,
"19-Jan-22",95,  433, 3610,    3084,    42,  19,  10,  2862,
"20-Jan-22",141, 222, 3693,    3149,    183, 19,  23,  2176,
"21-Jan-22",247, 426, 3455,    4016,    68,  0,   1,   2759,
"22-Jan-22",413, 931, 4435,    4922,    184, 2,   39,  3993,
"23-Jan-22",389, 1340,    5433,    5071,    200, 48,  27,  4495,
"24-Jan-22",281, 940, 6875,    5009,    343, 47,  71,  3713,
"25-Jan-22",314, 454, 5167,    4555,    127, 1,   68,  3554,
"26-Jan-22",315, 973, 5789,    3809,    203, 1,   105, 4456,
"27-Jan-22",269, 1217,    6776,    4578,    227, 91,  17,  5373,
"28-Jan-22",248, 1320,    5942,    3569,    271, 91,  156, 4260,
"29-Jan-22",155, 1406,    6771,    4328,    426, 44,  109, 4566)

#change date to format date
df$date <- ymd(df$date)

# I both create a new column "week_num" and group it by this variables
## Then I summarize that for each column except for "date", take each sum
df %>%
  group_by(week_num=lubridate::isoweek(date)) %>% 
  summarize(across(-c("date"),sum))

# I get this results
week_num     A     B     C     D     E     F     G     H
     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1        3  2017  6028 33189 26785   990   243   310 25464
2        4  1482  4572 34639 26850  1324   131   400 23080

Group_by() and summarize() are relatively straight forward. Across() is a fairly new verb that is super powerful. It allows you to reference columns using tidy selection principles (eg. starts_with(), c(1:9), etc) and if you apply it a formula it will allow that formula to each of the selected columns. Less typing!

Alternatively you would have to individually sum each column A=sum(A) which is more typing.

alejandro_hagan
  • 843
  • 2
  • 13