Hello,
Maybe the following will get you close to what you want.
# remove the last row from every df
pon1 <- lapply(pon1, function(DF){
DF[[1]] <- as.Date(DF[["Tanggal"]], "%d-%m-%Y")
DF[-nrow(DF), ]
})
# order the list by year-month
inx_ym <- sapply(pon1, function(DF){
format(DF[["Tanggal"]][1], "%Y-%m")
})
pon1 <- pon1[order(inx_ym)]
# get the minimum and maximum of every "RR"
min.RR <- sapply(pon1, function(DF) min(DF[["RR"]], na.rm = TRUE))
max.RR <- sapply(pon1, function(DF) max(DF[["RR"]], na.rm = TRUE))
Hope this helps,
Rui Barradas
Às 07:50 de 13/11/19, ani jaya escreveu:
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.
______________________________________________
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.
