Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-12T06:11:07.893Z
Answers.LastActivityDate: 2023-10-12T06:11:07.893Z
Answers.UserDisplayName: haci
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-12T07:53:12.137Z
Answers.Text: 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.
Answers.UserDisplayName: ATpoint
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-10-12T07:39:58.153Z
Answers.LastActivityDate: 2023-10-12T07:47:27.773Z
Answers.LastEditDate: 2023-10-12T07:47:27.773Z
Answers.UserDisplayName: ATpoint
Body: <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>
  
Tags: <r><genomics><bash><tsv>
Title: How to calculate average from a column when consecutive cells are similar in different columns?
ThreadText: # 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
    <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](https://stackoverflow.com/a/34487771/7236472)
  
  
  ---
  
  ### 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:
  
  ```bash
  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.  
  
  ---
  
  