How do I reshape this wide table in R to allow pairwise comparisons?

Question

I have the following data:

structure(list(id = c(174L, 187L, 188L, 190L, 213L, 234L), p7 = c(1L, 
1L, NA, 1L, 0L, 1L), p4 = c(1L, NA, NA, 1L, 1L, 1L), p3 = c(1L, 
1L, 1L, 1L, NA, 1L), p6 = c(1L, NA, NA, 1L, 1L, 1L), p1 = c(1L, 
1L, 1L, 1L, NA, 1L), p2 = c(1L, 1L, 1L, 1L, 1L, 1L), p5 = c(1L, 
0L, NA, 1L, NA, 1L), p8 = c(1L, NA, NA, 1L, 1L, 1L), q1 = c(1L, 
1L, NA, NA, NA, NA), q2 = c(NA, NA, 0L, NA, NA, NA), q3 = c(1L, 
1L, 1L, NA, 0L, 0L), q4 = c(NA, NA, NA, 0L, 0L, 0L), q5 = c(NA, 
1L, 0L, 1L, 0L, 1L), q6 = c(1L, NA, 1L, 1L, 0L, NA), q7 = c(0L, 
NA, 0L, 0L, NA, 1L), q8 = c(1L, NA, 0L, 0L, 0L, 0L)), .Names = c("id", 
"p7", "p4", "p3", "p6", "p1", "p2", "p5", "p8", "q1", "q2", "q3", 
"q4", "q5", "q6", "q7", "q8"), row.names = c(NA, 6L), class = "data.frame")

and I need a data frame where the first column contains the id repeated 8 times, the second column contains all the values labelled with p (in that order), and the third column contains all the values labelled with q (in that order). I need this so I can perform pairwise comparisons between corresponding ps and qs data points.

Any hints?

score 2 · Accepted Answer · answered Mar 24 '17 at 13:04

library(reshape2)

df = structure(list(id=c(174L,187L,188L,190L,213L,234L),p7=c(1L,
1L,NA,1L,0L,1L),p4=c(1L,NA,NA,1L,1L,1L),p3=c(1L,
1L,1L,1L,NA,1L),p6=c(1L,NA,NA,1L,1L,1L),p1=c(1L,
1L,1L,1L,NA,1L),p2=c(1L,1L,1L,1L,1L,1L),p5=c(1L,
0L,NA,1L,NA,1L),p8=c(1L,NA,NA,1L,1L,1L),q1=c(1L,
1L,NA,NA,NA,NA),q2=c(NA,NA,0L,NA,NA,NA),q3=c(1L,
1L,1L,NA,0L,0L),q4=c(NA,NA,NA,0L,0L,0L),q5=c(NA,
1L,0L,1L,0L,1L),q6=c(1L,NA,1L,1L,0L,NA),q7=c(0L,
NA,0L,0L,NA,1L),q8=c(1L,NA,0L,0L,0L,0L)),.Names=c("id",
"p7","p4","p3","p6","p1","p2","p5","p8","q1","q2","q3",
"q4","q5","q6","q7","q8"),row.names=c(NA,6L),class="data.frame")

p = melt(df, id.vars=c("id"),measure.vars=c("p7", "p4", "p3", "p6","p1", "p2", "p5", "p8"), variable.name="p")
q = melt(df, id.vars=c("id"),measure.vars=c("q1", "q2", "q3", "q4","q5", "q6", "q7", "q8"), variable.name="q")
both = cbind(p, q)

It's a dataframe so you could simply view using: head(both) – Aleksandr Mar 24 '17 at 13:34 — Aleksandr, Mar 24 '17 at 13:34

score 2 · Answer 2 · answered Mar 24 '17 at 15:07

2

We can do this easily with melt from data.table which can take multiple measure patterns

library(data.table)
melt(setDT(df), measure = patterns("^p\\d+", "^q\\d+"), 
                  value.name = c("p", "q"), variable.name = "grp")

answered Mar 24 '17 at 15:07

akrun

874,273
37
540
662

How do I reshape this wide table in R to allow pairwise comparisons?

2 Answers2