Running ifelse statement by a variable

Question

I am trying to run an ifelse statement with a by group and cannot see how to do it in R.

For example if I have

I want to create a column which gives a 1 if order = max(order), and a 0 otherwise for each ID. So overall would give

1 1 0 0 1 1 0 1

My ifelse statement is therefore

ifelse(ORDER == max(ORDER), 1, 0)

How would I do this for each ID variable (preferably without a for loop)?

Thanks.

Possible duplicate of [Extract the maximum value within each group in a dataframe](https://stackoverflow.com/questions/25314336/extract-the-maximum-value-within-each-group-in-a-dataframe) — tjebo, Jun 22 '18 at 13:59
That link is not a duplicate of this question. There's no extraction happening here. — Rich Scriven, Jun 22 '18 at 14:02

markus · Accepted Answer · 2018-06-22T14:03:48.017

5

In base R you can use ave() for the grouping and do

with(df, ave(ORDER, ID, FUN = function(x) x == max(x)))
#[1] 1 1 0 0 1 1 0 1

Thanks to @RichScriven.

data

df <- structure(list(ID = c(1L, 2L, 3L, 3L, 3L, 6L, 7L, 7L), ORDER = c(1L, 
1L, 1L, 2L, 3L, 1L, 1L, 2L)), .Names = c("ID", "ORDER"), class = "data.frame", row.names = c(NA, 
-8L))

edited Jun 22 '18 at 14:03

answered Jun 22 '18 at 13:59

markus

25,843
5
39
58

2

Or just `function(x) x == max(x)` since `ORDER` is already numeric – Rich Scriven Jun 22 '18 at 14:01

score 1 · Answer 2 · answered Jun 22 '18 at 13:59

library('data.table')
setDT(df1) # make df1 as data.table by reference
# check for the condition and convert it to integer
df1[, m_ord := as.integer(ORDER == max(ORDER)), by = .(ID)]  
df1

#    ID ORDER m_ord
# 1:  1     1     1
# 2:  2     1     1
# 3:  3     1     0
# 4:  3     2     0
# 5:  3     3     1
# 6:  6     1     1
# 7:  7     1     0
# 8:  7     2     1

Data:

df1 <- read.table(text='ID  ORDER
1   1
                  2   1
                  3   1
                  3   2
                  3   3
                  6   1
                  7   1
                  7   2', header = TRUE, stringsAsFactors = FALSE)

Sotos · Answer 3 · 2018-06-22T14:44:31.710

1

Here is another idea that does not require any grouping. You first order your data frame and then find the duplicates, i.e.

df1 <- df[order(df$ID, df$ORDER),]
as.integer(!duplicated(df1$ID, fromLast = TRUE))
#[1] 1 1 0 0 1 1 0 1

NOTE: The above method assumes that the maximum value only occurs once (Thanks to Ryan for commenting)

edited Jun 22 '18 at 14:44

answered Jun 22 '18 at 14:19

Sotos

51,121
6
32
66

Although if `ORDER == max(ORDER)` for more than 1 row this will give a different result than the other answers. – IceCreamToucan Jun 22 '18 at 14:36
@Ryan yes I saw It but thought to share this as It uses a different method than grouping and aggregating which might be useful to other similar use cases. Let me add it as a note in my answer though. – Sotos Jun 22 '18 at 14:43

Running ifelse statement by a variable

3 Answers3

Linked