How to label by combination of available data in each row in R

Question

So I have three columns of Boolean values. I want to produce a fourth column that contains labels for the different arrangements of data I could have. Paint-by-numbers, if you will.

Example:

A  |  B  |  C  |  newCol
------------------------
0  |  0  |  0  |   0
1  |  0  |  0  |   1
0  |  1  |  0  |   2
0  |  0  |  1  |   3
1  |  1  |  0  |   4
0  |  1  |  1  |   5
1  |  0  |  1  |   6
1  |  1  |  1  |   7

So, based on the arrangement that happens among A, B, and C to have a corresponding label.

Preferably using tidyverse approach.

Answer 1

You can use the knowledge that factors are internally encoded as integers to get the result you want.

First paste together each row's values.

lvls <- apply(df1[-4], 1, paste, collapse = "")

Then coerce to class "factor" and from there to class "integer".

f <- factor(lvls, levels = unique(lvls))
as.integer(f) - 1
#[1] 0 1 2 3 4 5 6

identical(df1$newCol, as.integer(f) - 1)
#[1] TRUE

Data.

df1 <- read.table(text = "
A  |  B  |  C  |  newCol
0  |  0  |  0  |   0
1  |  0  |  0  |   1
0  |  1  |  0  |   2
0  |  0  |  1  |   3
1  |  1  |  0  |   4
0  |  1  |  1  |   5
1  |  1  |  1  |   6                  
", header = TRUE, sep = "|")

Answer 2

Using data.table package we can preserve the original sorting (recommended):

library(data.table)

setDT(df1)[,new_col:=.GRP-1, by = c("A", "B","C")]

#if you want the column as factor (one-liner, no need for previous line)
setDT(df1)[,new_col:=.GRP-1, by = c("A", "B","C")][,new_col:=as.factor(new_col)] 

---

Using dplyr we can do something like below:

_{(Rui's solution implemented in dplyr with minimal modification to consider possibility of duplicate rows):}

This also preserves the sorting;

df1 %>% mutate(mtemp=paste0(A,B,C)) %>%  
        mutate(new_col = as.integer(factor(mtemp, levels = unique(.$mtemp)))-1) %>% 
        select(-mtemp)

We can use a dummy variable to label the data:

df1 %>% mutate(mtemp = paste0(A,B,C)) %>% 
        group_by(mtemp) %>% arrange(mtemp) %>% ungroup() %>%
        mutate(new_col = c(0,cumsum(lead(mtemp)[-n()] != lag(mtemp)[-1]))) %>% select(-mtemp)

# # A tibble: 8 x 5
#       A     B     C      newCol  new_col
#       <dbl> <dbl> <dbl>  <int>   <dbl>
# 1     0     0     0      0       0
# 2     0     0     0      0       0
# 3     0     0     1      3       1
# 4     0     1     0      2       2
# 5     0     1     1      5       3
# 6     1     0     0      1       4
# 7     1     1     0      4       5
# 8     1     1     1      6       6

or in reference to this thread:

df1 %>% 
  mutate(group_id = group_indices(., paste0(A,B,C)))

Explanation on dplyr solutions:

First solution creates a dummy variable by pasting all three desired variables together; in the next step, each group of that dummy var gets a unique id (compare newCol to new_col). Basically if mtemp changes between any two rows, we get True (its numeric value is 1) as the answer of our comparison (lead(mtemp)...) and then cumsum adds it to the previous id generated which eventually results in different ids for different mtemp (combination of A, B, and C). This solution relies on arrangement of the dummy variable and therefore does not address the sorting requirements.

For the other solution, just read up on ?group_indices.

Data:

df1 <- structure(list(A = c(0, 1, 0, 0, 0, 1, 0, 1), B = c(0, 0, 0, 
1, 0, 1, 1, 1), C = c(0, 0, 0, 0, 1, 0, 1, 1), newCol = c(0L, 
1L, 0L, 2L, 3L, 4L, 5L, 6L)), class = "data.frame", row.names = c(NA, 
-8L))

Answer 3

If it doesn't matter what your new column values are for each unique row, you can use this one liner:

df$newCol <- as.numeric(as.factor(paste(df$a, df$b, df$c, sep = "")))

If you need your new column to be precisely 0-7 for each unique row as specified you can use a two-liner and unique()

df$newCol <- as.factor(paste(df$a, df$b, df$c, sep = ""))
df$newCol <- as.numeric(factor(df$newCol, levels = unique(df$newCol)))-1

If you need this column as a factor then just do this to the result:

df$newCol <- as.factor(df$newCol)