R - subtracting different columns if condition is met

Question

I have a huge data frame that is like:

df = data.frame(A = c(1,54,23,2), B=c(1,2,4,65), C=c("+","-","-","+"))

> df
   A  B C
1  1  1 +
2 54  2 -
3 23  4 -
4  2 65 +

I need to subtract the rows based on different conditions, and add these results in a new column:

 A - B if C == +
 B - A if C == -

So, my output would be:

> new_df
   A  B C   D
1  1  1 +   0
2 54  2 - -52
3 23  4 - -19
4  2 65 + -63

Can `C` be any other values, or only +/- ? – neilfws Feb 07 '18 at 00:01 — neilfws, Feb 07 '18 at 00:01

score 3 · Answer 1 · answered Feb 07 '18 at 00:04

3

This assumes that only two conditions, + and -, are in column C.

df$D <- with(df, ifelse(C %in% "+", A - B, B - A))
df
#    A  B C   D
# 1  1  1 +   0
# 2 54  2 - -52
# 3 23  4 - -19
# 4  2 65 + -63

answered Feb 07 '18 at 00:04

www

38,575
12
48
84

score 2 · Answer 2 · answered Feb 07 '18 at 00:07

Better to add stringsAsFactors = FALSE when you create a data frame. Also, I don't like to use df for variable names since there is a df() function:

df1 <- data.frame(A = c(1, 54, 23, 2), 
                  B = c(1, 2, 4, 65), 
                  C = c("+", "-", "-", "+"), 
                  stringsAsFactors = FALSE)

Assuming that C is only + or -, you can use dplyr::mutate() and test using ifelse():

library(dplyr)
df1 %>% 
  mutate(D = ifelse(C == "+", A - B, B - A))

score 1 · Answer 3 · answered Feb 07 '18 at 00:11

using dplyr:

If there are definitely only + and - in the C column you can do:

library(dplyr)

df2 <- df %>%
     mutate(D = ifelse(C == '+', A - B, B - A))

I would generally do:

df2 <- df %>%
     mutate(D = ifelse(C == '+', A - B,
                ifelse(C == '-', B - A, NA)))

Just in case there are some that do not have + or -.

Thomas Guillerme · Answer 4 · 2018-02-07T00:11:30.817

0

Alternatively, if you want to evaluate the arithmetic information in column C (as in addition or subtraction), you can use eval(parse(txt)) (more about that here: Evaluate expression given as a string).

## Transforming into a matrix (simplifies everything into characters)
df_mat <- as.matrix(df)

## Function for evaluation the rows
eval.row <- function(row) {
    eval(parse(text= paste(row[1], row[3], row[2])))
}

## For the first row
eval.row(df_mat[1,])
# [1] 2

## For the whole data frame
apply(df_mat, 1, eval.row)
# [1]  2 52 19 67

## Updating the data.frame
df$D <- apply(df_mat, 1, eval.row)

edited Feb 07 '18 at 00:11

answered Feb 07 '18 at 00:06

Thomas Guillerme

1,747
4
16
23

Thanks for sharing information about `eval` and `parse`, but it seems like the OP was asking something more straightforward. Here `+` and `-` is just a symbol to indicate `A - B` or `B - A`. – www Feb 07 '18 at 00:09
Fair enough. I've edited my answer to reflect the nuance. – Thomas Guillerme Feb 07 '18 at 00:11
1

Look at the output OP expects and then see whether your output matches what is expected – Onyambu Feb 07 '18 at 00:13

score 0 · Answer 5 · answered Feb 07 '18 at 00:16

0

This answer should work for you https://stackoverflow.com/a/19000310/6395612

You can use with like this:

df['D'] = with(df, ifelse(C=='+', A - B, B - A))

answered Feb 07 '18 at 00:16

MathLal

382
3
12

score 0 · Answer 6 · answered Feb 11 '18 at 17:41

0

A base solution:

df$D = (df$B-df$A)*sign((df$C=="-")-0.5)
#    A  B C   D
# 1  1  1 +   0
# 2 54  2 - -52
# 3 23  4 - -19
# 4  2 65 + -63

can also be written df <- transform(df, D = (B-A)*sign((C=="-")-0.5))

answered Feb 11 '18 at 17:41

moodymudskipper

46,417
11
121
167

R - subtracting different columns if condition is met

6 Answers6