I'm trying to consolidate the below given input
ID TIMEFRAME
193 18:30:0:390
177 18:30:0:605
180 18:30:0:710
215 18:30:0:690
165 18:30:0:580
195 18:30:0:170
252 18:30:0:185
198 18:30:0:265
246 18:30:0:370
183 18:30:0:330
225 18:30:0:110
173 18:30:0:220
66 18:30:0:790
248 18:30:0:545
186 18:30:0:600
244 18:30:0:65
240 18:30:0:10
245 18:30:0:170
44 18:30:0:565
178 18:30:0:305
218 18:30:0:700
168 18:30:0:110
213 18:30:0:225
199 18:30:0:255
206 18:30:0:145
135 18:30:0:250
12 18:30:0:130
209 18:30:0:45
222 18:30:0:275
188 18:30:0:55
11 18:30:0:545
174 18:30:0:360
208 18:30:0:635
152 18:30:0:15
146 18:30:0:655
35 18:30:0:700
78 18:30:0:130
50 18:30:0:340
167 18:30:0:930
184 18:30:0:600
191 18:30:0:545
219 18:30:0:390
76 18:30:0:150
23 18:30:0:560
39 18:30:0:345
200 18:30:0:885
60 18:30:0:175
88 18:30:0:770
116 18:30:0:250
85 18:30:0:985
33 18:30:0:725
211 18:30:0:825
98 18:30:0:635
229 18:30:0:475
164 18:30:0:55
250 18:30:0:305
190 18:30:0:330
144 18:30:0:540
216 18:30:0:810
217 18:30:0:260
129 18:30:0:120
147 18:30:0:500
166 18:30:0:640
117 18:30:0:700
145 18:30:0:665
91 18:30:0:550
140 18:30:0:105
104 18:30:0:430
68 18:30:0:585
2 18:30:0:625
251 18:30:0:490
197 18:30:0:730
4 18:30:0:175
40 18:30:0:720
156 18:30:0:860
94 18:30:0:310
110 18:30:0:955
221 18:30:0:690
137 18:30:0:20
31 18:30:0:660
189 18:30:0:230
16 18:30:0:20
187 18:30:0:485
27 18:30:0:985
176 18:30:0:405
175 18:30:0:990
210 18:30:0:625
30 18:30:0:20
42 18:30:0:510
28 18:30:0:275
109 18:30:0:770
69 18:30:0:435
243 18:30:0:415
120 18:30:0:545
138 18:30:0:735
80 18:30:0:870
121 18:30:0:255
22 18:30:0:850
162 18:30:0:455
212 18:30:0:295
142 18:30:0:195
172 18:30:0:570
105 18:30:0:480
202 18:30:0:345
133 18:30:0:950
81 18:30:0:920
103 18:30:0:630
157 18:30:0:940
79 18:30:0:60
132 18:30:0:775
54 18:30:0:680
123 18:30:0:55
223 18:30:0:740
96 18:30:0:55
160 18:30:0:90
82 18:30:0:880
41 18:30:0:150
86 18:30:0:345
101 18:30:0:460
119 18:30:0:20
102 18:30:0:225
38 18:30:0:385
3 18:30:0:315
113 18:30:0:265
43 18:30:0:50
67 18:30:0:915
249 18:30:0:415
5 18:30:0:265
126 18:30:0:660
13 18:30:0:415
214 18:30:0:640
106 18:30:0:970
6 18:30:0:540
58 18:30:0:830
48 18:30:0:895
to
67 TIMEFRAME1 TIMEFRAME2 TIMEFRAME3 ....TIMEFRAME-N COUNT(TIMEFRAMES per ID)
I'm trying with this code from awk, but somehow, the TIMEFRAMEs are note Sorted and IDs are repetitive.
awk 'BEGIN { x=0; } /NE Id/ { NEID[x++]=$14"|"$18; } END { for(i in NEID) {split(NEID,b,"|"); if(b[1] in NEID) {NEARR[b[1]]=NEARR[b[1]]" "b[2]; } } for (k in NEARR) { print NEARR[k] } } '