CODEWITHSUNDEEP
r
wotter
wotter

Reputation: 528

How to compute a column that depends on a function that uses the value of a variable of each row?

This is a mock-up based on mtcars of what I would like to do:

I have tried solutions with *apply but I am somehow never able to make the function called work only on a subset that depends on the value of a variable of the row that is processed (hope this makes sense). 

x = mtcars[1:6,c("disp","am")]
# expected values are the number of cars that have less disp while having the same am
x$expected = c(1,1,0,1,2,0) 
#this ordered table is for findInterval
a = x[order(x$disp),] 
a
# I use the findInterval function to get the number of values and I try subsetting the call
# -0.1 is to deal with the closed intervalq 
x$try1 = findInterval(x$disp-0.1, a$disp[a$am==x$am])
x
# try1 values are not computed depending on the subsetting of a

Any solution will do; the use of the findInterval function is not mandatory.

I'd rather have a more general solution enabling a column value to be computed by calling a function that takes values from the current row to compute the expected value.

Upvotes: 3

Views: 206

Answers (2)

Khashaa
Khashaa

Reputation: 7373

As pointed out by @dimitris_ps, the previous solution neglects the duplicated counts. Following provides the remedy.

library(dplyr)
x %>% 
  group_by(am) %>%
  mutate(expected=findInterval(disp, sort(disp) + 0.0001))

or

library(data.table)
setDT(x)[, expected:=findInterval(disp, sort(disp) + 0.0001), by=am]

Upvotes: 4

dimitris_ps
dimitris_ps

Reputation: 5951

Based on @Khashaa's logic this is my approach

library(dplyr)
mtcars %>% 
  group_by(am) %>%
  mutate(expected=match(disp, sort(disp))-1)

Upvotes: 3

Related Questions