Replace values in columns based on a column index in a R data set

Question

I have a dataset with multiple columns - all filled with zeros - and one numeric index column. Now I would like to replace the zero within the column that match the column index (1 = first column, 2 = second column...)

Simplified example:

input <- tibble(X1 = c(0,0,0,0), X2 = c(0,0,0,0), X3 = c(0,0,0,0), index = c(1,2,2,3))
output <- tibble(X1 = c(1,0,0,0), X2 = c(0,1,1,0), X3 = c(0,0,0,1), index = c(1,2,2,3))

I already found one solution, but I'm curious if there is a better/easier way to write it (maybe with base R).

input %>% 
    select(index) %>% 
    bind_cols(map2_dfc(.x = c('X1', 'X2', 'X3'),
    .y = 1:3,
    .f = ~input %>%
    transmute(!!sym(.x) := if_else(.y == index, 1, 0))))`

MrFlick · Accepted Answer · 2023-01-25 21:39:13Z

4

If you use data.frames and base R, you can do

input <- as.data.frame(input)
input[cbind(seq_along(input$index), input$index)] <- 1
input

answered Jan 25, 2023 at 21:39

MrFlick

209k19 gold badges300 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Onyambu · Accepted Answer · 2023-01-25 21:54:38Z

3

input[1:3] <- model.matrix(~factor(index)+0, input)

input
# A tibble: 4 × 4
     X1    X2    X3 index
  <dbl> <dbl> <dbl> <dbl>
1     1     0     0     1
2     0     1     0     2
3     0     1     0     2
4     0     0     1     3

answered Jan 25, 2023 at 21:54

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

Comments

jkatam · Accepted Answer · 2023-01-25 23:53:20Z

1

Here is an approach with map2_dfc

 input %>% mutate(map2_dfc(.x=across(starts_with('X')), 
.y=seq_along(1: (length(.)-1)), .f= ~ ifelse(index==.y, 1, 0)))

^{Created on 2023-01-25 with reprex v2.0.2}

# A tibble: 4 × 4
     X1    X2    X3 index
  <dbl> <dbl> <dbl> <dbl>
1     1     0     0     1
2     0     1     0     2
3     0     1     0     2
4     0     0     1     3

answered Jan 25, 2023 at 23:53

jkatam

3,4651 gold badge7 silver badges12 bronze badges

Collectives™ on Stack Overflow

Replace values in columns based on a column index in a R data set

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related