Select non-NA values and assign variables based on column names

Question

I have been given a dataset where participants do seven trials in one of 16 possible conditions. The 16 conditions arise from a 2x2x2x2 design (that is, there are four manipulated variables each with two levels). Let’s say Var1 has levels ‘Pot’ and ‘Pan’. Var2 has levels ‘Hi’ and ‘Low’. Var 3 has levels ‘Up’ and ‘Down’. Var 4 has levels ‘One’ and ‘Two’.

The dataset includes columns for each observation in each condition for each participant – that is, for each row there are 112 (16*7) columns (along with some columns containing demographic stuff etc.), 105 (15*7) of which are empty. The conditions are encoded in the column labels, so the columns range from ‘PotHiUp1’ to ‘PanLowDown2’.

The data thus look like this:

Var1 <- c('Pot', 'Pan')
Var2 <- c('Hi', 'Low')
Var3 <- c('Up', 'Down')
Var4 <- c('One','Two')
Obs <- seq(1,7,1)

df <- expand.grid(Var1,Var2,Var3,Var4,Obs)
df <- df %>% 
  arrange(Var1,Var2,Var3,Var4)

x <- apply(df,1,paste,collapse="")

id <- seq(1,16,1)
age <- rep(20,16)
df <- as.data.frame(cbind(id, age))

for (i in 1:length(x)) {
  df[,ncol(df)+1] <- NA
  names(df)[ncol(df)] <- paste0(x[i])
}

j <- seq(3,ncol(df),7)

for (i in 1:nrow(df)) {
    df[i,c(j[i]:(j[i]+6))] <- 10
}

I want to tidy this data frame so that for each row there are 4 columns (one for each variable) specifying the condition and 7 columns with the observations.

My solution is to filter the data using dplyr like so:

Df1 <- df %>% 
  filter(!is.na(PotHiUpOne1)) %>% 
  mutate(Var1 = 'pot', Var2 = 'hi', Var3 = 'up', Var4 = 'one')

then remove the NA columns like so:

Df1 <- Filter(function(x)!all(is.na(x)), Df1)

I do this 16 times (once for each condition) and then finally bind the 16 dataframes I’ve created back together after renaming the seven remaining observation columns so that they match.

I am wondering if anyone can suggest a more efficient approach, preferably using dplyr.

Edit: I should add that when I say "efficient" I really mean a more elegant approach code-wise rather than something that will run fast (the dataset is not large) - i.e., something that doesn't involve writing out more or less the same block of code 16 times.

Answer 1

Hope this is what you want:

library(data.table)

dtt <- as.data.table(df)
dtt2 <-  melt(dtt, id.vars = c('id', 'age'))[!is.na(value)]
dtt2[, c('var1', 'var2', 'var3', 'var4', 'cond') := tstrsplit(variable, '(?!^)(?=[A-Z0-9])', perl = T)]
dtt2[, variable := NULL]
dcast(dtt2, ... ~ cond, value.var = 'value')
#     id age var1 var2 var3 var4  1  2  3  4  5  6  7
#  1:  1  20  Pot   Hi   Up  One 10 10 10 10 10 10 10
#  2:  2  20  Pot   Hi   Up  Two 10 10 10 10 10 10 10
#  3:  3  20  Pot   Hi Down  One 10 10 10 10 10 10 10
#  4:  4  20  Pot   Hi Down  Two 10 10 10 10 10 10 10
#  5:  5  20  Pot  Low   Up  One 10 10 10 10 10 10 10
#  6:  6  20  Pot  Low   Up  Two 10 10 10 10 10 10 10
#  7:  7  20  Pot  Low Down  One 10 10 10 10 10 10 10
#  8:  8  20  Pot  Low Down  Two 10 10 10 10 10 10 10
#  9:  9  20  Pan   Hi   Up  One 10 10 10 10 10 10 10
# 10: 10  20  Pan   Hi   Up  Two 10 10 10 10 10 10 10
# 11: 11  20  Pan   Hi Down  One 10 10 10 10 10 10 10
# 12: 12  20  Pan   Hi Down  Two 10 10 10 10 10 10 10
# 13: 13  20  Pan  Low   Up  One 10 10 10 10 10 10 10
# 14: 14  20  Pan  Low   Up  Two 10 10 10 10 10 10 10
# 15: 15  20  Pan  Low Down  One 10 10 10 10 10 10 10
# 16: 16  20  Pan  Low Down  Two 10 10 10 10 10 10 10

Answer 2

Ok this isn't as clean as mt1022's solution, but it doesnt require data.table. Requires dplyr for the case_when function and base for everything else.

Define two new functions, find_conditions and transform.

find_conditions is a bit bulky but might be useful as you can easily add in new definitions if needs be.

find_conditions <- function(x){
  x1 <- x
  x1 <- case_when(
    x1 == "PotHiUpOne" ~ c("pot", "hi", "up", "one"),
    x1 == "PotHiUpTwo" ~ c("pot", "hi", "up", "two"),
    x1 == "PotHiDownOne" ~ c("pot", "hi", "down", "one"),
    x1 == "PotHiDownTwo" ~ c("pot", "hi", "down", "two"),
    x1 == "PotLowUpOne" ~ c("pot", "low", "up", "one"),
    x1 == "PotLowUpTwo" ~ c("pot", "low", "up", "two"),
    x1 == "PotLowDownOne" ~ c("pot", "low", "down", "one"),
    x1 == "PotLowDownTwo" ~ c("pot", "low", "down", "two"),
    x1 == "PanHiUpOne" ~ c("pan", "hi", "up", "one"),
    x1 == "PanHiUpTwo" ~ c("pan", "hi", "up", "two"),
    x1 == "PanHiDownOne" ~ c("pan", "hi", "down", "one"),
    x1 == "PanHiDownTwo" ~ c("pan", "hi", "down", "two"),
    x1 == "PanLowUpOne" ~ c("pan", "low", "up", "one"),
    x1 == "PanLowUpTwo" ~ c("pan", "low", "up", "two"),
    x1 == "PanLowDownOne" ~ c("pan", "low", "down", "one"),
    x1 == "PanLowDownTwo" ~ c("pan", "low", "down", "two")
  )
  if(NA %in% x1){
    cat("Error: Input not recognized")
  }
  else{
    return(x1)
  }
}

transform takes the row from df and transforms it to the form we want. It depends on the find_conditions function we've already defined.

transform <- function(row){
  row1 <- row[3:length(row)] # Forget about id and age columns, will put them back at the end

  cols <- colnames(row1)[!is.na(row1)] # Get names of the columns which are not NA
  cols <- substr(cols,1,nchar(cols)-1) # Slice off the last character (The number)
  cols <- cols[!duplicated(cols)] # Columns should all have the same name now - find it by removing duplicates
  vars <- find_conditions(cols) # Use our new find_conditions function to break it up into individual conditions

  row1 <- row1[!is.na(row1)] # Keep only non-NA values

  new_row <- c(row[1:2],row1,vars) # put id, age, row1, vars together
  as.vector(unlist(new_row)) # Return as an unnamed vector
}

Now using these two functions it's pretty easy:

l1 <- list() # Initialize empty list
for (i in 1:nrow(df)){
  l1[[i]] <- transform(df[i,]) # Fill list with transformed rows
}

DF1 <- data.frame(do.call("rbind",l1)) # Bind the transformed rows together

Left it in a loop as you said its not a large dataset. Good luck!