<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>

CC BY-SA 4.0
2024-04-11T19:05:36.940Z
2024-04-11T19:30:42.193Z
2024-04-11T19:30:42.193Z
haci
<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>

CC BY-SA 4.0
2024-04-11T14:33:47.910Z
What have you tried? Explore dplyr's grouping/aggregate functions.
Ram RS
CC BY-SA 4.0
2024-04-11T15:34:10.623Z
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.
Sextus Empiricus
CC BY-SA 4.0
2024-04-11T15:38:30.900Z
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?
Sextus Empiricus
<r><genomics><bed><dataframe>
How to measure mean of a column for every 10000 rows condition on other column?
# 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)
}
```


---

