2

I have a large file that lists a long column unix times one value for each row, incrementing in intervals of 0.01s. For one day of data, this amounts to 8.64 million rows.

135699840000
135699840001
135699840002
135699840003
135699840004

I would like to run a command on every row of this file, that calculates the serial date number for each line - a day counter from a reference year 01/01/0000 that matlab uses for time.

735235.0000000000
735235.0000001157
735235.0000002314
735235.0000003472
735235.0000004629

I am new to coding, but have managed to get this working using a while loop. However, this is hideously inefficient and is taking hours to run.

while read epochtimerange; do
echo "scale=10; (($epochtimerange/(100*86400))+719529)" |bc
done < epochtimerangetmp.txt > serialdaterangetmp.txt

I think there must be a way to run this using awk, but I can't get this to work. It is important that I am able to keep my 10 decimal place precision on my output.

Would someone be able to help me? Thanks.

  • Since the data increments, why bother reading it from a file? You know the starting value and the increment. You just need the last value to know when to stop. – Kaz Nov 15 '16 at 2:54
  • I considered this but the increment of the desired output is not constant at this precision because of the rounding, whereas the increment of the input is. @Kaz – L. Marsden Nov 15 '16 at 10:56
  • That's true: your output is in tens of "nano-days" which don't have a multiple that corresponds exactly to hundredths of seconds. However, the ratio between them is a rational number, so a running error term can precisely take care of it (similarly to, for instance, how the Bresenham line-segment rasterization algorithm determines the irregular "staircase" of a slanted line on a raster display). – Kaz Nov 16 '16 at 21:17
1

The simple approach: Use ex to modify the lines and to pass the whole buffer (modified file) through bc. Then print the modified version.

printf '%s\n' '%s:.*:&/8640000+719529:' 0a scale=10 . '%!bc' %p 'q!' | ex file.txt

Output on your sample file:

735235.0000000000
735235.0000001157
735235.0000002314
735235.0000003472
735235.0000004629

Or to save changes rather than just printing them:

printf '%s\n' '%s:.*:&/8640000+719529:' 0a scale=10 . '%!bc' x | ex file.txt

Explanation:

To see what commands are passed to ex, run the printf command by itself:

$ printf '%s\n' '%s:.*:&/8640000+719529:' 0a scale=10 . '%!bc' %p 'q!'
%s:.*:&/8640000+719529:
0a
scale=10
.
%!bc
%p
q!

Let's break these down as ex commands now. The first one is rather complex, so I'll format the explanation specially:

%s:.*:&/8640000+719529:
%  - For every line of the buffer (file)
 s  - Run a substitute command
  :  - Using ':' as the regex delimiter
   .*  - Match each entire line
     :  - and replace with
      &  - The entire line, followed by
       /8640000+719529  - this text
                      :  - End command

0a means "append text after line 0," in other words at the start of the buffer (file).

The text scale=10 is the literal text to append.

The . on a line by itself ends the "append" command.

The command %!bc passes the contents of the entire buffer as standard input to the external command bc, and replaces the entire buffer with the output that is produced.

The %p means to print the entire buffer (to standard output).

q! means to exit without saving changes.


If you have a very, very large file, in the tens of millions of lines, this apparently gives trouble. I have researched possible solutions for this using ex and there are some ways it could be done, but I finally discarded that approach in favor of a much, much simpler one, which still uses only POSIX specified tools.

Use split to split your file into chunks, then run the earlier specified command on each chunk and cat the resulting output all together:

split -l 1000000 -a 3 file.txt myprefix.
for f in myprefix.???; do
  printf '%s\n' '%s:.*:&/8640000+719529:' 0a scale=10 . '%!bc' %p 'q!' |
    ex "$f"
done > myoutputfile.txt
rm myprefix.???

The split command is used here to split file.txt into chunks each an even million lines long (with the remainder put into a file also, of course). Since -a 3 is specified, the suffix on the chunks will be 3 characters long. myprefix.aaa, myprefix.aab, etc.

Each file can then be processed by ex individually, and with no need to save changes, since we will just redirect the output of that entire loop to myoutputfile.txt (and then remove the chunk files, for neatness).

  • This seems to work fine for 8.64 million rows. However, when I scale up to 25.92 million rows the output file is blank. Any idea why? – L. Marsden Nov 15 '16 at 9:20
  • @L.Marsden, interesting! Can you find a more exact threshold in between? Try a binary search, i.e. try with 17.28 million rows, then if that outputs blank, try with 12.96 million, if that works try 15.12 million, etc. My initial guess would be memory limitations on how many rows Vim can hold in memory, but I don't know for sure. (I can write a command that will do the file in chunks rather than all at once.) – Wildcard Nov 15 '16 at 9:25
  • I have narrowed it down somewhat. It seems to work for 23.76million rows, but not 24.84 million. Your help in writing a command that will do this in chunks would be much appreciated. @Wildcard – L. Marsden Nov 15 '16 at 10:49
  • @L.Marsden, done. (And don't forget to accept the answer if it works for you.) :) – Wildcard Nov 15 '16 at 11:45
  • Downvoter please comment. Why?? – Wildcard Nov 15 '16 at 12:09
6

It is a known fact that shells have a very slow processing speed.
What you ask for could be implemented in the shell like this:

#!/bin/bash
while read line; do
    bc <<<"scale=10;($line/(100*86400))+719529"
done <datafile

It takes about 1.1 seconds to process 1000 lines.
The whole lot of 8.640 million should take about 2 hours and 41 minutes.

Also, the numeric results from bc are incorrectly rounded.
The five lines from your example will yield this values:

735235.0000000000
735235.0000001157
735235.0000002314
735235.0000003472
735235.0000004629

Lets change the precision to 20 to see more digits:

735235.00000000000000000000
735235.00000011574074074074
735235.00000023148148148148
735235.00000034722222222222
735235.00000046296296296296

For example, the third one that ends on 2314 is incorrectly rounded, the next digit after the 4 shown is an 8, it should have been rounded to 5.

AWK

We may have a faster solution with awk. Implementing what you ask for in awk will look like this:

$ awk '{printf ("%.10f\n",($0/(100*86400))+719529)}' datafile

735235.0000000000
735235.0000001157
735235.0000002314
735235.0000003473
735235.0000004630

It takes only 0.006 (6 milliseconds) to process 1000 lines. The whole 8.64 million lines should be processed in about 50 seconds.
But awk is already above its range of precision. By default it use a 64 bit floating point representation of values. That representation has about 15 decimal digits of precision. Your data results have an integer part of 6 digits, the decimal part could be estimated as correct only to the 8th digit.
In fact, if we try to extend the number of digits:

awk '{printf ("%.20f\n",($0/(100*86400))+719529)}' datafile

we get just noise:

735235.00000000000000000000
735235.00000011571682989597
735235.00000023143365979195
735235.00000034726690500975
735235.00000046298373490572

Compare to the more precise bc results:

735235.00000000000000000000
735235.00000000000000000000

735235.00000011571682989597
735235.00000011574074074074

735235.00000023143365979195
735235.00000023148148148148

735235.00000034726690500975
735235.00000034722222222222

735235.00000046298373490572
735235.00000046296296296296

To really solve this problem, we need a more precise awk.

Multiprecision AWK

If you are using GNU awk (I will call it gawk for here on) and it has been compiled with MPFR (multiple-precision floating-point library) you can get more precision.

Check that your awk has the library (just ask for its version):

$ awk --version
GNU Awk 4.1.3, API: 1.1 (GNU MPFR 3.1.5, GNU MP 6.1.1)
Copyright (C) 1989, 1991-2015 Free Software Foundation.

And modify the awk command to use that available precision:

gawk -M -v PREC=100 '{printf ("%.20f\n",($0/(100*86400))+719529)}' datafile

735235.00000000000000000000
735235.00000011574074074074
735235.00000023148148148148
735235.00000034722222222222
735235.00000046296296296296

The results are the same as those of high precision bc.
In this case, we get the speed of awk and the precision of bc.

The final command for the 10 decimal digits you ask for, is:

gawk -M -v PREC=100 '{printf ("%.10f\n",($0/(100*86400))+719529)}' datafile

735235.0000000000
735235.0000001157
735235.0000002315
735235.0000003472
735235.0000004630

All values are correctly rounded.

2

Doing this in the shell will be really really slow.

$ awk '{printf "%.10f\n", (($1/(100*86400))+719529)}' filename
735235.0000000000
735235.0000001157
735235.0000002314
735235.0000003473
735235.0000004630

As you can see from the last entry, you'll get slightly different rounding results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.