<pre><code>library(dplyr)
cols_of_interest &lt;- c(&quot;chrom&quot;, &quot;start_pr&quot;, &quot;end_pr&quot;)

test_data |&gt;
  group_by(across(all_of(cols_of_interest))) |&gt;
  summarize(Mean_WPS = mean(WPS_win))
</code></pre>
<p>gives the following with your test data above:</p>
<pre><code># A tibble: 3 × 4
# Groups:   chrom, start_pr [3]
  chrom start_pr  end_pr Mean_WPS
  &lt;chr&gt;    &lt;int&gt;   &lt;int&gt;    &lt;dbl&gt;
1 chr1    981918  984117   -1.59 
2 chr1    999138 1001337   -0.857
3 chr1    999973 1002172   -0.857
</code></pre>
<p><a href="https://stackoverflow.com/a/34487771/7236472">Ref for the code used above</a></p>

CC BY-SA 4.0
2023-10-12T06:11:07.893Z
2023-10-12T06:11:07.893Z
haci
<p>I use <code>bedtools merge</code> with a little trick to combine the first three columns into a single one to &quot;mock&quot; each of the unique intervals into one &quot;chromosome&quot; so bedtools easily knows that these are the same:</p>
<pre class="lang-bash prettyprint-override"><code>cat example.bed \
| awk 'NR &gt; 1 {OFS=&quot;\t&quot;; print <span class="math-container">$1"--"$</span>2&quot;--&quot;<span class="math-container">$3"--", 1, 1, $</span>7}' \
| bedtools merge -i - -c 4 -o mean \
| awk 'BEGIN {OFS=&quot;\t&quot;; print &quot;chrom&quot;, &quot;start_pr&quot;, &quot;end_pr&quot;, &quot;Mean_WPS&quot;} {split(<span class="math-container">$1,x,"--"); print x[1], x[2], x[3], $</span>4}'

chrom   start_pr        end_pr  Mean_WPS
chr1    981918  984117  -1.590909091
chr1    999138  1001337 -0.8571428571
chr1    999973  1002172 -0.8571428571
</code></pre>
<p>First <code>awk</code> 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.</p>
<p><code>bedtools</code> command: Merges the identical intervals, taking the mean of the fourth column.</p>
<p>Second <code>awk</code> 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.</p>
<p>Memory footprint of this should be minimal.</p>

CC BY-SA 4.0
2023-10-12T07:53:12.137Z
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.
ATpoint
CC BY-SA 4.0
2023-10-12T07:39:58.153Z
2023-10-12T07:47:27.773Z
2023-10-12T07:47:27.773Z
ATpoint
<p>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,</p>
<pre><code>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
</code></pre>
<p>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,</p>
<pre><code>chrom   start_pr    end_pr  Mean_WPS
chr1    981918  984117  -1.590909091
chr1    999138  1001337 -0.857142857
chr1    999973  1002172 -0.857142857
</code></pre>
<p>I'm open to both R and Bash. Any suggestion to overcome this issue would be heartily appreciated.</p>

<r><genomics><bash><tsv>
How to calculate average from a column when consecutive cells are similar in different columns?
# How to calculate average from a column when consecutive cells are similar in different columns?

(Asked by: username_0 on 2023-10-12)

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.

**Tags:** r, genomics, bash, tsv

---

## 2 answers

### Answer by username_1 on 2023-10-12. Score: 2 | Accepted answer

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
             
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 by username_2 on 2023-10-12. Score: 0

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.

> **Comments:**
> **username_2**: 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.  

---

