Dear R-Help, I have 30 of year-based excel files and each file contain month sheets. I have some problem here. My data is daily rainfall but there is extra 1 day (first date of next month) for several sheets. My main goal is to get the minimum value for every month.
First, how to extract those data to list of data frame based on year and delete every overlapping date? Second, how to sort it based on date with ascending order (old to new)? Third, how to get the maximum together with the date? I did this one, ... file.list <- list.files(pattern='*.xlsx') file.list<-mixedsort(file.list) # https://stackoverflow.com/questions/12945687/read-all-worksheets-in-an-excel-workbook-into-an-r-list-with-data-frames read_excel_allsheets <- function(filename, tibble = FALSE) { sheets <- readxl::excel_sheets(filename) x <- lapply(sheets, function(X) read.xlsx(filename, sheet=X, rows=9:40, cols=1:2)) if(!tibble) x <- lapply(x, as.data.frame) names(x) <- sheets x } pon<-lapply(file.list, function(i) read_excel_allsheets(i)) pon1<-do.call("rbind",pon) names(pon1) <- paste0("M.", 1:360) pon1 <-lapply(pon1,function(x){x$RR[x$RR==8888] <- NA; x}) pon1 <-lapply(pon1,function(x){x$RR[x$RR==""] <- NA; x}) maxi<-lapply(pon1, function(x) max(x$RR,na.rm=T)) maxi<-data.frame(Reduce(rbind, maxi)) names(maxi)<-"maxi" .... but the list start from January for every year, and move to February and so on. And there is no date in "maxi". Here some sample what I get from my simple code. > pon1[256:258]$M.256 Tanggal RR 1 01-09-2001 5.2 2 02-09-2001 0.3 3 03-09-2001 29.0 4 04-09-2001 0.7 5 05-09-2001 9.6 6 06-09-2001 0.7 7 07-09-2001 NA 8 08-09-2001 13.2 9 09-09-2001 NA 10 10-09-2001 NA 11 11-09-2001 0.0 12 12-09-2001 66.0 13 13-09-2001 0.0 14 14-09-2001 57.6 15 15-09-2001 18.0 16 16-09-2001 29.2 17 17-09-2001 52.2 18 18-09-2001 7.0 19 19-09-2001 NA 20 20-09-2001 74.5 21 21-09-2001 20.3 22 22-09-2001 49.6 23 23-09-2001 0.0 24 24-09-2001 1.3 25 25-09-2001 0.0 26 26-09-2001 1.0 27 27-09-2001 0.1 28 28-09-2001 1.9 29 29-09-2001 9.5 30 30-09-2001 3.3 31 01-10-2001 0.0 $M.257 Tanggal RR 1 01-09-2002 0.0 2 02-09-2002 0.0 3 03-09-2002 0.0 4 04-09-2002 12.8 5 05-09-2002 1.0 6 06-09-2002 0.0 7 07-09-2002 NA 8 08-09-2002 22.2 9 09-09-2002 NA 10 10-09-2002 NA 11 11-09-2002 0.0 12 12-09-2002 0.0 13 13-09-2002 0.0 14 14-09-2002 NA 15 15-09-2002 0.0 16 16-09-2002 0.0 17 17-09-2002 0.0 18 18-09-2002 13.3 19 19-09-2002 0.0 20 20-09-2002 0.0 21 21-09-2002 0.0 22 22-09-2002 0.0 23 23-09-2002 0.0 24 24-09-2002 0.0 25 25-09-2002 0.0 26 26-09-2002 0.5 27 27-09-2002 2.1 28 28-09-2002 NA 29 29-09-2002 18.5 30 30-09-2002 0.0 31 01-10-2002 NA $M.258 Tanggal RR 1 01-09-2003 0.0 2 02-09-2003 0.0 3 03-09-2003 0.0 4 04-09-2003 4.0 5 05-09-2003 0.3 6 06-09-2003 0.0 7 07-09-2003 NA 8 08-09-2003 0.0 9 09-09-2003 0.0 10 10-09-2003 0.0 11 11-09-2003 NA 12 12-09-2003 1.0 13 13-09-2003 0.0 14 14-09-2003 60.0 15 15-09-2003 4.5 16 16-09-2003 0.1 17 17-09-2003 2.1 18 18-09-2003 NA 19 19-09-2003 0.0 20 20-09-2003 NA 21 21-09-2003 NA 22 22-09-2003 31.5 23 23-09-2003 42.0 24 24-09-2003 43.3 25 25-09-2003 2.8 26 26-09-2003 21.4 27 27-09-2003 0.8 28 28-09-2003 42.3 29 29-09-2003 5.3 30 30-09-2003 17.3 31 01-10-2003 0.0 Any lead or help is very appreciate. Best, Ani [[alternative HTML version deleted]] ______________________________________________ R-help@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.