Changing cell values in data table with column names (R)?

user_13817953 0
Viewed 344

I have a data table with cells contaning 0 and 1. I would like to change each "1" in a column with a column name and each "0" with NA.

set.seed(45)
DT = data.table(
  names = c("n1", "n2", "n3", "n4", "n5"),
  a = sample(c(0, 1), size = 5, replace = TRUE),
  b = sample(c(0, 1), size = 5, replace = TRUE),
  c = sample(c(0, 1), size = 5, replace = TRUE))

What I start with:

   names a b c
1:    n1 0 1 1
2:    n2 0 0 0
3:    n3 1 0 0
4:    n4 1 1 1
5:    n5 0 0 1

What I want:

   names  a  b  c
1:    n1 NA  b  c
2:    n2 NA NA NA
3:    n3  a NA NA
4:    n4  a  b  c
5:    n5 NA NA  c

In case when trying to change this per column, here column 2. I change all 0s with NAs - and 1s stay ones. I tried changing 1s with DT[x, 2] <- colnames(DT)[2] but it doesn't work. It's commented out because if I run it with that both 1s and 0s turn NA.

for (x in c(1:nrow(DT))) {
  test <- as.integer(DT[x, 2])
  if (test == 1) {
    #DT[x, 2] <- colnames(DT)[2]
  }
  else {
    DT[x, 2] <- NA
  }
}

Also if I try setting column number with variable in order to do this more easily for all columns. It calls an error.

col <- 2
for (x in c(1:nrow(DT))) {
  test <- as.integer(DT[x, col])
  if (test == 1) {
    #DT[x, 2] <- colnames(DT)[col]
  }
  else {
    DT[x, col] <- NA
  }
}

Same if I try to use for loop to go through columns and rows.


for (c in c(2:4)) {
 for (r in c(1:nrow(DT))) {
   test <- as.integer(DT[r, c])
   if (test == 1) {
     print("yes")
     #DT[r, c] <- colnames(DT)[c]
   }
   else {
     DT[r, c] <- NA
   }
 }
}

Could someone help me identify the error? Or is there prehaps some package that does this for me?

data.table r
3 Answers

A fast solution:

cols <- c('a', 'b', 'c')
for (cl in cols) {
  set(DT, j = cl, value = fifelse(DT[[cl]]==0, NA_character_, cl))
}

And one more alternative solution with Map():

DT[, (cols) := Map(function(x, y) fifelse(x==0, y, NA_character_), .SD, cols), .SDcols = cols]
user_45522950

Here is one option

DT[, lapply(replace(.SD * col(.SD), !.SD, NA), function(v) names(.SD)[v]), names]

which gives

   names    a    b    c
1:    n1 <NA>    b    c
2:    n2 <NA> <NA> <NA>
3:    n3    a <NA> <NA>
4:    n4    a    b    c
5:    n5 <NA> <NA>    c
user_121587570

Here's a tidyverse/purrr option:

map2_df(DT, names(DT), ~  replace(.x, .x==1, .y) %>% replace(. == 0, NA))

# A tibble: 5 x 4
  names a     b     c    
  <chr> <chr> <chr> <chr>
1 n1    NA    b     c    
2 n2    NA    NA    NA   
3 n3    a     NA    NA   
4 n4    a     b     c    
5 n5    NA    NA    c
user_129679380
Related