CODEWITHSUNDEEP
rmerge
M1ke
M1ke

Reputation: 77

R Merging multiple columns into one depending on if the cell is empty

I have conducted an experiment with four conditions, and I want to merge the four columns into one; depending on if the cell in the column in empty or not. For example, the df looks like this:

df <- data.frame(
  ResponseID = c (1:6),
  ZER_Condition = c ("Low","Med","","","",""),
  LOW_Condition = c ("","","High","Low","",""),
  MED_Condition = c ("","","","","High",""),
  HIG_Condition = c ("","","","","","Low")
)

I want to merge the columns so that I get the following column:

Merged_Condition = c ("Low","Med","High","Low","High","Low")

I have tried the following function, but it doesn't work:

df %>% mutate (Merged_Condition = coalesce(ZER_Condition,LOW_Condition,MED_Condition,HIG_Condition)) %>%
  select(ResponseID, Merged_Condition)

Upvotes: 1

Views: 1780

Answers (3)

crestor
crestor

Reputation: 1466

Conceptually, you are trying to pivot the dataframe. So, the most clear way of writing it would be:

df %>%
  pivot_longer(-ResponseID) %>%
  filter(value != "")

Upvotes: 0

K.RAC
K.RAC

Reputation: 233

Try replacing the "" in your columns with NA and your code should work.

df <- data.frame(
  ResponseID = c (1:6),
  ZER_Condition = c ("Low","Med",NA,NA,NA,NA),
  LOW_Condition = c (NA,NA,"High","Low",NA,NA),
  MED_Condition = c (NA,NA,NA,NA,"High",NA),
  HIG_Condition = c (NA,NA,NA,NA,NA,"Low")
)

df %>% mutate (Merged_Condition = coalesce(ZER_Condition,LOW_Condition,MED_Condition,HIG_Condition)) %>%
  select(ResponseID, Merged_Condition)

Upvotes: 1

Peace Wang
Peace Wang

Reputation: 2419

library(data.table)
setDT(df)
# fill empty as NA
df[df == ""] <- NA

# merge all columns except ResponseID
df[, Merged_Condition := fcoalesce(.SD[,-1])]

Upvotes: 0

Related Questions