I have a binary table like this in my R script:
>class(forCount)
[1] "table"
>forCount
Gene
Filename CTX-M-27 IMI-1 IMP-39 IMP-4 KPC-2 NDM-1
batch0_01032019_ENT1 0 1 0 0 0 1
batch0_01032019_ENT2 0 0 0 0 1 1
batch0_01032019_ENT3 0 0 0 0 0 1
batch0_01032019_ENT4 0 0 0 0 0 1
batch0_01032019_ENT5 0 0 0 0 0 1
batch0_01032019_ENT6 0 0 0 0 0 1
batch0_01032019_ENT7 0 0 0 0 0 1
How do I get the following information from this?
NDM-1 5
NDM-1&IMI-1 1
NDM-1&KPC-2 1
Edit1: Above data was dummy data. As per @RonakShah request adding dput information. This is the sample of my data in the table.
> dput(forCount)
structure(c(0L, 0L, 0L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L), .Dim = c(6L, 16L), .Dimnames = structure(list(AssemblyFile = c("batch0_01032019_ENT1110",
"batch0_01032019_ENT1125", "batch0_01032019_ENT1332", "batch0_01032019_ENT1349",
"batch0_01032019_ENT1449", "batch0_01032019_ENT1607"), CPGene = c("",
"CTX-M-27", "IMI-1", "IMP-39", "IMP-4", "KPC-2", "NDM-1", "NDM-4",
"NDM-5", "NDM-7", "NDM-9", "OXA-181", "OXA-23", "OXA-232", "OXA-48",
"VIM-4")), .Names = c("AssemblyFile", "CPGene")), class = "table")
From the above pasted dput data, I expect the following output, which is out of 6 samples, 5 samples have KPC-2 and 1 sample has both KPC-2 & CTX-M-27.
KPC-2 5
KPC-2&CTX-M-27 1
You could convert the table to dataframe and paste the column names in each row which has 1 as value in them and count their occurrence using table.
df <- as.data.frame.matrix(forCount)
table(apply(df, 1, function(x) paste(names(df)[which(x == 1)], collapse = " & ")))
#CTX-M-27 & KPC-2 KPC-2
# 1 5