Dear R-Help,
I have a list of data frame that I import from excel file using read.xlsx
command.
sheets <- openxlsx::getSheetNames("rainfall.xlsx")
test <- lapply(sheets,function(i) read.xlsx("rainfall.xlsx",
sheet=i,
startRow=8, cols=1:2))
names(test) <- sprintf("%i", 1986:2015)
And I got a data.frame with 365 rows and two columns contains date and
rainfall data.
There is a value in rainfall data that i want to change as NA (8888)
test[1]$`1986`
Date RR
1 01-01-1986 0
2 02-01-1986 7
3 03-01-1986 72
4 04-01-1986 4
5 05-01-1986 19
6 06-01-1986 4
7 07-01-1986 16
8 08-01-1986 21
9 09-01-1986 34
10 10-01-1986 72
11 11-01-1986 93
12 12-01-1986 178
13 13-01-1986 86
14 14-01-1986 11
15 15-01-1986 0
16 16-01-1986 31
17 17-01-1986 22
18 18-01-1986 18
19 19-01-1986 3
20 20-01-1986 0
21 21-01-1986 31
22 22-01-1986 46
23 23-01-1986 4
24 24-01-1986 40
25 25-01-1986 63
26 26-01-1986 125
27 27-01-1986 33
28 28-01-1986 44
29 29-01-1986 6
30 30-01-1986 0
31 31-01-1986 0
32 01-02-1986 2
33 02-02-1986 71
34 03-02-1986 8888
35 04-02-1986 0
36 05-02-1986 0
37 06-02-1986 56
38 07-02-1986 19
39 08-02-1986 3
40 09-02-1986 7
41 10-02-1986 24
42 11-02-1986 55
43 12-02-1986 0
44 13-02-1986 0
[[alternative HTML version deleted]]
I'm a little unclear, but maybe ?is.na . As in:> x <- c(1:3,8888) > x[1] 1 2 3 8888> is.na(x) <- x==8888 ## rhs is an "index vector" of logicals > x[1] 1 2 3 NA Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Thu, Oct 17, 2019 at 6:38 PM ani jaya <gaaauul at gmail.com> wrote:> Dear R-Help, > > I have a list of data frame that I import from excel file using read.xlsx > command. > > sheets <- openxlsx::getSheetNames("rainfall.xlsx") > test <- lapply(sheets,function(i) read.xlsx("rainfall.xlsx", sheet=i, > startRow=8, cols=1:2)) > names(test) <- sprintf("%i", 1986:2015) > > And I got a data.frame with 365 rows and two columns contains date and > rainfall data. > There is a value in rainfall data that i want to change as NA (8888) > > test[1]$`1986` > Date RR > 1 01-01-1986 0 > 2 02-01-1986 7 > 3 03-01-1986 72 > 4 04-01-1986 4 > 5 05-01-1986 19 > 6 06-01-1986 4 > 7 07-01-1986 16 > 8 08-01-1986 21 > 9 09-01-1986 34 > 10 10-01-1986 72 > 11 11-01-1986 93 > 12 12-01-1986 178 > 13 13-01-1986 86 > 14 14-01-1986 11 > 15 15-01-1986 0 > 16 16-01-1986 31 > 17 17-01-1986 22 > 18 18-01-1986 18 > 19 19-01-1986 3 > 20 20-01-1986 0 > 21 21-01-1986 31 > 22 22-01-1986 46 > 23 23-01-1986 4 > 24 24-01-1986 40 > 25 25-01-1986 63 > 26 26-01-1986 125 > 27 27-01-1986 33 > 28 28-01-1986 44 > 29 29-01-1986 6 > 30 30-01-1986 0 > 31 31-01-1986 0 > 32 01-02-1986 2 > 33 02-02-1986 71 > 34 03-02-1986 8888 > 35 04-02-1986 0 > 36 05-02-1986 0 > 37 06-02-1986 56 > 38 07-02-1986 19 > 39 08-02-1986 3 > 40 09-02-1986 7 > 41 10-02-1986 24 > 42 11-02-1986 55 > 43 12-02-1986 0 > 44 13-02-1986 0 > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at 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. >[[alternative HTML version deleted]]
Thank you Mr. Bert, but my data frame is in the list, here 'test' list of data frame have 30 data frames (elements), names '1986' ~ '2015', and each data frame contain two variables, date and R.>a2<-rbind(test$`1987`) >is.na(a2$RR)<- a2$RR==8888Above is good enough but only for '1987'. Is it possible to do that in looping for the rest of dataframe? Best, Saat M. On Fri, Oct 18, 2019 at 10:47 AM Bert Gunter <bgunter.4567 at gmail.com> wrote:> I'm a little unclear, but maybe ?is.na . > > As in: > > > x <- c(1:3,8888) > > x > [1] 1 2 3 8888 > > is.na(x) <- x==8888 ## rhs is an "index vector" of logicals > > x > [1] 1 2 3 NA > > > Bert Gunter > > "The trouble with having an open mind is that people keep coming along and > sticking things into it." > -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) > > > On Thu, Oct 17, 2019 at 6:38 PM ani jaya <gaaauul at gmail.com> wrote: > >> Dear R-Help, >> >> I have a list of data frame that I import from excel file using read.xlsx >> command. >> >> sheets <- openxlsx::getSheetNames("rainfall.xlsx") >> test <- lapply(sheets,function(i) read.xlsx("rainfall.xlsx", sheet=i, >> startRow=8, cols=1:2)) >> names(test) <- sprintf("%i", 1986:2015) >> >> And I got a data.frame with 365 rows and two columns contains date and >> rainfall data. >> There is a value in rainfall data that i want to change as NA (8888) >> >> test[1]$`1986` >> Date RR >> 1 01-01-1986 0 >> 2 02-01-1986 7 >> 3 03-01-1986 72 >> 4 04-01-1986 4 >> 5 05-01-1986 19 >> 6 06-01-1986 4 >> 7 07-01-1986 16 >> 8 08-01-1986 21 >> 9 09-01-1986 34 >> 10 10-01-1986 72 >> 11 11-01-1986 93 >> 12 12-01-1986 178 >> 13 13-01-1986 86 >> 14 14-01-1986 11 >> 15 15-01-1986 0 >> 16 16-01-1986 31 >> 17 17-01-1986 22 >> 18 18-01-1986 18 >> 19 19-01-1986 3 >> 20 20-01-1986 0 >> 21 21-01-1986 31 >> 22 22-01-1986 46 >> 23 23-01-1986 4 >> 24 24-01-1986 40 >> 25 25-01-1986 63 >> 26 26-01-1986 125 >> 27 27-01-1986 33 >> 28 28-01-1986 44 >> 29 29-01-1986 6 >> 30 30-01-1986 0 >> 31 31-01-1986 0 >> 32 01-02-1986 2 >> 33 02-02-1986 71 >> 34 03-02-1986 8888 >> 35 04-02-1986 0 >> 36 05-02-1986 0 >> 37 06-02-1986 56 >> 38 07-02-1986 19 >> 39 08-02-1986 3 >> 40 09-02-1986 7 >> 41 10-02-1986 24 >> 42 11-02-1986 55 >> 43 12-02-1986 0 >> 44 13-02-1986 0 >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help at 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. >> >[[alternative HTML version deleted]]
Hi ani,
You say you want to replace 8888 with NA, so:
# it will be easier if you don't use numbers for the names of the data
frames
names(test) <- paste0("Y",1986:2015)
makeNA(x)<-function(x,varname,value) {
x[,varname][x[,varname]<-value]<-NA
return(x)
}
lapply(test,makeNA,list("RR",8888))
Warning - untested
Jim
On Fri, Oct 18, 2019 at 12:38 PM ani jaya <gaaauul at gmail.com>
wrote:>
> Dear R-Help,
>
> I have a list of data frame that I import from excel file using read.xlsx
> command.
>
> sheets <- openxlsx::getSheetNames("rainfall.xlsx")
> test <- lapply(sheets,function(i) read.xlsx("rainfall.xlsx",
sheet=i,
> startRow=8, cols=1:2))
> names(test) <- sprintf("%i", 1986:2015)
>
> And I got a data.frame with 365 rows and two columns contains date and
> rainfall data.
> There is a value in rainfall data that i want to change as NA (8888)
>
> test[1]$`1986`
> Date RR
> 1 01-01-1986 0
> 2 02-01-1986 7
> 3 03-01-1986 72
> 4 04-01-1986 4
> 5 05-01-1986 19
> 6 06-01-1986 4
> 7 07-01-1986 16
> 8 08-01-1986 21
> 9 09-01-1986 34
> 10 10-01-1986 72
> 11 11-01-1986 93
> 12 12-01-1986 178
> 13 13-01-1986 86
> 14 14-01-1986 11
> 15 15-01-1986 0
> 16 16-01-1986 31
> 17 17-01-1986 22
> 18 18-01-1986 18
> 19 19-01-1986 3
> 20 20-01-1986 0
> 21 21-01-1986 31
> 22 22-01-1986 46
> 23 23-01-1986 4
> 24 24-01-1986 40
> 25 25-01-1986 63
> 26 26-01-1986 125
> 27 27-01-1986 33
> 28 28-01-1986 44
> 29 29-01-1986 6
> 30 30-01-1986 0
> 31 31-01-1986 0
> 32 01-02-1986 2
> 33 02-02-1986 71
> 34 03-02-1986 8888
> 35 04-02-1986 0
> 36 05-02-1986 0
> 37 06-02-1986 56
> 38 07-02-1986 19
> 39 08-02-1986 3
> 40 09-02-1986 7
> 41 10-02-1986 24
> 42 11-02-1986 55
> 43 12-02-1986 0
> 44 13-02-1986 0
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at 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.
Hi ani,
Sorry, a typo in the function - should be:
makeNA(x)<-function(x,varname,value) {
x[,varname][x[,varname]==value]<-NA
return(x)
}
Jim
On Fri, Oct 18, 2019 at 2:01 PM Jim Lemon <drjimlemon at gmail.com>
wrote:>
> Hi ani,
> You say you want to replace 8888 with NA, so:
>
> # it will be easier if you don't use numbers for the names of the data
frames
> names(test) <- paste0("Y",1986:2015)
> makeNA(x)<-function(x,varname,value) {
> x[,varname][x[,varname]<-value]<-NA
> return(x)
> }
> lapply(test,makeNA,list("RR",8888))
>
> Warning - untested
>
> Jim
>
> On Fri, Oct 18, 2019 at 12:38 PM ani jaya <gaaauul at gmail.com>
wrote:
> >
> > Dear R-Help,
> >
> > I have a list of data frame that I import from excel file using
read.xlsx
> > command.
> >
> > sheets <- openxlsx::getSheetNames("rainfall.xlsx")
> > test <- lapply(sheets,function(i)
read.xlsx("rainfall.xlsx", sheet=i,
> > startRow=8, cols=1:2))
> > names(test) <- sprintf("%i", 1986:2015)
> >
> > And I got a data.frame with 365 rows and two columns contains date and
> > rainfall data.
> > There is a value in rainfall data that i want to change as NA (8888)
> >
> > test[1]$`1986`
> > Date RR
> > 1 01-01-1986 0
> > 2 02-01-1986 7
> > 3 03-01-1986 72
> > 4 04-01-1986 4
> > 5 05-01-1986 19
> > 6 06-01-1986 4
> > 7 07-01-1986 16
> > 8 08-01-1986 21
> > 9 09-01-1986 34
> > 10 10-01-1986 72
> > 11 11-01-1986 93
> > 12 12-01-1986 178
> > 13 13-01-1986 86
> > 14 14-01-1986 11
> > 15 15-01-1986 0
> > 16 16-01-1986 31
> > 17 17-01-1986 22
> > 18 18-01-1986 18
> > 19 19-01-1986 3
> > 20 20-01-1986 0
> > 21 21-01-1986 31
> > 22 22-01-1986 46
> > 23 23-01-1986 4
> > 24 24-01-1986 40
> > 25 25-01-1986 63
> > 26 26-01-1986 125
> > 27 27-01-1986 33
> > 28 28-01-1986 44
> > 29 29-01-1986 6
> > 30 30-01-1986 0
> > 31 31-01-1986 0
> > 32 01-02-1986 2
> > 33 02-02-1986 71
> > 34 03-02-1986 8888
> > 35 04-02-1986 0
> > 36 05-02-1986 0
> > 37 06-02-1986 56
> > 38 07-02-1986 19
> > 39 08-02-1986 3
> > 40 09-02-1986 7
> > 41 10-02-1986 24
> > 42 11-02-1986 55
> > 43 12-02-1986 0
> > 44 13-02-1986 0
> >
> > [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > R-help at 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.
Hello,
Why not use read.xlsx argument 'na.strings', an argument that exists in
many file reading functions? (read.table, and derivatives.)
test <- lapply(sheets,function(i) {
read.xlsx("rainfall.xlsx", sheet = i,
startRow = 8, cols = 1:2,
na.strings = "8888")
})
Hope this helps,
Rui Barradas
?s 02:38 de 18/10/19, ani jaya escreveu:> Dear R-Help,
>
> I have a list of data frame that I import from excel file using read.xlsx
> command.
>
> sheets <- openxlsx::getSheetNames("rainfall.xlsx")
> test <- lapply(sheets,function(i) read.xlsx("rainfall.xlsx",
sheet=i,
> startRow=8, cols=1:2))
> names(test) <- sprintf("%i", 1986:2015)
>
> And I got a data.frame with 365 rows and two columns contains date and
> rainfall data.
> There is a value in rainfall data that i want to change as NA (8888)
>
> test[1]$`1986`
> Date RR
> 1 01-01-1986 0
> 2 02-01-1986 7
> 3 03-01-1986 72
> 4 04-01-1986 4
> 5 05-01-1986 19
> 6 06-01-1986 4
> 7 07-01-1986 16
> 8 08-01-1986 21
> 9 09-01-1986 34
> 10 10-01-1986 72
> 11 11-01-1986 93
> 12 12-01-1986 178
> 13 13-01-1986 86
> 14 14-01-1986 11
> 15 15-01-1986 0
> 16 16-01-1986 31
> 17 17-01-1986 22
> 18 18-01-1986 18
> 19 19-01-1986 3
> 20 20-01-1986 0
> 21 21-01-1986 31
> 22 22-01-1986 46
> 23 23-01-1986 4
> 24 24-01-1986 40
> 25 25-01-1986 63
> 26 26-01-1986 125
> 27 27-01-1986 33
> 28 28-01-1986 44
> 29 29-01-1986 6
> 30 30-01-1986 0
> 31 31-01-1986 0
> 32 01-02-1986 2
> 33 02-02-1986 71
> 34 03-02-1986 8888
> 35 04-02-1986 0
> 36 05-02-1986 0
> 37 06-02-1986 56
> 38 07-02-1986 19
> 39 08-02-1986 3
> 40 09-02-1986 7
> 41 10-02-1986 24
> 42 11-02-1986 55
> 43 12-02-1986 0
> 44 13-02-1986 0
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at 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.
>
Hai Rui,
It seems doesnt work for me, the "8888" still there.
So I used this one (Bert suggestion),
test<-lapply(test,function(x){x$RR[x$RR==8888] <- NA; x})
Best,
Ani
On Sat, Oct 19, 2019 at 6:55 PM Rui Barradas <ruipbarradas at sapo.pt>
wrote:
> Hello,
>
> Why not use read.xlsx argument 'na.strings', an argument that
exists in
> many file reading functions? (read.table, and derivatives.)
>
>
> test <- lapply(sheets,function(i) {
> read.xlsx("rainfall.xlsx", sheet = i,
> startRow = 8, cols = 1:2,
> na.strings = "8888")
> })
>
>
> Hope this helps,
>
> Rui Barradas
>
> ?s 02:38 de 18/10/19, ani jaya escreveu:
> > Dear R-Help,
> >
> > I have a list of data frame that I import from excel file using
read.xlsx
> > command.
> >
> > sheets <- openxlsx::getSheetNames("rainfall.xlsx")
> > test <- lapply(sheets,function(i)
read.xlsx("rainfall.xlsx", sheet=i,
> > startRow=8, cols=1:2))
> > names(test) <- sprintf("%i", 1986:2015)
> >
> > And I got a data.frame with 365 rows and two columns contains date and
> > rainfall data.
> > There is a value in rainfall data that i want to change as NA (8888)
> >
> > test[1]$`1986`
> > Date RR
> > 1 01-01-1986 0
> > 2 02-01-1986 7
> > 3 03-01-1986 72
> > 4 04-01-1986 4
> > 5 05-01-1986 19
> > 6 06-01-1986 4
> > 7 07-01-1986 16
> > 8 08-01-1986 21
> > 9 09-01-1986 34
> > 10 10-01-1986 72
> > 11 11-01-1986 93
> > 12 12-01-1986 178
> > 13 13-01-1986 86
> > 14 14-01-1986 11
> > 15 15-01-1986 0
> > 16 16-01-1986 31
> > 17 17-01-1986 22
> > 18 18-01-1986 18
> > 19 19-01-1986 3
> > 20 20-01-1986 0
> > 21 21-01-1986 31
> > 22 22-01-1986 46
> > 23 23-01-1986 4
> > 24 24-01-1986 40
> > 25 25-01-1986 63
> > 26 26-01-1986 125
> > 27 27-01-1986 33
> > 28 28-01-1986 44
> > 29 29-01-1986 6
> > 30 30-01-1986 0
> > 31 31-01-1986 0
> > 32 01-02-1986 2
> > 33 02-02-1986 71
> > 34 03-02-1986 8888
> > 35 04-02-1986 0
> > 36 05-02-1986 0
> > 37 06-02-1986 56
> > 38 07-02-1986 19
> > 39 08-02-1986 3
> > 40 09-02-1986 7
> > 41 10-02-1986 24
> > 42 11-02-1986 55
> > 43 12-02-1986 0
> > 44 13-02-1986 0
> >
> > [[alternative HTML version deleted]]
> >
> > ______________________________________________
> > R-help at 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.
> >
>
[[alternative HTML version deleted]]