List of data frame

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

List of data frame

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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: List of data frame

Bert Gunter-2
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 <[hidden email]> 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]]
>
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: List of data frame

ani jaya
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==8888

Above 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 <[hidden email]> 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 <[hidden email]> 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]]
>>
>> ______________________________________________
>> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: List of data frame

Jim Lemon-4
In reply to this post by ani jaya
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 <[hidden email]> 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]]
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: List of data frame

Jim Lemon-4
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 <[hidden email]> 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 <[hidden email]> 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]]
> >
> > ______________________________________________
> > [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: List of data frame

Rui Barradas
In reply to this post by ani jaya
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]]
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: List of data frame

ani jaya
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 <[hidden email]> 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]]
> >
> > ______________________________________________
> > [hidden email] 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]]

______________________________________________
[hidden email] 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.