Replace value in one column with value from another column in the same row with certain condition

user_15867455 0
Viewed 91

I have a data frame with columns "A", "B", and "Dict"

  • "Dict" has values of either "A" or "B" , which indicates the correct columns that values should be in for each row
  • columns "A" or "B" have numeric values

For example:

A            B             Dict
0           123            B
0           123            A
123         0              A

I want to move the value in "B" to "A" if "Dict" has A in the same row, but keep other rows the same if the "Dict" has correct matchings (the value should be in the column that "Dict" indicates, otherwise it will be 0):

A            B             Dict
0           123             B
123          0              A
123          0              A

Does anyone know how I should proceed?

Edit: There are negative values in the columns too. Sorry I should've clarified.

dataframe r
4 Answers

With dplyr, you could use Dict == cur_column() in across() to determine where to swap A and B.

library(dplyr)

df %>%
  mutate(across(A:B, ~ ifelse(Dict == cur_column(), pmax(A, B), pmin(A, B))))

    A   B Dict
1   0 123    B
2 123   0    A
3 123   0    A
user_100689850

Another base R:

ind <- dat$Dict != c("A","B")[max.col(dat[,c("A","B")])]
ind
# [1] FALSE  TRUE FALSE
dat[ind,c("A","B")] <- dat[ind,c("B","A")]
dat
#     A   B Dict
# 1   0 123    B
# 2 123   0    A
# 3 123   0    A

Data

dat <- structure(list(A = c(0L, 123L, 123L), B = c(123L, 0L, 0L), Dict = c("B", "A", "A")), row.names = c(NA, -3L), class = "data.frame")
user_33582720

Credits to Darren Tsai. Using his solution we could use A+B instead of pmax because of the 0:

With this dataframe:

df <- structure(list(A = c(0L, 0L, 123L), B = c(123L, 123L, 0L), Dict = c("B", 
"A", "A")), class = "data.frame", row.names = c(NA, -3L))

library(dplyr)
df %>%
  mutate(across(A:B, ~ ifelse(Dict == cur_column(), A+B, 0)))

I get this:

    A   B Dict
1   0 123    B
2 123   0    A
3 123   0    A
user_133216470

This might work :

df$A=ifelse(df$Dict=="A" & df$A==0,df$B,ifelse(df$Dict=="A",df$A,0))
df$B=ifelse(df$Dict=="B" & df$B==0,df$A,ifelse(df$Dict=="B",df$B,0))

df
    A   B Dict
1   0 123    B
2 123   0    A
3 123   0    A
user_153682350
Related