Re: [R] Adding two or more columns of a data frame for each row when NAs are present.
I think, here is the solution. If NA is included in read.table list the row becomes a factor: $ Q21: Factor w/ 3 levels 1, 2, NA: 1 2 3 2 2. This will not work with rowSums. If I put the missing value as a blank, then it is still read as NA but the whole row is considered as an integer and OK for rowSums etc. A missing value in col Q00 will be interpreted as a row with one less value at the end, I think. Ian + yy - read.table( header = T, sep=,, text = + Q00, Q20, Q21, Q22, Q23, Q24 + 0, 0, 1, 2, 3, 4 + 0, 1, 2, 3, 4, 5 + 0, 0, NA, 3, 4, 5 + 0, 1, 2, 3, 4, 5 + 0, 1, 2, 3, 4, 5) + yy Q00 Q20 Q21 Q22 Q23 Q24 1 0 0 1 2 3 4 2 0 1 2 3 4 5 3 0 0 NA 3 4 5 4 0 1 2 3 4 5 5 0 1 2 3 4 5 + str(yy) 'data.frame': 5 obs. of 6 variables: $ Q00: int 0 0 0 0 0 $ Q20: int 0 1 0 1 1 $ Q21: Factor w/ 3 levels 1, 2, NA: 1 2 3 2 2 $ Q22: int 2 3 3 3 3 $ Q23: int 3 4 4 4 4 $ Q24: int 4 5 5 5 5 + yy - read.table( header = T, sep=,, text = + Q00, Q20, Q21, Q22, Q23, Q24 + 0, 0, 1, 2, 3, 4 + 0, 0, , 3, 4, 5 + 0, 1, 2, 3, 4, 5) + yy Q00 Q20 Q21 Q22 Q23 Q24 1 0 0 1 2 3 4 2 0 0 NA 3 4 5 3 0 1 2 3 4 5 + str(yy) 'data.frame': 3 obs. of 6 variables: $ Q00: int 0 0 0 $ Q20: int 0 0 1 $ Q21: int 1 NA 2 $ Q22: int 2 3 3 $ Q23: int 3 4 4 $ Q24: int 4 5 5 + x - transform( yy, Example 6 +mySum = rowSums(yy[ ,c(Q00,Q20,Q21,Q23)], na.rm=T), +myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24)), +myMean = rowMeans(yy[ ,c(Q00,Q20,Q21,Q23)], na.rm=T) + ) + x Q00 Q20 Q21 Q22 Q23 Q24 mySum myCount myMean 1 0 0 1 2 3 4 4 3 1.00 2 0 0 NA 3 4 5 4 2 1.33 3 0 1 2 3 4 5 7 3 1.75 __ R-help@r-project.org mailing list 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.
Re: [R] Adding two or more columns of a data frame for each row when NAs are present.
Hi, Thanks, your method does indeed work. Thank you. Last night, I worked out something similar and found out about rowMeans as well. Kind wishes, Ian yy - read.table( header = T, sep=,, text = Q20, Q21, Q22, Q23, Q24 0,1, 2,3,4 1,NA,2,3,4 2,1, 2,3,4 5,NA,3,NA,NA) yy Q20 Q21 Q22 Q23 Q24 1 0 1 2 3 4 2 1 NA 2 3 4 3 2 1 2 3 4 4 5 NA 3 NA NA + yy yy x + x - transform( yy, + mySum = rowSums(yy[ ,c(Q20,Q21,Q23)], na.rm=T), + myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24)), + myMean = rowMeans(yy[ ,c(Q20,Q21,Q23)], na.rm=T) + ) + x Q20 Q21 Q22 Q23 Q24 mySum myCount myMean 1 0 1 2 3 4 4 3 1.33 2 1 NA 2 3 4 4 2 2.00 3 2 1 2 3 4 6 3 2.00 4 5 NA 3 NA NA 5 1 5.00 However, if there is NA in first column read then rowSums gives an error. I think that is what is happening. How do I solve that? + yy - read.table( header = T, sep=,, text = + Q20, Q21, Q22, Q23, Q24 + 0,1, 2,3,4 + 1,NA,2,3,4 + NA,1, 2,3,4 + 5,NA,3,NA,NA) + yy Q20 Q21 Q22 Q23 Q24 1 0 1 2 3 4 2 1 NA 2 3 4 3 NA 1 2 3 4 4 5 NA 3 NA NA + rm(x) + x - transform( yy, Example 6 + mySum = rowSums(yy[ ,c(Q20,Q21,Q23)], na.rm=T), + myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24)), + myMean = rowMeans(yy[ ,c(Q20,Q21,Q23)], na.rm=T) + ) Error in rowSums(yy[, c(Q20, Q21, Q23)], na.rm = T) : 'x' must be numeric Calls: transform - transform.data.frame - eval - eval - rowSums yy __ R-help@r-project.org mailing list 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] Adding two or more columns of a data frame for each row when NAs are present.
I am fairly new to R and would like help with the problem below. I am
trying to sum and count several rows in the data frame yy below. All works
well as in example 1. When I try to add the columns, with an NA in Q21, I
get as NA as mySum. I would like NA to be treated as O, or igored.
I wrote a function to try to count an NA element as 0, Example 3 function.
It works with a few warnings, Example 4, but still gives NA instead of the
addition when there is an NA in an element.
In Example 6 7, I tried using sum() but it just sums the whole data
frame, I think,
How do I add together several columns giving the result for each row in
mySum? NA should be treated as a 0. Please, note, I do not want to sum all
the columns, as I think rowSums would do, just the selected ones.
Thanks for your help.
Ian,
yy - read.table( header = T, sep=,, text = ## to create a data frame
+ Q20, Q21, Q22, Q23, Q24
+ 0,1, 2,3,4
+ 1,NA,2,3,4
+ 2,1, 2,3,4)
+ yy
Q20 Q21 Q22 Q23 Q24
1 0 12 3 4
2 1 NA 2 3 4
3 2 12 3 4
x - transform( yy, ## Example 1
+ mySum = as.numeric(Q20) + as.numeric(Q22) + as.numeric(Q24),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 6 3
2 1 NA 2 3 4 7 2
3 2 12 3 4 8 3
+ x - transform( yy, Example 2
+ mySum = as.numeric(Q20) + as.numeric(Q21) + as.numeric(Q24),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 5 3
2 1 NA 2 3 4NA 2
3 2 12 3 4 7 3
NifAvail - function(x) { if (is.na(x)) x-0 else x - x
### Example 3
+ return(as.numeric(x))
+ } #end function
+ NifAvail(5)
[1] 5
+ NifAvail(NA)
[1] 0
x - transform( yy,
+ mySum = NifAvail(Q20) + NifAvail(Q22) + NifAvail(Q24),
### Example 4
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
Warning messages:
1: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
2: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
3: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 6 3
2 1 NA 2 3 4 7 2
3 2 12 3 4 8 3
x - transform( yy,
+ mySum = NifAvail(Q20) + NifAvail(Q21) + NifAvail(Q24),
Example 5
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
Warning messages:
1: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
2: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
3: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 5 3
2 1 NA 2 3 4NA 2
3 2 12 3 4 7 3
x - transform( yy,
Example 6
+ mySum = sum(as.numeric(Q20), as.numeric(Q21), as.numeric(Q23), na.rm=T),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 414 3
2 1 NA 2 3 414 2
3 2 12 3 414 3
x - transform( yy, #
Example 7
+ mySum = sum(as.numeric(Q20), as.numeric(Q22), as.numeric(Q23), na.rm=T),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 418 3
2 1 NA 2 3 418 2
3 2 12 3 418 3
__
R-help@r-project.org mailing list
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.
Re: [R] Adding two or more columns of a data frame for each row when NAs are present.
Hi:
Does this work for you?
yy
Q20 Q21 Q22 Q23 Q24
1 0 1 2 3 4
2 1 NA 2 3 4
3 2 1 2 3 4
rowSums(yy, na.rm = TRUE)
1 2 3
10 10 12
# Use a subset of the variables in yy:
selectVars - paste('Q', c(20, 21, 24), sep = '')
rowSums(yy[, selectVars], na.rm = TRUE)
1 2 3
5 5 7
HTH,
Dennis
On Sun, Nov 20, 2011 at 12:38 PM, Ian Strang hamame...@ntlworld.com wrote:
I am fairly new to R and would like help with the problem below. I am trying
to sum and count several rows in the data frame yy below. All works well as
in example 1. When I try to add the columns, with an NA in Q21, I get as NA
as mySum. I would like NA to be treated as O, or igored.
I wrote a function to try to count an NA element as 0, Example 3 function.
It works with a few warnings, Example 4, but still gives NA instead of the
addition when there is an NA in an element.
In Example 6 7, I tried using sum() but it just sums the whole data frame,
I think,
How do I add together several columns giving the result for each row in
mySum? NA should be treated as a 0. Please, note, I do not want to sum all
the columns, as I think rowSums would do, just the selected ones.
Thanks for your help.
Ian,
yy - read.table( header = T, sep=,, text = ## to create a data
frame
+ Q20, Q21, Q22, Q23, Q24
+ 0,1, 2,3,4
+ 1,NA,2,3,4
+ 2,1, 2,3,4)
+ yy
Q20 Q21 Q22 Q23 Q24
1 0 1 2 3 4
2 1 NA 2 3 4
3 2 1 2 3 4
x - transform( yy, ## Example 1
+ mySum = as.numeric(Q20) + as.numeric(Q22) + as.numeric(Q24),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 1 2 3 4 6 3
2 1 NA 2 3 4 7 2
3 2 1 2 3 4 8 3
+ x - transform( yy, Example 2
+ mySum = as.numeric(Q20) + as.numeric(Q21) + as.numeric(Q24),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 1 2 3 4 5 3
2 1 NA 2 3 4 NA 2
3 2 1 2 3 4 7 3
NifAvail - function(x) { if (is.na(x)) x-0 else x - x ###
Example 3
+ return(as.numeric(x))
+ } #end function
+ NifAvail(5)
[1] 5
+ NifAvail(NA)
[1] 0
x - transform( yy,
+ mySum = NifAvail(Q20) + NifAvail(Q22) + NifAvail(Q24),
### Example 4
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
Warning messages:
1: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
2: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
3: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 1 2 3 4 6 3
2 1 NA 2 3 4 7 2
3 2 1 2 3 4 8 3
x - transform( yy,
+ mySum = NifAvail(Q20) + NifAvail(Q21) + NifAvail(Q24),
Example 5
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
Warning messages:
1: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
2: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
3: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 1 2 3 4 5 3
2 1 NA 2 3 4 NA 2
3 2 1 2 3 4 7 3
x - transform( yy,
Example 6
+ mySum = sum(as.numeric(Q20), as.numeric(Q21), as.numeric(Q23), na.rm=T),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 1 2 3 4 14 3
2 1 NA 2 3 4 14 2
3 2 1 2 3 4 14 3
x - transform( yy, #
Example 7
+ mySum = sum(as.numeric(Q20), as.numeric(Q22), as.numeric(Q23), na.rm=T),
+ myCount =
as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 1 2 3 4 18 3
2 1 NA 2 3 4 18 2
3 2 1 2 3 4 18 3
__
R-help@r-project.org mailing list
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
Re: [R] Adding two or more columns of a data frame for each row when NAs are present.
On Nov 20, 2011, at 3:38 PM, Ian Strang wrote:
I am fairly new to R and would like help with the problem below. I
am trying to sum and count several rows in the data frame yy below.
All works well as in example 1. When I try to add the columns, with
an NA in Q21, I get as NA as mySum. I would like NA to be treated as
O, or igored.
Ignored is by far the better option and that is easily accomplished
by reading the help page for 'sum' and using the obvious parameter
settings.
?sum
I wrote a function to try to count an NA element as 0, Example 3
function. It works with a few warnings, Example 4, but still gives
NA instead of the addition when there is an NA in an element.
In Example 6 7, I tried using sum() but it just sums the whole
data frame, I think,
It sums whatever you give it.
How do I add together several columns giving the result for each row
in mySum?
?rowSums # which also has the same parameter setting for dealing with
NAs.
NA should be treated as a 0.
Nooo , n, no. If it's missing it's not 0.
Please, note, I do not want to sum all the columns, as I think
rowSums would do, just the selected ones.
Fine. then select them:
?[
--
David.
Thanks for your help.
Ian,
yy - read.table( header = T, sep=,, text = ## to create a
data frame
+ Q20, Q21, Q22, Q23, Q24
+ 0,1, 2,3,4
+ 1,NA,2,3,4
+ 2,1, 2,3,4)
+ yy
Q20 Q21 Q22 Q23 Q24
1 0 12 3 4
2 1 NA 2 3 4
3 2 12 3 4
x - transform( yy, ## Example 1
+ mySum = as.numeric(Q20) + as.numeric(Q22) + as.numeric(Q24),
+ myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))
+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 6 3
2 1 NA 2 3 4 7 2
3 2 12 3 4 8 3
+ x - transform( yy, Example 2
+ mySum = as.numeric(Q20) + as.numeric(Q21) + as.numeric(Q24),
+ myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))
+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 5 3
2 1 NA 2 3 4NA 2
3 2 12 3 4 7 3
NifAvail - function(x) { if (is.na(x)) x-0 else x - x
### Example 3
+ return(as.numeric(x))
+ } #end function
+ NifAvail(5)
[1] 5
+ NifAvail(NA)
[1] 0
x - transform( yy,
+ mySum = NifAvail(Q20) + NifAvail(Q22) + NifAvail(Q24),
### Example 4
+ myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))
+as.numeric(!is.na(Q24))
+ )
Warning messages:
1: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
2: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
3: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 6 3
2 1 NA 2 3 4 7 2
3 2 12 3 4 8 3
x - transform( yy,
+ mySum = NifAvail(Q20) + NifAvail(Q21) + NifAvail(Q24),
Example 5
+ myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))
+as.numeric(!is.na(Q24))
+ )
Warning messages:
1: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
2: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
3: In if (is.na(x)) x - 0 else x - x :
the condition has length 1 and only the first element will be used
x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 4 5 3
2 1 NA 2 3 4NA 2
3 2 12 3 4 7 3
x - transform( yy,
Example 6
+ mySum = sum(as.numeric(Q20), as.numeric(Q21), as.numeric(Q23),
na.rm=T),
+ myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))
+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 414 3
2 1 NA 2 3 414 2
3 2 12 3 414 3
x - transform( yy,
# Example 7
+ mySum = sum(as.numeric(Q20), as.numeric(Q22), as.numeric(Q23),
na.rm=T),
+ myCount = as.numeric(!is.na(Q20))+as.numeric(!is.na(Q21))
+as.numeric(!is.na(Q24))
+ )
+ x
Q20 Q21 Q22 Q23 Q24 mySum myCount
1 0 12 3 418 3
2 1 NA 2 3 418 2
3 2 12 3 418 3
__
R-help@r-project.org mailing list
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.
David Winsemius, MD
West Hartford, CT
__
R-help@r-project.org mailing list
