how to compare two columns across many rows

user_10101956 0
Viewed 80

I have a data like this 

df<- structure(list(X1 = c(37L, 36L, 35L, 35L, 35L, 34L, 29L, 28L, 
28L, 26L, 25L, 24L), X2 = c(40L, 44L, 49L, 41L, 0L, 0L, 0L, 25L, 
26L, 37L, 0L, 22L), M1 = c(60L, 55L, 47L, 46L, 36L, 42L, 25L, 
0L, 33L, 42L, 50L, 22L), M2 = c(19L, 0L, 32L, 44L, 27L, 31L, 
17L, 0L, 18L, 45L, 10L, 11L)), class = "data.frame", row.names = c(NA, 
-12L))

I want to compare each pairs of columns 

1 with 2
1 with 3
1 with 4
2 with 3
2 with 4

I want to know which rows and how many rows of column 1 has values (except 0) in comparison to other columns The same for the others too

for example

X1 has 12 values row 5,6,7, 11 has values while X2 does not row 8 has value while M1 does not Row 2 and 8 has values while M2 does not

So output would be like this 

   Total X1+  X1-  X2+  X2-  M1+  M1-  M2+  M2-
X1   12   0    0   4    0     2    0    1    0 
X2   8    0    4   0    0     1    4    2    4
M1   11   0    1   4    1     0    0    1    0  
M2   10   0    2   4    2     0    1    0    0

To calculate the total of each column, I can do the following 

colSums(df != 0)

I tried to work with df$X1[df$X1 %in% df$X2] for the rest but I could not figure it out how to make such a output

r
1 Answers

In my code I use %>% in library(dplyr) just for tidiness. I don't use other functions in it.

df2 <- data.frame(df != 0)
pair <- expand.grid(1:4, 1:4)
name <- names(df)

mapply(function(x, y){
  a <- sum(df2[[x]] - df2[[y]] == 1)   # TRUE - FALSE = 1
  b <- sum(df2[[x]] - df2[[y]] == -1)  # FALSE - TRUE = -1
  return(c(a, b))
}, pair[[1]], pair[[2]]) %>% t %>%
   matrix(4, 8, dimnames = list(name, c(paste0(name, "+"), c(paste0(name, "-"))))) %>%
   as.data.frame

Output: (I mark the two values X1-M1+ and X1-M2+ with *, which means the outcomes different with yours.)

#    X1+ X2+  M1+  M2+ X1- X2- M1- M2-
# X1   0   4   1*   2*   0   0   0   0
# X2   0   0   1    2    4   0   4   4
# M1   0   4   0    1    1   1   0   0
# M2   0   4   0    0    2   2   1   0

Description

  • df2 : Turn the data to booleans which mean whether the values are unequal to zero.
  • pair: Create all possible pairs of columns.
  • a: For the condition you expect in X1+ X2+ M1+ M2+
  • b: For the condition you expect in X1- X2- M1- M2-
user_100689850
Related