Home > Back-end >  Add a grouping column by row number in R
Add a grouping column by row number in R

Time:09-06

I have time series data:

mytime <- seq.Date(as.Date("2015-01-01"), as.Date("2022-01-01"), "day")
value <- rnorm(n = length(mytime))
df <- cbind.data.frame(mytime, value)

I want to create a new column which starts with 1 and increases by 1 for every fixed number of rows. For example, the first 100 rows get a value of 1, the next 100 rows get a value of 2, and so on.

CodePudding user response:

This should work:

library(tidyverse)

mytime <- seq.Date(as.Date("2015-01-01"), as.Date("2022-01-01"), "day")
value <- rnorm(n = length(mytime))
df <- cbind.data.frame(mytime, value)

df %>%
  mutate(grouping = rep(c(1:100), each = 100)[1:length(value)])
#>          mytime         value grouping
#> 1    2015-01-01  0.8188726406        1
#> 2    2015-01-02 -0.7051437552        1
#> 3    2015-01-03 -0.4052449198        1
#> 4    2015-01-04  0.1069765445        1
#> 5    2015-01-05 -1.7624077376        1
#> 6    2015-01-06 -1.3541046081        1
#> 7    2015-01-07 -1.5836060959        1
#> 8    2015-01-08  1.6975299938        1
#> 9    2015-01-09  0.1365021842        1
#> 10   2015-01-10  1.3583782190        1
#> 11   2015-01-11  0.4389094423        1
#> 12   2015-01-12 -1.2695437977        1
#> 13   2015-01-13 -0.0185099335        1
#> 14   2015-01-14 -1.2504712388        1
#> 15   2015-01-15  0.4999558250        1
#> 16   2015-01-16  0.7138838410        1
#> 17   2015-01-17 -1.7709035964        1
#> 18   2015-01-18 -0.6555022760        1
#> 19   2015-01-19  0.2771728385        1
#> 20   2015-01-20 -0.2647472076        1
#> 21   2015-01-21 -0.1846148670        1
#> 22   2015-01-22 -1.7964703879        1
#> 23   2015-01-23  1.0119717818        1
#> 24   2015-01-24 -1.4243738227        1
#> 25   2015-01-25 -1.2956929904        1
#> 26   2015-01-26 -0.6236723296        1
#> 27   2015-01-27 -0.4082983500        1
#> 28   2015-01-28  2.3180442246        1
#> 29   2015-01-29 -0.3830073587        1
#> 30   2015-01-30  1.7191377387        1
#> 31   2015-01-31  1.8575794944        1
#> 32   2015-02-01 -2.1582163873        1
#> 33   2015-02-02  1.7012159685        1
#> 34   2015-02-03 -1.1991523660        1
#> 35   2015-02-04  0.9475081079        1
#> 36   2015-02-05  0.1152464165        1
#> 37   2015-02-06 -2.6407434397        1
#> 38   2015-02-07  1.3821892807        1
#> 39   2015-02-08  0.8054893231        1
#> 40   2015-02-09  0.9223800849        1
#> 41   2015-02-10 -0.3546256458        1
#> 42   2015-02-11  0.6478162596        1
#> 43   2015-02-12 -0.7505130565        1
#> 44   2015-02-13 -2.0366870996        1
#> 45   2015-02-14  1.0649788649        1
#> 46   2015-02-15 -0.9217779191        1
#> 47   2015-02-16 -0.3045693249        1
#> 48   2015-02-17  0.0518172566        1
#> 49   2015-02-18  0.0532814044        1
#> 50   2015-02-19  0.1876367083        1
#> 51   2015-02-20  0.3327661457        1
#> 52   2015-02-21 -0.2952679556        1
#> 53   2015-02-22  0.3293960050        1
#> 54   2015-02-23  1.2409077698        1
#> 55   2015-02-24  0.3580355273        1
#> 56   2015-02-25 -1.4924835886        1
#> 57   2015-02-26  0.7058099312        1
#> 58   2015-02-27  0.2104966444        1
#> 59   2015-02-28 -0.3057447517        1
#> 60   2015-03-01  1.5756875721        1
#> 61   2015-03-02 -0.1917941771        1
#> 62   2015-03-03  0.5913340531        1
#> 63   2015-03-04 -0.5700276892        1
#> 64   2015-03-05  1.0740621827        1
#> 65   2015-03-06 -1.2117093430        1
#> 66   2015-03-07  1.1110831399        1
#> 67   2015-03-08 -0.4552585955        1
#> 68   2015-03-09 -0.8588412294        1
#> 69   2015-03-10  1.9932422428        1
#> 70   2015-03-11 -1.7018407616        1
#> 71   2015-03-12 -0.0308941351        1
#> 72   2015-03-13  0.5055698207        1
#> 73   2015-03-14  0.4188607070        1
#> 74   2015-03-15  0.7982967262        1
#> 75   2015-03-16 -2.2995915989        1
#> 76   2015-03-17 -0.5689886197        1
#> 77   2015-03-18 -0.7878760699        1
#> 78   2015-03-19  1.9519211037        1
#> 79   2015-03-20  0.9026785904        1
#> 80   2015-03-21  1.3952120899        1
#> 81   2015-03-22 -0.6474826181        1
#> 82   2015-03-23  0.8958113474        1
#> 83   2015-03-24 -1.2238473311        1
#> 84   2015-03-25  0.4058042441        1
#> 85   2015-03-26 -0.5709496280        1
#> 86   2015-03-27 -0.4189819537        1
#> 87   2015-03-28 -0.3253399775        1
#> 88   2015-03-29 -0.2504487158        1
#> 89   2015-03-30 -0.5048374234        1
#> 90   2015-03-31  0.2755789912        1
#> 91   2015-04-01  0.8922287071        1
#> 92   2015-04-02  1.1172195419        1
#> 93   2015-04-03 -1.6022222969        1
#> 94   2015-04-04 -0.2639181444        1
#> 95   2015-04-05 -1.0666771455        1
#> 96   2015-04-06  0.5772296824        1
#> 97   2015-04-07  0.3058956784        1
#> 98   2015-04-08  0.3958394775        1
#> 99   2015-04-09  0.4086626441        1
#> 100  2015-04-10  0.5702892942        1
#> 101  2015-04-11 -0.3305672962        2
#> 102  2015-04-12  1.1116674141        2
#> 103  2015-04-13 -0.3013942049        2
#> 104  2015-04-14 -0.5810105837        2
#> 105  2015-04-15  0.5907661366        2
#> 106  2015-04-16 -0.0033270315        2
#> 107  2015-04-17 -0.5018928764        2
#> 108  2015-04-18 -0.7306115691        2
#> 109  2015-04-19  0.8195806083        2
#> 110  2015-04-20  0.1874579340        2
#> 111  2015-04-21  0.2209803536        2
#> 112  2015-04-22 -1.3126491196        2
#> 113  2015-04-23  0.3083904082        2
#> 114  2015-04-24  0.7918114136        2
#> 115  2015-04-25 -0.6279447046        2
#> 116  2015-04-26  0.1982677395        2
#> 117  2015-04-27  0.1015474687        2
#> 118  2015-04-28 -0.9389828948        2
#> 119  2015-04-29 -0.3207971613        2
#> 120  2015-04-30  1.0007723074        2
#> 121  2015-05-01  0.3239017453        2
#> 122  2015-05-02 -1.2809204214        2
#> 123  2015-05-03 -0.5277261595        2
#> 124  2015-05-04  0.7688500927        2
#> 125  2015-05-05  1.0318535818        2
#> 126  2015-05-06  1.4980233862        2
#> 127  2015-05-07 -0.7881506596        2
#> 128  2015-05-08 -0.0463271920        2
#> 129  2015-05-09  2.0093012251        2
#> 130  2015-05-10 -0.9134628460        2
#> 131  2015-05-11 -2.3400047323        2
#> 132  2015-05-12 -1.1726139699        2
#> 133  2015-05-13  0.7844263225        2
#> 134  2015-05-14  1.3506619127        2
#> 135  2015-05-15 -2.5540631175        2
#> 136  2015-05-16  0.6912525372        2
#> 137  2015-05-17  0.7538481654        2
....

Created on 2022-09-05 with reprex v2.0.2

CodePudding user response:

We could also utilize the row_number():

library(dplyr)

df |>
  mutate(grouping = ceiling(row_number()/100))

Output:

        mytime        value grouping
1   2015-01-01  0.143792098        1
2   2015-01-02 -1.401455624        1
3   2015-01-03 -1.858456039        1
4   2015-01-04  1.187416005        1
5   2015-01-05  1.379141495        1
6   2015-01-06 -0.675080153        1
7   2015-01-07  0.320909205        1
8   2015-01-08  1.852616919        1
9   2015-01-09 -0.846052547        1
10  2015-01-10 -0.658311621        1
11  2015-01-11  0.222296116        1
12  2015-01-12 -0.543392482        1
13  2015-01-13 -0.755015488        1
14  2015-01-14 -0.178678382        1
15  2015-01-15  1.110967146        1
16  2015-01-16 -1.275580679        1
17  2015-01-17 -0.010064079        1
18  2015-01-18 -2.170296324        1
19  2015-01-19  1.250837273        1
20  2015-01-20 -1.209153067        1
21  2015-01-21 -0.550676735        1
22  2015-01-22  0.952916907        1
23  2015-01-23  0.277654831        1
24  2015-01-24  0.042829946        1
25  2015-01-25 -0.240098180        1
26  2015-01-26 -0.746263380        1
27  2015-01-27 -0.284752154        1
28  2015-01-28  0.346689091        1
29  2015-01-29 -0.666216586        1
30  2015-01-30 -0.640442501        1
31  2015-01-31 -0.244509760        1
32  2015-02-01 -2.075441987        1
33  2015-02-02  0.147406620        1
34  2015-02-03  0.363748658        1
35  2015-02-04  0.134561515        1
36  2015-02-05 -0.391123031        1
37  2015-02-06 -0.170565332        1
38  2015-02-07  0.183892659        1
39  2015-02-08 -0.854721228        1
40  2015-02-09  1.278300433        1
41  2015-02-10 -1.421003730        1
42  2015-02-11  0.913688901        1
43  2015-02-12 -0.877178883        1
44  2015-02-13  0.467617692        1
45  2015-02-14 -1.903758723        1
46  2015-02-15 -0.525691357        1
47  2015-02-16 -0.324291219        1
48  2015-02-17 -0.001652138        1
49  2015-02-18 -1.451039958        1
50  2015-02-19 -0.143701884        1
51  2015-02-20  0.921537907        1
52  2015-02-21  0.307838066        1
53  2015-02-22  1.251906011        1
54  2015-02-23 -1.824026442        1
55  2015-02-24 -1.883911514        1
56  2015-02-25  0.465843894        1
57  2015-02-26  0.087336821        1
58  2015-02-27 -0.257907284        1
59  2015-02-28 -1.215340438        1
60  2015-03-01 -0.737590344        1
61  2015-03-02 -1.152280630        1
62  2015-03-03  0.445959871        1
63  2015-03-04  0.412874111        1
64  2015-03-05  0.912774140        1
65  2015-03-06 -0.753539221        1
66  2015-03-07 -0.247727125        1
67  2015-03-08  1.248229876        1
68  2015-03-09 -0.857405365        1
69  2015-03-10 -2.062565968        1
70  2015-03-11  0.906372397        1
71  2015-03-12  1.770847797        1
72  2015-03-13 -1.194959910        1
73  2015-03-14  0.705680544        1
74  2015-03-15  0.608626405        1
75  2015-03-16  0.483917761        1
76  2015-03-17  0.486972548        1
77  2015-03-18  0.167493580        1
78  2015-03-19  1.007013432        1
79  2015-03-20  1.540288238        1
80  2015-03-21 -0.082749960        1
81  2015-03-22  0.267562341        1
82  2015-03-23  0.334862334        1
83  2015-03-24  0.678018653        1
84  2015-03-25  0.816515100        1
85  2015-03-26  1.059476108        1
86  2015-03-27  0.622612181        1
87  2015-03-28  0.851457454        1
88  2015-03-29  1.044443068        1
89  2015-03-30 -0.601267237        1
90  2015-03-31  0.569441548        1
91  2015-04-01  1.592983829        1
92  2015-04-02  1.283704270        1
93  2015-04-03  0.200713538        1
94  2015-04-04  0.902635425        1
95  2015-04-05  0.542227464        1
96  2015-04-06 -0.329488879        1
97  2015-04-07  0.040194473        1
98  2015-04-08 -0.863276688        1
99  2015-04-09 -0.830596568        1
100 2015-04-10 -0.666276306        1
101 2015-04-11  0.738113129        2
102 2015-04-12 -1.152088593        2
103 2015-04-13  0.309580066        2
104 2015-04-14  0.639723004        2
105 2015-04-15  0.926298625        2
106 2015-04-16 -1.044929798        2
107 2015-04-17 -1.088962011        2
108 2015-04-18  0.137856131        2
109 2015-04-19  0.846136781        2
110 2015-04-20  0.372345665        2
111 2015-04-21  3.400435187        2
112 2015-04-22 -2.026547096        2
113 2015-04-23 -0.106970853        2
114 2015-04-24 -1.226614624        2
115 2015-04-25  0.918546253        2
116 2015-04-26  0.027024114        2
117 2015-04-27 -2.127191506        2
118 2015-04-28 -1.600815099        2
119 2015-04-29  0.749681304        2
120 2015-04-30  0.721914459        2
121 2015-05-01 -0.338230147        2
122 2015-05-02  0.913592837        2
123 2015-05-03  0.587794938        2
124 2015-05-04 -0.851625256        2
125 2015-05-05 -0.345100249        2
126 2015-05-06  1.195675453        2
127 2015-05-07 -1.163156366        2
128 2015-05-08  0.006734588        2
129 2015-05-09  1.410087674        2
130 2015-05-10  1.322741860        2
131 2015-05-11 -0.297038999        2
132 2015-05-12 -0.197173515        2
133 2015-05-13  0.224360972        2
134 2015-05-14  0.516641666        2
135 2015-05-15 -0.779288529        2
136 2015-05-16  0.579790369        2
137 2015-05-17 -1.455354422        2
138 2015-05-18  0.080913482        2
139 2015-05-19 -0.144821155        2
140 2015-05-20 -0.114079060        2
141 2015-05-21 -0.763828057        2
142 2015-05-22  0.707339053        2
143 2015-05-23  0.647765433        2
144 2015-05-24 -1.490961303        2
145 2015-05-25  0.620563653        2
146 2015-05-26 -0.543335407        2
147 2015-05-27  0.104817520        2
148 2015-05-28 -0.003077069        2
149 2015-05-29  0.703242269        2
150 2015-05-30 -0.432612310        2
151 2015-05-31  0.765172967        2
152 2015-06-01  0.662351120        2
153 2015-06-02  0.320601441        2
154 2015-06-03 -1.542552690        2
155 2015-06-04 -0.841613323        2
156 2015-06-05  0.244023691        2
157 2015-06-06 -0.363205416        2
158 2015-06-07  0.425083853        2
159 2015-06-08  0.480960952        2
160 2015-06-09  1.171789654        2
161 2015-06-10  0.689310253        2
162 2015-06-11  0.069911244        2
163 2015-06-12  1.211315304        2
164 2015-06-13 -2.992856256        2
165 2015-06-14 -1.725439305        2
166 2015-06-15 -0.427232751        2
167 2015-06-16 -0.320677428        2
168 2015-06-17 -0.625616224        2
169 2015-06-18  0.436684268        2
170 2015-06-19 -0.051345979        2
171 2015-06-20 -0.005905043        2
172 2015-06-21 -0.650648380        2
173 2015-06-22  0.104280158        2
174 2015-06-23  0.692602024        2
175 2015-06-24 -0.284524585        2
176 2015-06-25  0.114234704        2
177 2015-06-26 -0.307465039        2
178 2015-06-27 -0.868424089        2
179 2015-06-28 -0.008077344        2
180 2015-06-29 -0.216263894        2
181 2015-06-30  0.716286098        2
182 2015-07-01 -0.246694377        2
183 2015-07-02 -0.514709162        2
184 2015-07-03  0.571000411        2
185 2015-07-04  0.951861313        2
186 2015-07-05 -0.657196354        2
187 2015-07-06  0.702772460        2
188 2015-07-07 -1.889945487        2
189 2015-07-08 -1.556305726        2
190 2015-07-09 -1.333879020        2
191 2015-07-10 -0.148308307        2
192 2015-07-11  0.862758957        2
193 2015-07-12  0.015712677        2
194 2015-07-13 -0.518988630        2
195 2015-07-14  0.381518862        2
196 2015-07-15 -0.920415442        2
197 2015-07-16 -0.291423016        2
198 2015-07-17  0.051580366        2
199 2015-07-18 -0.653667887        2
200 2015-07-19 -1.159563927        2
201 2015-07-20 -0.524343555        3
202 2015-07-21 -0.499934439        3
203 2015-07-22  0.890589850        3
204 2015-07-23 -0.583243838        3
205 2015-07-24  0.464586806        3
206 2015-07-25 -1.072116565        3
207 2015-07-26 -1.995098501        3
208 2015-07-27 -1.398424995        3
209 2015-07-28 -0.047756678        3
210 2015-07-29  0.993838354        3
211 2015-07-30  0.274223295        3
212 2015-07-31 -1.274376302        3
213 2015-08-01 -1.586586701        3
214 2015-08-02  0.230695873        3
215 2015-08-03  0.151248025        3
216 2015-08-04  1.631408895        3
217 2015-08-05 -0.878848837        3
218 2015-08-06  0.451727327        3
219 2015-08-07  0.392156218        3
220 2015-08-08  0.544240403        3
221 2015-08-09 -0.211142978        3
222 2015-08-10  1.364874158        3
223 2015-08-11 -0.541504849        3
224 2015-08-12 -0.089349427        3
225 2015-08-13 -0.815008782        3
226 2015-08-14 -0.121764644        3
227 2015-08-15 -1.741367522        3
228 2015-08-16  2.043085589        3
229 2015-08-17  1.051024717        3
230 2015-08-18  0.071467837        3
231 2015-08-19  0.346026920        3
232 2015-08-20  0.190915132        3
233 2015-08-21 -1.104888803        3
234 2015-08-22 -0.193678833        3
235 2015-08-23  0.453708267        3
236 2015-08-24 -0.114886984        3
237 2015-08-25  0.279705350        3
238 2015-08-26 -0.291677485        3
239 2015-08-27 -1.046920131        3
240 2015-08-28  0.546206788        3
241 2015-08-29  0.417895255        3
242 2015-08-30  0.607427357        3
243 2015-08-31  0.386263173        3
244 2015-09-01  1.693325483        3
245 2015-09-02 -0.269513707        3
246 2015-09-03  0.972799720        3
247 2015-09-04 -0.136891511        3
248 2015-09-05  0.036534446        3
249 2015-09-06 -0.818723816        3
250 2015-09-07 -0.270747970        3
251 2015-09-08 -0.099214990        3
252 2015-09-09 -0.441796094        3
253 2015-09-10 -0.785450099        3
254 2015-09-11 -0.266662717        3
255 2015-09-12 -0.185548366        3
256 2015-09-13 -0.587839058        3
257 2015-09-14  0.570935157        3
258 2015-09-15  0.339546529        3
259 2015-09-16  0.436241922        3
260 2015-09-17 -1.345637228        3
261 2015-09-18  0.265399285        3
262 2015-09-19 -0.490105412        3
263 2015-09-20  0.497014587        3
264 2015-09-21 -0.073881747        3
265 2015-09-22 -1.339337587        3
266 2015-09-23 -1.575732554        3
267 2015-09-24  1.590806011        3
268 2015-09-25  0.283380826        3
269 2015-09-26 -0.437666267        3
270 2015-09-27  0.086035992        3
271 2015-09-28 -0.205143330        3
272 2015-09-29 -0.368002399        3
273 2015-09-30  0.277060950        3
274 2015-10-01  1.184281033        3
275 2015-10-02 -0.042580777        3
276 2015-10-03 -0.034058572        3
277 2015-10-04  1.822264836        3
278 2015-10-05  1.418461585        3
279 2015-10-06 -1.663314285        3
280 2015-10-07  0.306396419        3
281 2015-10-08 -0.133955098        3
282 2015-10-09  1.785256820        3
283 2015-10-10  2.144114886        3
284 2015-10-11  1.946704788        3
285 2015-10-12  1.081968268        3
286 2015-10-13  0.466607356        3
287 2015-10-14  0.708654794        3
288 2015-10-15  0.250716353        3
289 2015-10-16  0.280578762        3
290 2015-10-17 -0.102182693        3
291 2015-10-18  1.519061748        3
292 2015-10-19 -0.240985742        3
293 2015-10-20 -1.392785238        3
294 2015-10-21 -1.213613515        3
295 2015-10-22  0.241381597        3
296 2015-10-23 -0.234988013        3
297 2015-10-24  1.620456160        3
298 2015-10-25  0.548044651        3
299 2015-10-26  1.520948096        3
300 2015-10-27 -1.069683544        3
301 2015-10-28 -2.149756515        4
302 2015-10-29 -0.371598782        4
303 2015-10-30 -0.017200805        4
304 2015-10-31  1.421065588        4
305 2015-11-01 -0.719738038        4
306 2015-11-02 -0.539361753        4
307 2015-11-03  0.127735424        4
308 2015-11-04  0.521494673        4
309 2015-11-05 -1.071468633        4
310 2015-11-06  0.311667225        4
311 2015-11-07  0.593034587        4
312 2015-11-08 -0.281065031        4
313 2015-11-09 -0.454378772        4
314 2015-11-10 -0.612201420        4
315 2015-11-11  1.261906072        4
316 2015-11-12 -0.832989599        4
317 2015-11-13  1.042128138        4
318 2015-11-14  0.101058897        4
319 2015-11-15  1.481095345        4
320 2015-11-16  0.550768802        4
321 2015-11-17  0.709517939        4
322 2015-11-18  1.403988053        4
323 2015-11-19  0.050966805        4
324 2015-11-20 -0.663606215        4
325 2015-11-21 -0.120978945        4
326 2015-11-22  0.830822407        4
327 2015-11-23 -0.846003819        4
328 2015-11-24  1.460456262        4
329 2015-11-25  0.758233907        4
330 2015-11-26  0.241672077        4
331 2015-11-27  0.461815643        4
332 2015-11-28  0.086404903        4
333 2015-11-29 -1.345535596        4
  • Related