This is a test version of Biostars. For the public version, visit https://www.biostars.org.
How to find pattern matching over range of columns but not present in other columns?

Hello,

I have a huge file with 0s and 1s. The position where there is a snp is given 1 and others are 0. Also, I have different populations in the same tsv file. My goal is to find signature SNPs present in a particular population (say population 1 is from column1 to column5). So I need the positions where 1 is present in all 5 columns for population 1 and 0s in all the other columns. 

POS     pop1  pop1 pop1 pop1 pop1 pop2 pop2 pop2 pop2 pop2 pop3 pop3 pop3 pop3 pop3 pop4 pop4 pop4 pop4 pop4
 746     1       1       1       1       1       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0           
 762     0       0       0       0       0       0       0       0       0       0       1      1       1       1       1       0       0       0       0       0

So my result for the signature snp position for pop1 would be 746 and for pop3 would be 762. Are there any tools to do this ? I tried using awk for this but it gave an error. My awk command is as follows ; 

$ awk -F '\t'  '$1,$2,$3,$4.$5 ~ /1/' file.tsv

Is there a way where I can specify range of columns in awk pattern match ?

I hope my question is clear. Any help would be appreciated. Thanks in advance.

snp r shell
• 3,748 views • link
updated by AK AK • written by Inquisitive8995 Inquisitive8995

Is the number of column per population equal for all population or do this vary? Do you know this number before starting?

• 0 views • link
written by finswimmer finswimmer

They not equal but I know the number before starting

• 0 views • link
written by Inquisitive8995 Inquisitive8995

You could start by summing up the numbers of 1s per group and then filter for groups where the sum of 1s is identical to the number of elements per group like for pop1 take only rows where sum is 5. I strongly suggest to solve these kinds of things yourself, you will see it enhances your skills and enables you to abstract the knowledge to other problems. Stuff like that you'll probably encounter repetitively during your career.

• 0 views • link
written by ATpoint ATpoint

2 answers

Using data.table, reshape wide-to-long, then get number of 1s and number of rows per "POS, pop", if numbers equal then subset.

library(data.table)

dt1 <- fread("
POS     pop1  pop1 pop1 pop1 pop1 pop2 pop2 pop2 pop2 pop2 pop3 pop3 pop3 pop3 pop3 pop4 pop4 pop4 pop4 pop4
746     1       1       1       1       1       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0           
762     0       0       0       0       0       0       0       0       0       0       1      1       1       1       1       0       0       0       0       0
")

melt(dt1, id.vars = "POS")[, list(countOnes = sum(value == 1), countPops = .N),
                           by = .(POS, variable)][ countOnes == countPops, ]

#    POS variable countOnes countPops
# 1: 746     pop1         5         5
# 2: 762     pop3         5         5
• 0 views • link
written by zx8754 zx8754

Hi Inquisitive8995,

Would there be two pops that are with all 1, or 1 pop with all 1 and another pop with some non-zero data? zx8754's solution is great, but note that:

So I need the positions where 1 is present in all 5 columns for population 1 and 0s in all the other columns.

When there are 2 pops with all 1 (777 see below example) or 1 pop with all 1 but another pop with non-zero data (778), you'll need to further filter them out:

> library(data.table)
> dt1 <- fread("
+ POS     pop1  pop1 pop1 pop1 pop1 pop2 pop2 pop2 pop2 pop2 pop3 pop3 pop3 pop3 pop3 pop4 pop4 pop4 pop4 pop4
+ 746     1       1       1       1       1       0       0       0       0       0       0       0       0       0       0       0       0       0       0       0           
+ 762     0       0       0       0       0       0       0       0       0       0       1      1       1       1       1       0       0       0       0       0
+ 777     1       1       1       1       1       0       0       0       0       0       1      1       1       1       1       0       0       0       0       0
+ 778     1       1       1       1       1       1       1       0       0       0       0      0       0       0       0       0       0       0       0       0
+ 779     0       1       1       1       1       0       0       0       0       0       0      0       0       0       0       0       0       0       0       0
+ ")
> melt(dt1, id.vars = "POS")[, list(countOnes = sum(value == 1), countPops = .N),
+                            by = .(POS, variable)][ countOnes == countPops, ]
   POS variable countOnes countPops
1: 746     pop1         5         5
2: 777     pop1         5         5
3: 778     pop1         5         5
4: 762     pop3         5         5
5: 777     pop3         5         5

An alternative but not so fancy way can be:

library(magrittr)

# Get pop size (number of total columns)
df <- read.delim("file.tsv", check.names = FALSE, row.names = 1)
nf <- table(colnames(df))

# Calculate sum for each pop across rows
df_rowsum <- t(rowsum(t(df), group = colnames(df)))
df_rowsum
#     pop1 pop2 pop3 pop4
# 746    5    0    0    0
# 762    0    0    5    0
# 777    5    0    5    0
# 778    5    2    0    0
# 779    4    0    0    0

# Get rows with all 0 sum except for 1 pop
df_rowsum_check <- rowSums(df_rowsum == 0) == (ncol(df_rowsum) - 1)

# Get rows with sum equal to pop size
df_popsize <- df_rowsum %in% nf[colnames(df_rowsum)] %>%
  matrix(ncol = ncol(df_rowsum)) %>%
  as.data.frame()
colnames(df_popsize) <- colnames(df_rowsum)
rownames(df_popsize) <- rownames(df_rowsum)
df_popsize_check <- rowSums(df_popsize) == "1"

# Select the positions that meet both
df_rowsum_check & df_popsize_check
#  746   762   777   778   779 
# TRUE  TRUE FALSE FALSE FALSE
• 0 views • link
written by AK AK

Log in to answer this question.

More posts like this