Insert in binary columns the value of another column

Question

Having a dataframe like this:

    data.frame(previous = c(1,2,2,1,3,3), `next` = c(1,1,2,3,1,3),
    col1_1 = c(1,0,0,0,0,0),
    col1_2 = c(0,0,0,0,0,0),
    col1_3 = c(0,0,0,1,0,0),
    col2_1 = c(0,1,0,0,0,0),
    col2_2 = c(0,0,1,0,0,0),
    col2_3 = c(0,0,0,0,0,0),
    col3_1 = c(0,0,0,0,1,0),
    col3_2 = c(0,0,0,0,0,0),
    col3_3 = c(0,0,0,0,0,1), id = c(1,2,3,4,5,6), salary = c(30,20,40,10,40,80))

How is it possible to check of columns until started with col insert the value of salary column where 1 exist.

Here the expected output:

    data.frame(previous = c(1,2,2,1,3,3), `next` = c(1,1,2,3,1,3),
    col1_1 = c(30,0,0,0,0,0),
    col1_2 = c(0,0,0,0,0,0),
    col1_3 = c(0,0,0,10,0,0),
    col2_1 = c(0,20,0,0,0,0),
    col2_2 = c(0,0,40,0,0,0),
    col2_3 = c(0,0,0,0,0,0),
    col3_1 = c(0,0,0,0,40,0),
    col3_2 = c(0,0,0,0,0,0),
    col3_3 = c(0,0,0,0,0,80), id = c(1,2,3,4,5,6), salary = c(30,20,40,10,40,80))

df[, grep('col', names(df))] <- df[, grep('col', names(df))] * df$salary — rawr
– rawr, Commented Apr 8, 2020 at 14:26

Ronak Shah · Accepted Answer · 2020-04-08 14:24:24Z

We can use mutate_at to apply this to various columns, use ifelse and replace 1 with corresponding salary value.

library(dplyr)
df %>% mutate_at(vars(starts_with('col')), ~ifelse(. == 1, salary, .))

#  previous next. col1_1 col1_2 col1_3 col2_1 col2_2 col2_3 col3_1 col3_2 col3_3 id salary
#1        1     1     30      0      0      0      0      0      0      0      0  1     30
#2        2     1      0      0      0     20      0      0      0      0      0  2     20
#3        2     2      0      0      0      0     40      0      0      0      0  3     40
#4        1     3      0      0     10      0      0      0      0      0      0  4     10
#5        3     1      0      0      0      0      0      0     40      0      0  5     40
#6        3     3      0      0      0      0      0      0      0      0     80  6     80

Or in base R :

cols <- grep('^col', names(df))
df[cols] <- lapply(df[cols], function(x) ifelse(x == 1, df$salary, x))

akrun · Accepted Answer · 2020-04-08 17:42:24Z

1

With dplyr, using case_when

library(dplyr)
df %>%
      mutate_at(vars(starts_with('col')), ~ case_when(. == 1 ~ salary, TRUE ~ .))

answered Apr 8, 2020 at 17:42

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Insert in binary columns the value of another column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related