Identifying, and assigning intuitive (-ish) names to, observed combinations of 0/1 values in R

user_6851212 0
Viewed 56

We have a large dataset of health records (1 row per patient) with several columns, each indicating whether or not the patient interacted with a particular type of healthcare provider (0=no, 1=yes). We are hoping to identify the combination of "yes"s (i.e., which providers were seen) for each patient.

The answers to this question get me a very long way toward my final goal, but I would like to find a way to assign slightly more human-readable names to the identified combinations of 0s and 1s.

The code below yields a toy dataset containing a factor (named "combo" here) with values consisting of 1s and 0s listed in the order in which they appear in the columns, separated by periods (e.g., 1.1.1.0.1.1).

df <- read.table(text = 
                "ID    Pr1    Pr2    Pr3    Pr4    Pr5    Pr6
                 1       1      1      1      0      1      1
                 2       0      0      1      1      0      1
                 3       0      0      1      1      0      1
                 4       0      1      0      0      1      1
                 5       0      1      0      1      1      1
                 6       0      1      0      1      1      1
                 7       1      1      1      1      1      1
                 8       0      1      0      1      1      1
                 9       0      0      0      0      0      1
                 ", header = TRUE)

combo <- do.call(interaction,c(df[-1],drop=TRUE))
df.new <- cbind(df, combo)

Because the real dataset has so many columns of 0/1 variables and potentially hundreds of observed combinations of 0s and 1s, these kinds of strings are going to be difficult to link back to the meaningful column names.

To make this connection a bit easier, what I would like to have is a new character or factor column with values that contain only the names of columns that have a value of 1, e.g., a combo value of 1.1.1.0.1.1 would yield a new value of "Pr1.Pr2.Pr3.Pr5.Pr6" and 0.0.0.0.0.1 would yield "Pr6". Even something like "Pr1.Pr2.Pr3.x.Pr5.Pr6" (or "x.x.x.x.x.Pr6") would be a bit easier to use than the original result.

Thanks for any assistance you can provide!

permutation r
3 Answers

This answer uses the tidyverse. You could always merge the resulting dataset with the original one if you want all columns.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -ID, names_to = "provider", values_to = "seen") %>%
  filter(seen == 1) %>%
  group_by(ID) %>%
  summarize(providers_seen = paste0(provider, collapse = "."))

# A tibble: 9 x 2
     ID providers_seen         
  <int> <chr>                  
1     1 Pr1.Pr2.Pr3.Pr5.Pr6    
2     2 Pr3.Pr4.Pr6            
3     3 Pr3.Pr4.Pr6            
4     4 Pr2.Pr5.Pr6            
5     5 Pr2.Pr4.Pr5.Pr6        
6     6 Pr2.Pr4.Pr5.Pr6        
7     7 Pr1.Pr2.Pr3.Pr4.Pr5.Pr6
8     8 Pr2.Pr4.Pr5.Pr6        
9     9 Pr6

EDIT:

I am also adding a base R solution, which could be helpful:

df$combo <- apply(df[, -1], 1, function(row){
  providers_seen <- names(df)[-1][as.logical(row)]
  paste0(na.omit(providers_seen), collapse = ".")
})

df

  ID Pr1 Pr2 Pr3 Pr4 Pr5 Pr6                   combo
1  1   1   1   1   0   1   1     Pr1.Pr2.Pr3.Pr5.Pr6
2  2   0   0   1   1   0   1             Pr3.Pr4.Pr6
3  3   0   0   1   1   0   1             Pr3.Pr4.Pr6
4  4   0   1   0   0   1   1             Pr2.Pr5.Pr6
5  5   0   1   0   1   1   1         Pr2.Pr4.Pr5.Pr6
6  6   0   1   0   1   1   1         Pr2.Pr4.Pr5.Pr6
7  7   1   1   1   1   1   1 Pr1.Pr2.Pr3.Pr4.Pr5.Pr6
8  8   0   1   0   1   1   1         Pr2.Pr4.Pr5.Pr6
9  9   0   0   0   0   0   1                     Pr6
user_12175580

Going through the data row-wise with apply, you can use ifelse to look at the value of each column and return either it's name or "x". This solves your suggestion of an easier to understand representation.

df$combo <- apply(df, 1, function(x) paste0(ifelse(x[2:length(x)] == 1, names(x[2:length(x)]), "x"), collapse = "."))

However, in response to the larger question about representing this data in an easy to understand way...

My approach would be to consider a shiny app with a series of check-boxes. Data is then filtered based on the combination of check boxes that are selected. I think you'll have too many permutations to have a visually appealing representation in a vector.

user_86750750

in base R you would do:

aggregate(ind~ID,cbind(ID = df$ID, stack(df[-1])),paste,collapse=".",subset = values>0)
  ID                     ind
1  1     Pr1.Pr2.Pr3.Pr5.Pr6
2  2             Pr3.Pr4.Pr6
3  3             Pr3.Pr4.Pr6
4  4             Pr2.Pr5.Pr6
5  5         Pr2.Pr4.Pr5.Pr6
6  6         Pr2.Pr4.Pr5.Pr6
7  7 Pr1.Pr2.Pr3.Pr4.Pr5.Pr6
8  8         Pr2.Pr4.Pr5.Pr6
9  9                     Pr6
user_83802720
Related