0
$\begingroup$

I wonder how can I calculate the average of column 7 (WPS_win) when consecutive cells in column 2, column 3, and column 1 are the same. The file is in bed format (tab-separated format). The original file is very large. The first few lines of the file look like this,

chrom   start_pr    end_pr  width_pr    start_win   end_win WPS_win .overlap
chr1    981918  984117  2200    981900  982000  -5  82
chr1    981918  984117  2200    982000  982100  -1  100
chr1    981918  984117  2200    982200  982300  -2  100
chr1    981918  984117  2200    982300  982400  2   100
chr1    981918  984117  2200    982400  982500  -3  100
chr1    981918  984117  2200    982500  982600  -1  100
chr1    981918  984117  2200    982600  982700  -3  100
chr1    981918  984117  2200    982700  982800  1   100
chr1    981918  984117  2200    982800  982900  -5  100
chr1    981918  984117  2200    982900  983000  0   100
chr1    981918  984117  2200    983000  983100  0   100
chr1    981918  984117  2200    983100  983200  -5  100
chr1    981918  984117  2200    983200  983300  -1  100
chr1    981918  984117  2200    983300  983400  -3  100
chr1    981918  984117  2200    983400  983500  1   100
chr1    981918  984117  2200    983500  983600  -2  100
chr1    981918  984117  2200    983600  983700  1   100
chr1    981918  984117  2200    983700  983800  -3  100
chr1    981918  984117  2200    983800  983900  1   100
chr1    981918  984117  2200    983900  984000  -4  100
chr1    981918  984117  2200    984000  984100  -1  100
chr1    981918  984117  2200    984100  984200  -2  17
chr1    999138  1001337 2200    999100  999200  1   62
chr1    999138  1001337 2200    999200  999300  -1  100
chr1    999138  1001337 2200    999700  999800  -1  100
chr1    999138  1001337 2200    999800  999900  -2  100
chr1    999138  1001337 2200    999900  1000000 -2  100
chr1    999138  1001337 2200    1000000 1000100 1   100
chr1    999138  1001337 2200    1000100 1000200 -2  100
chr1    999138  1001337 2200    1000200 1000300 0   100
chr1    999138  1001337 2200    1000300 1000400 -1  100
chr1    999138  1001337 2200    1000400 1000500 -2  100
chr1    999138  1001337 2200    1000500 1000600 -2  100
chr1    999138  1001337 2200    1000600 1000700 2   100
chr1    999138  1001337 2200    1000700 1000800 -1  100
chr1    999138  1001337 2200    1000800 1000900 -2  100
chr1    999973  1002172 2200    999900  1000000 -2  27
chr1    999973  1002172 2200    1000000 1000100 1   100
chr1    999973  1002172 2200    1000100 1000200 -2  100
chr1    999973  1002172 2200    1000200 1000300 0   100
chr1    999973  1002172 2200    1000300 1000400 -1  100
chr1    999973  1002172 2200    1000400 1000500 -2  100
chr1    999973  1002172 2200    1000500 1000600 -2  100
chr1    999973  1002172 2200    1000600 1000700 2   100
chr1    999973  1002172 2200    1000700 1000800 -1  100
chr1    999973  1002172 2200    1000800 1000900 -2  100
chr1    999973  1002172 2200    1001600 1001700 -1  100
chr1    999973  1002172 2200    1001700 1001800 -2  100
chr1    999973  1002172 2200    1001800 1001900 1   100
chr1    999973  1002172 2200    1001900 1002000 -1  100

In the above table, the first 22 rows of cols 1, 2, and 3 are the same. Therefore, The expected output would look like this,

chrom   start_pr    end_pr  Mean_WPS
chr1    981918  984117  -1.590909091
chr1    999138  1001337 -0.857142857
chr1    999973  1002172 -0.857142857

I'm open to both R and Bash. Any suggestion to overcome this issue would be heartily appreciated.

$\endgroup$

2 Answers 2

2
$\begingroup$
library(dplyr)
cols_of_interest <- c("chrom", "start_pr", "end_pr")

test_data |>
  group_by(across(all_of(cols_of_interest))) |>
  summarize(Mean_WPS = mean(WPS_win))

gives the following with your test data above:

# A tibble: 3 × 4
# Groups:   chrom, start_pr [3]
  chrom start_pr  end_pr Mean_WPS
  <chr>    <int>   <int>    <dbl>
1 chr1    981918  984117   -1.59 
2 chr1    999138 1001337   -0.857
3 chr1    999973 1002172   -0.857

Ref for the code used above

$\endgroup$
0
$\begingroup$

I use bedtools merge with a little trick to combine the first three columns into a single one to "mock" each of the unique intervals into one "chromosome" so bedtools easily knows that these are the same:

cat example.bed \
| awk 'NR > 1 {OFS="\t"; print $1"--"$2"--"$3"--", 1, 1, $7}' \
| bedtools merge -i - -c 4 -o mean \
| awk 'BEGIN {OFS="\t"; print "chrom", "start_pr", "end_pr", "Mean_WPS"} {split($1,x,"--"); print x[1], x[2], x[3], $4}'

chrom   start_pr        end_pr  Mean_WPS
chr1    981918  984117  -1.590909091
chr1    999138  1001337 -0.8571428571
chr1    999973  1002172 -0.8571428571

First awk command: Skips first line, combines first three columns into one and prints this together with a single position for start and end plus the score.

bedtools command: Merges the identical intervals, taking the mean of the fourth column.

Second awk command: Splits the first column again into the former chrom-start-end and puts the average score in column 4, and also adds a header line.

Memory footprint of this should be minimal.

$\endgroup$
1
  • $\begingroup$ Side note: If one copies the example data from OP then simply do a tr -s " " | tr " " "\t" on it to remove the many whitespaces and replace by a single one, then replace whitespace by tab to create a proper tsv file for parsing. $\endgroup$
    – ATpoint
    Oct 12, 2023 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.