How do I do a multirow formula?

Question

I am trying to use a multirow formula to have a new calculated column but can't quite figure it out.

Suppose I my data is this:

I want to create a calculated column z in which would have the following logic:

If the value of x is equal to the previous value of x then y-previous(x) else 0.

The first thing to do is specify what you want for your first value. — IRTFM, Apr 04 '19 at 19:26
@divibisan : I doin't think that was a duplicate, although I'm not ruling out the possibility that some other question that involved using a lag variable to construct a second variable might exist. — IRTFM, Apr 04 '19 at 21:16
@42- I'm totally unclear on what they're trying to do, but if it's what all 3 of these answers are doing, then I was wrong on the duplicate — divibisan, Apr 04 '19 at 21:17

score 2 · Accepted Answer · answered Apr 04 '19 at 19:32

Try this:

# load package
library(dplyr)
# reproduce your data
df <- data.frame(x = rep(1:2, c(4, 3)),
                 y = c(2, 2, 6, 7, 4, 5, 9))
df %>%
  mutate(z = case_when(x == lag(x) ~ y - lag(x),
                       TRUE ~ 0))

Hope it helps

niko · Answer 2 · 2019-04-04T19:49:00.237

1

Or in base R this can be done with ifelse

df$z <- c(0, ifelse(diff(df$x) == 0, 1, 0)*(df$y[-1]-df$x[-nrow(df)]))
#   x y z
# 1 1 2 0
# 2 1 2 1
# 3 1 6 5
# 4 1 7 6
# 5 2 4 0
# 6 2 5 3
# 7 2 9 7

Data

df <- structure(list(x = c(1L, 1L, 1L, 1L, 2L, 2L, 2L), y = c(2, 2, 
                                                          6, 7, 4, 5, 9)), class = "data.frame", row.names = c(NA, -7L))

edited Apr 04 '19 at 19:49

answered Apr 04 '19 at 19:35

niko

5,253
1
12
32

IRTFM · Answer 3 · 2019-04-04T22:48:28.477

Boolean arithmetic works with head and tail used to construct the lagged variables. (first implementation used wrong logic):

dat$new <- with(dat, c(0,  # starting value for no prior x
                   tail(y,-1)-head(x, -1)) * #The values if x[-1]==x
              # then construct the x[-1] == x logical vector
                     ( c(0,                        # starting
                   tail(x,-1)== head(x,-1)))) # prior x == current x 

> dat
  x y new
1 1 2   0
2 1 2   1
3 1 6   5
4 1 7   6
5 2 4   0
6 2 5   3
7 2 9   7

How do I do a multirow formula?

3 Answers3