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). So my
question is how to change those value in all the data frame?
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
.
.
.
Should I extract the data frame from the list one by one?
Thank you for your help!
Best,
Saat M.
(Sorry for the uncompleted email before).
[[alternative HTML version deleted]]
Hello
You can use the following function,
######################################################
replace_missings <- function(x, replacement) {
is_missings <- is.na(x)
x[is_missings] <- replacement
message(sum(is_missings), " missings replaced by the given value ",
replacement)
x
}
A <- c(0,1,2,3,4,5,6,7,8,9,10)
B <- c(12,15,3,65,NA,12,NA,4,6,25,4)
df <- data.frame(A,B)
replace_missings(df, replacement = 0)
#################################################
Regards
Mayooran
-----Original Message-----
From: R-help <r-help-bounces at r-project.org> On Behalf Of ani jaya
Sent: Friday, 18 October 2019 2:43 PM
To: r-help <r-help at r-project.org>
Subject: [R] NA value in list of data frame
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). So my
question is how to change those value in all the data frame?
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
.
.
.
Should I extract the data frame from the list one by one?
Thank you for your help!
Best,
Saat M.
(Sorry for the uncompleted email before).
[[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.
On 18/10/19 2:58 PM, Thevaraja, Mayooran wrote:> Hello > You can use the following function, > ###################################################### > replace_missings <- function(x, replacement) { > is_missings <- is.na(x) > x[is_missings] <- replacement > message(sum(is_missings), " missings replaced by the given value ", replacement) > x > } > > A <- c(0,1,2,3,4,5,6,7,8,9,10) > B <- c(12,15,3,65,NA,12,NA,4,6,25,4) > > df <- data.frame(A,B) > > replace_missings(df, replacement = 0) > #################################################The OP wanted to solve the reverse problem. He wanted to replace 8888 by NA, not NA by (e.g.) 8888. cheers, Rolf -- Honorary Research Fellow Department of Statistics University of Auckland Phone: +64-9-373-7599 ext. 88276
On 18/10/19 2:43 PM, ani jaya 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). So my > question is how to change those value in all the data frame? > > 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 > . > . > . > > Should I extract the data frame from the list one by one?Try (something like): revisedTest <- lapply(test,function(x){x$RR[x$RR==8888] <- NA; x}) Or, using Bert Gunter's slick is.na() trick: revisedTest <- lapply(test,function(x){is.na(x$RR) <- x$RR==8888; x}) (Note: not tested.) cheers, Rolf Turner -- Honorary Research Fellow Department of Statistics University of Auckland Phone: +64-9-373-7599 ext. 88276
Thank you very much everyone. All fine now!! On Fri, Oct 18, 2019 at 11:42 AM Rolf Turner <r.turner at auckland.ac.nz> wrote:> > On 18/10/19 2:43 PM, ani jaya 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). So > my > > question is how to change those value in all the data frame? > > > > 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 > > . > > . > > . > > > > Should I extract the data frame from the list one by one? > > Try (something like): > > revisedTest <- lapply(test,function(x){x$RR[x$RR==8888] <- NA; x}) > > Or, using Bert Gunter's slick is.na() trick: > > revisedTest <- lapply(test,function(x){is.na(x$RR) <- x$RR==8888; x}) > > (Note: not tested.) > > cheers, > > Rolf Turner > > -- > Honorary Research Fellow > Department of Statistics > University of Auckland > Phone: +64-9-373-7599 ext. 88276 >[[alternative HTML version deleted]]