CODEWITHSUNDEEP
rexcel
Anubhav Dikshit
Anubhav Dikshit

Reputation: 1829

Whats the R equivalent of the following Excel operation

I have two column, col1 and col2, and I have the following formula in excel under col3

col1    col2    col3
0       0       0     
1       0       1
0       1       1
0       0       0
1       1       1
0       0       0

Assuming col1 is cell A1

C2 formula: =A2
C3 formula: =IF(A3=1,1,IF(B2=1,0,C2))

I could only achieve the first part,

df$col3 <- ifelse(df$col1 == 1, 1, 0)

How can I do this in R, assuming my data frame is called 'df'

Upvotes: 2

Views: 182

Answers (4)

zx8754
zx8754

Reputation: 56189

Using dplyr::lag() function:

df <- read.table(text = "col1    col2    col3
0       0       0     
1       0       1
0       1       1
0       0       0
1       1       1
0       0       0", header = TRUE)

library(dplyr)
result <- df %>%
  # C3 formula: =IF(A3=1,1,IF(B2=1,0,C2))
  mutate(res = ifelse(col1 == 1, 1, ifelse(lag(col2) == 1, 0, NA)),
         res = ifelse(is.na(res), lag(res), res))

# C2 formula: =A2
result$res[1] <- result$col1[1]

result
#   col1 col2 col3 res
# 1    0    0    0   0
# 2    1    0    1   1
# 3    0    1    1   1
# 4    0    0    0   0
# 5    1    1    1   1
# 6    0    0    0   0

Upvotes: 2

arturro
arturro

Reputation: 1606

 df=data.frame(col1=c(0,1,0,0,1,0), col2=c(0,0,1,0,1,0))

 # shift B column to get "previous" value in every row.
 df$col2_prev=head(c(NA,df$col2),-1);

 df$col3 <- ifelse(is.na(df$col2_prev), 
                     df$col2,  
                     ifelse(df$col1 == 1, 1, 
                           ifelse(df$col2_prev == 1, 0, df$col2)
                           )
                  )

 df[c("col1","col2","col3")]

  col1 col2 col3
1    0    0    0
2    1    0    1
3    0    1    1
4    0    0    0
5    1    1    1
6    0    0    0

Upvotes: 1

Abraxas
Abraxas

Reputation: 96

Your C3 formula is an or-operation on col1 and col2. As formula:

col3 = col1 OR col2

So basically do an or-operation:

In R:

col1 <- c(0, 1, 0, 0, 1, 0)
col2 <- c(0, 0, 1, 0, 1, 0)
df <- data.frame(col1, col2)
df$col3 <- (df$col1 == 1 | df$col2 == 1) * 1
df

Multiplicating with 1 converts the logical values to numeric.

In Excel you could optimize col3 too:

C3 formula =N(OR(A2:B2))

Again: The N() formula transforms your logical values to numeric.

Upvotes: 1

digEmAll
digEmAll

Reputation: 57210

I would use a simple for-loop :

df <- read.csv(text="col1,col2,expectedCol3
0,0,0     
1,0,1
0,1,1
0,0,0
1,1,1
0,0,0")

df$col3 <- NA # initialize column
for(i in 1:nrow(df)){
  if(i == 1){
    df$col3[i] <- df$col1[i]
  }else{
    df$col3[i] <- ifelse(df$col1[i] == 1, 1, ifelse(df$col2[i-1]==1,0,df$col3[i-1]))
  }
}

# are expected and calculated identical ?
identical(df$col3,df$expectedCol3)
# > TRUE

Upvotes: 5

Related Questions