Answers.Body: <p>The fact that you mentioned over 30M rows and would like to use <code>R</code>, here is a solution with <code>data.table</code>. This works on your toy data above (minus the row IDs).</p>
  <p>Basically, 1) data is read in chunks (by chromosome) thanks to <code>data.table::fread()</code>'s <code>cmd</code> argument, 2) the <code>group</code> variable defines the chunks (of 1000), 3) chunks are averaged.</p>
  <pre><code>library(data.table)
  
  chromosomes &lt;- c(paste0(&quot;chr&quot;, 1:22), &quot;X&quot;, &quot;Y&quot;)
  
  res &lt;- data.frame()
  
  for (chr in chromosomes) {
    
    # 1
    cmd &lt;- paste(&quot;grep&quot;, chr, &quot;test.txt&quot;)
    table &lt;- fread(cmd = cmd)
  
    # 2
    table[, group := ceiling(.I / 1000)]
  
    # 3
    table[, mean:= mean(V4), by = group]
  
    # data is read w/o column names, so V1 to V4 and two columns are added afterwards
    temp_res &lt;- unique(table[,c(&quot;V1&quot;,&quot;group&quot;,&quot;mean&quot;)])
    res &lt;- rbind(res, temp_res)
  }
  <span class="math-container">```</span>
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-04-11T19:05:36.940Z
Answers.LastActivityDate: 2024-04-11T19:30:42.193Z
Answers.LastEditDate: 2024-04-11T19:30:42.193Z
Answers.UserDisplayName: haci
Body: <p>I have a large bed-like dataframe with over 30 million rows.  What is the best way to calculate the mean value of <code>fourth</code> column <code>Mnuc.occ</code> for each 1 MB?</p>
  <p>That is, after every 10000 rows of the <code>second</code> and <code>third</code> columns, it is going to calculate the mean of the <code>fourth</code> (<code>Mnuc.occ</code>) column until the <code>first</code> column character is identical. The first column values range from <code>chr1-22</code>, <code>X</code>, and <code>Y</code>.</p>
  <p>This is the first 100 rows of the dataframe,</p>
  <pre><code>    head(MoccWin2, 100)
      chrom start   end  Mnuc.occ
  101  chr1 10000 10100 1.1040662
  102  chr1 10100 10200 2.4800527
  103  chr1 10200 10300 2.3774872
  104  chr1 10300 10400 1.5302868
  105  chr1 10400 10500 0.9100506
  106  chr1 10500 10600 0.9593023
  107  chr1 10600 10700 1.1641849
  108  chr1 10700 10800 1.0714583
  109  chr1 10800 10900 1.0224870
  110  chr1 10900 11000 0.9918500
  111  chr1 11000 11100 0.9350614
  112  chr1 11100 11200 0.9508936
  113  chr1 11200 11300 0.9664682
  114  chr1 11300 11400 0.9940215
  115  chr1 11400 11500 1.0403650
  116  chr1 11500 11600 1.0890285
  117  chr1 11600 11700 1.0483800
  118  chr1 11700 11800 0.8518338
  119  chr1 11800 11900 0.7346045
  120  chr1 11900 12000 0.7682374
  121  chr1 12000 12100 0.8008606
  122  chr1 12100 12200 0.8092831
  123  chr1 12200 12300 0.8123239
  124  chr1 12300 12400 0.7620709
  125  chr1 12400 12500 0.7762382
  126  chr1 12500 12600 0.8005416
  127  chr1 12600 12700 0.8087839
  128  chr1 12700 12800 0.8209275
  129  chr1 12800 12900 0.8089455
  130  chr1 12900 13000 0.8024111
  131  chr1 13000 13100 0.9118222
  132  chr1 13100 13200 0.9794749
  133  chr1 13200 13300 0.9248129
  134  chr1 13300 13400 0.9679726
  135  chr1 13400 13500 1.0839498
  136  chr1 13500 13600 1.0681117
  137  chr1 13600 13700 1.0580199
  138  chr1 13700 13800 0.9696408
  139  chr1 13800 13900 0.9414343
  140  chr1 13900 14000 1.0127619
  141  chr1 14000 14100 1.1240054
  142  chr1 14100 14200 1.1146685
  143  chr1 14200 14300 1.0689007
  144  chr1 14300 14400 1.1095060
  145  chr1 14400 14500 1.1571254
  146  chr1 14500 14600 1.1790622
  147  chr1 14600 14700 1.1745363
  148  chr1 14700 14800 1.1982927
  149  chr1 14800 14900 1.1433007
  150  chr1 14900 15000 1.1179703
  151  chr1 15000 15100 1.1700841
  152  chr1 15100 15200 1.1828714
  153  chr1 15200 15300 1.0548872
  154  chr1 15300 15400 1.0035861
  155  chr1 15400 15500 1.1752040
  156  chr1 15500 15600 1.2811080
  157  chr1 15600 15700 1.2966526
  158  chr1 15700 15800 1.2534465
  159  chr1 15800 15900 1.1911138
  160  chr1 15900 16000 1.0732241
  161  chr1 16000 16100 1.1509326
  162  chr1 16100 16200 1.1632787
  163  chr1 16200 16300 1.1123614
  164  chr1 16300 16400 1.0475494
  165  chr1 16400 16500 1.1490898
  166  chr1 16500 16600 1.1743798
  167  chr1 16600 16700 1.1538763
  168  chr1 16700 16800 1.1152664
  169  chr1 16800 16900 1.0935545
  170  chr1 16900 17000 1.0783431
  171  chr1 17000 17100 0.9963264
  172  chr1 17100 17200 0.9471138
  173  chr1 17200 17300 0.9444394
  174  chr1 17300 17400 0.9666628
  175  chr1 17400 17500 0.9914187
  176  chr1 17500 17600 1.0384729
  177  chr1 17600 17700 1.0415952
  178  chr1 17700 17800 1.0901377
  179  chr1 17800 17900 1.0844210
  180  chr1 17900 18000 1.0691614
  181  chr1 18000 18100 1.1441354
  182  chr1 18100 18200 1.1991584
  183  chr1 18200 18300 1.1969477
  184  chr1 18300 18400 1.1963778
  185  chr1 18400 18500 1.1787441
  186  chr1 18500 18600 1.1322677
  187  chr1 18600 18700 1.0980561
  188  chr1 18700 18800 1.2007794
  189  chr1 18800 18900 1.2912411
  190  chr1 18900 19000 1.1936124
  191  chr1 19000 19100 1.0884831
  192  chr1 19100 19200 1.0877788
  193  chr1 19200 19300 1.1149980
  194  chr1 19300 19400 1.0299228
  195  chr1 19400 19500 0.9889423
  196  chr1 19500 19600 1.0026908
  197  chr1 19600 19700 0.9682903
  198  chr1 19700 19800 1.0586240
  199  chr1 19800 19900 1.1105628
  200  chr1 19900 20000 1.0171287
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-04-11T14:33:47.910Z
Comments.Text: What have you tried? Explore dplyr's grouping/aggregate functions.
Comments.UserDisplayName: Ram RS
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-04-11T15:34:10.623Z
Comments.Text: The term 'for each 1MB' is not so clear. What is an MB? You explain later that it relates to 10000 rows, but it might be useful to spell it out more clearly.
Comments.UserDisplayName: Sextus Empiricus
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2024-04-11T15:38:30.900Z
Comments.Text: I would think about simply computing the mean of all rows from $[k\cdot10000,(k+1)\cdot 10000]$. But maybe you have additional considerations, like including checks whether all the rows are ordered and there are no missing rows?
Comments.UserDisplayName: Sextus Empiricus
Tags: <r><genomics><bed><dataframe>
Title: How to measure mean of a column for every 10000 rows condition on other column?
ThreadText: # How to measure mean of a column for every 10000 rows condition on other column?
  
  (Asked by: username_0 on 2024-04-11)
  
  I have a large bed-like dataframe with over 30 million rows. What is the best way to calculate the mean value of `fourth` column `Mnuc.occ` for each 1 MB?
  
  That is, after every 10000 rows of the `second` and `third` columns, it is going to calculate the mean of the `fourth` (`Mnuc.occ`) column until the `first` column character is identical. The first column values range from `chr1-22`, `X`, and `Y`.
  
  This is the first 100 rows of the dataframe,
  
  ```
      head(MoccWin2, 100)
      chrom start   end  Mnuc.occ
  101  chr1 10000 10100 1.1040662
  102  chr1 10100 10200 2.4800527
  103  chr1 10200 10300 2.3774872
  104  chr1 10300 10400 1.5302868
  105  chr1 10400 10500 0.9100506
  106  chr1 10500 10600 0.9593023
  107  chr1 10600 10700 1.1641849
  108  chr1 10700 10800 1.0714583
  109  chr1 10800 10900 1.0224870
  110  chr1 10900 11000 0.9918500
  111  chr1 11000 11100 0.9350614
  112  chr1 11100 11200 0.9508936
  113  chr1 11200 11300 0.9664682
  114  chr1 11300 11400 0.9940215
  115  chr1 11400 11500 1.0403650
  116  chr1 11500 11600 1.0890285
  117  chr1 11600 11700 1.0483800
  118  chr1 11700 11800 0.8518338
  119  chr1 11800 11900 0.7346045
  120  chr1 11900 12000 0.7682374
  121  chr1 12000 12100 0.8008606
  122  chr1 12100 12200 0.8092831
  123  chr1 12200 12300 0.8123239
  124  chr1 12300 12400 0.7620709
  125  chr1 12400 12500 0.7762382
  126  chr1 12500 12600 0.8005416
  127  chr1 12600 12700 0.8087839
  128  chr1 12700 12800 0.8209275
  129  chr1 12800 12900 0.8089455
  130  chr1 12900 13000 0.8024111
  131  chr1 13000 13100 0.9118222
  132  chr1 13100 13200 0.9794749
  133  chr1 13200 13300 0.9248129
  134  chr1 13300 13400 0.9679726
  135  chr1 13400 13500 1.0839498
  136  chr1 13500 13600 1.0681117
  137  chr1 13600 13700 1.0580199
  138  chr1 13700 13800 0.9696408
  139  chr1 13800 13900 0.9414343
  140  chr1 13900 14000 1.0127619
  141  chr1 14000 14100 1.1240054
  142  chr1 14100 14200 1.1146685
  143  chr1 14200 14300 1.0689007
  144  chr1 14300 14400 1.1095060
  145  chr1 14400 14500 1.1571254
  146  chr1 14500 14600 1.1790622
  147  chr1 14600 14700 1.1745363
  148  chr1 14700 14800 1.1982927
  149  chr1 14800 14900 1.1433007
  150  chr1 14900 15000 1.1179703
  151  chr1 15000 15100 1.1700841
  152  chr1 15100 15200 1.1828714
  153  chr1 15200 15300 1.0548872
  154  chr1 15300 15400 1.0035861
  155  chr1 15400 15500 1.1752040
  156  chr1 15500 15600 1.2811080
  157  chr1 15600 15700 1.2966526
  158  chr1 15700 15800 1.2534465
  159  chr1 15800 15900 1.1911138
  160  chr1 15900 16000 1.0732241
  161  chr1 16000 16100 1.1509326
  162  chr1 16100 16200 1.1632787
  163  chr1 16200 16300 1.1123614
  164  chr1 16300 16400 1.0475494
  165  chr1 16400 16500 1.1490898
  166  chr1 16500 16600 1.1743798
  167  chr1 16600 16700 1.1538763
  168  chr1 16700 16800 1.1152664
  169  chr1 16800 16900 1.0935545
  170  chr1 16900 17000 1.0783431
  171  chr1 17000 17100 0.9963264
  172  chr1 17100 17200 0.9471138
  173  chr1 17200 17300 0.9444394
  174  chr1 17300 17400 0.9666628
  175  chr1 17400 17500 0.9914187
  176  chr1 17500 17600 1.0384729
  177  chr1 17600 17700 1.0415952
  178  chr1 17700 17800 1.0901377
  179  chr1 17800 17900 1.0844210
  180  chr1 17900 18000 1.0691614
  181  chr1 18000 18100 1.1441354
  182  chr1 18100 18200 1.1991584
  183  chr1 18200 18300 1.1969477
  184  chr1 18300 18400 1.1963778
  185  chr1 18400 18500 1.1787441
  186  chr1 18500 18600 1.1322677
  187  chr1 18600 18700 1.0980561
  188  chr1 18700 18800 1.2007794
  189  chr1 18800 18900 1.2912411
  190  chr1 18900 19000 1.1936124
  191  chr1 19000 19100 1.0884831
  192  chr1 19100 19200 1.0877788
  193  chr1 19200 19300 1.1149980
  194  chr1 19300 19400 1.0299228
  195  chr1 19400 19500 0.9889423
  196  chr1 19500 19600 1.0026908
  197  chr1 19600 19700 0.9682903
  198  chr1 19700 19800 1.0586240
  199  chr1 19800 19900 1.1105628
  200  chr1 19900 20000 1.0171287
  
  ```
  
  **Tags:** r, genomics, bed, dataframe
  
  > **Comments:**
  > **username_1**: What have you tried? Explore dplyr's grouping/aggregate functions.  
  > **username_2**: The term 'for each 1MB' is not so clear. What is an MB? You explain later that it relates to 10000 rows, but it might be useful to spell it out more clearly.  
  > **username_2**: I would think about simply computing the mean of all rows from $[k\cdot10000,(k+1)\cdot 10000]$. But maybe you have additional considerations, like including checks whether all the rows are ordered and there are no missing rows?  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_3 on 2024-04-11. Score: 3
  
  The fact that you mentioned over 30M rows and would like to use `R`, here is a solution with `data.table`. This works on your toy data above (minus the row IDs).
  
  Basically, 1) data is read in chunks (by chromosome) thanks to `data.table::fread()`'s `cmd` argument, 2) the `group` variable defines the chunks (of 1000), 3) chunks are averaged.
  
  ```
  library(data.table)
  
  chromosomes <- c(paste0("chr", 1:22), "X", "Y")
  
  res <- data.frame()
  
  for (chr in chromosomes) {
    
    # 1
    cmd <- paste("grep", chr, "test.txt")
    table <- fread(cmd = cmd)
  
    # 2
    table[, group := ceiling(.I / 1000)]
  
    # 3
    table[, mean:= mean(V4), by = group]
  
    # data is read w/o column names, so V1 to V4 and two columns are added afterwards
    temp_res <- unique(table[,c("V1","group","mean")])
    res <- rbind(res, temp_res)
  }
  ```
  
  ```
  
  
  ---
  
  