CODEWITHSUNDEEP
rif-statementconditional-statements
Dfeld
Dfeld

Reputation: 197

If conditions and copying values from different rows

I have the following data:

Data <- data.frame(Project=c(123,123,123,123,123,123,124,124,124,124,124,125,125,125),
               Name=c("Harry","David","David","Harry","Peter","Peter","John","Alex","Alex","Mary","Mary","Dan","Joe","Joe"),
                      Value=c(1,4,7,3,8,9,8,3,2,5,6,2,2,1),
                      OldValue=c("","Open","In Progress","Complete","Open","In Progress","Complete","Open","In Progress","System Declined","In Progress","","Open","In Progress"),
                      NewValue=c("Open","In Progress","Complete","Open","In Progress","Complete","Open","In Progress","System Declined","In Progress","Complete","Open","In Progress","Complete"))

The data should look like this

I want to create another column called EditedBy that applies the following logic.

  1. IF the project in row 1 equals the project in row 2 AND the New Value in row 1 equals "Open" THEN take the name from row 2. If either of the first two conditions are False, then stick with the name in the first row.

So the data should look like this

How can I do this?

Upvotes: 0

Views: 55

Answers (1)

akrun
akrun

Reputation: 887991

We can do this with data.table

library(data.table)
setDT(Data)[, EditedBy := Name[2L] ,.(Project, grp=cumsum(NewValue == "Open"|
                         shift(NewValue == "System Declined", fill=TRUE)))]

Data
# Project  Name Value        OldValue        NewValue EditedBy
# 1:     123 Harry     1                            Open    David
# 2:     123 David     4            Open     In Progress    David
# 3:     123 David     7     In Progress        Complete    David
# 4:     123 Harry     3        Complete            Open    Peter
# 5:     123 Peter     8            Open     In Progress    Peter
# 6:     123 Peter     9     In Progress        Complete    Peter
# 7:     124  John     8        Complete            Open     Alex
# 8:     124  Alex     3            Open     In Progress     Alex
# 9:     124  Alex     2     In Progress System Declined     Alex
#10:     124  Mary     5 System Declined     In Progress     Mary
#11:     124  Mary     6     In Progress        Complete     Mary
#12:     125   Dan     2                            Open      Joe
#13:     125   Joe     2            Open     In Progress      Joe
#14:     125   Joe     1     In Progress        Complete      Joe

Upvotes: 1

Related Questions