How to calculate average from a column when consecutive cells are similar in different columns?

Question

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.

Answer 1

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

Answer 2

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.