I need to convert my hourly data to daily data by taking the sum

data set :

structure(list(time = structure(c(1506406740, 1506406770, 1506406860,
1506406890, 1506406920, 1506406950, 1506406980, 1506407010, 1506407040,
1506407070), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
Column3 = c(131, 131, 131, 131, 131, 131, 131, 131, 131,
131), m_Pm = c(2.402842, 2.556558, 2.805165, 2.97428, 3.101824,
3.23984, 3.359587, 3.474448, 3.62753, 3.773597)), row.names = c(NA,
-10L), class = c("tbl_df", "tbl", "data.frame"))


It works alright till the first aggregate function . after that I am not able to get the correct answer after the sum function . I'm getting large values instead

library(dplyr)
library(ggplot2)

attach(data)
data %>% #filtering 131 and 132
select(time,Column3,m_Pm) %>%
filter(data,Column3=="131")
filter(data,Column3=="132")
data_131<-filter(data,Column3=="131")
data_132<-filter(data,Column3=="132")

#datehour column (dailyaverage)
data_131$datehour<-format(data_131$time,"%Y-%m-%d %H")
aggregate(m_Pm~datehour,data_131, mean)

#datecolumn

data_131$date1<-format(data_131$time,"%Y-%m-%d")
dE_131<-aggregate(m_Pm~date1,data_131,sum)
dE_131}


Since the data is too huge, I cannot post it here.
It is a 7 months data of every 30 seconds.

dput(head(YourDataSet,10))

tibbletime

1 Answer
1

library(dplyr)
df %>% mutate(dayH= format(time,"%Y-%m-%d %H"), day= format(time,"%Y-%m-%d")) %>%
group_by(dayH) %>% mutate(th=mean(m_Pm)) %>% distinct(dayH, .keep_all = TRUE) %>%
group_by(day) %>% summarise(tday=sum(th))

# A tibble: 1 x 2
day tday
<chr> <dbl>
1 2017-09-26 15.7


df %>% mutate(dayH= format(time,"%Y-%m-%d %H"), day= format(time,"%Y-%m-%d"), month=format(time,"%Y-%m")) %>%
group_by(dayH) %>% mutate(th=mean(m_Pm)) %>% distinct(dayH, .keep_all = TRUE) %>%
group_by(day) %>% mutate(tday=sum(th)) %>% distinct(day, .keep_all = TRUE) %>%
group_by(month) %>% summarise(tmonth=sum(tday))


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.