4

I'm pretty new to R and have a question regarding selecting the max values in a column.

I have the following data frame:

          X      Y
 [1,]     1     10
 [2,]     1     12
 [3,]     1     NA
 [4,]     2     5
 [5,]     2     6
 [6,]     2     7
 [7,]     2     8
 [8,]     3     NA
 [9,]     3     NA
[10,]     3     1

I would like to select the max value of column Y and replace all values of Y within each group with that value. My output data frame would look like this:

          X      Y
 [1,]     1     12
 [2,]     1     12
 [3,]     1     12
 [4,]     2     8
 [5,]     2     8
 [6,]     2     8
 [7,]     2     8
 [8,]     3     1
 [9,]     3     1
[10,]     3     1

Any help would be appreciated. Thanks!

Here's the data

Data <- structure(list(X = c(1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L), 
                     Y = c(10L, 12L, NA, 5L, 6L, 7L, 8L, NA, NA, 1L)), 
                .Names = c("X", "Y"), class = "data.frame",
                row.names = c("[1,]", "[2,]", "[3,]", "[4,]", "[5,]", "[6,]", "[7,]", "[8,]", "[9,]", "[10,]"))
Jilber Urbina
  • 58,147
  • 10
  • 114
  • 138
Matt
  • 119
  • 3
  • 6
  • Welcome to StackOverflow. If you made a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) that demonstrates your question / problem, we would find it easier to answer. – Andrie Aug 21 '12 at 20:53

2 Answers2

8

You can use ave with a custom function that wraps max, so you can remove NA values:

Data$Y <- ave(Data$Y, Data$X, FUN=function(x) max(x, na.rm=TRUE))
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
0

With the dplyr or data.table packages, you get an easy way group to calculate grouped operations.

dplyr solution

require(dplyr)
Data %>% group_by(X) %>% mutate(Y = max(Y, na.rm=TRUE))

data.table solution

require(data.table)
setDT(Data)[, Y:=max(Y, na.rm=TRUE), by=X][]
shadow
  • 21,823
  • 4
  • 63
  • 77