[R] data load from excel files

[ani jaya]

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]]