[R] Sum data according to date in sequence
jim holtman
jho|tm@n @end|ng |rom gm@||@com
Fri Nov 3 16:58:18 CET 2023
Is this what you are after?
library(tidyverse)
library(lubridate)
input <- structure(list(StationName = c("PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1", "PALO ALTO CA / CAMBRIDGE #1",
"PALO ALTO CA / CAMBRIDGE #1"), date = c("1/14/2016", "1/14/2016",
"1/14/2016", "1/15/2016", "1/15/2016", "1/15/2016", "1/15/2016",
"1/16/2016", "1/16/2016", "1/16/2016", "1/16/2016", "1/16/2016",
"1/16/2016", "1/16/2016", "1/17/2016", "1/17/2016", "1/17/2016",
"1/17/2016", "1/17/2016", "1/18/2016"), time = c("12:09", "19:50",
"20:22", "8:25", "14:23", "18:17", "21:46", "10:19", "12:12",
"14:12", "16:22", "19:16", "19:19", "20:24", "9:54", "12:16",
"13:53", "19:03", "22:00", "8:58"),
EnergykWh = c(4.680496, 6.272414,
1.032782, 11.004884, 10.096824, 6.658797, 4.808874, 1.469384,
2.996239, 0.303222, 4.988339, 8.131804, 0.117156, 3.285669, 1.175608,
3.677487, 1.068393, 8.820755, 8.138583, 9.0575)),
row.names = c(NA, 20L), class = "data.frame")
# convert date from character to Date
byDate <- input |>
mutate(newdate = mdy(date)) |>
group_by(newdate) |>
summarise(total = sum(EnergykWh))
byDate
## # A tibble: 5 × 2
## newdate total
## <date> <dbl>
## 1 2016-01-14 12.0
## 2 2016-01-15 32.6
## 3 2016-01-16 21.3
## 4 2016-01-17 22.9
## 5 2016-01-18 9.06
Thanks
Jim Holtman
*Data Munger Guru*
*What is the problem that you are trying to solve?Tell me what you want to
do, not how you want to do it.*
On Fri, Nov 3, 2023 at 2:51 AM roslinazairimah zakaria <roslinaump using gmail.com>
wrote:
> Hi,
> I tried this:
> # extract date from the time stamp
> dt1 <- cbind(as.Date(dt$EndDate, format="%m/%d/%Y"), dt$EnergykWh)
> head(dt1)
> colnames(dt1) <- c("date", "EnergykWh")
> and
> my dt1 becomes these, the dates are replace by numbers.
>
> dt1 <- cbind(as.Date(dt$EndDate, format="%m/%d/%Y"), dt$EnergykWh)
> dput(head(dt1))
> colnames(dt1) <- c("date", "EnergykWh")
> dput(head(dt1))
>
>
> > dput(head(dt1))structure(c(16814, 16814, 16814, 16815, 16815, 16815,
> 4.680496,
> 6.272414, 1.032782, 11.004884, 10.096824, 6.658797), dim = c(6L,
> 2L), dimnames = list(NULL, c("date", "EnergykWh")))
>
> Then I tried this:
> library(dplyr)
> dt1 %>%
> group_by(date) %>%
> summarise(EnergykWh.sum = sum(EnergykWh))
> and got this errors
>
> dt1 %>%+ group_by(date) %>%+ summarise(EnergykWh.sum =
> sum(EnergykWh))Error in UseMethod("group_by") :
> no applicable method for 'group_by' applied to an object of class
> "c('matrix', 'array', 'double', 'numeric')"
>
>
>
> On Fri, Nov 3, 2023 at 7:23 AM roslinazairimah zakaria <
> roslinaump using gmail.com>
> wrote:
>
> > Dear all,
> >
> > I have this set of data. I would like to sum the EnergykWh according date
> > sequences.
> >
> > > head(dt1,20) StationName date time EnergykWh
> > 1 PALO ALTO CA / CAMBRIDGE #1 1/14/2016 12:09 4.680496
> > 2 PALO ALTO CA / CAMBRIDGE #1 1/14/2016 19:50 6.272414
> > 3 PALO ALTO CA / CAMBRIDGE #1 1/14/2016 20:22 1.032782
> > 4 PALO ALTO CA / CAMBRIDGE #1 1/15/2016 8:25 11.004884
> > 5 PALO ALTO CA / CAMBRIDGE #1 1/15/2016 14:23 10.096824
> > 6 PALO ALTO CA / CAMBRIDGE #1 1/15/2016 18:17 6.658797
> > 7 PALO ALTO CA / CAMBRIDGE #1 1/15/2016 21:46 4.808874
> > 8 PALO ALTO CA / CAMBRIDGE #1 1/16/2016 10:19 1.469384
> > 9 PALO ALTO CA / CAMBRIDGE #1 1/16/2016 12:12 2.996239
> > 10 PALO ALTO CA / CAMBRIDGE #1 1/16/2016 14:12 0.303222
> > 11 PALO ALTO CA / CAMBRIDGE #1 1/16/2016 16:22 4.988339
> > 12 PALO ALTO CA / CAMBRIDGE #1 1/16/2016 19:16 8.131804
> > 13 PALO ALTO CA / CAMBRIDGE #1 1/16/2016 19:19 0.117156
> > 14 PALO ALTO CA / CAMBRIDGE #1 1/16/2016 20:24 3.285669
> > 15 PALO ALTO CA / CAMBRIDGE #1 1/17/2016 9:54 1.175608
> > 16 PALO ALTO CA / CAMBRIDGE #1 1/17/2016 12:16 3.677487
> > 17 PALO ALTO CA / CAMBRIDGE #1 1/17/2016 13:53 1.068393
> > 18 PALO ALTO CA / CAMBRIDGE #1 1/17/2016 19:03 8.820755
> > 19 PALO ALTO CA / CAMBRIDGE #1 1/17/2016 22:00 8.138583
> > 20 PALO ALTO CA / CAMBRIDGE #1 1/18/2016 8:58 9.057500
> >
> > I have tried this:
> > library(dplyr)
> > sums <- dt1 %>%
> > group_by(date) %>%
> > summarise(EnergykWh = sum(EnergykWh))
> >
> > head(sums,20)
> >
> > The date is not by daily sequence but by year sequence.
> >
> > > head(sums,20)# A tibble: 20 × 2
> > date EnergykWh
> > <chr> <dbl> 1 1/1/2017 25.3 2 1/1/2018 61.0 3
> 1/1/2019 0.627 4 1/1/2020 10.7 5 1/10/2017 69.4 6
> 1/10/2018 54.5 7 1/10/2019 49.1 8 1/10/2020 45.9 9
> 1/11/2017 73.9 10 1/11/2018 53.3 11 1/11/2019 93.5 12
> 1/11/2020 66.7 13 1/12/2017 78.6 14 1/12/2018 42.2 15
> 1/12/2019 22.7 16 1/12/2020 80.9 17 1/13/2017 85.6 18
> 1/13/2018 46.4 19 1/13/2019 40.0 20 1/13/2020 121.
> >
> >
> >
> > Thank you very much for any help given.
> >
> >
> > --
> > *Roslinazairimah Zakaria*
> > *Tel: +609-5492370; Fax. No.+609-5492766*
> >
> > *Email: roslinazairimah using ump.edu.my <roslinazairimah using ump.edu.my>;
> > roslinaump using gmail.com <roslinaump using gmail.com>*
> > Faculty of Industrial Sciences & Technology
> > University Malaysia Pahang
> > Lebuhraya Tun Razak, 26300 Gambang, Pahang, Malaysia
> >
>
>
> --
> *Roslinazairimah Zakaria*
> *Tel: +609-5492370; Fax. No.+609-5492766*
>
> *Email: roslinazairimah using ump.edu.my <roslinazairimah using ump.edu.my>;
> roslinaump using gmail.com <roslinaump using gmail.com>*
> Faculty of Industrial Sciences & Technology
> University Malaysia Pahang
> Lebuhraya Tun Razak, 26300 Gambang, Pahang, Malaysia
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
[[alternative HTML version deleted]]
More information about the R-help
mailing list