summarise by group of columns using min and maintaing row number

0
votes

I have a data frame with 3 columns

df <- data.frame(ID1=c(rep(1,4),rep(2,4)), ID2=rep(1:2,4), value=1:8)

I need to recover the min for each group (ID1, ID2) and the position(row.name) of this min in the original table.

Using group_by and summarise, I have obtained the min but I can't see a way to obtain the position as summarise gets rid of the columns not summarised and not used for group.

df<-data.frame(ID1=c(rep(1,4),rep(2,4)), ID2=rep(1:2,4), value=1:8)
df[['X']] <- paste0(df$ID1,'.',df$ID2)
df <- group_by( df, X )
df <- summarise( df, Objective=min(value)  )

Any ideas on how to solve this to get?

    X Objective Position
1 1.1         1        1
2 1.2         2        2
3 2.1         5        5
4 2.2         6        6

Thanks in advance

3
rpositionminsummarization
I guess it could work if one could make assumptions about the order of the rows. But still it doesn't work. I get: [1] ID1 ID2 value <0 rows> (or 0-length row.names) – Picarus

3 Answers

3
votes

If I understand correct and since you're already using dplyr, you could do it like this:

library(dplyr); library(tidyr)
unite(df, X, ID1:ID2, sep = ".") %>% 
     mutate(Position = row_number()) %>% 
     group_by(X) %>% slice(which.min(value))

#Source: local data frame [4 x 3]
#Groups: X
#
#    X value Position
#1 1.1     1        1
#2 1.2     2        2
#3 2.1     5        5
#4 2.2     6        6

Or alternatively (only dplyr) - I'd rather use this one:

mutate(df, Position = row_number()) %>% group_by(ID1, ID2) %>% slice(which.min(value))
#Source: local data frame [4 x 4]
#Groups: ID1, ID2
#
#  ID1 ID2 value Position
#1   1   1     1        1
#2   1   2     2        2
#3   2   1     5        5
#4   2   2     6        6

data

df <- data.frame(ID1=rep(1:2, each = 4), ID2=rep(1:2,4), value=1:8)
2
votes

Here's how would I approach this using data.table (rn would be your row number).

library(data.table)
setDT(df, keep.rownames = TRUE)[, .SD[which.min(value)], list(ID1, ID2)]
#    ID1 ID2 rn value
# 1:   1   1  1     1
# 2:   1   2  2     2
# 3:   2   1  5     5
# 4:   2   2  6     6

Another option is ordering and then picking the unique values

unique(setorder(df, value), by = c("ID1", "ID2"))
#    ID1 ID2 rn value
# 1:   1   1  1     1
# 2:   1   2  2     2
# 3:   2   1  5     5
# 4:   2   2  6     6

Both approaches don't require creating X column

Or using base R

df <- df[order(df$value), ]
df[!duplicated(df[, 1:2]), ]
#   ID1 ID2 value
# 1   1   1     1
# 2   1   2     2
# 5   2   1     5
# 6   2   2     6

data

df <- data.frame(ID1=c(rep(1,4),rep(2,4)), ID2=rep(1:2,4), value=1:8)
1
votes

Using Aggregate:

Data:

df<-data.frame(ID1=c(rep(1,4),rep(2,4)), ID2=rep(1:2,4), value=1:8)
df[['X']] <- paste0(df$ID1,'.',df$ID2)
df$rn<-row.names(df)                #rn is the row number
df<-df[c("X","rn","value")]
#> df
#    X rn value
#1 1.1  1     1
#2 1.2  2     2
#3 1.1  3     3
#4 1.2  4     4
#5 2.1  5     5
#6 2.2  6     6
#7 2.1  7     7
#8 2.2  8     8

Aggregate step:

df2<- aggregate(df, by=list(c(df$X)), min)
#> df2
#  Group.1   X rn value
#1     1.1 1.1  1     1
#2     1.2 1.2  2     2
#3     2.1 2.1  5     5
#4     2.2 2.2  6     6