CODEWITHSUNDEEP
r
perumadan
perumadan

Reputation: 73

Subtract from previous rows based on conditions

I have a following DF where i would like to poulate values under TEST for RANK == 2 with the following logic.

? = MIN( (GROUP_VALUE - TEST[VALUE OF RANK = 1] ), GROUP_VALUE / POINT )

   country    GROUP_VALUE   RANK  POINT      TEST
1      USA       2000        1      2.5      1500
2      USA       2000        2      2.5      **?**
3       UK       1000        1      2.5      1000 
4      JPN       3000        1      3.5      1000
5      JPN       3000        2      3.5      **?**

Expected Output:

   country    GROUP_VALUE   RANK  POINT      TEST
1      USA       2000        1      2.5      1500
2      USA       2000        2      2.5     *500*
3       UK       1000        1      2.5      1000 
4      JPN       3000        1      3.5      1000
5      JPN       3000        2      3.5     *857.15*

I have tried with the following code but failed to get the expected output.

df$test = ifelse(df$rank == 2, min((df$GROUP_VALUE / df$point), df$group_value - lag(df$test), 0)

Upvotes: 0

Views: 66

Answers (1)

jasbner
jasbner

Reputation: 2283

You can do this with dplyr. mutate is used to create the new column based on your conditions. I also add a group_by stage so that we can delineate between countries.

library(dplyr)
df$TEST <- as.numeric(df$TEST)
df %>%
  group_by(country) %>%
  mutate(TEST = case_when(RANK == 2 ~ min(GROUP_VALUE - TEST[RANK == 1],GROUP_VALUE/POINT), TRUE ~ TEST))

#  country GROUP_VALUE  RANK POINT  TEST
#  <chr>         <int> <int> <dbl> <dbl>
#1 USA            2000     1   2.5 1500 
#2 USA            2000     2   2.5  500 
#3 UK             1000     1   2.5 1000 
#4 JPN            3000     1   3.5 1000 
#5 JPN            3000     2   3.5  857.

Upvotes: 1

Related Questions