Efficient way to create pairs of all combinations of column names as a rows in a data.frame

Here's a data.table solution using melt that should work well unless the database is absolutely massive, in which case you can always split it by ID, but I'd guess this works fast for your case.

library(data.table)
df = data.frame("blue" = c(0,1,1,0,1),
                "yellow" = c(0,0,1,0,1),
                "green" = c(1,1,1,0,0),
                "letter" = c("A","B","C","D","E"),
                "id" = c(23,57,48,3,12))

#convert to data.table and melt
setDT(df)

df = melt(df, id.vars = c("letter","id"))

#combine blue/yellow, blue/green, and yellow/green
df1 = merge(df[variable == "blue"],df[variable == "yellow"], by = c("letter","id"))
df2 = merge(df[variable == "blue"],df[variable == "green"], by = c("letter","id"))
df3 = merge(df[variable == "yellow"],df[variable == "green"], by = c("letter","id"))

df = rbindlist(list(df1,df2,df3))

#now fix names..
setnames(df, c("variable.x","value.x","variable.y","value.y"), c("col_1","c1","col_2","c2"))

#optionally rearrange cols...
df = df[,.(col_1,col_2,c1,c2,letter,id)]

Consider following base solution to dynamically fit any needed set of values:

Data

txt <- '  blue yellow green Letter ID
1    0      0     1      A 23
2    1      0     1      B 57
3    1      1     1      C 48
4    0      0     0      D  3
5    1      1     0      E 12'

df <- read.table(text = txt, header=TRUE)

Solution

# DEFINE VECTOR OF VALUES 
vals <- c("blue", "yellow", "green")

# RESHAPE DATA LONG
rdf <- reshape(df, idvar = c("Letter", "ID"), 
               varying = vals, times = vals, 
               v.names = "C", timevar = "Col1", ids = NULL,
               new.row.names = 1:1E4, direction = "long")

# HELPER DF FOR ALL POSSIBLE COMBNS (AVOID REVERSE DUPLICATES)
col_df <- subset(expand.grid(Col1 = vals, Col2 = vals,
                             stringsAsFactors = FALSE),
                 Col1 < Col2)

# MERGE TWICE FOR EACH SET OF COLs
mdf <- merge(merge(rdf, col_df, by.x="Col1", by.y="Col1"), rdf, 
             by.x=c("Letter", "ID", "Col2"),
             by.y=c("Letter", "ID", "Col1"),
             suffixes = c(1, 2))

# RE-ORDER ROWS AND COLUMNS
mdf <- data.frame(with(mdf, mdf[order(Letter, ID), 
                                c("Letter", "ID", "Col1", "Col2", "C1", "C2")]), 
                  row.names = NULL)

Output

mdf

#    Letter ID  Col1   Col2 C1 C2
# 1       A 23  blue  green  0  1
# 2       A 23  blue yellow  0  0
# 3       A 23 green yellow  1  0
# 4       B 57  blue  green  1  1
# 5       B 57 green yellow  1  0
# 6       B 57  blue yellow  1  0
# 7       C 48  blue  green  1  1
# 8       C 48 green yellow  1  1
# 9       C 48  blue yellow  1  1
# 10      D  3  blue  green  0  0
# 11      D  3 green yellow  0  0
# 12      D  3  blue yellow  0  0
# 13      E 12  blue  green  1  0
# 14      E 12  blue yellow  1  1
# 15      E 12 green yellow  0  1

The known bottleneck is possibly the base::reshape for very large data frames. Here is a faster function using matrix manipulation:

matrix_melt <- function(df1, key, indName, valName) {
  value_cols <- names(df1)[ !(names(df1) %in% key)]
  mat_inds <- matrix(matrix(value_cols, nrow=nrow(df1), ncol=ncol(df1)-2, byrow=TRUE), ncol=1)
  mat_vals <- matrix(df1[value_cols], ncol= 1, byrow = TRUE)

  df2 <- setNames(data.frame(df1[key], unlist(mat_inds), unlist(mat_vals), 
                             row.names = NULL, stringsAsFactors = FALSE),
                  c(key, indName, valName))
  return(df2)
}

rdf <- matrix_melt(df, c("Letter", "ID"), "Col1", "C")
rdf