Match the nearest timestamp in milliseconds
Clash Royale CLAN TAG#URR8PPP
up vote
0
down vote
favorite
I have a large data.txt file as below; for every timestamp, I need to find the nearest time match 5 minutes later and the value on column 4. Now print both in two new columns.
for example for "2018-02-16 16:45:29.557 farads 0.0004300000"
I need to find the best time available 5 minutes later (i.e. approximately) 16:50:40.486 farads 0.0002400000.
Please note that there are no exact time matches here, that is why only the nearest match is required. Also the code should seamlessly work when the date changes from 2018-02-16 to 2018-02-17.
final output should be
"2018-02-16 16:45:29.557 farads 0.0004300000 16:50:40.486 0.0002400000"
How can I make this happen?
col1 col2 col3 col4
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
2018-02-16 16:52:43.612 farads 0.0005200000
2018-02-16 16:53:23.550 farads 0.0003900000
2018-02-16 16:54:03.276 farads 0.0005300000
2018-02-16 16:54:44.223 farads 0.0003800000
2018-02-16 16:55:24.769 farads 0.0003200000
2018-02-16 16:56:10.028 farads 0.0002700000
2018-02-16 16:56:57.624 farads 0.0000900000
2018-02-16 16:57:37.387 farads 0.0003000000
2018-02-16 16:58:16.929 farads 0.0005800000
2018-02-16 16:58:56.961 farads 0.0003000000
2018-02-16 16:59:39.217 farads 0.0001900000
2018-02-16 17:00:19.129 farads 0.0005800000
2018-02-16 17:00:59.328 farads 0.0001500000
2018-02-16 17:01:39.138 farads 0.0005400000
2018-02-16 17:02:19.786 farads 0.0006600000
2018-02-16 17:03:00.236 farads 0.0004700000
2018-02-16 17:03:44.343 farads 0.0003300000
2018-02-16 17:04:24.996 farads 0.0002200000
2018-02-16 17:05:05.754 farads 0.0003200000
2018-02-16 17:05:48.512 farads 0.0004600000
2018-02-16 17:06:29.248 farads 0.0003700000
2018-02-16 17:07:09.819 farads 0.0001300000
2018-02-16 17:07:50.392 farads 0.0005500000
2018-02-16 17:08:32.397 farads 0.0002000000
2018-02-16 17:09:14.778 farads 0.0003000000
2018-02-16 17:09:57.688 farads 0.0003100000
2018-02-16 17:10:37.237 farads 0.0003900000
2018-02-16 17:11:21.559 farads 0.0003500000
2018-02-16 17:12:00.945 farads 0.0003500000
2018-02-16 17:12:00.946 farads 0.0003500000
2018-02-16 17:12:44.127 farads 0.0003200000
2018-02-16 17:13:26.579 farads 0.0003800000
2018-02-16 17:14:09.175 farads 0.0001100000
2018-02-16 17:14:49.552 farads 0.0001300000
2018-02-16 17:14:49.553 farads 0.0001300000
2018-02-16 17:15:31.044 farads 0.0002000000
2018-02-16 17:16:12.038 farads 0.0000400000
2018-02-16 17:16:12.039 farads 0.0000400000
2018-02-16 17:16:52.956 farads 0.0002300000
2018-02-16 17:17:33.238 farads 0.0001900000
2018-02-16 17:18:12.986 farads 0.0001900000
2018-02-16 17:18:12.987 farads 0.0001900000
2018-02-16 17:18:54.214 farads 0.0002300000
2018-02-16 17:19:34.432 farads 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000
2018-02-16 17:21:03.320 farads 0.0001100000
2018-02-16 17:21:43.477 farads 0.0002000000
2018-02-16 17:22:27.400 farads 0.0003500000
2018-02-16 17:23:11.224 farads 0.0001700000
2018-02-16 17:23:52.907 farads 0.0001100000
2018-02-16 17:24:40.392 farads 0.0001500000
2018-02-16 17:25:23.026 farads 0.0001400000
2018-02-16 17:26:03.886 farads 0.0003100000
2018-02-16 17:26:45.191 farads 0.0001900000
2018-02-16 17:26:45.192 farads 0.0001900000
2018-02-16 17:27:28.652 farads 0.0001000000
2018-02-16 17:28:09.625 farads 0.0002000000
2018-02-16 17:28:49.753 farads 0.0001500000
2018-02-16 17:29:29.224 farads 0.0005600000
2018-02-16 17:30:10.520 farads 0.0002100000
2018-02-16 17:30:50.702 farads 0.0001700000
2018-02-16 17:31:30.077 farads 0.0004800000
2018-02-16 17:32:11.586 farads 0.0003800000
2018-02-16 17:32:51.434 farads 0.0003600000
2018-02-16 17:33:31.457 farads 0.0005300000
2018-02-16 17:34:10.910 farads 0.0007600000
2018-02-16 17:34:51.174 farads 0.0004400000
2018-02-16 17:34:51.175 farads 0.0004400000
2018-02-16 17:35:31.234 farads 0.0004800000
2018-02-16 17:36:22.164 farads 0.0002600000
2018-02-16 17:37:02.616 farads 0.0004100000
2018-02-16 17:37:42.127 farads 0.0003500000
2018-02-16 17:38:23.346 farads 0.0004100000
2018-02-16 17:39:04.611 farads 0.0002400000
2018-02-16 17:39:46.119 farads 0.0000700000
2018-02-16 17:40:33.633 farads 0.0001500000
2018-02-16 17:41:13.307 farads 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000
2018-02-16 17:42:33.994 farads 0.0002300000
2018-02-16 17:43:14.389 farads 0.0004000000
2018-02-16 17:43:54.324 farads 0.0002200000
2018-02-16 17:44:36.122 farads 0.0001800000
2018-02-16 17:45:16.828 farads 0.0002600000
2018-02-16 17:45:59.158 farads 0.0001500000
2018-02-16 17:46:45.126 farads 0.0000700000
2018-02-16 17:47:25.236 farads 0.0004400000
2018-02-16 17:48:12.096 farads 0.0002200000
2018-02-16 17:49:01.891 farads 0.0001600000
2018-02-16 17:49:50.422 farads 0.0002100000
2018-02-16 17:50:31.222 farads 0.0001300000
2018-02-16 17:51:12.651 farads 0.0002600000
2018-02-16 17:51:12.652 farads 0.0002600000
2018-02-16 17:51:53.478 farads 0.0001300000
2018-02-16 17:52:34.145 farads 0.0004600000
2018-02-16 17:53:14.374 farads 0.0003300000
2018-02-16 23:53:53.906 farads 0.0002600000
2018-02-16 23:54:34.453 farads 0.0001200000
2018-02-16 23:55:15.512 farads 0.0001600000
2018-02-16 23:55:58.161 farads 0.0001800000
2018-02-16 23:56:46.602 farads 0.0002600000
2018-02-16 23:57:26.829 farads 0.0003100000
2018-02-16 23:57:26.830 farads 0.0003100000
2018-02-16 23:58:06.991 farads 0.0004400000
2018-02-16 23:58:47.104 farads 0.0003600000
2018-02-16 23:58:47.105 farads 0.0003600000
2018-02-16 23:59:27.080 farads 0.0002300000
2018-02-17 00:00:07.008 farads 0.0002900000
2018-02-17 00:00:07.009 farads 0.0002900000
2018-02-17 00:00:51.685 farads 0.0004900000
2018-02-17 00:01:30.835 farads 0.0003000000
2018-02-17 00:02:14.187 farads 0.0004300000
2018-02-17 00:02:56.048 farads 0.0004700000
2018-02-17 00:03:39.758 farads 0.0004200000
2018-02-17 00:04:19.990 farads 0.0001600000
2018-02-17 00:04:59.854 farads 0.0001700000
2018-02-17 00:05:40.967 farads 0.0001400000
2018-02-17 00:06:24.584 farads 0.0001000000
2018-02-17 00:07:04.742 farads 0.0002500000
2018-02-17 00:07:48.107 farads 0.0003600000
2018-02-17 00:08:31.136 farads 0.0000700000
2018-02-17 00:09:12.429 farads 0.0001500000
2018-02-17 00:09:59.567 farads 0.0002500000
2018-02-17 00:10:41.062 farads 0.0001900000
2018-02-17 00:11:21.016 farads 0.0001600000
2018-02-17 00:12:00.863 farads 0.0001600000
2018-02-17 00:12:41.023 farads 0.0002400000
2018-02-17 00:13:22.429 farads 0.0001500000
2018-02-17 00:14:04.826 farads 0.0004100000
2018-02-17 00:14:51.079 farads 0.0001600000
2018-02-17 00:15:31.247 farads 0.0003500000
2018-02-17 00:16:17.396 farads 0.0001900000
2018-02-17 00:16:56.912 farads 0.0002100000
2018-02-17 00:17:37.895 farads 0.0001800000
2018-02-17 00:18:18.354 farads 0.0003700000
2018-02-17 00:18:58.071 farads 0.0004700000
2018-02-17 18:19:38.135 farads 0.0002000000
2018-02-17 18:20:22.373 farads 0.0002600000
2018-02-17 18:21:02.161 farads 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000
2018-02-17 18:22:25.394 farads 0.0002500000
2018-02-17 18:23:06.549 farads 0.0003100000
2018-02-17 18:23:46.638 farads 0.0002100000
2018-02-17 18:24:27.966 farads 0.0001800000
2018-02-17 18:25:11.832 farads 0.0002800000
2018-02-17 18:25:52.344 farads 0.0003000000
2018-02-17 18:26:33.672 farads 0.0002600000
2018-02-17 18:27:15.499 farads 0.0004300000
2018-02-17 18:27:55.288 farads 0.0004800000
2018-02-17 18:28:56.699 farads 0.0004200000
2018-02-17 18:29:40.909 farads 0.0002100000
2018-02-17 18:30:20.942 farads 0.0003400000
2018-02-17 18:31:03.937 farads 0.0003500000
2018-02-17 18:31:51.329 farads 0.0002500000
2018-02-17 18:32:32.608 farads 0.0005000000
2018-02-17 18:33:12.869 farads 0.0004900000
2018-02-17 18:33:52.725 farads 0.0002300000
2018-02-17 18:34:39.022 farads 0.0001300000
2018-02-17 18:35:20.579 farads 0.0002800000
2018-02-17 18:36:00.487 farads 0.0002400000
2018-02-17 18:36:51.908 farads 0.0004500000
2018-02-17 18:37:33.667 farads 0.0002500000
2018-02-17 18:38:13.989 farads 0.0004700000
2018-02-17 18:38:53.753 farads 0.0003500000
2018-02-17 18:39:34.052 farads 0.0004100000
text-processing awk perl timestamps r
asked Mar 5 at 19:52
kalamata_Olive
33
 |Â
show 4 more comments
up vote
0
down vote
favorite
I have a large data.txt file as below; for every timestamp, I need to find the nearest time match 5 minutes later and the value on column 4. Now print both in two new columns.
for example for "2018-02-16 16:45:29.557 farads 0.0004300000"
I need to find the best time available 5 minutes later (i.e. approximately) 16:50:40.486 farads 0.0002400000.
Please note that there are no exact time matches here, that is why only the nearest match is required. Also the code should seamlessly work when the date changes from 2018-02-16 to 2018-02-17.
final output should be
"2018-02-16 16:45:29.557 farads 0.0004300000 16:50:40.486 0.0002400000"
How can I make this happen?
col1 col2 col3 col4
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
2018-02-16 16:52:43.612 farads 0.0005200000
2018-02-16 16:53:23.550 farads 0.0003900000
2018-02-16 16:54:03.276 farads 0.0005300000
2018-02-16 16:54:44.223 farads 0.0003800000
2018-02-16 16:55:24.769 farads 0.0003200000
2018-02-16 16:56:10.028 farads 0.0002700000
2018-02-16 16:56:57.624 farads 0.0000900000
2018-02-16 16:57:37.387 farads 0.0003000000
2018-02-16 16:58:16.929 farads 0.0005800000
2018-02-16 16:58:56.961 farads 0.0003000000
2018-02-16 16:59:39.217 farads 0.0001900000
2018-02-16 17:00:19.129 farads 0.0005800000
2018-02-16 17:00:59.328 farads 0.0001500000
2018-02-16 17:01:39.138 farads 0.0005400000
2018-02-16 17:02:19.786 farads 0.0006600000
2018-02-16 17:03:00.236 farads 0.0004700000
2018-02-16 17:03:44.343 farads 0.0003300000
2018-02-16 17:04:24.996 farads 0.0002200000
2018-02-16 17:05:05.754 farads 0.0003200000
2018-02-16 17:05:48.512 farads 0.0004600000
2018-02-16 17:06:29.248 farads 0.0003700000
2018-02-16 17:07:09.819 farads 0.0001300000
2018-02-16 17:07:50.392 farads 0.0005500000
2018-02-16 17:08:32.397 farads 0.0002000000
2018-02-16 17:09:14.778 farads 0.0003000000
2018-02-16 17:09:57.688 farads 0.0003100000
2018-02-16 17:10:37.237 farads 0.0003900000
2018-02-16 17:11:21.559 farads 0.0003500000
2018-02-16 17:12:00.945 farads 0.0003500000
2018-02-16 17:12:00.946 farads 0.0003500000
2018-02-16 17:12:44.127 farads 0.0003200000
2018-02-16 17:13:26.579 farads 0.0003800000
2018-02-16 17:14:09.175 farads 0.0001100000
2018-02-16 17:14:49.552 farads 0.0001300000
2018-02-16 17:14:49.553 farads 0.0001300000
2018-02-16 17:15:31.044 farads 0.0002000000
2018-02-16 17:16:12.038 farads 0.0000400000
2018-02-16 17:16:12.039 farads 0.0000400000
2018-02-16 17:16:52.956 farads 0.0002300000
2018-02-16 17:17:33.238 farads 0.0001900000
2018-02-16 17:18:12.986 farads 0.0001900000
2018-02-16 17:18:12.987 farads 0.0001900000
2018-02-16 17:18:54.214 farads 0.0002300000
2018-02-16 17:19:34.432 farads 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000
2018-02-16 17:21:03.320 farads 0.0001100000
2018-02-16 17:21:43.477 farads 0.0002000000
2018-02-16 17:22:27.400 farads 0.0003500000
2018-02-16 17:23:11.224 farads 0.0001700000
2018-02-16 17:23:52.907 farads 0.0001100000
2018-02-16 17:24:40.392 farads 0.0001500000
2018-02-16 17:25:23.026 farads 0.0001400000
2018-02-16 17:26:03.886 farads 0.0003100000
2018-02-16 17:26:45.191 farads 0.0001900000
2018-02-16 17:26:45.192 farads 0.0001900000
2018-02-16 17:27:28.652 farads 0.0001000000
2018-02-16 17:28:09.625 farads 0.0002000000
2018-02-16 17:28:49.753 farads 0.0001500000
2018-02-16 17:29:29.224 farads 0.0005600000
2018-02-16 17:30:10.520 farads 0.0002100000
2018-02-16 17:30:50.702 farads 0.0001700000
2018-02-16 17:31:30.077 farads 0.0004800000
2018-02-16 17:32:11.586 farads 0.0003800000
2018-02-16 17:32:51.434 farads 0.0003600000
2018-02-16 17:33:31.457 farads 0.0005300000
2018-02-16 17:34:10.910 farads 0.0007600000
2018-02-16 17:34:51.174 farads 0.0004400000
2018-02-16 17:34:51.175 farads 0.0004400000
2018-02-16 17:35:31.234 farads 0.0004800000
2018-02-16 17:36:22.164 farads 0.0002600000
2018-02-16 17:37:02.616 farads 0.0004100000
2018-02-16 17:37:42.127 farads 0.0003500000
2018-02-16 17:38:23.346 farads 0.0004100000
2018-02-16 17:39:04.611 farads 0.0002400000
2018-02-16 17:39:46.119 farads 0.0000700000
2018-02-16 17:40:33.633 farads 0.0001500000
2018-02-16 17:41:13.307 farads 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000
2018-02-16 17:42:33.994 farads 0.0002300000
2018-02-16 17:43:14.389 farads 0.0004000000
2018-02-16 17:43:54.324 farads 0.0002200000
2018-02-16 17:44:36.122 farads 0.0001800000
2018-02-16 17:45:16.828 farads 0.0002600000
2018-02-16 17:45:59.158 farads 0.0001500000
2018-02-16 17:46:45.126 farads 0.0000700000
2018-02-16 17:47:25.236 farads 0.0004400000
2018-02-16 17:48:12.096 farads 0.0002200000
2018-02-16 17:49:01.891 farads 0.0001600000
2018-02-16 17:49:50.422 farads 0.0002100000
2018-02-16 17:50:31.222 farads 0.0001300000
2018-02-16 17:51:12.651 farads 0.0002600000
2018-02-16 17:51:12.652 farads 0.0002600000
2018-02-16 17:51:53.478 farads 0.0001300000
2018-02-16 17:52:34.145 farads 0.0004600000
2018-02-16 17:53:14.374 farads 0.0003300000
2018-02-16 23:53:53.906 farads 0.0002600000
2018-02-16 23:54:34.453 farads 0.0001200000
2018-02-16 23:55:15.512 farads 0.0001600000
2018-02-16 23:55:58.161 farads 0.0001800000
2018-02-16 23:56:46.602 farads 0.0002600000
2018-02-16 23:57:26.829 farads 0.0003100000
2018-02-16 23:57:26.830 farads 0.0003100000
2018-02-16 23:58:06.991 farads 0.0004400000
2018-02-16 23:58:47.104 farads 0.0003600000
2018-02-16 23:58:47.105 farads 0.0003600000
2018-02-16 23:59:27.080 farads 0.0002300000
2018-02-17 00:00:07.008 farads 0.0002900000
2018-02-17 00:00:07.009 farads 0.0002900000
2018-02-17 00:00:51.685 farads 0.0004900000
2018-02-17 00:01:30.835 farads 0.0003000000
2018-02-17 00:02:14.187 farads 0.0004300000
2018-02-17 00:02:56.048 farads 0.0004700000
2018-02-17 00:03:39.758 farads 0.0004200000
2018-02-17 00:04:19.990 farads 0.0001600000
2018-02-17 00:04:59.854 farads 0.0001700000
2018-02-17 00:05:40.967 farads 0.0001400000
2018-02-17 00:06:24.584 farads 0.0001000000
2018-02-17 00:07:04.742 farads 0.0002500000
2018-02-17 00:07:48.107 farads 0.0003600000
2018-02-17 00:08:31.136 farads 0.0000700000
2018-02-17 00:09:12.429 farads 0.0001500000
2018-02-17 00:09:59.567 farads 0.0002500000
2018-02-17 00:10:41.062 farads 0.0001900000
2018-02-17 00:11:21.016 farads 0.0001600000
2018-02-17 00:12:00.863 farads 0.0001600000
2018-02-17 00:12:41.023 farads 0.0002400000
2018-02-17 00:13:22.429 farads 0.0001500000
2018-02-17 00:14:04.826 farads 0.0004100000
2018-02-17 00:14:51.079 farads 0.0001600000
2018-02-17 00:15:31.247 farads 0.0003500000
2018-02-17 00:16:17.396 farads 0.0001900000
2018-02-17 00:16:56.912 farads 0.0002100000
2018-02-17 00:17:37.895 farads 0.0001800000
2018-02-17 00:18:18.354 farads 0.0003700000
2018-02-17 00:18:58.071 farads 0.0004700000
2018-02-17 18:19:38.135 farads 0.0002000000
2018-02-17 18:20:22.373 farads 0.0002600000
2018-02-17 18:21:02.161 farads 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000
2018-02-17 18:22:25.394 farads 0.0002500000
2018-02-17 18:23:06.549 farads 0.0003100000
2018-02-17 18:23:46.638 farads 0.0002100000
2018-02-17 18:24:27.966 farads 0.0001800000
2018-02-17 18:25:11.832 farads 0.0002800000
2018-02-17 18:25:52.344 farads 0.0003000000
2018-02-17 18:26:33.672 farads 0.0002600000
2018-02-17 18:27:15.499 farads 0.0004300000
2018-02-17 18:27:55.288 farads 0.0004800000
2018-02-17 18:28:56.699 farads 0.0004200000
2018-02-17 18:29:40.909 farads 0.0002100000
2018-02-17 18:30:20.942 farads 0.0003400000
2018-02-17 18:31:03.937 farads 0.0003500000
2018-02-17 18:31:51.329 farads 0.0002500000
2018-02-17 18:32:32.608 farads 0.0005000000
2018-02-17 18:33:12.869 farads 0.0004900000
2018-02-17 18:33:52.725 farads 0.0002300000
2018-02-17 18:34:39.022 farads 0.0001300000
2018-02-17 18:35:20.579 farads 0.0002800000
2018-02-17 18:36:00.487 farads 0.0002400000
2018-02-17 18:36:51.908 farads 0.0004500000
2018-02-17 18:37:33.667 farads 0.0002500000
2018-02-17 18:38:13.989 farads 0.0004700000
2018-02-17 18:38:53.753 farads 0.0003500000
2018-02-17 18:39:34.052 farads 0.0004100000
text-processing awk perl timestamps r
asked Mar 5 at 19:52
kalamata_Olive
33
2018-02-16 16:45:29.557and16:50:40.486have 5 minutes difference, not 5 seconds. Secondly, if you need for every timestamp - update your final output
– RomanPerekhrest
Mar 5 at 20:01
I changed the question to 5 mins. Thanks for pointing it out, I actually meant 5 mins. Also depending upon the time period the two new columns will actually begin with NAs
– kalamata_Olive
Mar 5 at 20:03
Are the timestamps in the file always sorted in ascending order?
– haukex
Mar 5 at 20:10
yes the time timestamps are always in ascending order or descending order.
– kalamata_Olive
Mar 5 at 20:11
How big will the data files normally be, and what is the maximum number of lines?
– haukex
Mar 5 at 20:30
 |Â
show 4 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a large data.txt file as below; for every timestamp, I need to find the nearest time match 5 minutes later and the value on column 4. Now print both in two new columns.
for example for "2018-02-16 16:45:29.557 farads 0.0004300000"
I need to find the best time available 5 minutes later (i.e. approximately) 16:50:40.486 farads 0.0002400000.
Please note that there are no exact time matches here, that is why only the nearest match is required. Also the code should seamlessly work when the date changes from 2018-02-16 to 2018-02-17.
final output should be
"2018-02-16 16:45:29.557 farads 0.0004300000 16:50:40.486 0.0002400000"
How can I make this happen?
col1 col2 col3 col4
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
2018-02-16 16:52:43.612 farads 0.0005200000
2018-02-16 16:53:23.550 farads 0.0003900000
2018-02-16 16:54:03.276 farads 0.0005300000
2018-02-16 16:54:44.223 farads 0.0003800000
2018-02-16 16:55:24.769 farads 0.0003200000
2018-02-16 16:56:10.028 farads 0.0002700000
2018-02-16 16:56:57.624 farads 0.0000900000
2018-02-16 16:57:37.387 farads 0.0003000000
2018-02-16 16:58:16.929 farads 0.0005800000
2018-02-16 16:58:56.961 farads 0.0003000000
2018-02-16 16:59:39.217 farads 0.0001900000
2018-02-16 17:00:19.129 farads 0.0005800000
2018-02-16 17:00:59.328 farads 0.0001500000
2018-02-16 17:01:39.138 farads 0.0005400000
2018-02-16 17:02:19.786 farads 0.0006600000
2018-02-16 17:03:00.236 farads 0.0004700000
2018-02-16 17:03:44.343 farads 0.0003300000
2018-02-16 17:04:24.996 farads 0.0002200000
2018-02-16 17:05:05.754 farads 0.0003200000
2018-02-16 17:05:48.512 farads 0.0004600000
2018-02-16 17:06:29.248 farads 0.0003700000
2018-02-16 17:07:09.819 farads 0.0001300000
2018-02-16 17:07:50.392 farads 0.0005500000
2018-02-16 17:08:32.397 farads 0.0002000000
2018-02-16 17:09:14.778 farads 0.0003000000
2018-02-16 17:09:57.688 farads 0.0003100000
2018-02-16 17:10:37.237 farads 0.0003900000
2018-02-16 17:11:21.559 farads 0.0003500000
2018-02-16 17:12:00.945 farads 0.0003500000
2018-02-16 17:12:00.946 farads 0.0003500000
2018-02-16 17:12:44.127 farads 0.0003200000
2018-02-16 17:13:26.579 farads 0.0003800000
2018-02-16 17:14:09.175 farads 0.0001100000
2018-02-16 17:14:49.552 farads 0.0001300000
2018-02-16 17:14:49.553 farads 0.0001300000
2018-02-16 17:15:31.044 farads 0.0002000000
2018-02-16 17:16:12.038 farads 0.0000400000
2018-02-16 17:16:12.039 farads 0.0000400000
2018-02-16 17:16:52.956 farads 0.0002300000
2018-02-16 17:17:33.238 farads 0.0001900000
2018-02-16 17:18:12.986 farads 0.0001900000
2018-02-16 17:18:12.987 farads 0.0001900000
2018-02-16 17:18:54.214 farads 0.0002300000
2018-02-16 17:19:34.432 farads 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000
2018-02-16 17:21:03.320 farads 0.0001100000
2018-02-16 17:21:43.477 farads 0.0002000000
2018-02-16 17:22:27.400 farads 0.0003500000
2018-02-16 17:23:11.224 farads 0.0001700000
2018-02-16 17:23:52.907 farads 0.0001100000
2018-02-16 17:24:40.392 farads 0.0001500000
2018-02-16 17:25:23.026 farads 0.0001400000
2018-02-16 17:26:03.886 farads 0.0003100000
2018-02-16 17:26:45.191 farads 0.0001900000
2018-02-16 17:26:45.192 farads 0.0001900000
2018-02-16 17:27:28.652 farads 0.0001000000
2018-02-16 17:28:09.625 farads 0.0002000000
2018-02-16 17:28:49.753 farads 0.0001500000
2018-02-16 17:29:29.224 farads 0.0005600000
2018-02-16 17:30:10.520 farads 0.0002100000
2018-02-16 17:30:50.702 farads 0.0001700000
2018-02-16 17:31:30.077 farads 0.0004800000
2018-02-16 17:32:11.586 farads 0.0003800000
2018-02-16 17:32:51.434 farads 0.0003600000
2018-02-16 17:33:31.457 farads 0.0005300000
2018-02-16 17:34:10.910 farads 0.0007600000
2018-02-16 17:34:51.174 farads 0.0004400000
2018-02-16 17:34:51.175 farads 0.0004400000
2018-02-16 17:35:31.234 farads 0.0004800000
2018-02-16 17:36:22.164 farads 0.0002600000
2018-02-16 17:37:02.616 farads 0.0004100000
2018-02-16 17:37:42.127 farads 0.0003500000
2018-02-16 17:38:23.346 farads 0.0004100000
2018-02-16 17:39:04.611 farads 0.0002400000
2018-02-16 17:39:46.119 farads 0.0000700000
2018-02-16 17:40:33.633 farads 0.0001500000
2018-02-16 17:41:13.307 farads 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000
2018-02-16 17:42:33.994 farads 0.0002300000
2018-02-16 17:43:14.389 farads 0.0004000000
2018-02-16 17:43:54.324 farads 0.0002200000
2018-02-16 17:44:36.122 farads 0.0001800000
2018-02-16 17:45:16.828 farads 0.0002600000
2018-02-16 17:45:59.158 farads 0.0001500000
2018-02-16 17:46:45.126 farads 0.0000700000
2018-02-16 17:47:25.236 farads 0.0004400000
2018-02-16 17:48:12.096 farads 0.0002200000
2018-02-16 17:49:01.891 farads 0.0001600000
2018-02-16 17:49:50.422 farads 0.0002100000
2018-02-16 17:50:31.222 farads 0.0001300000
2018-02-16 17:51:12.651 farads 0.0002600000
2018-02-16 17:51:12.652 farads 0.0002600000
2018-02-16 17:51:53.478 farads 0.0001300000
2018-02-16 17:52:34.145 farads 0.0004600000
2018-02-16 17:53:14.374 farads 0.0003300000
2018-02-16 23:53:53.906 farads 0.0002600000
2018-02-16 23:54:34.453 farads 0.0001200000
2018-02-16 23:55:15.512 farads 0.0001600000
2018-02-16 23:55:58.161 farads 0.0001800000
2018-02-16 23:56:46.602 farads 0.0002600000
2018-02-16 23:57:26.829 farads 0.0003100000
2018-02-16 23:57:26.830 farads 0.0003100000
2018-02-16 23:58:06.991 farads 0.0004400000
2018-02-16 23:58:47.104 farads 0.0003600000
2018-02-16 23:58:47.105 farads 0.0003600000
2018-02-16 23:59:27.080 farads 0.0002300000
2018-02-17 00:00:07.008 farads 0.0002900000
2018-02-17 00:00:07.009 farads 0.0002900000
2018-02-17 00:00:51.685 farads 0.0004900000
2018-02-17 00:01:30.835 farads 0.0003000000
2018-02-17 00:02:14.187 farads 0.0004300000
2018-02-17 00:02:56.048 farads 0.0004700000
2018-02-17 00:03:39.758 farads 0.0004200000
2018-02-17 00:04:19.990 farads 0.0001600000
2018-02-17 00:04:59.854 farads 0.0001700000
2018-02-17 00:05:40.967 farads 0.0001400000
2018-02-17 00:06:24.584 farads 0.0001000000
2018-02-17 00:07:04.742 farads 0.0002500000
2018-02-17 00:07:48.107 farads 0.0003600000
2018-02-17 00:08:31.136 farads 0.0000700000
2018-02-17 00:09:12.429 farads 0.0001500000
2018-02-17 00:09:59.567 farads 0.0002500000
2018-02-17 00:10:41.062 farads 0.0001900000
2018-02-17 00:11:21.016 farads 0.0001600000
2018-02-17 00:12:00.863 farads 0.0001600000
2018-02-17 00:12:41.023 farads 0.0002400000
2018-02-17 00:13:22.429 farads 0.0001500000
2018-02-17 00:14:04.826 farads 0.0004100000
2018-02-17 00:14:51.079 farads 0.0001600000
2018-02-17 00:15:31.247 farads 0.0003500000
2018-02-17 00:16:17.396 farads 0.0001900000
2018-02-17 00:16:56.912 farads 0.0002100000
2018-02-17 00:17:37.895 farads 0.0001800000
2018-02-17 00:18:18.354 farads 0.0003700000
2018-02-17 00:18:58.071 farads 0.0004700000
2018-02-17 18:19:38.135 farads 0.0002000000
2018-02-17 18:20:22.373 farads 0.0002600000
2018-02-17 18:21:02.161 farads 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000
2018-02-17 18:22:25.394 farads 0.0002500000
2018-02-17 18:23:06.549 farads 0.0003100000
2018-02-17 18:23:46.638 farads 0.0002100000
2018-02-17 18:24:27.966 farads 0.0001800000
2018-02-17 18:25:11.832 farads 0.0002800000
2018-02-17 18:25:52.344 farads 0.0003000000
2018-02-17 18:26:33.672 farads 0.0002600000
2018-02-17 18:27:15.499 farads 0.0004300000
2018-02-17 18:27:55.288 farads 0.0004800000
2018-02-17 18:28:56.699 farads 0.0004200000
2018-02-17 18:29:40.909 farads 0.0002100000
2018-02-17 18:30:20.942 farads 0.0003400000
2018-02-17 18:31:03.937 farads 0.0003500000
2018-02-17 18:31:51.329 farads 0.0002500000
2018-02-17 18:32:32.608 farads 0.0005000000
2018-02-17 18:33:12.869 farads 0.0004900000
2018-02-17 18:33:52.725 farads 0.0002300000
2018-02-17 18:34:39.022 farads 0.0001300000
2018-02-17 18:35:20.579 farads 0.0002800000
2018-02-17 18:36:00.487 farads 0.0002400000
2018-02-17 18:36:51.908 farads 0.0004500000
2018-02-17 18:37:33.667 farads 0.0002500000
2018-02-17 18:38:13.989 farads 0.0004700000
2018-02-17 18:38:53.753 farads 0.0003500000
2018-02-17 18:39:34.052 farads 0.0004100000
text-processing awk perl timestamps r
asked Mar 5 at 19:52
kalamata_Olive
33
I have a large data.txt file as below; for every timestamp, I need to find the nearest time match 5 minutes later and the value on column 4. Now print both in two new columns.
for example for "2018-02-16 16:45:29.557 farads 0.0004300000"
I need to find the best time available 5 minutes later (i.e. approximately) 16:50:40.486 farads 0.0002400000.
Please note that there are no exact time matches here, that is why only the nearest match is required. Also the code should seamlessly work when the date changes from 2018-02-16 to 2018-02-17.
final output should be
"2018-02-16 16:45:29.557 farads 0.0004300000 16:50:40.486 0.0002400000"
How can I make this happen?
col1 col2 col3 col4
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
2018-02-16 16:52:43.612 farads 0.0005200000
2018-02-16 16:53:23.550 farads 0.0003900000
2018-02-16 16:54:03.276 farads 0.0005300000
2018-02-16 16:54:44.223 farads 0.0003800000
2018-02-16 16:55:24.769 farads 0.0003200000
2018-02-16 16:56:10.028 farads 0.0002700000
2018-02-16 16:56:57.624 farads 0.0000900000
2018-02-16 16:57:37.387 farads 0.0003000000
2018-02-16 16:58:16.929 farads 0.0005800000
2018-02-16 16:58:56.961 farads 0.0003000000
2018-02-16 16:59:39.217 farads 0.0001900000
2018-02-16 17:00:19.129 farads 0.0005800000
2018-02-16 17:00:59.328 farads 0.0001500000
2018-02-16 17:01:39.138 farads 0.0005400000
2018-02-16 17:02:19.786 farads 0.0006600000
2018-02-16 17:03:00.236 farads 0.0004700000
2018-02-16 17:03:44.343 farads 0.0003300000
2018-02-16 17:04:24.996 farads 0.0002200000
2018-02-16 17:05:05.754 farads 0.0003200000
2018-02-16 17:05:48.512 farads 0.0004600000
2018-02-16 17:06:29.248 farads 0.0003700000
2018-02-16 17:07:09.819 farads 0.0001300000
2018-02-16 17:07:50.392 farads 0.0005500000
2018-02-16 17:08:32.397 farads 0.0002000000
2018-02-16 17:09:14.778 farads 0.0003000000
2018-02-16 17:09:57.688 farads 0.0003100000
2018-02-16 17:10:37.237 farads 0.0003900000
2018-02-16 17:11:21.559 farads 0.0003500000
2018-02-16 17:12:00.945 farads 0.0003500000
2018-02-16 17:12:00.946 farads 0.0003500000
2018-02-16 17:12:44.127 farads 0.0003200000
2018-02-16 17:13:26.579 farads 0.0003800000
2018-02-16 17:14:09.175 farads 0.0001100000
2018-02-16 17:14:49.552 farads 0.0001300000
2018-02-16 17:14:49.553 farads 0.0001300000
2018-02-16 17:15:31.044 farads 0.0002000000
2018-02-16 17:16:12.038 farads 0.0000400000
2018-02-16 17:16:12.039 farads 0.0000400000
2018-02-16 17:16:52.956 farads 0.0002300000
2018-02-16 17:17:33.238 farads 0.0001900000
2018-02-16 17:18:12.986 farads 0.0001900000
2018-02-16 17:18:12.987 farads 0.0001900000
2018-02-16 17:18:54.214 farads 0.0002300000
2018-02-16 17:19:34.432 farads 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000
2018-02-16 17:21:03.320 farads 0.0001100000
2018-02-16 17:21:43.477 farads 0.0002000000
2018-02-16 17:22:27.400 farads 0.0003500000
2018-02-16 17:23:11.224 farads 0.0001700000
2018-02-16 17:23:52.907 farads 0.0001100000
2018-02-16 17:24:40.392 farads 0.0001500000
2018-02-16 17:25:23.026 farads 0.0001400000
2018-02-16 17:26:03.886 farads 0.0003100000
2018-02-16 17:26:45.191 farads 0.0001900000
2018-02-16 17:26:45.192 farads 0.0001900000
2018-02-16 17:27:28.652 farads 0.0001000000
2018-02-16 17:28:09.625 farads 0.0002000000
2018-02-16 17:28:49.753 farads 0.0001500000
2018-02-16 17:29:29.224 farads 0.0005600000
2018-02-16 17:30:10.520 farads 0.0002100000
2018-02-16 17:30:50.702 farads 0.0001700000
2018-02-16 17:31:30.077 farads 0.0004800000
2018-02-16 17:32:11.586 farads 0.0003800000
2018-02-16 17:32:51.434 farads 0.0003600000
2018-02-16 17:33:31.457 farads 0.0005300000
2018-02-16 17:34:10.910 farads 0.0007600000
2018-02-16 17:34:51.174 farads 0.0004400000
2018-02-16 17:34:51.175 farads 0.0004400000
2018-02-16 17:35:31.234 farads 0.0004800000
2018-02-16 17:36:22.164 farads 0.0002600000
2018-02-16 17:37:02.616 farads 0.0004100000
2018-02-16 17:37:42.127 farads 0.0003500000
2018-02-16 17:38:23.346 farads 0.0004100000
2018-02-16 17:39:04.611 farads 0.0002400000
2018-02-16 17:39:46.119 farads 0.0000700000
2018-02-16 17:40:33.633 farads 0.0001500000
2018-02-16 17:41:13.307 farads 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000
2018-02-16 17:42:33.994 farads 0.0002300000
2018-02-16 17:43:14.389 farads 0.0004000000
2018-02-16 17:43:54.324 farads 0.0002200000
2018-02-16 17:44:36.122 farads 0.0001800000
2018-02-16 17:45:16.828 farads 0.0002600000
2018-02-16 17:45:59.158 farads 0.0001500000
2018-02-16 17:46:45.126 farads 0.0000700000
2018-02-16 17:47:25.236 farads 0.0004400000
2018-02-16 17:48:12.096 farads 0.0002200000
2018-02-16 17:49:01.891 farads 0.0001600000
2018-02-16 17:49:50.422 farads 0.0002100000
2018-02-16 17:50:31.222 farads 0.0001300000
2018-02-16 17:51:12.651 farads 0.0002600000
2018-02-16 17:51:12.652 farads 0.0002600000
2018-02-16 17:51:53.478 farads 0.0001300000
2018-02-16 17:52:34.145 farads 0.0004600000
2018-02-16 17:53:14.374 farads 0.0003300000
2018-02-16 23:53:53.906 farads 0.0002600000
2018-02-16 23:54:34.453 farads 0.0001200000
2018-02-16 23:55:15.512 farads 0.0001600000
2018-02-16 23:55:58.161 farads 0.0001800000
2018-02-16 23:56:46.602 farads 0.0002600000
2018-02-16 23:57:26.829 farads 0.0003100000
2018-02-16 23:57:26.830 farads 0.0003100000
2018-02-16 23:58:06.991 farads 0.0004400000
2018-02-16 23:58:47.104 farads 0.0003600000
2018-02-16 23:58:47.105 farads 0.0003600000
2018-02-16 23:59:27.080 farads 0.0002300000
2018-02-17 00:00:07.008 farads 0.0002900000
2018-02-17 00:00:07.009 farads 0.0002900000
2018-02-17 00:00:51.685 farads 0.0004900000
2018-02-17 00:01:30.835 farads 0.0003000000
2018-02-17 00:02:14.187 farads 0.0004300000
2018-02-17 00:02:56.048 farads 0.0004700000
2018-02-17 00:03:39.758 farads 0.0004200000
2018-02-17 00:04:19.990 farads 0.0001600000
2018-02-17 00:04:59.854 farads 0.0001700000
2018-02-17 00:05:40.967 farads 0.0001400000
2018-02-17 00:06:24.584 farads 0.0001000000
2018-02-17 00:07:04.742 farads 0.0002500000
2018-02-17 00:07:48.107 farads 0.0003600000
2018-02-17 00:08:31.136 farads 0.0000700000
2018-02-17 00:09:12.429 farads 0.0001500000
2018-02-17 00:09:59.567 farads 0.0002500000
2018-02-17 00:10:41.062 farads 0.0001900000
2018-02-17 00:11:21.016 farads 0.0001600000
2018-02-17 00:12:00.863 farads 0.0001600000
2018-02-17 00:12:41.023 farads 0.0002400000
2018-02-17 00:13:22.429 farads 0.0001500000
2018-02-17 00:14:04.826 farads 0.0004100000
2018-02-17 00:14:51.079 farads 0.0001600000
2018-02-17 00:15:31.247 farads 0.0003500000
2018-02-17 00:16:17.396 farads 0.0001900000
2018-02-17 00:16:56.912 farads 0.0002100000
2018-02-17 00:17:37.895 farads 0.0001800000
2018-02-17 00:18:18.354 farads 0.0003700000
2018-02-17 00:18:58.071 farads 0.0004700000
2018-02-17 18:19:38.135 farads 0.0002000000
2018-02-17 18:20:22.373 farads 0.0002600000
2018-02-17 18:21:02.161 farads 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000
2018-02-17 18:22:25.394 farads 0.0002500000
2018-02-17 18:23:06.549 farads 0.0003100000
2018-02-17 18:23:46.638 farads 0.0002100000
2018-02-17 18:24:27.966 farads 0.0001800000
2018-02-17 18:25:11.832 farads 0.0002800000
2018-02-17 18:25:52.344 farads 0.0003000000
2018-02-17 18:26:33.672 farads 0.0002600000
2018-02-17 18:27:15.499 farads 0.0004300000
2018-02-17 18:27:55.288 farads 0.0004800000
2018-02-17 18:28:56.699 farads 0.0004200000
2018-02-17 18:29:40.909 farads 0.0002100000
2018-02-17 18:30:20.942 farads 0.0003400000
2018-02-17 18:31:03.937 farads 0.0003500000
2018-02-17 18:31:51.329 farads 0.0002500000
2018-02-17 18:32:32.608 farads 0.0005000000
2018-02-17 18:33:12.869 farads 0.0004900000
2018-02-17 18:33:52.725 farads 0.0002300000
2018-02-17 18:34:39.022 farads 0.0001300000
2018-02-17 18:35:20.579 farads 0.0002800000
2018-02-17 18:36:00.487 farads 0.0002400000
2018-02-17 18:36:51.908 farads 0.0004500000
2018-02-17 18:37:33.667 farads 0.0002500000
2018-02-17 18:38:13.989 farads 0.0004700000
2018-02-17 18:38:53.753 farads 0.0003500000
2018-02-17 18:39:34.052 farads 0.0004100000
text-processing awk perl timestamps r
asked Mar 5 at 19:52
kalamata_Olive
33
edited Mar 7 at 0:41
asked Mar 5 at 19:52
kalamata_Olive
33
asked Mar 5 at 19:52
kalamata_Olive
33
asked Mar 5 at 19:52
kalamata_Olive
33
33
2018-02-16 16:45:29.557and16:50:40.486have 5 minutes difference, not 5 seconds. Secondly, if you need for every timestamp - update your final output
– RomanPerekhrest
Mar 5 at 20:01
I changed the question to 5 mins. Thanks for pointing it out, I actually meant 5 mins. Also depending upon the time period the two new columns will actually begin with NAs
– kalamata_Olive
Mar 5 at 20:03
Are the timestamps in the file always sorted in ascending order?
– haukex
Mar 5 at 20:10
yes the time timestamps are always in ascending order or descending order.
– kalamata_Olive
Mar 5 at 20:11
How big will the data files normally be, and what is the maximum number of lines?
– haukex
Mar 5 at 20:30
 |Â
show 4 more comments
2018-02-16 16:45:29.557and16:50:40.486have 5 minutes difference, not 5 seconds. Secondly, if you need for every timestamp - update your final output
– RomanPerekhrest
Mar 5 at 20:01
I changed the question to 5 mins. Thanks for pointing it out, I actually meant 5 mins. Also depending upon the time period the two new columns will actually begin with NAs
– kalamata_Olive
Mar 5 at 20:03
Are the timestamps in the file always sorted in ascending order?
– haukex
Mar 5 at 20:10
yes the time timestamps are always in ascending order or descending order.
– kalamata_Olive
Mar 5 at 20:11
How big will the data files normally be, and what is the maximum number of lines?
– haukex
Mar 5 at 20:30
2018-02-16 16:45:29.557 and 16:50:40.486 have 5 minutes difference, not 5 seconds. Secondly, if you need for every timestamp - update your final output– RomanPerekhrest
Mar 5 at 20:01
2018-02-16 16:45:29.557 and 16:50:40.486 have 5 minutes difference, not 5 seconds. Secondly, if you need for every timestamp - update your final output– RomanPerekhrest
Mar 5 at 20:01
I changed the question to 5 mins. Thanks for pointing it out, I actually meant 5 mins. Also depending upon the time period the two new columns will actually begin with NAs
– kalamata_Olive
Mar 5 at 20:03
I changed the question to 5 mins. Thanks for pointing it out, I actually meant 5 mins. Also depending upon the time period the two new columns will actually begin with NAs
– kalamata_Olive
Mar 5 at 20:03
Are the timestamps in the file always sorted in ascending order?
– haukex
Mar 5 at 20:10
Are the timestamps in the file always sorted in ascending order?
– haukex
Mar 5 at 20:10
yes the time timestamps are always in ascending order or descending order.
– kalamata_Olive
Mar 5 at 20:11
yes the time timestamps are always in ascending order or descending order.
– kalamata_Olive
Mar 5 at 20:11
How big will the data files normally be, and what is the maximum number of lines?
– haukex
Mar 5 at 20:30
How big will the data files normally be, and what is the maximum number of lines?
– haukex
Mar 5 at 20:30
 |Â
show 4 more comments
2 Answers
2
active
oldest
votes
up vote
0
down vote
accepted
Here's an option using awk and GNU date; it uses date to do the "heavy lifting" of computing the timestamps in fractional seconds from the date and time fields; awk then stores each record in an array indexed by that timestamp. One side effect of this is that duplicate records are "erased" -- only result is returned for the 16:45:29.557 entry. After computing all of the timestamps-in-seconds, we loop through the records looking for the closest match to "+5 minutes" from each entry. If that closest entry's timestamp difference is less than 2 minutes (from the 5 minute target), then we print the corresponding date and time and farad reading (by simply replacing the string "farads" with the empty string); otherwise we print "NA" fields, as per your comment. That's a deviation from your spec, on the assumption that a matching entry for a given record could be "tomorrow".
getline seconds
t[3]=seconds"."t[2]
records[t[3]]=$0
END
for (record in records)
min=(0 + "INF")
for (others in records)
# skip myself
if (others == record) continue
difference=(others - record) - (5 * 60)
if (difference < 0) difference=-difference
if (difference < min)
min=difference
matchfor[record]=records[others]
if (min > (60*2))
print records[record], "NA NA"
else
sub("farads", "", matchfor[record])
print records[record], matchfor[record]
Output from the sample input (unsorted):
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
... and piped to |sort:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
The script's output, run on the expanded input, is:
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:53:23.550 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 2018-02-16 16:54:03.276 0.0005300000
2018-02-16 16:49:59.075 farads 0.0000700000 2018-02-16 16:54:44.223 0.0003800000
2018-02-16 16:50:40.486 farads 0.0002400000 2018-02-16 16:55:24.769 0.0003200000
2018-02-16 16:51:22.525 farads 0.0005900000 2018-02-16 16:56:10.028 0.0002700000
2018-02-16 16:52:01.997 farads 0.0003900000 2018-02-16 16:56:57.624 0.0000900000
2018-02-16 16:52:43.612 farads 0.0005200000 2018-02-16 16:57:37.387 0.0003000000
2018-02-16 16:53:23.550 farads 0.0003900000 2018-02-16 16:58:16.929 0.0005800000
2018-02-16 16:54:03.276 farads 0.0005300000 2018-02-16 16:58:56.961 0.0003000000
2018-02-16 16:54:44.223 farads 0.0003800000 2018-02-16 16:59:39.217 0.0001900000
2018-02-16 16:55:24.769 farads 0.0003200000 2018-02-16 17:00:19.129 0.0005800000
2018-02-16 16:56:10.028 farads 0.0002700000 2018-02-16 17:00:59.328 0.0001500000
2018-02-16 16:56:57.624 farads 0.0000900000 2018-02-16 17:01:39.138 0.0005400000
2018-02-16 16:57:37.387 farads 0.0003000000 2018-02-16 17:02:19.786 0.0006600000
2018-02-16 16:58:16.929 farads 0.0005800000 2018-02-16 17:03:00.236 0.0004700000
2018-02-16 16:58:56.961 farads 0.0003000000 2018-02-16 17:03:44.343 0.0003300000
2018-02-16 16:59:39.217 farads 0.0001900000 2018-02-16 17:04:24.996 0.0002200000
2018-02-16 17:00:19.129 farads 0.0005800000 2018-02-16 17:05:05.754 0.0003200000
2018-02-16 17:00:59.328 farads 0.0001500000 2018-02-16 17:05:48.512 0.0004600000
2018-02-16 17:01:39.138 farads 0.0005400000 2018-02-16 17:06:29.248 0.0003700000
2018-02-16 17:02:19.786 farads 0.0006600000 2018-02-16 17:07:09.819 0.0001300000
2018-02-16 17:03:00.236 farads 0.0004700000 2018-02-16 17:07:50.392 0.0005500000
2018-02-16 17:03:44.343 farads 0.0003300000 2018-02-16 17:08:32.397 0.0002000000
2018-02-16 17:04:24.996 farads 0.0002200000 2018-02-16 17:09:14.778 0.0003000000
2018-02-16 17:05:05.754 farads 0.0003200000 2018-02-16 17:09:57.688 0.0003100000
2018-02-16 17:05:48.512 farads 0.0004600000 2018-02-16 17:10:37.237 0.0003900000
2018-02-16 17:06:29.248 farads 0.0003700000 2018-02-16 17:11:21.559 0.0003500000
2018-02-16 17:07:09.819 farads 0.0001300000 2018-02-16 17:12:00.946 0.0003500000
2018-02-16 17:07:50.392 farads 0.0005500000 2018-02-16 17:12:44.127 0.0003200000
2018-02-16 17:08:32.397 farads 0.0002000000 2018-02-16 17:13:26.579 0.0003800000
2018-02-16 17:09:14.778 farads 0.0003000000 2018-02-16 17:14:09.175 0.0001100000
2018-02-16 17:09:57.688 farads 0.0003100000 2018-02-16 17:14:49.553 0.0001300000
2018-02-16 17:10:37.237 farads 0.0003900000 2018-02-16 17:15:31.044 0.0002000000
2018-02-16 17:11:21.559 farads 0.0003500000 2018-02-16 17:16:12.039 0.0000400000
2018-02-16 17:12:00.945 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:00.946 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:44.127 farads 0.0003200000 2018-02-16 17:17:33.238 0.0001900000
2018-02-16 17:13:26.579 farads 0.0003800000 2018-02-16 17:18:12.987 0.0001900000
2018-02-16 17:14:09.175 farads 0.0001100000 2018-02-16 17:18:54.214 0.0002300000
2018-02-16 17:14:49.552 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:14:49.553 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:15:31.044 farads 0.0002000000 2018-02-16 17:20:22.002 0.0000700000
2018-02-16 17:16:12.038 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:12.039 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:52.956 farads 0.0002300000 2018-02-16 17:21:43.477 0.0002000000
2018-02-16 17:17:33.238 farads 0.0001900000 2018-02-16 17:22:27.400 0.0003500000
2018-02-16 17:18:12.986 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:12.987 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:54.214 farads 0.0002300000 2018-02-16 17:23:52.907 0.0001100000
2018-02-16 17:19:34.432 farads 0.0001500000 2018-02-16 17:24:40.392 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000 2018-02-16 17:25:23.026 0.0001400000
2018-02-16 17:21:03.320 farads 0.0001100000 2018-02-16 17:26:03.886 0.0003100000
2018-02-16 17:21:43.477 farads 0.0002000000 2018-02-16 17:26:45.191 0.0001900000
2018-02-16 17:22:27.400 farads 0.0003500000 2018-02-16 17:27:28.652 0.0001000000
2018-02-16 17:23:11.224 farads 0.0001700000 2018-02-16 17:28:09.625 0.0002000000
2018-02-16 17:23:52.907 farads 0.0001100000 2018-02-16 17:28:49.753 0.0001500000
2018-02-16 17:24:40.392 farads 0.0001500000 2018-02-16 17:29:29.224 0.0005600000
2018-02-16 17:25:23.026 farads 0.0001400000 2018-02-16 17:30:10.520 0.0002100000
2018-02-16 17:26:03.886 farads 0.0003100000 2018-02-16 17:30:50.702 0.0001700000
2018-02-16 17:26:45.191 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:26:45.192 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:27:28.652 farads 0.0001000000 2018-02-16 17:32:11.586 0.0003800000
2018-02-16 17:28:09.625 farads 0.0002000000 2018-02-16 17:32:51.434 0.0003600000
2018-02-16 17:28:49.753 farads 0.0001500000 2018-02-16 17:33:31.457 0.0005300000
2018-02-16 17:29:29.224 farads 0.0005600000 2018-02-16 17:34:10.910 0.0007600000
2018-02-16 17:30:10.520 farads 0.0002100000 2018-02-16 17:34:51.175 0.0004400000
2018-02-16 17:30:50.702 farads 0.0001700000 2018-02-16 17:35:31.234 0.0004800000
2018-02-16 17:31:30.077 farads 0.0004800000 2018-02-16 17:36:22.164 0.0002600000
2018-02-16 17:32:11.586 farads 0.0003800000 2018-02-16 17:37:02.616 0.0004100000
2018-02-16 17:32:51.434 farads 0.0003600000 2018-02-16 17:37:42.127 0.0003500000
2018-02-16 17:33:31.457 farads 0.0005300000 2018-02-16 17:38:23.346 0.0004100000
2018-02-16 17:34:10.910 farads 0.0007600000 2018-02-16 17:39:04.611 0.0002400000
2018-02-16 17:34:51.174 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:34:51.175 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:35:31.234 farads 0.0004800000 2018-02-16 17:40:33.633 0.0001500000
2018-02-16 17:36:22.164 farads 0.0002600000 2018-02-16 17:41:13.308 0.0001500000
2018-02-16 17:37:02.616 farads 0.0004100000 2018-02-16 17:41:54.643 0.0003100000
2018-02-16 17:37:42.127 farads 0.0003500000 2018-02-16 17:42:33.994 0.0002300000
2018-02-16 17:38:23.346 farads 0.0004100000 2018-02-16 17:43:14.389 0.0004000000
2018-02-16 17:39:04.611 farads 0.0002400000 2018-02-16 17:43:54.324 0.0002200000
2018-02-16 17:39:46.119 farads 0.0000700000 2018-02-16 17:44:36.122 0.0001800000
2018-02-16 17:40:33.633 farads 0.0001500000 2018-02-16 17:45:16.828 0.0002600000
2018-02-16 17:41:13.307 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000 2018-02-16 17:46:45.126 0.0000700000
2018-02-16 17:42:33.994 farads 0.0002300000 2018-02-16 17:47:25.236 0.0004400000
2018-02-16 17:43:14.389 farads 0.0004000000 2018-02-16 17:48:12.096 0.0002200000
2018-02-16 17:43:54.324 farads 0.0002200000 2018-02-16 17:49:01.891 0.0001600000
2018-02-16 17:44:36.122 farads 0.0001800000 2018-02-16 17:49:50.422 0.0002100000
2018-02-16 17:45:16.828 farads 0.0002600000 2018-02-16 17:50:31.222 0.0001300000
2018-02-16 17:45:59.158 farads 0.0001500000 2018-02-16 17:51:12.651 0.0002600000
2018-02-16 17:46:45.126 farads 0.0000700000 2018-02-16 17:51:53.478 0.0001300000
2018-02-16 17:47:25.236 farads 0.0004400000 2018-02-16 17:52:34.145 0.0004600000
2018-02-16 17:48:12.096 farads 0.0002200000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:01.891 farads 0.0001600000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:50.422 farads 0.0002100000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:50:31.222 farads 0.0001300000 NA NA
2018-02-16 17:51:12.651 farads 0.0002600000 NA NA
2018-02-16 17:51:12.652 farads 0.0002600000 NA NA
2018-02-16 17:51:53.478 farads 0.0001300000 NA NA
2018-02-16 17:52:34.145 farads 0.0004600000 NA NA
2018-02-16 17:53:14.374 farads 0.0003300000 NA NA
2018-02-16 23:53:53.906 farads 0.0002600000 2018-02-16 23:58:47.105 0.0003600000
2018-02-16 23:54:34.453 farads 0.0001200000 2018-02-16 23:59:27.080 0.0002300000
2018-02-16 23:55:15.512 farads 0.0001600000 2018-02-17 00:00:07.009 0.0002900000
2018-02-16 23:55:58.161 farads 0.0001800000 2018-02-17 00:00:51.685 0.0004900000
2018-02-16 23:56:46.602 farads 0.0002600000 2018-02-17 00:01:30.835 0.0003000000
2018-02-16 23:57:26.829 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:57:26.830 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:58:06.991 farads 0.0004400000 2018-02-17 00:02:56.048 0.0004700000
2018-02-16 23:58:47.104 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:58:47.105 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:59:27.080 farads 0.0002300000 2018-02-17 00:04:19.990 0.0001600000
2018-02-17 00:00:07.008 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:07.009 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:51.685 farads 0.0004900000 2018-02-17 00:05:40.967 0.0001400000
2018-02-17 00:01:30.835 farads 0.0003000000 2018-02-17 00:06:24.584 0.0001000000
2018-02-17 00:02:14.187 farads 0.0004300000 2018-02-17 00:07:04.742 0.0002500000
2018-02-17 00:02:56.048 farads 0.0004700000 2018-02-17 00:07:48.107 0.0003600000
2018-02-17 00:03:39.758 farads 0.0004200000 2018-02-17 00:08:31.136 0.0000700000
2018-02-17 00:04:19.990 farads 0.0001600000 2018-02-17 00:09:12.429 0.0001500000
2018-02-17 00:04:59.854 farads 0.0001700000 2018-02-17 00:09:59.567 0.0002500000
2018-02-17 00:05:40.967 farads 0.0001400000 2018-02-17 00:10:41.062 0.0001900000
2018-02-17 00:06:24.584 farads 0.0001000000 2018-02-17 00:11:21.016 0.0001600000
2018-02-17 00:07:04.742 farads 0.0002500000 2018-02-17 00:12:00.863 0.0001600000
2018-02-17 00:07:48.107 farads 0.0003600000 2018-02-17 00:12:41.023 0.0002400000
2018-02-17 00:08:31.136 farads 0.0000700000 2018-02-17 00:13:22.429 0.0001500000
2018-02-17 00:09:12.429 farads 0.0001500000 2018-02-17 00:14:04.826 0.0004100000
2018-02-17 00:09:59.567 farads 0.0002500000 2018-02-17 00:14:51.079 0.0001600000
2018-02-17 00:10:41.062 farads 0.0001900000 2018-02-17 00:15:31.247 0.0003500000
2018-02-17 00:11:21.016 farads 0.0001600000 2018-02-17 00:16:17.396 0.0001900000
2018-02-17 00:12:00.863 farads 0.0001600000 2018-02-17 00:16:56.912 0.0002100000
2018-02-17 00:12:41.023 farads 0.0002400000 2018-02-17 00:17:37.895 0.0001800000
2018-02-17 00:13:22.429 farads 0.0001500000 2018-02-17 00:18:18.354 0.0003700000
2018-02-17 00:14:04.826 farads 0.0004100000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:14:51.079 farads 0.0001600000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:15:31.247 farads 0.0003500000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:16:17.396 farads 0.0001900000 NA NA
2018-02-17 00:16:56.912 farads 0.0002100000 NA NA
2018-02-17 00:17:37.895 farads 0.0001800000 NA NA
2018-02-17 00:18:18.354 farads 0.0003700000 NA NA
2018-02-17 00:18:58.071 farads 0.0004700000 NA NA
2018-02-17 18:19:38.135 farads 0.0002000000 2018-02-17 18:24:27.966 0.0001800000
2018-02-17 18:20:22.373 farads 0.0002600000 2018-02-17 18:25:11.832 0.0002800000
2018-02-17 18:21:02.161 farads 0.0003000000 2018-02-17 18:25:52.344 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000 2018-02-17 18:26:33.672 0.0002600000
2018-02-17 18:22:25.394 farads 0.0002500000 2018-02-17 18:27:15.499 0.0004300000
2018-02-17 18:23:06.549 farads 0.0003100000 2018-02-17 18:27:55.288 0.0004800000
2018-02-17 18:23:46.638 farads 0.0002100000 2018-02-17 18:28:56.699 0.0004200000
2018-02-17 18:24:27.966 farads 0.0001800000 2018-02-17 18:29:40.909 0.0002100000
2018-02-17 18:25:11.832 farads 0.0002800000 2018-02-17 18:30:20.942 0.0003400000
2018-02-17 18:25:52.344 farads 0.0003000000 2018-02-17 18:31:03.937 0.0003500000
2018-02-17 18:26:33.672 farads 0.0002600000 2018-02-17 18:31:51.329 0.0002500000
2018-02-17 18:27:15.499 farads 0.0004300000 2018-02-17 18:32:32.608 0.0005000000
2018-02-17 18:27:55.288 farads 0.0004800000 2018-02-17 18:33:12.869 0.0004900000
2018-02-17 18:28:56.699 farads 0.0004200000 2018-02-17 18:33:52.725 0.0002300000
2018-02-17 18:29:40.909 farads 0.0002100000 2018-02-17 18:34:39.022 0.0001300000
2018-02-17 18:30:20.942 farads 0.0003400000 2018-02-17 18:35:20.579 0.0002800000
2018-02-17 18:31:03.937 farads 0.0003500000 2018-02-17 18:36:00.487 0.0002400000
2018-02-17 18:31:51.329 farads 0.0002500000 2018-02-17 18:36:51.908 0.0004500000
2018-02-17 18:32:32.608 farads 0.0005000000 2018-02-17 18:37:33.667 0.0002500000
2018-02-17 18:33:12.869 farads 0.0004900000 2018-02-17 18:38:13.989 0.0004700000
2018-02-17 18:33:52.725 farads 0.0002300000 2018-02-17 18:38:53.753 0.0003500000
2018-02-17 18:34:39.022 farads 0.0001300000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:35:20.579 farads 0.0002800000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:00.487 farads 0.0002400000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:51.908 farads 0.0004500000 NA NA
2018-02-17 18:37:33.667 farads 0.0002500000 NA NA
2018-02-17 18:38:13.989 farads 0.0004700000 NA NA
2018-02-17 18:38:53.753 farads 0.0003500000 NA NA
2018-02-17 18:39:34.052 farads 0.0004100000 NA NA
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
I am getting error when I run this code as follows: time-elapsed.awk: line 6: syntax error near unexpected token$2,' time-elapsed.awk: line 6:split($2, t, ".")'
– kalamata_Olive
Mar 6 at 20:53
How are you running it? Useawk -f time-elapsed.awk < input
– Jeff Schaller
Mar 6 at 20:59
either way I am getting the same error: bash time-elapsed.awk < data.txt | less OR bash time-elapsed.awk data.txt | less
– kalamata_Olive
Mar 6 at 21:06
well the awk -f script is running but only printing the four original columns i.e. 2018-02-16 16:42:53.926 farads 0.0000000000 2018-02-16 16:42:53.927 farads 0.0000000000 2018-02-16 16:43:33.167 farads 0.0000800000 2018-02-16 16:44:21.357 farads 0.0002600000 2018-02-16 16:45:29.557 farads 0.0004300000
– kalamata_Olive
Mar 6 at 21:22
It's not printing the last two three columns: 2018-02-16 16:52:01.997 0.0003900000
– kalamata_Olive
Mar 6 at 21:38
 |Â
show 16 more comments
up vote
2
down vote
Note: When I originally posted this, the code had a bug that caused times that were five minutes before the hour to not be matched up with their corresponding later times. Since I did not have the time to fix the bug then, I deleted the post, but now that bug should be fixed, and I've undeleted the answer. However, since you only provided a single line of expected output, please take care to test this code against a broader set of sample inputs.
You said you're not that worried about CPU time, but my initial attempt which
simply brute-forced it by comparing every line to every other line took 30s
to run on a 1800 line file, so I optimized it with the %mins hash, keyed on
the time down to the minute, so that only minutes that are +4, +5, and +6
minutes from each timestamp are taken into consideration. This version
takes ~4s to run on a ~4000 line input file.
use warnings;
use strict;
use DateTime;
use DateTime::Format::Strptime;
my $strp = DateTime::Format::Strptime->new( on_error=>'croak',
pattern => '%Y-%m-%d %H:%M:%S.%3N' );
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:.]+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = $strp->parse_datetime("$1 $2");
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$3, dt=>$dt, seek=>$dt->clone->add(minutes=>5) ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_ms,$min_other);
for my $other (@candidates)
my $diff_ms = abs($cur->seek->subtract_datetime_absolute($other->dt)
->in_units('nanoseconds'))/1e6;
if (!defined $min_diff_ms
print $cur->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," farads ",
$cur->farads, defined($min_other) ? ( " ",
$min_other->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," ",
$min_other->farads ) : '', "n";
Your original sample input:
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.558 farads 0.0004300000
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Output for that input:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Here is an even faster but less accurate version, it ignores milliseconds
and uses the core module Time::Piece
instead of the somewhat heavy DateTime
(although I can still highly recommend the latter). Compared to the above, it takes a fraction of a second to run. You can see the difference in accuracy, but you did say that ± 1 minute is acceptable.
use warnings;
use strict;
use Time::Piece;
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:]+)(.d+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = Time::Piece->strptime("$1 $2", '%Y-%m-%d %H:%M:%S');
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$4, timestr=>"$1 $2$3", epoch=>$dt->epoch ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_s,$min_other);
for my $other (@candidates) $diff_s<$min_diff_s)
$min_diff_s = $diff_s;
$min_other = $other;
print $cur->timestr," farads ",$cur->farads,
defined($min_other)
? ( " ", $min_other->timestr," ",$min_other->farads )
: '', "n";
Output:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
(Note I normally wouldn't have provided a complete solution to a question
with no code, but this was an interesting question for me.)
answered Mar 5 at 23:03
haukex
2839
Thank you! sir, I was myself trying something on these lines but couldn;t make it work: #!/bin/bash while read d1_1 d1_2 d2_1 d2_2; do secdiff=$(( $(date -d "$d2_1 $d2_2" +%s) - $(date -d "$d1_1 $d1_2" +%s) )) nanosecdiff=$(( $(date -d "$d2_1 $d2_2" +%N) - $(date -d "$d1_1 $d1_2" +%N) )) printf "%s %s - %s %s = %d millisecondsn" $d2_1 $d2_2 $d1_1 $d1_2 $(( (secdiff * 1000) + (nanosecdiff / 1000000) )) done
– kalamata_Olive
Mar 6 at 0:30
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
Here's an option using awk and GNU date; it uses date to do the "heavy lifting" of computing the timestamps in fractional seconds from the date and time fields; awk then stores each record in an array indexed by that timestamp. One side effect of this is that duplicate records are "erased" -- only result is returned for the 16:45:29.557 entry. After computing all of the timestamps-in-seconds, we loop through the records looking for the closest match to "+5 minutes" from each entry. If that closest entry's timestamp difference is less than 2 minutes (from the 5 minute target), then we print the corresponding date and time and farad reading (by simply replacing the string "farads" with the empty string); otherwise we print "NA" fields, as per your comment. That's a deviation from your spec, on the assumption that a matching entry for a given record could be "tomorrow".
getline seconds
t[3]=seconds"."t[2]
records[t[3]]=$0
END
for (record in records)
min=(0 + "INF")
for (others in records)
# skip myself
if (others == record) continue
difference=(others - record) - (5 * 60)
if (difference < 0) difference=-difference
if (difference < min)
min=difference
matchfor[record]=records[others]
if (min > (60*2))
print records[record], "NA NA"
else
sub("farads", "", matchfor[record])
print records[record], matchfor[record]
Output from the sample input (unsorted):
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
... and piped to |sort:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
The script's output, run on the expanded input, is:
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:53:23.550 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 2018-02-16 16:54:03.276 0.0005300000
2018-02-16 16:49:59.075 farads 0.0000700000 2018-02-16 16:54:44.223 0.0003800000
2018-02-16 16:50:40.486 farads 0.0002400000 2018-02-16 16:55:24.769 0.0003200000
2018-02-16 16:51:22.525 farads 0.0005900000 2018-02-16 16:56:10.028 0.0002700000
2018-02-16 16:52:01.997 farads 0.0003900000 2018-02-16 16:56:57.624 0.0000900000
2018-02-16 16:52:43.612 farads 0.0005200000 2018-02-16 16:57:37.387 0.0003000000
2018-02-16 16:53:23.550 farads 0.0003900000 2018-02-16 16:58:16.929 0.0005800000
2018-02-16 16:54:03.276 farads 0.0005300000 2018-02-16 16:58:56.961 0.0003000000
2018-02-16 16:54:44.223 farads 0.0003800000 2018-02-16 16:59:39.217 0.0001900000
2018-02-16 16:55:24.769 farads 0.0003200000 2018-02-16 17:00:19.129 0.0005800000
2018-02-16 16:56:10.028 farads 0.0002700000 2018-02-16 17:00:59.328 0.0001500000
2018-02-16 16:56:57.624 farads 0.0000900000 2018-02-16 17:01:39.138 0.0005400000
2018-02-16 16:57:37.387 farads 0.0003000000 2018-02-16 17:02:19.786 0.0006600000
2018-02-16 16:58:16.929 farads 0.0005800000 2018-02-16 17:03:00.236 0.0004700000
2018-02-16 16:58:56.961 farads 0.0003000000 2018-02-16 17:03:44.343 0.0003300000
2018-02-16 16:59:39.217 farads 0.0001900000 2018-02-16 17:04:24.996 0.0002200000
2018-02-16 17:00:19.129 farads 0.0005800000 2018-02-16 17:05:05.754 0.0003200000
2018-02-16 17:00:59.328 farads 0.0001500000 2018-02-16 17:05:48.512 0.0004600000
2018-02-16 17:01:39.138 farads 0.0005400000 2018-02-16 17:06:29.248 0.0003700000
2018-02-16 17:02:19.786 farads 0.0006600000 2018-02-16 17:07:09.819 0.0001300000
2018-02-16 17:03:00.236 farads 0.0004700000 2018-02-16 17:07:50.392 0.0005500000
2018-02-16 17:03:44.343 farads 0.0003300000 2018-02-16 17:08:32.397 0.0002000000
2018-02-16 17:04:24.996 farads 0.0002200000 2018-02-16 17:09:14.778 0.0003000000
2018-02-16 17:05:05.754 farads 0.0003200000 2018-02-16 17:09:57.688 0.0003100000
2018-02-16 17:05:48.512 farads 0.0004600000 2018-02-16 17:10:37.237 0.0003900000
2018-02-16 17:06:29.248 farads 0.0003700000 2018-02-16 17:11:21.559 0.0003500000
2018-02-16 17:07:09.819 farads 0.0001300000 2018-02-16 17:12:00.946 0.0003500000
2018-02-16 17:07:50.392 farads 0.0005500000 2018-02-16 17:12:44.127 0.0003200000
2018-02-16 17:08:32.397 farads 0.0002000000 2018-02-16 17:13:26.579 0.0003800000
2018-02-16 17:09:14.778 farads 0.0003000000 2018-02-16 17:14:09.175 0.0001100000
2018-02-16 17:09:57.688 farads 0.0003100000 2018-02-16 17:14:49.553 0.0001300000
2018-02-16 17:10:37.237 farads 0.0003900000 2018-02-16 17:15:31.044 0.0002000000
2018-02-16 17:11:21.559 farads 0.0003500000 2018-02-16 17:16:12.039 0.0000400000
2018-02-16 17:12:00.945 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:00.946 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:44.127 farads 0.0003200000 2018-02-16 17:17:33.238 0.0001900000
2018-02-16 17:13:26.579 farads 0.0003800000 2018-02-16 17:18:12.987 0.0001900000
2018-02-16 17:14:09.175 farads 0.0001100000 2018-02-16 17:18:54.214 0.0002300000
2018-02-16 17:14:49.552 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:14:49.553 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:15:31.044 farads 0.0002000000 2018-02-16 17:20:22.002 0.0000700000
2018-02-16 17:16:12.038 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:12.039 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:52.956 farads 0.0002300000 2018-02-16 17:21:43.477 0.0002000000
2018-02-16 17:17:33.238 farads 0.0001900000 2018-02-16 17:22:27.400 0.0003500000
2018-02-16 17:18:12.986 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:12.987 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:54.214 farads 0.0002300000 2018-02-16 17:23:52.907 0.0001100000
2018-02-16 17:19:34.432 farads 0.0001500000 2018-02-16 17:24:40.392 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000 2018-02-16 17:25:23.026 0.0001400000
2018-02-16 17:21:03.320 farads 0.0001100000 2018-02-16 17:26:03.886 0.0003100000
2018-02-16 17:21:43.477 farads 0.0002000000 2018-02-16 17:26:45.191 0.0001900000
2018-02-16 17:22:27.400 farads 0.0003500000 2018-02-16 17:27:28.652 0.0001000000
2018-02-16 17:23:11.224 farads 0.0001700000 2018-02-16 17:28:09.625 0.0002000000
2018-02-16 17:23:52.907 farads 0.0001100000 2018-02-16 17:28:49.753 0.0001500000
2018-02-16 17:24:40.392 farads 0.0001500000 2018-02-16 17:29:29.224 0.0005600000
2018-02-16 17:25:23.026 farads 0.0001400000 2018-02-16 17:30:10.520 0.0002100000
2018-02-16 17:26:03.886 farads 0.0003100000 2018-02-16 17:30:50.702 0.0001700000
2018-02-16 17:26:45.191 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:26:45.192 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:27:28.652 farads 0.0001000000 2018-02-16 17:32:11.586 0.0003800000
2018-02-16 17:28:09.625 farads 0.0002000000 2018-02-16 17:32:51.434 0.0003600000
2018-02-16 17:28:49.753 farads 0.0001500000 2018-02-16 17:33:31.457 0.0005300000
2018-02-16 17:29:29.224 farads 0.0005600000 2018-02-16 17:34:10.910 0.0007600000
2018-02-16 17:30:10.520 farads 0.0002100000 2018-02-16 17:34:51.175 0.0004400000
2018-02-16 17:30:50.702 farads 0.0001700000 2018-02-16 17:35:31.234 0.0004800000
2018-02-16 17:31:30.077 farads 0.0004800000 2018-02-16 17:36:22.164 0.0002600000
2018-02-16 17:32:11.586 farads 0.0003800000 2018-02-16 17:37:02.616 0.0004100000
2018-02-16 17:32:51.434 farads 0.0003600000 2018-02-16 17:37:42.127 0.0003500000
2018-02-16 17:33:31.457 farads 0.0005300000 2018-02-16 17:38:23.346 0.0004100000
2018-02-16 17:34:10.910 farads 0.0007600000 2018-02-16 17:39:04.611 0.0002400000
2018-02-16 17:34:51.174 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:34:51.175 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:35:31.234 farads 0.0004800000 2018-02-16 17:40:33.633 0.0001500000
2018-02-16 17:36:22.164 farads 0.0002600000 2018-02-16 17:41:13.308 0.0001500000
2018-02-16 17:37:02.616 farads 0.0004100000 2018-02-16 17:41:54.643 0.0003100000
2018-02-16 17:37:42.127 farads 0.0003500000 2018-02-16 17:42:33.994 0.0002300000
2018-02-16 17:38:23.346 farads 0.0004100000 2018-02-16 17:43:14.389 0.0004000000
2018-02-16 17:39:04.611 farads 0.0002400000 2018-02-16 17:43:54.324 0.0002200000
2018-02-16 17:39:46.119 farads 0.0000700000 2018-02-16 17:44:36.122 0.0001800000
2018-02-16 17:40:33.633 farads 0.0001500000 2018-02-16 17:45:16.828 0.0002600000
2018-02-16 17:41:13.307 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000 2018-02-16 17:46:45.126 0.0000700000
2018-02-16 17:42:33.994 farads 0.0002300000 2018-02-16 17:47:25.236 0.0004400000
2018-02-16 17:43:14.389 farads 0.0004000000 2018-02-16 17:48:12.096 0.0002200000
2018-02-16 17:43:54.324 farads 0.0002200000 2018-02-16 17:49:01.891 0.0001600000
2018-02-16 17:44:36.122 farads 0.0001800000 2018-02-16 17:49:50.422 0.0002100000
2018-02-16 17:45:16.828 farads 0.0002600000 2018-02-16 17:50:31.222 0.0001300000
2018-02-16 17:45:59.158 farads 0.0001500000 2018-02-16 17:51:12.651 0.0002600000
2018-02-16 17:46:45.126 farads 0.0000700000 2018-02-16 17:51:53.478 0.0001300000
2018-02-16 17:47:25.236 farads 0.0004400000 2018-02-16 17:52:34.145 0.0004600000
2018-02-16 17:48:12.096 farads 0.0002200000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:01.891 farads 0.0001600000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:50.422 farads 0.0002100000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:50:31.222 farads 0.0001300000 NA NA
2018-02-16 17:51:12.651 farads 0.0002600000 NA NA
2018-02-16 17:51:12.652 farads 0.0002600000 NA NA
2018-02-16 17:51:53.478 farads 0.0001300000 NA NA
2018-02-16 17:52:34.145 farads 0.0004600000 NA NA
2018-02-16 17:53:14.374 farads 0.0003300000 NA NA
2018-02-16 23:53:53.906 farads 0.0002600000 2018-02-16 23:58:47.105 0.0003600000
2018-02-16 23:54:34.453 farads 0.0001200000 2018-02-16 23:59:27.080 0.0002300000
2018-02-16 23:55:15.512 farads 0.0001600000 2018-02-17 00:00:07.009 0.0002900000
2018-02-16 23:55:58.161 farads 0.0001800000 2018-02-17 00:00:51.685 0.0004900000
2018-02-16 23:56:46.602 farads 0.0002600000 2018-02-17 00:01:30.835 0.0003000000
2018-02-16 23:57:26.829 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:57:26.830 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:58:06.991 farads 0.0004400000 2018-02-17 00:02:56.048 0.0004700000
2018-02-16 23:58:47.104 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:58:47.105 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:59:27.080 farads 0.0002300000 2018-02-17 00:04:19.990 0.0001600000
2018-02-17 00:00:07.008 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:07.009 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:51.685 farads 0.0004900000 2018-02-17 00:05:40.967 0.0001400000
2018-02-17 00:01:30.835 farads 0.0003000000 2018-02-17 00:06:24.584 0.0001000000
2018-02-17 00:02:14.187 farads 0.0004300000 2018-02-17 00:07:04.742 0.0002500000
2018-02-17 00:02:56.048 farads 0.0004700000 2018-02-17 00:07:48.107 0.0003600000
2018-02-17 00:03:39.758 farads 0.0004200000 2018-02-17 00:08:31.136 0.0000700000
2018-02-17 00:04:19.990 farads 0.0001600000 2018-02-17 00:09:12.429 0.0001500000
2018-02-17 00:04:59.854 farads 0.0001700000 2018-02-17 00:09:59.567 0.0002500000
2018-02-17 00:05:40.967 farads 0.0001400000 2018-02-17 00:10:41.062 0.0001900000
2018-02-17 00:06:24.584 farads 0.0001000000 2018-02-17 00:11:21.016 0.0001600000
2018-02-17 00:07:04.742 farads 0.0002500000 2018-02-17 00:12:00.863 0.0001600000
2018-02-17 00:07:48.107 farads 0.0003600000 2018-02-17 00:12:41.023 0.0002400000
2018-02-17 00:08:31.136 farads 0.0000700000 2018-02-17 00:13:22.429 0.0001500000
2018-02-17 00:09:12.429 farads 0.0001500000 2018-02-17 00:14:04.826 0.0004100000
2018-02-17 00:09:59.567 farads 0.0002500000 2018-02-17 00:14:51.079 0.0001600000
2018-02-17 00:10:41.062 farads 0.0001900000 2018-02-17 00:15:31.247 0.0003500000
2018-02-17 00:11:21.016 farads 0.0001600000 2018-02-17 00:16:17.396 0.0001900000
2018-02-17 00:12:00.863 farads 0.0001600000 2018-02-17 00:16:56.912 0.0002100000
2018-02-17 00:12:41.023 farads 0.0002400000 2018-02-17 00:17:37.895 0.0001800000
2018-02-17 00:13:22.429 farads 0.0001500000 2018-02-17 00:18:18.354 0.0003700000
2018-02-17 00:14:04.826 farads 0.0004100000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:14:51.079 farads 0.0001600000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:15:31.247 farads 0.0003500000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:16:17.396 farads 0.0001900000 NA NA
2018-02-17 00:16:56.912 farads 0.0002100000 NA NA
2018-02-17 00:17:37.895 farads 0.0001800000 NA NA
2018-02-17 00:18:18.354 farads 0.0003700000 NA NA
2018-02-17 00:18:58.071 farads 0.0004700000 NA NA
2018-02-17 18:19:38.135 farads 0.0002000000 2018-02-17 18:24:27.966 0.0001800000
2018-02-17 18:20:22.373 farads 0.0002600000 2018-02-17 18:25:11.832 0.0002800000
2018-02-17 18:21:02.161 farads 0.0003000000 2018-02-17 18:25:52.344 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000 2018-02-17 18:26:33.672 0.0002600000
2018-02-17 18:22:25.394 farads 0.0002500000 2018-02-17 18:27:15.499 0.0004300000
2018-02-17 18:23:06.549 farads 0.0003100000 2018-02-17 18:27:55.288 0.0004800000
2018-02-17 18:23:46.638 farads 0.0002100000 2018-02-17 18:28:56.699 0.0004200000
2018-02-17 18:24:27.966 farads 0.0001800000 2018-02-17 18:29:40.909 0.0002100000
2018-02-17 18:25:11.832 farads 0.0002800000 2018-02-17 18:30:20.942 0.0003400000
2018-02-17 18:25:52.344 farads 0.0003000000 2018-02-17 18:31:03.937 0.0003500000
2018-02-17 18:26:33.672 farads 0.0002600000 2018-02-17 18:31:51.329 0.0002500000
2018-02-17 18:27:15.499 farads 0.0004300000 2018-02-17 18:32:32.608 0.0005000000
2018-02-17 18:27:55.288 farads 0.0004800000 2018-02-17 18:33:12.869 0.0004900000
2018-02-17 18:28:56.699 farads 0.0004200000 2018-02-17 18:33:52.725 0.0002300000
2018-02-17 18:29:40.909 farads 0.0002100000 2018-02-17 18:34:39.022 0.0001300000
2018-02-17 18:30:20.942 farads 0.0003400000 2018-02-17 18:35:20.579 0.0002800000
2018-02-17 18:31:03.937 farads 0.0003500000 2018-02-17 18:36:00.487 0.0002400000
2018-02-17 18:31:51.329 farads 0.0002500000 2018-02-17 18:36:51.908 0.0004500000
2018-02-17 18:32:32.608 farads 0.0005000000 2018-02-17 18:37:33.667 0.0002500000
2018-02-17 18:33:12.869 farads 0.0004900000 2018-02-17 18:38:13.989 0.0004700000
2018-02-17 18:33:52.725 farads 0.0002300000 2018-02-17 18:38:53.753 0.0003500000
2018-02-17 18:34:39.022 farads 0.0001300000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:35:20.579 farads 0.0002800000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:00.487 farads 0.0002400000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:51.908 farads 0.0004500000 NA NA
2018-02-17 18:37:33.667 farads 0.0002500000 NA NA
2018-02-17 18:38:13.989 farads 0.0004700000 NA NA
2018-02-17 18:38:53.753 farads 0.0003500000 NA NA
2018-02-17 18:39:34.052 farads 0.0004100000 NA NA
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
I am getting error when I run this code as follows: time-elapsed.awk: line 6: syntax error near unexpected token$2,' time-elapsed.awk: line 6:split($2, t, ".")'
– kalamata_Olive
Mar 6 at 20:53
How are you running it? Useawk -f time-elapsed.awk < input
– Jeff Schaller
Mar 6 at 20:59
either way I am getting the same error: bash time-elapsed.awk < data.txt | less OR bash time-elapsed.awk data.txt | less
– kalamata_Olive
Mar 6 at 21:06
well the awk -f script is running but only printing the four original columns i.e. 2018-02-16 16:42:53.926 farads 0.0000000000 2018-02-16 16:42:53.927 farads 0.0000000000 2018-02-16 16:43:33.167 farads 0.0000800000 2018-02-16 16:44:21.357 farads 0.0002600000 2018-02-16 16:45:29.557 farads 0.0004300000
– kalamata_Olive
Mar 6 at 21:22
It's not printing the last two three columns: 2018-02-16 16:52:01.997 0.0003900000
– kalamata_Olive
Mar 6 at 21:38
 |Â
show 16 more comments
up vote
0
down vote
accepted
Here's an option using awk and GNU date; it uses date to do the "heavy lifting" of computing the timestamps in fractional seconds from the date and time fields; awk then stores each record in an array indexed by that timestamp. One side effect of this is that duplicate records are "erased" -- only result is returned for the 16:45:29.557 entry. After computing all of the timestamps-in-seconds, we loop through the records looking for the closest match to "+5 minutes" from each entry. If that closest entry's timestamp difference is less than 2 minutes (from the 5 minute target), then we print the corresponding date and time and farad reading (by simply replacing the string "farads" with the empty string); otherwise we print "NA" fields, as per your comment. That's a deviation from your spec, on the assumption that a matching entry for a given record could be "tomorrow".
getline seconds
t[3]=seconds"."t[2]
records[t[3]]=$0
END
for (record in records)
min=(0 + "INF")
for (others in records)
# skip myself
if (others == record) continue
difference=(others - record) - (5 * 60)
if (difference < 0) difference=-difference
if (difference < min)
min=difference
matchfor[record]=records[others]
if (min > (60*2))
print records[record], "NA NA"
else
sub("farads", "", matchfor[record])
print records[record], matchfor[record]
Output from the sample input (unsorted):
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
... and piped to |sort:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
The script's output, run on the expanded input, is:
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:53:23.550 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 2018-02-16 16:54:03.276 0.0005300000
2018-02-16 16:49:59.075 farads 0.0000700000 2018-02-16 16:54:44.223 0.0003800000
2018-02-16 16:50:40.486 farads 0.0002400000 2018-02-16 16:55:24.769 0.0003200000
2018-02-16 16:51:22.525 farads 0.0005900000 2018-02-16 16:56:10.028 0.0002700000
2018-02-16 16:52:01.997 farads 0.0003900000 2018-02-16 16:56:57.624 0.0000900000
2018-02-16 16:52:43.612 farads 0.0005200000 2018-02-16 16:57:37.387 0.0003000000
2018-02-16 16:53:23.550 farads 0.0003900000 2018-02-16 16:58:16.929 0.0005800000
2018-02-16 16:54:03.276 farads 0.0005300000 2018-02-16 16:58:56.961 0.0003000000
2018-02-16 16:54:44.223 farads 0.0003800000 2018-02-16 16:59:39.217 0.0001900000
2018-02-16 16:55:24.769 farads 0.0003200000 2018-02-16 17:00:19.129 0.0005800000
2018-02-16 16:56:10.028 farads 0.0002700000 2018-02-16 17:00:59.328 0.0001500000
2018-02-16 16:56:57.624 farads 0.0000900000 2018-02-16 17:01:39.138 0.0005400000
2018-02-16 16:57:37.387 farads 0.0003000000 2018-02-16 17:02:19.786 0.0006600000
2018-02-16 16:58:16.929 farads 0.0005800000 2018-02-16 17:03:00.236 0.0004700000
2018-02-16 16:58:56.961 farads 0.0003000000 2018-02-16 17:03:44.343 0.0003300000
2018-02-16 16:59:39.217 farads 0.0001900000 2018-02-16 17:04:24.996 0.0002200000
2018-02-16 17:00:19.129 farads 0.0005800000 2018-02-16 17:05:05.754 0.0003200000
2018-02-16 17:00:59.328 farads 0.0001500000 2018-02-16 17:05:48.512 0.0004600000
2018-02-16 17:01:39.138 farads 0.0005400000 2018-02-16 17:06:29.248 0.0003700000
2018-02-16 17:02:19.786 farads 0.0006600000 2018-02-16 17:07:09.819 0.0001300000
2018-02-16 17:03:00.236 farads 0.0004700000 2018-02-16 17:07:50.392 0.0005500000
2018-02-16 17:03:44.343 farads 0.0003300000 2018-02-16 17:08:32.397 0.0002000000
2018-02-16 17:04:24.996 farads 0.0002200000 2018-02-16 17:09:14.778 0.0003000000
2018-02-16 17:05:05.754 farads 0.0003200000 2018-02-16 17:09:57.688 0.0003100000
2018-02-16 17:05:48.512 farads 0.0004600000 2018-02-16 17:10:37.237 0.0003900000
2018-02-16 17:06:29.248 farads 0.0003700000 2018-02-16 17:11:21.559 0.0003500000
2018-02-16 17:07:09.819 farads 0.0001300000 2018-02-16 17:12:00.946 0.0003500000
2018-02-16 17:07:50.392 farads 0.0005500000 2018-02-16 17:12:44.127 0.0003200000
2018-02-16 17:08:32.397 farads 0.0002000000 2018-02-16 17:13:26.579 0.0003800000
2018-02-16 17:09:14.778 farads 0.0003000000 2018-02-16 17:14:09.175 0.0001100000
2018-02-16 17:09:57.688 farads 0.0003100000 2018-02-16 17:14:49.553 0.0001300000
2018-02-16 17:10:37.237 farads 0.0003900000 2018-02-16 17:15:31.044 0.0002000000
2018-02-16 17:11:21.559 farads 0.0003500000 2018-02-16 17:16:12.039 0.0000400000
2018-02-16 17:12:00.945 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:00.946 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:44.127 farads 0.0003200000 2018-02-16 17:17:33.238 0.0001900000
2018-02-16 17:13:26.579 farads 0.0003800000 2018-02-16 17:18:12.987 0.0001900000
2018-02-16 17:14:09.175 farads 0.0001100000 2018-02-16 17:18:54.214 0.0002300000
2018-02-16 17:14:49.552 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:14:49.553 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:15:31.044 farads 0.0002000000 2018-02-16 17:20:22.002 0.0000700000
2018-02-16 17:16:12.038 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:12.039 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:52.956 farads 0.0002300000 2018-02-16 17:21:43.477 0.0002000000
2018-02-16 17:17:33.238 farads 0.0001900000 2018-02-16 17:22:27.400 0.0003500000
2018-02-16 17:18:12.986 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:12.987 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:54.214 farads 0.0002300000 2018-02-16 17:23:52.907 0.0001100000
2018-02-16 17:19:34.432 farads 0.0001500000 2018-02-16 17:24:40.392 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000 2018-02-16 17:25:23.026 0.0001400000
2018-02-16 17:21:03.320 farads 0.0001100000 2018-02-16 17:26:03.886 0.0003100000
2018-02-16 17:21:43.477 farads 0.0002000000 2018-02-16 17:26:45.191 0.0001900000
2018-02-16 17:22:27.400 farads 0.0003500000 2018-02-16 17:27:28.652 0.0001000000
2018-02-16 17:23:11.224 farads 0.0001700000 2018-02-16 17:28:09.625 0.0002000000
2018-02-16 17:23:52.907 farads 0.0001100000 2018-02-16 17:28:49.753 0.0001500000
2018-02-16 17:24:40.392 farads 0.0001500000 2018-02-16 17:29:29.224 0.0005600000
2018-02-16 17:25:23.026 farads 0.0001400000 2018-02-16 17:30:10.520 0.0002100000
2018-02-16 17:26:03.886 farads 0.0003100000 2018-02-16 17:30:50.702 0.0001700000
2018-02-16 17:26:45.191 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:26:45.192 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:27:28.652 farads 0.0001000000 2018-02-16 17:32:11.586 0.0003800000
2018-02-16 17:28:09.625 farads 0.0002000000 2018-02-16 17:32:51.434 0.0003600000
2018-02-16 17:28:49.753 farads 0.0001500000 2018-02-16 17:33:31.457 0.0005300000
2018-02-16 17:29:29.224 farads 0.0005600000 2018-02-16 17:34:10.910 0.0007600000
2018-02-16 17:30:10.520 farads 0.0002100000 2018-02-16 17:34:51.175 0.0004400000
2018-02-16 17:30:50.702 farads 0.0001700000 2018-02-16 17:35:31.234 0.0004800000
2018-02-16 17:31:30.077 farads 0.0004800000 2018-02-16 17:36:22.164 0.0002600000
2018-02-16 17:32:11.586 farads 0.0003800000 2018-02-16 17:37:02.616 0.0004100000
2018-02-16 17:32:51.434 farads 0.0003600000 2018-02-16 17:37:42.127 0.0003500000
2018-02-16 17:33:31.457 farads 0.0005300000 2018-02-16 17:38:23.346 0.0004100000
2018-02-16 17:34:10.910 farads 0.0007600000 2018-02-16 17:39:04.611 0.0002400000
2018-02-16 17:34:51.174 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:34:51.175 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:35:31.234 farads 0.0004800000 2018-02-16 17:40:33.633 0.0001500000
2018-02-16 17:36:22.164 farads 0.0002600000 2018-02-16 17:41:13.308 0.0001500000
2018-02-16 17:37:02.616 farads 0.0004100000 2018-02-16 17:41:54.643 0.0003100000
2018-02-16 17:37:42.127 farads 0.0003500000 2018-02-16 17:42:33.994 0.0002300000
2018-02-16 17:38:23.346 farads 0.0004100000 2018-02-16 17:43:14.389 0.0004000000
2018-02-16 17:39:04.611 farads 0.0002400000 2018-02-16 17:43:54.324 0.0002200000
2018-02-16 17:39:46.119 farads 0.0000700000 2018-02-16 17:44:36.122 0.0001800000
2018-02-16 17:40:33.633 farads 0.0001500000 2018-02-16 17:45:16.828 0.0002600000
2018-02-16 17:41:13.307 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000 2018-02-16 17:46:45.126 0.0000700000
2018-02-16 17:42:33.994 farads 0.0002300000 2018-02-16 17:47:25.236 0.0004400000
2018-02-16 17:43:14.389 farads 0.0004000000 2018-02-16 17:48:12.096 0.0002200000
2018-02-16 17:43:54.324 farads 0.0002200000 2018-02-16 17:49:01.891 0.0001600000
2018-02-16 17:44:36.122 farads 0.0001800000 2018-02-16 17:49:50.422 0.0002100000
2018-02-16 17:45:16.828 farads 0.0002600000 2018-02-16 17:50:31.222 0.0001300000
2018-02-16 17:45:59.158 farads 0.0001500000 2018-02-16 17:51:12.651 0.0002600000
2018-02-16 17:46:45.126 farads 0.0000700000 2018-02-16 17:51:53.478 0.0001300000
2018-02-16 17:47:25.236 farads 0.0004400000 2018-02-16 17:52:34.145 0.0004600000
2018-02-16 17:48:12.096 farads 0.0002200000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:01.891 farads 0.0001600000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:50.422 farads 0.0002100000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:50:31.222 farads 0.0001300000 NA NA
2018-02-16 17:51:12.651 farads 0.0002600000 NA NA
2018-02-16 17:51:12.652 farads 0.0002600000 NA NA
2018-02-16 17:51:53.478 farads 0.0001300000 NA NA
2018-02-16 17:52:34.145 farads 0.0004600000 NA NA
2018-02-16 17:53:14.374 farads 0.0003300000 NA NA
2018-02-16 23:53:53.906 farads 0.0002600000 2018-02-16 23:58:47.105 0.0003600000
2018-02-16 23:54:34.453 farads 0.0001200000 2018-02-16 23:59:27.080 0.0002300000
2018-02-16 23:55:15.512 farads 0.0001600000 2018-02-17 00:00:07.009 0.0002900000
2018-02-16 23:55:58.161 farads 0.0001800000 2018-02-17 00:00:51.685 0.0004900000
2018-02-16 23:56:46.602 farads 0.0002600000 2018-02-17 00:01:30.835 0.0003000000
2018-02-16 23:57:26.829 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:57:26.830 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:58:06.991 farads 0.0004400000 2018-02-17 00:02:56.048 0.0004700000
2018-02-16 23:58:47.104 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:58:47.105 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:59:27.080 farads 0.0002300000 2018-02-17 00:04:19.990 0.0001600000
2018-02-17 00:00:07.008 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:07.009 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:51.685 farads 0.0004900000 2018-02-17 00:05:40.967 0.0001400000
2018-02-17 00:01:30.835 farads 0.0003000000 2018-02-17 00:06:24.584 0.0001000000
2018-02-17 00:02:14.187 farads 0.0004300000 2018-02-17 00:07:04.742 0.0002500000
2018-02-17 00:02:56.048 farads 0.0004700000 2018-02-17 00:07:48.107 0.0003600000
2018-02-17 00:03:39.758 farads 0.0004200000 2018-02-17 00:08:31.136 0.0000700000
2018-02-17 00:04:19.990 farads 0.0001600000 2018-02-17 00:09:12.429 0.0001500000
2018-02-17 00:04:59.854 farads 0.0001700000 2018-02-17 00:09:59.567 0.0002500000
2018-02-17 00:05:40.967 farads 0.0001400000 2018-02-17 00:10:41.062 0.0001900000
2018-02-17 00:06:24.584 farads 0.0001000000 2018-02-17 00:11:21.016 0.0001600000
2018-02-17 00:07:04.742 farads 0.0002500000 2018-02-17 00:12:00.863 0.0001600000
2018-02-17 00:07:48.107 farads 0.0003600000 2018-02-17 00:12:41.023 0.0002400000
2018-02-17 00:08:31.136 farads 0.0000700000 2018-02-17 00:13:22.429 0.0001500000
2018-02-17 00:09:12.429 farads 0.0001500000 2018-02-17 00:14:04.826 0.0004100000
2018-02-17 00:09:59.567 farads 0.0002500000 2018-02-17 00:14:51.079 0.0001600000
2018-02-17 00:10:41.062 farads 0.0001900000 2018-02-17 00:15:31.247 0.0003500000
2018-02-17 00:11:21.016 farads 0.0001600000 2018-02-17 00:16:17.396 0.0001900000
2018-02-17 00:12:00.863 farads 0.0001600000 2018-02-17 00:16:56.912 0.0002100000
2018-02-17 00:12:41.023 farads 0.0002400000 2018-02-17 00:17:37.895 0.0001800000
2018-02-17 00:13:22.429 farads 0.0001500000 2018-02-17 00:18:18.354 0.0003700000
2018-02-17 00:14:04.826 farads 0.0004100000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:14:51.079 farads 0.0001600000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:15:31.247 farads 0.0003500000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:16:17.396 farads 0.0001900000 NA NA
2018-02-17 00:16:56.912 farads 0.0002100000 NA NA
2018-02-17 00:17:37.895 farads 0.0001800000 NA NA
2018-02-17 00:18:18.354 farads 0.0003700000 NA NA
2018-02-17 00:18:58.071 farads 0.0004700000 NA NA
2018-02-17 18:19:38.135 farads 0.0002000000 2018-02-17 18:24:27.966 0.0001800000
2018-02-17 18:20:22.373 farads 0.0002600000 2018-02-17 18:25:11.832 0.0002800000
2018-02-17 18:21:02.161 farads 0.0003000000 2018-02-17 18:25:52.344 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000 2018-02-17 18:26:33.672 0.0002600000
2018-02-17 18:22:25.394 farads 0.0002500000 2018-02-17 18:27:15.499 0.0004300000
2018-02-17 18:23:06.549 farads 0.0003100000 2018-02-17 18:27:55.288 0.0004800000
2018-02-17 18:23:46.638 farads 0.0002100000 2018-02-17 18:28:56.699 0.0004200000
2018-02-17 18:24:27.966 farads 0.0001800000 2018-02-17 18:29:40.909 0.0002100000
2018-02-17 18:25:11.832 farads 0.0002800000 2018-02-17 18:30:20.942 0.0003400000
2018-02-17 18:25:52.344 farads 0.0003000000 2018-02-17 18:31:03.937 0.0003500000
2018-02-17 18:26:33.672 farads 0.0002600000 2018-02-17 18:31:51.329 0.0002500000
2018-02-17 18:27:15.499 farads 0.0004300000 2018-02-17 18:32:32.608 0.0005000000
2018-02-17 18:27:55.288 farads 0.0004800000 2018-02-17 18:33:12.869 0.0004900000
2018-02-17 18:28:56.699 farads 0.0004200000 2018-02-17 18:33:52.725 0.0002300000
2018-02-17 18:29:40.909 farads 0.0002100000 2018-02-17 18:34:39.022 0.0001300000
2018-02-17 18:30:20.942 farads 0.0003400000 2018-02-17 18:35:20.579 0.0002800000
2018-02-17 18:31:03.937 farads 0.0003500000 2018-02-17 18:36:00.487 0.0002400000
2018-02-17 18:31:51.329 farads 0.0002500000 2018-02-17 18:36:51.908 0.0004500000
2018-02-17 18:32:32.608 farads 0.0005000000 2018-02-17 18:37:33.667 0.0002500000
2018-02-17 18:33:12.869 farads 0.0004900000 2018-02-17 18:38:13.989 0.0004700000
2018-02-17 18:33:52.725 farads 0.0002300000 2018-02-17 18:38:53.753 0.0003500000
2018-02-17 18:34:39.022 farads 0.0001300000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:35:20.579 farads 0.0002800000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:00.487 farads 0.0002400000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:51.908 farads 0.0004500000 NA NA
2018-02-17 18:37:33.667 farads 0.0002500000 NA NA
2018-02-17 18:38:13.989 farads 0.0004700000 NA NA
2018-02-17 18:38:53.753 farads 0.0003500000 NA NA
2018-02-17 18:39:34.052 farads 0.0004100000 NA NA
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
I am getting error when I run this code as follows: time-elapsed.awk: line 6: syntax error near unexpected token$2,' time-elapsed.awk: line 6:split($2, t, ".")'
– kalamata_Olive
Mar 6 at 20:53
How are you running it? Useawk -f time-elapsed.awk < input
– Jeff Schaller
Mar 6 at 20:59
either way I am getting the same error: bash time-elapsed.awk < data.txt | less OR bash time-elapsed.awk data.txt | less
– kalamata_Olive
Mar 6 at 21:06
well the awk -f script is running but only printing the four original columns i.e. 2018-02-16 16:42:53.926 farads 0.0000000000 2018-02-16 16:42:53.927 farads 0.0000000000 2018-02-16 16:43:33.167 farads 0.0000800000 2018-02-16 16:44:21.357 farads 0.0002600000 2018-02-16 16:45:29.557 farads 0.0004300000
– kalamata_Olive
Mar 6 at 21:22
It's not printing the last two three columns: 2018-02-16 16:52:01.997 0.0003900000
– kalamata_Olive
Mar 6 at 21:38
 |Â
show 16 more comments
up vote
0
down vote
accepted
up vote
0
down vote
accepted
Here's an option using awk and GNU date; it uses date to do the "heavy lifting" of computing the timestamps in fractional seconds from the date and time fields; awk then stores each record in an array indexed by that timestamp. One side effect of this is that duplicate records are "erased" -- only result is returned for the 16:45:29.557 entry. After computing all of the timestamps-in-seconds, we loop through the records looking for the closest match to "+5 minutes" from each entry. If that closest entry's timestamp difference is less than 2 minutes (from the 5 minute target), then we print the corresponding date and time and farad reading (by simply replacing the string "farads" with the empty string); otherwise we print "NA" fields, as per your comment. That's a deviation from your spec, on the assumption that a matching entry for a given record could be "tomorrow".
getline seconds
t[3]=seconds"."t[2]
records[t[3]]=$0
END
for (record in records)
min=(0 + "INF")
for (others in records)
# skip myself
if (others == record) continue
difference=(others - record) - (5 * 60)
if (difference < 0) difference=-difference
if (difference < min)
min=difference
matchfor[record]=records[others]
if (min > (60*2))
print records[record], "NA NA"
else
sub("farads", "", matchfor[record])
print records[record], matchfor[record]
Output from the sample input (unsorted):
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
... and piped to |sort:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
The script's output, run on the expanded input, is:
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:53:23.550 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 2018-02-16 16:54:03.276 0.0005300000
2018-02-16 16:49:59.075 farads 0.0000700000 2018-02-16 16:54:44.223 0.0003800000
2018-02-16 16:50:40.486 farads 0.0002400000 2018-02-16 16:55:24.769 0.0003200000
2018-02-16 16:51:22.525 farads 0.0005900000 2018-02-16 16:56:10.028 0.0002700000
2018-02-16 16:52:01.997 farads 0.0003900000 2018-02-16 16:56:57.624 0.0000900000
2018-02-16 16:52:43.612 farads 0.0005200000 2018-02-16 16:57:37.387 0.0003000000
2018-02-16 16:53:23.550 farads 0.0003900000 2018-02-16 16:58:16.929 0.0005800000
2018-02-16 16:54:03.276 farads 0.0005300000 2018-02-16 16:58:56.961 0.0003000000
2018-02-16 16:54:44.223 farads 0.0003800000 2018-02-16 16:59:39.217 0.0001900000
2018-02-16 16:55:24.769 farads 0.0003200000 2018-02-16 17:00:19.129 0.0005800000
2018-02-16 16:56:10.028 farads 0.0002700000 2018-02-16 17:00:59.328 0.0001500000
2018-02-16 16:56:57.624 farads 0.0000900000 2018-02-16 17:01:39.138 0.0005400000
2018-02-16 16:57:37.387 farads 0.0003000000 2018-02-16 17:02:19.786 0.0006600000
2018-02-16 16:58:16.929 farads 0.0005800000 2018-02-16 17:03:00.236 0.0004700000
2018-02-16 16:58:56.961 farads 0.0003000000 2018-02-16 17:03:44.343 0.0003300000
2018-02-16 16:59:39.217 farads 0.0001900000 2018-02-16 17:04:24.996 0.0002200000
2018-02-16 17:00:19.129 farads 0.0005800000 2018-02-16 17:05:05.754 0.0003200000
2018-02-16 17:00:59.328 farads 0.0001500000 2018-02-16 17:05:48.512 0.0004600000
2018-02-16 17:01:39.138 farads 0.0005400000 2018-02-16 17:06:29.248 0.0003700000
2018-02-16 17:02:19.786 farads 0.0006600000 2018-02-16 17:07:09.819 0.0001300000
2018-02-16 17:03:00.236 farads 0.0004700000 2018-02-16 17:07:50.392 0.0005500000
2018-02-16 17:03:44.343 farads 0.0003300000 2018-02-16 17:08:32.397 0.0002000000
2018-02-16 17:04:24.996 farads 0.0002200000 2018-02-16 17:09:14.778 0.0003000000
2018-02-16 17:05:05.754 farads 0.0003200000 2018-02-16 17:09:57.688 0.0003100000
2018-02-16 17:05:48.512 farads 0.0004600000 2018-02-16 17:10:37.237 0.0003900000
2018-02-16 17:06:29.248 farads 0.0003700000 2018-02-16 17:11:21.559 0.0003500000
2018-02-16 17:07:09.819 farads 0.0001300000 2018-02-16 17:12:00.946 0.0003500000
2018-02-16 17:07:50.392 farads 0.0005500000 2018-02-16 17:12:44.127 0.0003200000
2018-02-16 17:08:32.397 farads 0.0002000000 2018-02-16 17:13:26.579 0.0003800000
2018-02-16 17:09:14.778 farads 0.0003000000 2018-02-16 17:14:09.175 0.0001100000
2018-02-16 17:09:57.688 farads 0.0003100000 2018-02-16 17:14:49.553 0.0001300000
2018-02-16 17:10:37.237 farads 0.0003900000 2018-02-16 17:15:31.044 0.0002000000
2018-02-16 17:11:21.559 farads 0.0003500000 2018-02-16 17:16:12.039 0.0000400000
2018-02-16 17:12:00.945 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:00.946 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:44.127 farads 0.0003200000 2018-02-16 17:17:33.238 0.0001900000
2018-02-16 17:13:26.579 farads 0.0003800000 2018-02-16 17:18:12.987 0.0001900000
2018-02-16 17:14:09.175 farads 0.0001100000 2018-02-16 17:18:54.214 0.0002300000
2018-02-16 17:14:49.552 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:14:49.553 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:15:31.044 farads 0.0002000000 2018-02-16 17:20:22.002 0.0000700000
2018-02-16 17:16:12.038 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:12.039 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:52.956 farads 0.0002300000 2018-02-16 17:21:43.477 0.0002000000
2018-02-16 17:17:33.238 farads 0.0001900000 2018-02-16 17:22:27.400 0.0003500000
2018-02-16 17:18:12.986 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:12.987 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:54.214 farads 0.0002300000 2018-02-16 17:23:52.907 0.0001100000
2018-02-16 17:19:34.432 farads 0.0001500000 2018-02-16 17:24:40.392 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000 2018-02-16 17:25:23.026 0.0001400000
2018-02-16 17:21:03.320 farads 0.0001100000 2018-02-16 17:26:03.886 0.0003100000
2018-02-16 17:21:43.477 farads 0.0002000000 2018-02-16 17:26:45.191 0.0001900000
2018-02-16 17:22:27.400 farads 0.0003500000 2018-02-16 17:27:28.652 0.0001000000
2018-02-16 17:23:11.224 farads 0.0001700000 2018-02-16 17:28:09.625 0.0002000000
2018-02-16 17:23:52.907 farads 0.0001100000 2018-02-16 17:28:49.753 0.0001500000
2018-02-16 17:24:40.392 farads 0.0001500000 2018-02-16 17:29:29.224 0.0005600000
2018-02-16 17:25:23.026 farads 0.0001400000 2018-02-16 17:30:10.520 0.0002100000
2018-02-16 17:26:03.886 farads 0.0003100000 2018-02-16 17:30:50.702 0.0001700000
2018-02-16 17:26:45.191 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:26:45.192 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:27:28.652 farads 0.0001000000 2018-02-16 17:32:11.586 0.0003800000
2018-02-16 17:28:09.625 farads 0.0002000000 2018-02-16 17:32:51.434 0.0003600000
2018-02-16 17:28:49.753 farads 0.0001500000 2018-02-16 17:33:31.457 0.0005300000
2018-02-16 17:29:29.224 farads 0.0005600000 2018-02-16 17:34:10.910 0.0007600000
2018-02-16 17:30:10.520 farads 0.0002100000 2018-02-16 17:34:51.175 0.0004400000
2018-02-16 17:30:50.702 farads 0.0001700000 2018-02-16 17:35:31.234 0.0004800000
2018-02-16 17:31:30.077 farads 0.0004800000 2018-02-16 17:36:22.164 0.0002600000
2018-02-16 17:32:11.586 farads 0.0003800000 2018-02-16 17:37:02.616 0.0004100000
2018-02-16 17:32:51.434 farads 0.0003600000 2018-02-16 17:37:42.127 0.0003500000
2018-02-16 17:33:31.457 farads 0.0005300000 2018-02-16 17:38:23.346 0.0004100000
2018-02-16 17:34:10.910 farads 0.0007600000 2018-02-16 17:39:04.611 0.0002400000
2018-02-16 17:34:51.174 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:34:51.175 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:35:31.234 farads 0.0004800000 2018-02-16 17:40:33.633 0.0001500000
2018-02-16 17:36:22.164 farads 0.0002600000 2018-02-16 17:41:13.308 0.0001500000
2018-02-16 17:37:02.616 farads 0.0004100000 2018-02-16 17:41:54.643 0.0003100000
2018-02-16 17:37:42.127 farads 0.0003500000 2018-02-16 17:42:33.994 0.0002300000
2018-02-16 17:38:23.346 farads 0.0004100000 2018-02-16 17:43:14.389 0.0004000000
2018-02-16 17:39:04.611 farads 0.0002400000 2018-02-16 17:43:54.324 0.0002200000
2018-02-16 17:39:46.119 farads 0.0000700000 2018-02-16 17:44:36.122 0.0001800000
2018-02-16 17:40:33.633 farads 0.0001500000 2018-02-16 17:45:16.828 0.0002600000
2018-02-16 17:41:13.307 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000 2018-02-16 17:46:45.126 0.0000700000
2018-02-16 17:42:33.994 farads 0.0002300000 2018-02-16 17:47:25.236 0.0004400000
2018-02-16 17:43:14.389 farads 0.0004000000 2018-02-16 17:48:12.096 0.0002200000
2018-02-16 17:43:54.324 farads 0.0002200000 2018-02-16 17:49:01.891 0.0001600000
2018-02-16 17:44:36.122 farads 0.0001800000 2018-02-16 17:49:50.422 0.0002100000
2018-02-16 17:45:16.828 farads 0.0002600000 2018-02-16 17:50:31.222 0.0001300000
2018-02-16 17:45:59.158 farads 0.0001500000 2018-02-16 17:51:12.651 0.0002600000
2018-02-16 17:46:45.126 farads 0.0000700000 2018-02-16 17:51:53.478 0.0001300000
2018-02-16 17:47:25.236 farads 0.0004400000 2018-02-16 17:52:34.145 0.0004600000
2018-02-16 17:48:12.096 farads 0.0002200000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:01.891 farads 0.0001600000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:50.422 farads 0.0002100000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:50:31.222 farads 0.0001300000 NA NA
2018-02-16 17:51:12.651 farads 0.0002600000 NA NA
2018-02-16 17:51:12.652 farads 0.0002600000 NA NA
2018-02-16 17:51:53.478 farads 0.0001300000 NA NA
2018-02-16 17:52:34.145 farads 0.0004600000 NA NA
2018-02-16 17:53:14.374 farads 0.0003300000 NA NA
2018-02-16 23:53:53.906 farads 0.0002600000 2018-02-16 23:58:47.105 0.0003600000
2018-02-16 23:54:34.453 farads 0.0001200000 2018-02-16 23:59:27.080 0.0002300000
2018-02-16 23:55:15.512 farads 0.0001600000 2018-02-17 00:00:07.009 0.0002900000
2018-02-16 23:55:58.161 farads 0.0001800000 2018-02-17 00:00:51.685 0.0004900000
2018-02-16 23:56:46.602 farads 0.0002600000 2018-02-17 00:01:30.835 0.0003000000
2018-02-16 23:57:26.829 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:57:26.830 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:58:06.991 farads 0.0004400000 2018-02-17 00:02:56.048 0.0004700000
2018-02-16 23:58:47.104 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:58:47.105 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:59:27.080 farads 0.0002300000 2018-02-17 00:04:19.990 0.0001600000
2018-02-17 00:00:07.008 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:07.009 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:51.685 farads 0.0004900000 2018-02-17 00:05:40.967 0.0001400000
2018-02-17 00:01:30.835 farads 0.0003000000 2018-02-17 00:06:24.584 0.0001000000
2018-02-17 00:02:14.187 farads 0.0004300000 2018-02-17 00:07:04.742 0.0002500000
2018-02-17 00:02:56.048 farads 0.0004700000 2018-02-17 00:07:48.107 0.0003600000
2018-02-17 00:03:39.758 farads 0.0004200000 2018-02-17 00:08:31.136 0.0000700000
2018-02-17 00:04:19.990 farads 0.0001600000 2018-02-17 00:09:12.429 0.0001500000
2018-02-17 00:04:59.854 farads 0.0001700000 2018-02-17 00:09:59.567 0.0002500000
2018-02-17 00:05:40.967 farads 0.0001400000 2018-02-17 00:10:41.062 0.0001900000
2018-02-17 00:06:24.584 farads 0.0001000000 2018-02-17 00:11:21.016 0.0001600000
2018-02-17 00:07:04.742 farads 0.0002500000 2018-02-17 00:12:00.863 0.0001600000
2018-02-17 00:07:48.107 farads 0.0003600000 2018-02-17 00:12:41.023 0.0002400000
2018-02-17 00:08:31.136 farads 0.0000700000 2018-02-17 00:13:22.429 0.0001500000
2018-02-17 00:09:12.429 farads 0.0001500000 2018-02-17 00:14:04.826 0.0004100000
2018-02-17 00:09:59.567 farads 0.0002500000 2018-02-17 00:14:51.079 0.0001600000
2018-02-17 00:10:41.062 farads 0.0001900000 2018-02-17 00:15:31.247 0.0003500000
2018-02-17 00:11:21.016 farads 0.0001600000 2018-02-17 00:16:17.396 0.0001900000
2018-02-17 00:12:00.863 farads 0.0001600000 2018-02-17 00:16:56.912 0.0002100000
2018-02-17 00:12:41.023 farads 0.0002400000 2018-02-17 00:17:37.895 0.0001800000
2018-02-17 00:13:22.429 farads 0.0001500000 2018-02-17 00:18:18.354 0.0003700000
2018-02-17 00:14:04.826 farads 0.0004100000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:14:51.079 farads 0.0001600000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:15:31.247 farads 0.0003500000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:16:17.396 farads 0.0001900000 NA NA
2018-02-17 00:16:56.912 farads 0.0002100000 NA NA
2018-02-17 00:17:37.895 farads 0.0001800000 NA NA
2018-02-17 00:18:18.354 farads 0.0003700000 NA NA
2018-02-17 00:18:58.071 farads 0.0004700000 NA NA
2018-02-17 18:19:38.135 farads 0.0002000000 2018-02-17 18:24:27.966 0.0001800000
2018-02-17 18:20:22.373 farads 0.0002600000 2018-02-17 18:25:11.832 0.0002800000
2018-02-17 18:21:02.161 farads 0.0003000000 2018-02-17 18:25:52.344 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000 2018-02-17 18:26:33.672 0.0002600000
2018-02-17 18:22:25.394 farads 0.0002500000 2018-02-17 18:27:15.499 0.0004300000
2018-02-17 18:23:06.549 farads 0.0003100000 2018-02-17 18:27:55.288 0.0004800000
2018-02-17 18:23:46.638 farads 0.0002100000 2018-02-17 18:28:56.699 0.0004200000
2018-02-17 18:24:27.966 farads 0.0001800000 2018-02-17 18:29:40.909 0.0002100000
2018-02-17 18:25:11.832 farads 0.0002800000 2018-02-17 18:30:20.942 0.0003400000
2018-02-17 18:25:52.344 farads 0.0003000000 2018-02-17 18:31:03.937 0.0003500000
2018-02-17 18:26:33.672 farads 0.0002600000 2018-02-17 18:31:51.329 0.0002500000
2018-02-17 18:27:15.499 farads 0.0004300000 2018-02-17 18:32:32.608 0.0005000000
2018-02-17 18:27:55.288 farads 0.0004800000 2018-02-17 18:33:12.869 0.0004900000
2018-02-17 18:28:56.699 farads 0.0004200000 2018-02-17 18:33:52.725 0.0002300000
2018-02-17 18:29:40.909 farads 0.0002100000 2018-02-17 18:34:39.022 0.0001300000
2018-02-17 18:30:20.942 farads 0.0003400000 2018-02-17 18:35:20.579 0.0002800000
2018-02-17 18:31:03.937 farads 0.0003500000 2018-02-17 18:36:00.487 0.0002400000
2018-02-17 18:31:51.329 farads 0.0002500000 2018-02-17 18:36:51.908 0.0004500000
2018-02-17 18:32:32.608 farads 0.0005000000 2018-02-17 18:37:33.667 0.0002500000
2018-02-17 18:33:12.869 farads 0.0004900000 2018-02-17 18:38:13.989 0.0004700000
2018-02-17 18:33:52.725 farads 0.0002300000 2018-02-17 18:38:53.753 0.0003500000
2018-02-17 18:34:39.022 farads 0.0001300000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:35:20.579 farads 0.0002800000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:00.487 farads 0.0002400000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:51.908 farads 0.0004500000 NA NA
2018-02-17 18:37:33.667 farads 0.0002500000 NA NA
2018-02-17 18:38:13.989 farads 0.0004700000 NA NA
2018-02-17 18:38:53.753 farads 0.0003500000 NA NA
2018-02-17 18:39:34.052 farads 0.0004100000 NA NA
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
Here's an option using awk and GNU date; it uses date to do the "heavy lifting" of computing the timestamps in fractional seconds from the date and time fields; awk then stores each record in an array indexed by that timestamp. One side effect of this is that duplicate records are "erased" -- only result is returned for the 16:45:29.557 entry. After computing all of the timestamps-in-seconds, we loop through the records looking for the closest match to "+5 minutes" from each entry. If that closest entry's timestamp difference is less than 2 minutes (from the 5 minute target), then we print the corresponding date and time and farad reading (by simply replacing the string "farads" with the empty string); otherwise we print "NA" fields, as per your comment. That's a deviation from your spec, on the assumption that a matching entry for a given record could be "tomorrow".
getline seconds
t[3]=seconds"."t[2]
records[t[3]]=$0
END
for (record in records)
min=(0 + "INF")
for (others in records)
# skip myself
if (others == record) continue
difference=(others - record) - (5 * 60)
if (difference < 0) difference=-difference
if (difference < min)
min=difference
matchfor[record]=records[others]
if (min > (60*2))
print records[record], "NA NA"
else
sub("farads", "", matchfor[record])
print records[record], matchfor[record]
Output from the sample input (unsorted):
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
... and piped to |sort:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 NA NA
2018-02-16 16:49:59.075 farads 0.0000700000 NA NA
2018-02-16 16:50:40.486 farads 0.0002400000 NA NA
2018-02-16 16:51:22.525 farads 0.0005900000 NA NA
2018-02-16 16:52:01.997 farads 0.0003900000 NA NA
The script's output, run on the expanded input, is:
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:43.612 0.0005200000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:53:23.550 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000 2018-02-16 16:54:03.276 0.0005300000
2018-02-16 16:49:59.075 farads 0.0000700000 2018-02-16 16:54:44.223 0.0003800000
2018-02-16 16:50:40.486 farads 0.0002400000 2018-02-16 16:55:24.769 0.0003200000
2018-02-16 16:51:22.525 farads 0.0005900000 2018-02-16 16:56:10.028 0.0002700000
2018-02-16 16:52:01.997 farads 0.0003900000 2018-02-16 16:56:57.624 0.0000900000
2018-02-16 16:52:43.612 farads 0.0005200000 2018-02-16 16:57:37.387 0.0003000000
2018-02-16 16:53:23.550 farads 0.0003900000 2018-02-16 16:58:16.929 0.0005800000
2018-02-16 16:54:03.276 farads 0.0005300000 2018-02-16 16:58:56.961 0.0003000000
2018-02-16 16:54:44.223 farads 0.0003800000 2018-02-16 16:59:39.217 0.0001900000
2018-02-16 16:55:24.769 farads 0.0003200000 2018-02-16 17:00:19.129 0.0005800000
2018-02-16 16:56:10.028 farads 0.0002700000 2018-02-16 17:00:59.328 0.0001500000
2018-02-16 16:56:57.624 farads 0.0000900000 2018-02-16 17:01:39.138 0.0005400000
2018-02-16 16:57:37.387 farads 0.0003000000 2018-02-16 17:02:19.786 0.0006600000
2018-02-16 16:58:16.929 farads 0.0005800000 2018-02-16 17:03:00.236 0.0004700000
2018-02-16 16:58:56.961 farads 0.0003000000 2018-02-16 17:03:44.343 0.0003300000
2018-02-16 16:59:39.217 farads 0.0001900000 2018-02-16 17:04:24.996 0.0002200000
2018-02-16 17:00:19.129 farads 0.0005800000 2018-02-16 17:05:05.754 0.0003200000
2018-02-16 17:00:59.328 farads 0.0001500000 2018-02-16 17:05:48.512 0.0004600000
2018-02-16 17:01:39.138 farads 0.0005400000 2018-02-16 17:06:29.248 0.0003700000
2018-02-16 17:02:19.786 farads 0.0006600000 2018-02-16 17:07:09.819 0.0001300000
2018-02-16 17:03:00.236 farads 0.0004700000 2018-02-16 17:07:50.392 0.0005500000
2018-02-16 17:03:44.343 farads 0.0003300000 2018-02-16 17:08:32.397 0.0002000000
2018-02-16 17:04:24.996 farads 0.0002200000 2018-02-16 17:09:14.778 0.0003000000
2018-02-16 17:05:05.754 farads 0.0003200000 2018-02-16 17:09:57.688 0.0003100000
2018-02-16 17:05:48.512 farads 0.0004600000 2018-02-16 17:10:37.237 0.0003900000
2018-02-16 17:06:29.248 farads 0.0003700000 2018-02-16 17:11:21.559 0.0003500000
2018-02-16 17:07:09.819 farads 0.0001300000 2018-02-16 17:12:00.946 0.0003500000
2018-02-16 17:07:50.392 farads 0.0005500000 2018-02-16 17:12:44.127 0.0003200000
2018-02-16 17:08:32.397 farads 0.0002000000 2018-02-16 17:13:26.579 0.0003800000
2018-02-16 17:09:14.778 farads 0.0003000000 2018-02-16 17:14:09.175 0.0001100000
2018-02-16 17:09:57.688 farads 0.0003100000 2018-02-16 17:14:49.553 0.0001300000
2018-02-16 17:10:37.237 farads 0.0003900000 2018-02-16 17:15:31.044 0.0002000000
2018-02-16 17:11:21.559 farads 0.0003500000 2018-02-16 17:16:12.039 0.0000400000
2018-02-16 17:12:00.945 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:00.946 farads 0.0003500000 2018-02-16 17:16:52.956 0.0002300000
2018-02-16 17:12:44.127 farads 0.0003200000 2018-02-16 17:17:33.238 0.0001900000
2018-02-16 17:13:26.579 farads 0.0003800000 2018-02-16 17:18:12.987 0.0001900000
2018-02-16 17:14:09.175 farads 0.0001100000 2018-02-16 17:18:54.214 0.0002300000
2018-02-16 17:14:49.552 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:14:49.553 farads 0.0001300000 2018-02-16 17:19:34.432 0.0001500000
2018-02-16 17:15:31.044 farads 0.0002000000 2018-02-16 17:20:22.002 0.0000700000
2018-02-16 17:16:12.038 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:12.039 farads 0.0000400000 2018-02-16 17:21:03.320 0.0001100000
2018-02-16 17:16:52.956 farads 0.0002300000 2018-02-16 17:21:43.477 0.0002000000
2018-02-16 17:17:33.238 farads 0.0001900000 2018-02-16 17:22:27.400 0.0003500000
2018-02-16 17:18:12.986 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:12.987 farads 0.0001900000 2018-02-16 17:23:11.224 0.0001700000
2018-02-16 17:18:54.214 farads 0.0002300000 2018-02-16 17:23:52.907 0.0001100000
2018-02-16 17:19:34.432 farads 0.0001500000 2018-02-16 17:24:40.392 0.0001500000
2018-02-16 17:20:22.002 farads 0.0000700000 2018-02-16 17:25:23.026 0.0001400000
2018-02-16 17:21:03.320 farads 0.0001100000 2018-02-16 17:26:03.886 0.0003100000
2018-02-16 17:21:43.477 farads 0.0002000000 2018-02-16 17:26:45.191 0.0001900000
2018-02-16 17:22:27.400 farads 0.0003500000 2018-02-16 17:27:28.652 0.0001000000
2018-02-16 17:23:11.224 farads 0.0001700000 2018-02-16 17:28:09.625 0.0002000000
2018-02-16 17:23:52.907 farads 0.0001100000 2018-02-16 17:28:49.753 0.0001500000
2018-02-16 17:24:40.392 farads 0.0001500000 2018-02-16 17:29:29.224 0.0005600000
2018-02-16 17:25:23.026 farads 0.0001400000 2018-02-16 17:30:10.520 0.0002100000
2018-02-16 17:26:03.886 farads 0.0003100000 2018-02-16 17:30:50.702 0.0001700000
2018-02-16 17:26:45.191 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:26:45.192 farads 0.0001900000 2018-02-16 17:31:30.077 0.0004800000
2018-02-16 17:27:28.652 farads 0.0001000000 2018-02-16 17:32:11.586 0.0003800000
2018-02-16 17:28:09.625 farads 0.0002000000 2018-02-16 17:32:51.434 0.0003600000
2018-02-16 17:28:49.753 farads 0.0001500000 2018-02-16 17:33:31.457 0.0005300000
2018-02-16 17:29:29.224 farads 0.0005600000 2018-02-16 17:34:10.910 0.0007600000
2018-02-16 17:30:10.520 farads 0.0002100000 2018-02-16 17:34:51.175 0.0004400000
2018-02-16 17:30:50.702 farads 0.0001700000 2018-02-16 17:35:31.234 0.0004800000
2018-02-16 17:31:30.077 farads 0.0004800000 2018-02-16 17:36:22.164 0.0002600000
2018-02-16 17:32:11.586 farads 0.0003800000 2018-02-16 17:37:02.616 0.0004100000
2018-02-16 17:32:51.434 farads 0.0003600000 2018-02-16 17:37:42.127 0.0003500000
2018-02-16 17:33:31.457 farads 0.0005300000 2018-02-16 17:38:23.346 0.0004100000
2018-02-16 17:34:10.910 farads 0.0007600000 2018-02-16 17:39:04.611 0.0002400000
2018-02-16 17:34:51.174 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:34:51.175 farads 0.0004400000 2018-02-16 17:39:46.119 0.0000700000
2018-02-16 17:35:31.234 farads 0.0004800000 2018-02-16 17:40:33.633 0.0001500000
2018-02-16 17:36:22.164 farads 0.0002600000 2018-02-16 17:41:13.308 0.0001500000
2018-02-16 17:37:02.616 farads 0.0004100000 2018-02-16 17:41:54.643 0.0003100000
2018-02-16 17:37:42.127 farads 0.0003500000 2018-02-16 17:42:33.994 0.0002300000
2018-02-16 17:38:23.346 farads 0.0004100000 2018-02-16 17:43:14.389 0.0004000000
2018-02-16 17:39:04.611 farads 0.0002400000 2018-02-16 17:43:54.324 0.0002200000
2018-02-16 17:39:46.119 farads 0.0000700000 2018-02-16 17:44:36.122 0.0001800000
2018-02-16 17:40:33.633 farads 0.0001500000 2018-02-16 17:45:16.828 0.0002600000
2018-02-16 17:41:13.307 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:13.308 farads 0.0001500000 2018-02-16 17:45:59.158 0.0001500000
2018-02-16 17:41:54.643 farads 0.0003100000 2018-02-16 17:46:45.126 0.0000700000
2018-02-16 17:42:33.994 farads 0.0002300000 2018-02-16 17:47:25.236 0.0004400000
2018-02-16 17:43:14.389 farads 0.0004000000 2018-02-16 17:48:12.096 0.0002200000
2018-02-16 17:43:54.324 farads 0.0002200000 2018-02-16 17:49:01.891 0.0001600000
2018-02-16 17:44:36.122 farads 0.0001800000 2018-02-16 17:49:50.422 0.0002100000
2018-02-16 17:45:16.828 farads 0.0002600000 2018-02-16 17:50:31.222 0.0001300000
2018-02-16 17:45:59.158 farads 0.0001500000 2018-02-16 17:51:12.651 0.0002600000
2018-02-16 17:46:45.126 farads 0.0000700000 2018-02-16 17:51:53.478 0.0001300000
2018-02-16 17:47:25.236 farads 0.0004400000 2018-02-16 17:52:34.145 0.0004600000
2018-02-16 17:48:12.096 farads 0.0002200000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:01.891 farads 0.0001600000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:49:50.422 farads 0.0002100000 2018-02-16 17:53:14.374 0.0003300000
2018-02-16 17:50:31.222 farads 0.0001300000 NA NA
2018-02-16 17:51:12.651 farads 0.0002600000 NA NA
2018-02-16 17:51:12.652 farads 0.0002600000 NA NA
2018-02-16 17:51:53.478 farads 0.0001300000 NA NA
2018-02-16 17:52:34.145 farads 0.0004600000 NA NA
2018-02-16 17:53:14.374 farads 0.0003300000 NA NA
2018-02-16 23:53:53.906 farads 0.0002600000 2018-02-16 23:58:47.105 0.0003600000
2018-02-16 23:54:34.453 farads 0.0001200000 2018-02-16 23:59:27.080 0.0002300000
2018-02-16 23:55:15.512 farads 0.0001600000 2018-02-17 00:00:07.009 0.0002900000
2018-02-16 23:55:58.161 farads 0.0001800000 2018-02-17 00:00:51.685 0.0004900000
2018-02-16 23:56:46.602 farads 0.0002600000 2018-02-17 00:01:30.835 0.0003000000
2018-02-16 23:57:26.829 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:57:26.830 farads 0.0003100000 2018-02-17 00:02:14.187 0.0004300000
2018-02-16 23:58:06.991 farads 0.0004400000 2018-02-17 00:02:56.048 0.0004700000
2018-02-16 23:58:47.104 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:58:47.105 farads 0.0003600000 2018-02-17 00:03:39.758 0.0004200000
2018-02-16 23:59:27.080 farads 0.0002300000 2018-02-17 00:04:19.990 0.0001600000
2018-02-17 00:00:07.008 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:07.009 farads 0.0002900000 2018-02-17 00:04:59.854 0.0001700000
2018-02-17 00:00:51.685 farads 0.0004900000 2018-02-17 00:05:40.967 0.0001400000
2018-02-17 00:01:30.835 farads 0.0003000000 2018-02-17 00:06:24.584 0.0001000000
2018-02-17 00:02:14.187 farads 0.0004300000 2018-02-17 00:07:04.742 0.0002500000
2018-02-17 00:02:56.048 farads 0.0004700000 2018-02-17 00:07:48.107 0.0003600000
2018-02-17 00:03:39.758 farads 0.0004200000 2018-02-17 00:08:31.136 0.0000700000
2018-02-17 00:04:19.990 farads 0.0001600000 2018-02-17 00:09:12.429 0.0001500000
2018-02-17 00:04:59.854 farads 0.0001700000 2018-02-17 00:09:59.567 0.0002500000
2018-02-17 00:05:40.967 farads 0.0001400000 2018-02-17 00:10:41.062 0.0001900000
2018-02-17 00:06:24.584 farads 0.0001000000 2018-02-17 00:11:21.016 0.0001600000
2018-02-17 00:07:04.742 farads 0.0002500000 2018-02-17 00:12:00.863 0.0001600000
2018-02-17 00:07:48.107 farads 0.0003600000 2018-02-17 00:12:41.023 0.0002400000
2018-02-17 00:08:31.136 farads 0.0000700000 2018-02-17 00:13:22.429 0.0001500000
2018-02-17 00:09:12.429 farads 0.0001500000 2018-02-17 00:14:04.826 0.0004100000
2018-02-17 00:09:59.567 farads 0.0002500000 2018-02-17 00:14:51.079 0.0001600000
2018-02-17 00:10:41.062 farads 0.0001900000 2018-02-17 00:15:31.247 0.0003500000
2018-02-17 00:11:21.016 farads 0.0001600000 2018-02-17 00:16:17.396 0.0001900000
2018-02-17 00:12:00.863 farads 0.0001600000 2018-02-17 00:16:56.912 0.0002100000
2018-02-17 00:12:41.023 farads 0.0002400000 2018-02-17 00:17:37.895 0.0001800000
2018-02-17 00:13:22.429 farads 0.0001500000 2018-02-17 00:18:18.354 0.0003700000
2018-02-17 00:14:04.826 farads 0.0004100000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:14:51.079 farads 0.0001600000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:15:31.247 farads 0.0003500000 2018-02-17 00:18:58.071 0.0004700000
2018-02-17 00:16:17.396 farads 0.0001900000 NA NA
2018-02-17 00:16:56.912 farads 0.0002100000 NA NA
2018-02-17 00:17:37.895 farads 0.0001800000 NA NA
2018-02-17 00:18:18.354 farads 0.0003700000 NA NA
2018-02-17 00:18:58.071 farads 0.0004700000 NA NA
2018-02-17 18:19:38.135 farads 0.0002000000 2018-02-17 18:24:27.966 0.0001800000
2018-02-17 18:20:22.373 farads 0.0002600000 2018-02-17 18:25:11.832 0.0002800000
2018-02-17 18:21:02.161 farads 0.0003000000 2018-02-17 18:25:52.344 0.0003000000
2018-02-17 18:21:43.806 farads 0.0002700000 2018-02-17 18:26:33.672 0.0002600000
2018-02-17 18:22:25.394 farads 0.0002500000 2018-02-17 18:27:15.499 0.0004300000
2018-02-17 18:23:06.549 farads 0.0003100000 2018-02-17 18:27:55.288 0.0004800000
2018-02-17 18:23:46.638 farads 0.0002100000 2018-02-17 18:28:56.699 0.0004200000
2018-02-17 18:24:27.966 farads 0.0001800000 2018-02-17 18:29:40.909 0.0002100000
2018-02-17 18:25:11.832 farads 0.0002800000 2018-02-17 18:30:20.942 0.0003400000
2018-02-17 18:25:52.344 farads 0.0003000000 2018-02-17 18:31:03.937 0.0003500000
2018-02-17 18:26:33.672 farads 0.0002600000 2018-02-17 18:31:51.329 0.0002500000
2018-02-17 18:27:15.499 farads 0.0004300000 2018-02-17 18:32:32.608 0.0005000000
2018-02-17 18:27:55.288 farads 0.0004800000 2018-02-17 18:33:12.869 0.0004900000
2018-02-17 18:28:56.699 farads 0.0004200000 2018-02-17 18:33:52.725 0.0002300000
2018-02-17 18:29:40.909 farads 0.0002100000 2018-02-17 18:34:39.022 0.0001300000
2018-02-17 18:30:20.942 farads 0.0003400000 2018-02-17 18:35:20.579 0.0002800000
2018-02-17 18:31:03.937 farads 0.0003500000 2018-02-17 18:36:00.487 0.0002400000
2018-02-17 18:31:51.329 farads 0.0002500000 2018-02-17 18:36:51.908 0.0004500000
2018-02-17 18:32:32.608 farads 0.0005000000 2018-02-17 18:37:33.667 0.0002500000
2018-02-17 18:33:12.869 farads 0.0004900000 2018-02-17 18:38:13.989 0.0004700000
2018-02-17 18:33:52.725 farads 0.0002300000 2018-02-17 18:38:53.753 0.0003500000
2018-02-17 18:34:39.022 farads 0.0001300000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:35:20.579 farads 0.0002800000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:00.487 farads 0.0002400000 2018-02-17 18:39:34.052 0.0004100000
2018-02-17 18:36:51.908 farads 0.0004500000 NA NA
2018-02-17 18:37:33.667 farads 0.0002500000 NA NA
2018-02-17 18:38:13.989 farads 0.0004700000 NA NA
2018-02-17 18:38:53.753 farads 0.0003500000 NA NA
2018-02-17 18:39:34.052 farads 0.0004100000 NA NA
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
edited Mar 7 at 1:30
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
answered Mar 6 at 2:40
Jeff Schaller
31.2k846105
31.2k846105
I am getting error when I run this code as follows: time-elapsed.awk: line 6: syntax error near unexpected token$2,' time-elapsed.awk: line 6:split($2, t, ".")'
– kalamata_Olive
Mar 6 at 20:53
How are you running it? Useawk -f time-elapsed.awk < input
– Jeff Schaller
Mar 6 at 20:59
either way I am getting the same error: bash time-elapsed.awk < data.txt | less OR bash time-elapsed.awk data.txt | less
– kalamata_Olive
Mar 6 at 21:06
well the awk -f script is running but only printing the four original columns i.e. 2018-02-16 16:42:53.926 farads 0.0000000000 2018-02-16 16:42:53.927 farads 0.0000000000 2018-02-16 16:43:33.167 farads 0.0000800000 2018-02-16 16:44:21.357 farads 0.0002600000 2018-02-16 16:45:29.557 farads 0.0004300000
– kalamata_Olive
Mar 6 at 21:22
It's not printing the last two three columns: 2018-02-16 16:52:01.997 0.0003900000
– kalamata_Olive
Mar 6 at 21:38
 |Â
show 16 more comments
I am getting error when I run this code as follows: time-elapsed.awk: line 6: syntax error near unexpected token$2,' time-elapsed.awk: line 6:split($2, t, ".")'
– kalamata_Olive
Mar 6 at 20:53
How are you running it? Useawk -f time-elapsed.awk < input
– Jeff Schaller
Mar 6 at 20:59
either way I am getting the same error: bash time-elapsed.awk < data.txt | less OR bash time-elapsed.awk data.txt | less
– kalamata_Olive
Mar 6 at 21:06
well the awk -f script is running but only printing the four original columns i.e. 2018-02-16 16:42:53.926 farads 0.0000000000 2018-02-16 16:42:53.927 farads 0.0000000000 2018-02-16 16:43:33.167 farads 0.0000800000 2018-02-16 16:44:21.357 farads 0.0002600000 2018-02-16 16:45:29.557 farads 0.0004300000
– kalamata_Olive
Mar 6 at 21:22
It's not printing the last two three columns: 2018-02-16 16:52:01.997 0.0003900000
– kalamata_Olive
Mar 6 at 21:38
I am getting error when I run this code as follows: time-elapsed.awk: line 6: syntax error near unexpected token
$2,' time-elapsed.awk: line 6: split($2, t, ".")'– kalamata_Olive
Mar 6 at 20:53
I am getting error when I run this code as follows: time-elapsed.awk: line 6: syntax error near unexpected token
$2,' time-elapsed.awk: line 6: split($2, t, ".")'– kalamata_Olive
Mar 6 at 20:53
How are you running it? Use
awk -f time-elapsed.awk < input– Jeff Schaller
Mar 6 at 20:59
How are you running it? Use
awk -f time-elapsed.awk < input– Jeff Schaller
Mar 6 at 20:59
either way I am getting the same error: bash time-elapsed.awk < data.txt | less OR bash time-elapsed.awk data.txt | less
– kalamata_Olive
Mar 6 at 21:06
either way I am getting the same error: bash time-elapsed.awk < data.txt | less OR bash time-elapsed.awk data.txt | less
– kalamata_Olive
Mar 6 at 21:06
well the awk -f script is running but only printing the four original columns i.e. 2018-02-16 16:42:53.926 farads 0.0000000000 2018-02-16 16:42:53.927 farads 0.0000000000 2018-02-16 16:43:33.167 farads 0.0000800000 2018-02-16 16:44:21.357 farads 0.0002600000 2018-02-16 16:45:29.557 farads 0.0004300000
– kalamata_Olive
Mar 6 at 21:22
well the awk -f script is running but only printing the four original columns i.e. 2018-02-16 16:42:53.926 farads 0.0000000000 2018-02-16 16:42:53.927 farads 0.0000000000 2018-02-16 16:43:33.167 farads 0.0000800000 2018-02-16 16:44:21.357 farads 0.0002600000 2018-02-16 16:45:29.557 farads 0.0004300000
– kalamata_Olive
Mar 6 at 21:22
It's not printing the last two three columns: 2018-02-16 16:52:01.997 0.0003900000
– kalamata_Olive
Mar 6 at 21:38
It's not printing the last two three columns: 2018-02-16 16:52:01.997 0.0003900000
– kalamata_Olive
Mar 6 at 21:38
 |Â
show 16 more comments
up vote
2
down vote
Note: When I originally posted this, the code had a bug that caused times that were five minutes before the hour to not be matched up with their corresponding later times. Since I did not have the time to fix the bug then, I deleted the post, but now that bug should be fixed, and I've undeleted the answer. However, since you only provided a single line of expected output, please take care to test this code against a broader set of sample inputs.
You said you're not that worried about CPU time, but my initial attempt which
simply brute-forced it by comparing every line to every other line took 30s
to run on a 1800 line file, so I optimized it with the %mins hash, keyed on
the time down to the minute, so that only minutes that are +4, +5, and +6
minutes from each timestamp are taken into consideration. This version
takes ~4s to run on a ~4000 line input file.
use warnings;
use strict;
use DateTime;
use DateTime::Format::Strptime;
my $strp = DateTime::Format::Strptime->new( on_error=>'croak',
pattern => '%Y-%m-%d %H:%M:%S.%3N' );
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:.]+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = $strp->parse_datetime("$1 $2");
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$3, dt=>$dt, seek=>$dt->clone->add(minutes=>5) ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_ms,$min_other);
for my $other (@candidates)
my $diff_ms = abs($cur->seek->subtract_datetime_absolute($other->dt)
->in_units('nanoseconds'))/1e6;
if (!defined $min_diff_ms
print $cur->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," farads ",
$cur->farads, defined($min_other) ? ( " ",
$min_other->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," ",
$min_other->farads ) : '', "n";
Your original sample input:
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.558 farads 0.0004300000
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Output for that input:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Here is an even faster but less accurate version, it ignores milliseconds
and uses the core module Time::Piece
instead of the somewhat heavy DateTime
(although I can still highly recommend the latter). Compared to the above, it takes a fraction of a second to run. You can see the difference in accuracy, but you did say that ± 1 minute is acceptable.
use warnings;
use strict;
use Time::Piece;
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:]+)(.d+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = Time::Piece->strptime("$1 $2", '%Y-%m-%d %H:%M:%S');
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$4, timestr=>"$1 $2$3", epoch=>$dt->epoch ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_s,$min_other);
for my $other (@candidates) $diff_s<$min_diff_s)
$min_diff_s = $diff_s;
$min_other = $other;
print $cur->timestr," farads ",$cur->farads,
defined($min_other)
? ( " ", $min_other->timestr," ",$min_other->farads )
: '', "n";
Output:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
(Note I normally wouldn't have provided a complete solution to a question
with no code, but this was an interesting question for me.)
answered Mar 5 at 23:03
haukex
2839
Thank you! sir, I was myself trying something on these lines but couldn;t make it work: #!/bin/bash while read d1_1 d1_2 d2_1 d2_2; do secdiff=$(( $(date -d "$d2_1 $d2_2" +%s) - $(date -d "$d1_1 $d1_2" +%s) )) nanosecdiff=$(( $(date -d "$d2_1 $d2_2" +%N) - $(date -d "$d1_1 $d1_2" +%N) )) printf "%s %s - %s %s = %d millisecondsn" $d2_1 $d2_2 $d1_1 $d1_2 $(( (secdiff * 1000) + (nanosecdiff / 1000000) )) done
– kalamata_Olive
Mar 6 at 0:30
add a comment |Â
up vote
2
down vote
Note: When I originally posted this, the code had a bug that caused times that were five minutes before the hour to not be matched up with their corresponding later times. Since I did not have the time to fix the bug then, I deleted the post, but now that bug should be fixed, and I've undeleted the answer. However, since you only provided a single line of expected output, please take care to test this code against a broader set of sample inputs.
You said you're not that worried about CPU time, but my initial attempt which
simply brute-forced it by comparing every line to every other line took 30s
to run on a 1800 line file, so I optimized it with the %mins hash, keyed on
the time down to the minute, so that only minutes that are +4, +5, and +6
minutes from each timestamp are taken into consideration. This version
takes ~4s to run on a ~4000 line input file.
use warnings;
use strict;
use DateTime;
use DateTime::Format::Strptime;
my $strp = DateTime::Format::Strptime->new( on_error=>'croak',
pattern => '%Y-%m-%d %H:%M:%S.%3N' );
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:.]+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = $strp->parse_datetime("$1 $2");
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$3, dt=>$dt, seek=>$dt->clone->add(minutes=>5) ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_ms,$min_other);
for my $other (@candidates)
my $diff_ms = abs($cur->seek->subtract_datetime_absolute($other->dt)
->in_units('nanoseconds'))/1e6;
if (!defined $min_diff_ms
print $cur->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," farads ",
$cur->farads, defined($min_other) ? ( " ",
$min_other->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," ",
$min_other->farads ) : '', "n";
Your original sample input:
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.558 farads 0.0004300000
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Output for that input:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Here is an even faster but less accurate version, it ignores milliseconds
and uses the core module Time::Piece
instead of the somewhat heavy DateTime
(although I can still highly recommend the latter). Compared to the above, it takes a fraction of a second to run. You can see the difference in accuracy, but you did say that ± 1 minute is acceptable.
use warnings;
use strict;
use Time::Piece;
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:]+)(.d+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = Time::Piece->strptime("$1 $2", '%Y-%m-%d %H:%M:%S');
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$4, timestr=>"$1 $2$3", epoch=>$dt->epoch ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_s,$min_other);
for my $other (@candidates) $diff_s<$min_diff_s)
$min_diff_s = $diff_s;
$min_other = $other;
print $cur->timestr," farads ",$cur->farads,
defined($min_other)
? ( " ", $min_other->timestr," ",$min_other->farads )
: '', "n";
Output:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
(Note I normally wouldn't have provided a complete solution to a question
with no code, but this was an interesting question for me.)
answered Mar 5 at 23:03
haukex
2839
Thank you! sir, I was myself trying something on these lines but couldn;t make it work: #!/bin/bash while read d1_1 d1_2 d2_1 d2_2; do secdiff=$(( $(date -d "$d2_1 $d2_2" +%s) - $(date -d "$d1_1 $d1_2" +%s) )) nanosecdiff=$(( $(date -d "$d2_1 $d2_2" +%N) - $(date -d "$d1_1 $d1_2" +%N) )) printf "%s %s - %s %s = %d millisecondsn" $d2_1 $d2_2 $d1_1 $d1_2 $(( (secdiff * 1000) + (nanosecdiff / 1000000) )) done
– kalamata_Olive
Mar 6 at 0:30
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Note: When I originally posted this, the code had a bug that caused times that were five minutes before the hour to not be matched up with their corresponding later times. Since I did not have the time to fix the bug then, I deleted the post, but now that bug should be fixed, and I've undeleted the answer. However, since you only provided a single line of expected output, please take care to test this code against a broader set of sample inputs.
You said you're not that worried about CPU time, but my initial attempt which
simply brute-forced it by comparing every line to every other line took 30s
to run on a 1800 line file, so I optimized it with the %mins hash, keyed on
the time down to the minute, so that only minutes that are +4, +5, and +6
minutes from each timestamp are taken into consideration. This version
takes ~4s to run on a ~4000 line input file.
use warnings;
use strict;
use DateTime;
use DateTime::Format::Strptime;
my $strp = DateTime::Format::Strptime->new( on_error=>'croak',
pattern => '%Y-%m-%d %H:%M:%S.%3N' );
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:.]+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = $strp->parse_datetime("$1 $2");
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$3, dt=>$dt, seek=>$dt->clone->add(minutes=>5) ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_ms,$min_other);
for my $other (@candidates)
my $diff_ms = abs($cur->seek->subtract_datetime_absolute($other->dt)
->in_units('nanoseconds'))/1e6;
if (!defined $min_diff_ms
print $cur->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," farads ",
$cur->farads, defined($min_other) ? ( " ",
$min_other->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," ",
$min_other->farads ) : '', "n";
Your original sample input:
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.558 farads 0.0004300000
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Output for that input:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Here is an even faster but less accurate version, it ignores milliseconds
and uses the core module Time::Piece
instead of the somewhat heavy DateTime
(although I can still highly recommend the latter). Compared to the above, it takes a fraction of a second to run. You can see the difference in accuracy, but you did say that ± 1 minute is acceptable.
use warnings;
use strict;
use Time::Piece;
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:]+)(.d+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = Time::Piece->strptime("$1 $2", '%Y-%m-%d %H:%M:%S');
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$4, timestr=>"$1 $2$3", epoch=>$dt->epoch ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_s,$min_other);
for my $other (@candidates) $diff_s<$min_diff_s)
$min_diff_s = $diff_s;
$min_other = $other;
print $cur->timestr," farads ",$cur->farads,
defined($min_other)
? ( " ", $min_other->timestr," ",$min_other->farads )
: '', "n";
Output:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
(Note I normally wouldn't have provided a complete solution to a question
with no code, but this was an interesting question for me.)
answered Mar 5 at 23:03
haukex
2839
Note: When I originally posted this, the code had a bug that caused times that were five minutes before the hour to not be matched up with their corresponding later times. Since I did not have the time to fix the bug then, I deleted the post, but now that bug should be fixed, and I've undeleted the answer. However, since you only provided a single line of expected output, please take care to test this code against a broader set of sample inputs.
You said you're not that worried about CPU time, but my initial attempt which
simply brute-forced it by comparing every line to every other line took 30s
to run on a 1800 line file, so I optimized it with the %mins hash, keyed on
the time down to the minute, so that only minutes that are +4, +5, and +6
minutes from each timestamp are taken into consideration. This version
takes ~4s to run on a ~4000 line input file.
use warnings;
use strict;
use DateTime;
use DateTime::Format::Strptime;
my $strp = DateTime::Format::Strptime->new( on_error=>'croak',
pattern => '%Y-%m-%d %H:%M:%S.%3N' );
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:.]+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = $strp->parse_datetime("$1 $2");
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$3, dt=>$dt, seek=>$dt->clone->add(minutes=>5) ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_ms,$min_other);
for my $other (@candidates)
my $diff_ms = abs($cur->seek->subtract_datetime_absolute($other->dt)
->in_units('nanoseconds'))/1e6;
if (!defined $min_diff_ms
print $cur->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," farads ",
$cur->farads, defined($min_other) ? ( " ",
$min_other->dt->strftime('%Y-%m-%d %H:%M:%S.%3N')," ",
$min_other->farads ) : '', "n";
Your original sample input:
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.557 farads 0.0004300000
2018-02-16 16:45:29.558 farads 0.0004300000
2018-02-16 16:46:09.300 farads 0.0004300000
2018-02-16 16:47:10.987 farads 0.0002800000
2018-02-16 16:47:51.611 farads 0.0006500000
2018-02-16 16:47:51.612 farads 0.0006500000
2018-02-16 16:48:34.077 farads 0.0006600000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Output for that input:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
Here is an even faster but less accurate version, it ignores milliseconds
and uses the core module Time::Piece
instead of the somewhat heavy DateTime
(although I can still highly recommend the latter). Compared to the above, it takes a fraction of a second to run. You can see the difference in accuracy, but you did say that ± 1 minute is acceptable.
use warnings;
use strict;
use Time::Piece;
my (@recs, %mins);
while (<>)
mAs* ([-d]+) s+ ([d:]+)(.d+) s+faradss+ ((?:d+.)?d+) s*zixms
or die "failed to parse line: $_";
my $dt = Time::Piece->strptime("$1 $2", '%Y-%m-%d %H:%M:%S');
my $min = int($dt->epoch/60);
my $rec = min=>$min, farads=>$4, timestr=>"$1 $2$3", epoch=>$dt->epoch ;
push @ $mins $min , $rec;
push @recs, $rec;
for my $cur (@recs)
my @candidates = (
@ $mins$cur->min+4 // ,
@ $mins$cur->min+5 // ,
@ $mins$cur->min+6 // );
my ($min_diff_s,$min_other);
for my $other (@candidates) $diff_s<$min_diff_s)
$min_diff_s = $diff_s;
$min_other = $other;
print $cur->timestr," farads ",$cur->farads,
defined($min_other)
? ( " ", $min_other->timestr," ",$min_other->farads )
: '', "n";
Output:
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.557 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:45:29.558 farads 0.0004300000 2018-02-16 16:49:17.015 0.0003300000
2018-02-16 16:46:09.300 farads 0.0004300000 2018-02-16 16:50:40.486 0.0002400000
2018-02-16 16:47:10.987 farads 0.0002800000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.611 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:47:51.612 farads 0.0006500000 2018-02-16 16:51:22.525 0.0005900000
2018-02-16 16:48:34.077 farads 0.0006600000 2018-02-16 16:52:01.997 0.0003900000
2018-02-16 16:49:17.015 farads 0.0003300000
2018-02-16 16:49:59.075 farads 0.0000700000
2018-02-16 16:50:40.486 farads 0.0002400000
2018-02-16 16:51:22.525 farads 0.0005900000
2018-02-16 16:52:01.997 farads 0.0003900000
(Note I normally wouldn't have provided a complete solution to a question
with no code, but this was an interesting question for me.)
answered Mar 5 at 23:03
haukex
2839
edited Mar 14 at 21:52
answered Mar 5 at 23:03
haukex
2839
answered Mar 5 at 23:03
haukex
2839
answered Mar 5 at 23:03
haukex
2839
2839
Thank you! sir, I was myself trying something on these lines but couldn;t make it work: #!/bin/bash while read d1_1 d1_2 d2_1 d2_2; do secdiff=$(( $(date -d "$d2_1 $d2_2" +%s) - $(date -d "$d1_1 $d1_2" +%s) )) nanosecdiff=$(( $(date -d "$d2_1 $d2_2" +%N) - $(date -d "$d1_1 $d1_2" +%N) )) printf "%s %s - %s %s = %d millisecondsn" $d2_1 $d2_2 $d1_1 $d1_2 $(( (secdiff * 1000) + (nanosecdiff / 1000000) )) done
– kalamata_Olive
Mar 6 at 0:30
add a comment |Â
Thank you! sir, I was myself trying something on these lines but couldn;t make it work: #!/bin/bash while read d1_1 d1_2 d2_1 d2_2; do secdiff=$(( $(date -d "$d2_1 $d2_2" +%s) - $(date -d "$d1_1 $d1_2" +%s) )) nanosecdiff=$(( $(date -d "$d2_1 $d2_2" +%N) - $(date -d "$d1_1 $d1_2" +%N) )) printf "%s %s - %s %s = %d millisecondsn" $d2_1 $d2_2 $d1_1 $d1_2 $(( (secdiff * 1000) + (nanosecdiff / 1000000) )) done
– kalamata_Olive
Mar 6 at 0:30
Thank you! sir, I was myself trying something on these lines but couldn;t make it work: #!/bin/bash while read d1_1 d1_2 d2_1 d2_2; do secdiff=$(( $(date -d "$d2_1 $d2_2" +%s) - $(date -d "$d1_1 $d1_2" +%s) )) nanosecdiff=$(( $(date -d "$d2_1 $d2_2" +%N) - $(date -d "$d1_1 $d1_2" +%N) )) printf "%s %s - %s %s = %d millisecondsn" $d2_1 $d2_2 $d1_1 $d1_2 $(( (secdiff * 1000) + (nanosecdiff / 1000000) )) done
– kalamata_Olive
Mar 6 at 0:30
Thank you! sir, I was myself trying something on these lines but couldn;t make it work: #!/bin/bash while read d1_1 d1_2 d2_1 d2_2; do secdiff=$(( $(date -d "$d2_1 $d2_2" +%s) - $(date -d "$d1_1 $d1_2" +%s) )) nanosecdiff=$(( $(date -d "$d2_1 $d2_2" +%N) - $(date -d "$d1_1 $d1_2" +%N) )) printf "%s %s - %s %s = %d millisecondsn" $d2_1 $d2_2 $d1_1 $d1_2 $(( (secdiff * 1000) + (nanosecdiff / 1000000) )) done
– kalamata_Olive
Mar 6 at 0:30
add a comment |Â
Â
draft saved
draft discarded
Â
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f428355%2fmatch-the-nearest-timestamp-in-milliseconds%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
2018-02-16 16:45:29.557and16:50:40.486have 5 minutes difference, not 5 seconds. Secondly, if you need for every timestamp - update your final output– RomanPerekhrest
Mar 5 at 20:01
I changed the question to 5 mins. Thanks for pointing it out, I actually meant 5 mins. Also depending upon the time period the two new columns will actually begin with NAs
– kalamata_Olive
Mar 5 at 20:03
Are the timestamps in the file always sorted in ascending order?
– haukex
Mar 5 at 20:10
yes the time timestamps are always in ascending order or descending order.
– kalamata_Olive
Mar 5 at 20:11
How big will the data files normally be, and what is the maximum number of lines?
– haukex
Mar 5 at 20:30