R Get minimum value in dataframe selecting rows on 2 columns

user_10238319 0
Viewed 640

I have a dataframe like the one I've simplified below. I want to first select rows with the same value based on column X, then in that selection select rows with the same value based on column Y. Then from that selection, I want to take the minimal value. I'm now using a forloop, but seems there must be an easier way. Thanks!

set.seed(123)    
data<-data.frame(X=rep(letters[1:3], each=8),Y=rep(c(1,2)),Z=sample(1:100, 12))
data
   X Y  Z
1  a 1 76
2  a 1 22
3  a 2 32
4  a 2 23
5  b 1 14
6  b 1 40
7  b 2 39
8  b 2 35
9  c 1 15
10 c 1 13
11 c 2 21
12 c 2 42

Desired outcome:

   X Y  Z
2  a 1 22
4  a 2 23
5  b 1 14
8  b 2 35
10 c 1 13
11 c 2 21
multiple-columns dataframe min r select
4 Answers

Here is a data.table solution:

library(data.table)
data = data.table(data)
data[, min(Z), by=c("X", "Y")]

EDIT based on OP's comment:

If there is a NA value in one of the columns we sort by, an additional row is created:

data[2,2] <-NA
data[, min(Z,na.rm = T), by=c("X", "Y")]

   X  Y V1
1: a  1 31
2: a NA 79
3: a  2 14
4: b  1 31
5: b  2 14
6: c  1 50
7: c  2 25
user_98356250 
library(tidyverse)
data %>%
  group_by(X, Y) %>%
  summarise(Z = min(Z))

Will do the trick! The other answer right now is the data.table way, this is tidyverse. Both are extremely powerful ways to approach data cleaning & manipulation - it could be helpful to familiarize yourself with one!

user_50202810

In base you can use aggregate to get min from Z grouped by the remaining columns like:

aggregate(Z~.,data,min)
#  X Y  Z
#1 a 1 31
#2 b 1 31
#3 c 1 50
#4 a 2 14
#5 b 2 14
#6 c 2 25

In case there is an NA in the groups:

data[2,2] <-NA

Ignore it:

aggregate(Z~.,data,min)
#  X Y  Z
#1 a 1 31
#2 b 1 31
#3 c 1 50
#4 a 2 14
#5 b 2 14
#6 c 2 25

Show it:

aggregate(data$Z, list(X=data$X, Y=addNA(data$Y)), min)
#  X    Y  x
#1 a    1 31
#2 b    1 31
#3 c    1 50
#4 a    2 14
#5 b    2 14
#6 c    2 25
#7 a <NA> 79
user_104885040

This code could benefit from splitting it up in multiple lines, but it works. in Base-R

do.call(rbind,
lapply(unlist(lapply(split(data,data$X), function(x) split(x,x$Y)),recursive=F), function(y) y[y$Z==min(y$Z),])
)

    X Y  Z
a.1 a 1 31
a.2 a 2 14
b.1 b 1 31
b.2 b 2 14
c.1 c 1 50
c.2 c 2 25
user_104424230
Related