-5

I have data with hourly. I need to convert into daily, monthly and then in the annual.

Also, some dates are missing in that, So i want to include that as well.

#Date
24/02/2000/05:25:00 NaN NaN NaN

26/02/2000/05:10:00 0.227 0.2002496 0.2009378 26/02/2000/06:50:00 NaN NaN NaN 27/02/2000/05:55:00 0.21 0.1687891 0.1630572 28/02/2000/05:00:00 NaN NaN 0.1265696 28/02/2000/06:35:00 0.136 0.1446176 0.1479067 29/02/2000/05:40:00 0.293 0.2279881 0.1900514 01/03/2000/04:45:00 NaN NaN NaN 01/03/2000/06:25:00 0.322 0.3068518 0.2880579 02/03/2000/05:30:00 0.332 0.2793714 0.2391622 02/03/2000/07:05:00 NaN NaN NaN 03/03/2000/06:10:00 0.335 0.2151302 0.2218139 04/03/2000/05:15:00 0.1 0.1138773 0.1168912 04/03/2000/06:55:00 NaN NaN NaN 05/03/2000/06:00:00 0.117 0.1333082 0.147145 06/03/2000/05:05:00 NaN 0.2426362 0.2401871 06/03/2000/06:40:00 NaN 0.32587 0.2845067 07/03/2000/05:45:00 0.323 0.3143821 0.3096662 08/03/2000/04:50:00 NaN NaN NaN 08/03/2000/06:30:00 0.236 0.23232 0.2300107 10/03/2000/06:20:00 0.113 0.1429935 0.1453774 11/03/2000/05:25:00 0.276 0.3238274 0.3150585 11/03/2000/07:00:00 NaN NaN NaN 12/03/2000/06:05:00 0.215 0.2537585 0.2512374 13/03/2000/05:10:00 0.163 0.2273455 0.2679352 13/03/2000/06:50:00 NaN NaN NaN 14/03/2000/05:55:00 0.09 0.1311507 0.1761056 15/03/2000/05:00:00 NaN NaN 0.1447348 15/03/2000/06:35:00 0.125 0.1232291 0.1387782 16/03/2000/05:40:00 0.019 0.06970426 0.11602 17/03/2000/04:45:00 NaN NaN NaN 17/03/2000/06:25:00 0.194 0.1964414 0.1874403 18/03/2000/05:30:00 0.263 0.2749394 0.242199 18/03/2000/07:05:00 NaN NaN NaN 19/03/2000/06:10:00 0.217 0.217737 0.2183706 20/03/2000/05:15:00 0.253 0.2307511 0.2089891 20/03/2000/06:55:00 NaN NaN NaN 21/03/2000/06:00:00 0.282 0.2413632 0.2511235 22/03/2000/05:05:00 NaN 0.382685 0.3944636 22/03/2000/06:45:00 NaN 0.2734097 0.241442 23/03/2000/05:50:00 0.347 0.3289219 0.3003848 24/03/2000/04:50:00 NaN NaN NaN 24/03/2000/06:30:00 0.18 0.1892378 0.2021516 25/03/2000/05:35:00 0.216 0.1871835 0.206762 26/03/2000/06:20:00 0.189 0.1836237 0.2116453 27/03/2000/05:25:00 0.195 0.1817446 0.1804464 27/03/2000/07:00:00 NaN NaN NaN 28/03/2000/06:05:00 0.208 0.168515 0.1819115 29/03/2000/05:10:00 0.162 0.1598227 0.1689523 29/03/2000/06:50:00 NaN NaN NaN 30/03/2000/05:55:00 0.145 0.1472181 0.1723774 31/03/2000/05:00:00 NaN NaN 0.157723 31/03/2000/06:35:00 0.226 0.2108984 0.2339231

user3473499
  • 23
  • 9
  • Please post wanted result, what do you mean by "daily", "monthly"? – pogibas Oct 29 '17 at 06:07
  • I mean, I want to get single value of each day. Some dates e.g. 20/03/2000 have 2 values at 2 different hour. Now I want to do the average of it. So that I can get one single value of each day. – user3473499 Oct 29 '17 at 06:16
  • What have you tried so far? Please produce a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – Kevin Arseneau Oct 29 '17 at 06:17
  • For monthly and annual calculation I tried file$dates <- with(file,as.POSIXct(date,format="%d/%m/%Y")) df <- transform(file,month=as.numeric(format(dates,"%m")),year=as.numeric(format(dates,"%Y"))) ddply(df,.(month),summarize,meaniiserb=mean(IISERB),meaniiser100=mean(IISER_100KM),meaniiser200=mean(IISER_200KM), na.rm=T) – user3473499 Oct 29 '17 at 06:29
  • Average of what? Do you need the average of the hours in the day?? Or?? – Onyambu Oct 29 '17 at 07:05
  • Can you post the results you expect from the data above? – Onyambu Oct 29 '17 at 07:21

2 Answers2

0

I guess you are talking about spliting your date variable in year, month, and day and then you want to calculate some grouping statistics of another varibale which you did not include in your example. If that is the case you could do the following: 

# load package 
library(dplyr)

#Date
 Date <- data.frame( Date =strptime(c("24/02/2000/05:25:00", 
"26/02/2000/05:10:00",
"26/02/2000/06:50:00",
"27/02/2000/05:56:00",
"28/02/2000/05:00:00",
"28/02/2000/06:35:00",
"29/02/2000/05:40:00",
"01/03/2000/04:45:00",
"01/03/2000/06:25:00",
"02/03/2000/05:30:00",
"02/03/2000/07:05:00",
"03/03/2000/06:10:00",
"04/03/2000/05:15:00",
"04/03/2000/06:55:00",
"05/03/2000/06:00:00",
"06/03/2000/05:05:00",
"06/03/2000/06:40:00",
"07/03/2000/05:45:00",
"08/03/2000/04:50:00",
"08/03/2000/06:30:00",
"10/03/2000/06:20:00",
"11/03/2000/05:25:00",
"11/03/2000/07:00:00",
"12/03/2000/06:05:00",
"13/03/2000/05:10:00",
"13/03/2000/06:50:00",
"14/03/2000/05:55:00",
"15/03/2000/05:00:00",
"15/03/2000/06:35:00",
"16/03/2000/05:40:00",
"17/03/2000/04:45:00",
"17/03/2000/06:25:00",
"18/03/2000/05:30:00",
"18/03/2000/07:05:00",
"19/03/2000/06:10:00",
"20/03/2000/05:15:00",
"20/03/2000/06:55:00",
"21/03/2000/06:00:00",
"22/03/2000/05:05:00",
"22/03/2000/06:45:00",
"23/03/2000/05:50:00",
"24/03/2000/04:50:00",
"24/03/2000/06:30:00",
"25/03/2000/05:35:00",
"26/03/2000/06:20:00",
"27/03/2000/05:25:00",
"27/03/2000/07:00:00",
"28/03/2000/06:05:00",
"29/03/2000/05:10:00",
"29/03/2000/06:50:00",
"30/03/2000/05:55:00",
"31/03/2000/05:00:00",
"31/03/2000/06:35:00"), format = "%d/%m/%Y/%H:%M:%S"))


# Split your Date variable in days, months, and years

Date[,"Year"] <- format(Date$Date, format = "%Y")
Date[,"Month"] <- format(Date$Date, format = "%m")
Date[,"Day"] <- format(Date$Date, format = "%d")

# Make up some random variable to calculate summary statistics on

Date[,"Random"] <- sample(seq(1,7,1),size=dim(Date)[1], replace = TRUE)

# Now you can calculate grouped statistics by day, month, or year

MonthMean <- Date %>%
                group_by(Month) %>%
                select(Month, Random) %>%
                summarise(Mean = mean(Random))

# Output 
# A tibble: 2 × 2
  Month     Mean
  <chr>    <dbl>
1    02 3.142857
2    03 4.217391
FAMG
  • 395
  • 1
  • 9
  • Thanks for providing this code, I also have variable, named as (IISERB) in 2 column. So how to get data with date. I assign dates as `as.character(file$Date) -> file$date` `time <- as.Date( file$date, "%d/%m/%Y")` – user3473499 Oct 29 '17 at 11:09
  • You are welcoome! Please edit your first post. It seems as if you have unquoted the source code. Please do not forget to mark the question as answered. For the next time please provide some reproducible example: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – FAMG Oct 29 '17 at 13:16
0

I have splited the data in Day,Month and Year then calculated Daymean, Monthlymean and Annualmean

using code:

# open the file
file1 <-read.table(file.choose(), header=T)
# View the content of the file
View(file1)

# assign the date
as.character(file1$Date) -> file1$date
time <- as.Date( file1$date, "%d/%m/%Y")

# seperate the day, month, year
file1[,"Year"] <- format(time,  format = "%Y")
file1[,"Month"] <- format(time, format = "%m")
file1[,"Day"] <- format(time, format = "%d")

# to see the updates file
View(file1)

# avearaging the dayily mean then same as month wise 
aggregate(file1[, 2:4], list(file$Day), mean, na.rm=T)
user3473499
  • 23
  • 9