1

I have a below input file which I need to split into multiple files based on the date in 3rd column. Basically all the same dated transactions should be splitted into particular dated file. Post splitting I need to create a header and Trailer. Trailer should contain the count of the records and sum of the amounts in 4th column(Sum of the amount for that date). In this case as I stated above I have very large numbers in the amount How can i integrate bc in the below code.

Input File

H|~^20200425|~^abcd|~^sum
R|~^abc|~^2019-03-06T12:33:52.27|~^123562388.23456|~^2018-04-12T12:33:52.27|~^hhh
R|~^abc|~^2019-03-05T12:33:52.27|~^105603.042|~^2018-10-23T12:33:52.27|~^aus
R|~^abc|~^2019-03-05T12:33:52.27|~^2054.026|~^2018-10-24T12:33:52.27|~^usa
R|~^abc|~^2019-03-06T12:33:52.27|~^10.00|~^2018-09-11T12:33:52.27|~^virginia
R|~^abc|~^2019-03-05T12:33:52.27|~^30.00|~^2018-08-05T12:33:52.27|~^ddd
R|~^abc|~^2019-03-06T12:33:52.27|~^15.03|~^2018-10-23T12:33:52.27|~^jjj
R|~^abc|~^2019-03-06T12:33:52.27|~^10.04|~^2018-04-08T12:33:52.27|~^jj
R|~^abc|~^2019-03-05T12:33:52.27|~^20.00|~^2018-07-23T12:33:52.27|~^audg
T|~^20200425|~^8|~^xxx|~^123670130.37256

Output file 20190305.txt

H|~^20200425|~^abcd|~^sum
R|~^abc|~^2019-03-05T12:33:52.27|~^105603.042|~^2018-10-23T12:33:52.27|~^aus
R|~^abc|~^2019-03-05T12:33:52.27|~^2054.026|~^2018-10-24T12:33:52.27|~^usa
R|~^abc|~^2019-03-05T12:33:52.27|~^30.00|~^2018-08-05T12:33:52.27|~^ddd
R|~^abc|~^2019-03-05T12:33:52.27|~^20.00|~^2018-07-23T12:33:52.27|~^audg
T|~^20200425|~^4|~^xxx|~^107707.068

Output file 20190306.txt

H|~^20200425|~^abcd|~^sum
R|~^abc|~^2019-03-06T12:33:52.27|~^123562388.23456|~^2018-04-12T12:33:52.27|~^hhh
R|~^abc|~^2019-03-06T12:33:52.27|~^10.00|~^2018-09-11T12:33:52.27|~^virginia
R|~^abc|~^2019-03-06T12:33:52.27|~^15.03|~^2018-10-23T12:33:52.27|~^jjj
R|~^abc|~^2019-03-06T12:33:52.27|~^10.04|~^2018-04-08T12:33:52.27|~^jj
T|~^20200425|~^4|~^xxx|~^123562423.30456

Code I'm using(PS:Suggested by one of our community member) Here's an awk solution:

awk -F'\\|~\\^' '{ 
            if($1=="H"){ 
                head=$0
            }
            else if($1=="T"){
                foot=$1"|~^"$2
                foot4=$4
            }
            else{
                date=$3;
                sub("T.*","", date);
                data[date][NR]=$0;
                sum[date]+=$4; 
                num[date]++
            }
           }
           END{
            for(date in data){
                file=date".txt";
                gsub("-","",file); 
                print head > file; 
                for(line in data[date]){
                    print data[date][line] > file
                } 
                printf "%s|~^%s|~^%s|~^%s\n", foot, num[date], 
                                              foot4, sum[date] > file
            }
           }' file 

Code is working Brilliantly. But in the step

sum[date]+=$4;

It is unable to sum large numbers. since I'm using %s at the last step, Trailer sum is getting printed with exponential value.

printf "%s|~^%s|~^%s|~^%s\n", foot, num[date], 
                                                  foot4, sum[date] > file

Here, I just wanted to apply sum the on large numbers and print the exact sum. (I tried bc(bash calculator) here but got stuck since this sum is based out of the array and also it's getting added based on the particular date).Please help me with this

Also, I tried "%.15g" at the trailer step

printf "%s|~^%s|~^%s|~^%.15g\n", foot, num[date], 
                                                  foot4, sum[date] > file

In this, I'm able to get the exact sum if the result is having 15 digits(including the decimal). If the sum result is exceeding 15 digits this isn't working. Kindly help

  • Based on the complexity of the awk script and the mention of large numbers you might want to use a language like Python or Ruby which handle much bigger numbers out of the box. – l0b0 May 16 at 23:08
  • @I0b0, Oh No!, It's hard to change now to python or other languages! Can you please suggest me to integrate into the above solution? Heard bc will work fine in the shell even for large numbers – hunter May 16 at 23:18
1

Without your big number issue taken into account, I would write the awk program something like this:

BEGIN {
        FS = "\\|~\\^"
        OFS= "|~^"
}

$1 == "H" {
        header = $0
}

$1 == "R" {
        name = $3
        sub("T.*", "", name)

        sum[name] += $4
        cnt[name] += 1

        if (cnt[name] == 1)
                print header >name ".txt"

        print >name ".txt"
}

$1 == "T" {
        for (name in sum)
                print $1, $2, cnt[name], $4, sum[name] >name ".txt"
}

For convenience, I set the output field separator, OFS, to |~^. This allows me to not worry about inserting it between fields that I output. The field separator for input, FS, is set to a regular expression that matches that string.

I then have three main blocks of code:

  1. One for parsing the H line. It is assumed that there only is one of these and that it occurs at the start. This simply stores the header line in the variable header.

  2. One for parsing the R lines. Each record contains the date that should be used as the output file name in the 3rd field. This is parsed out in the same manner as you do it. The sum for that date is accumulated, and a counter is incremented too.

    If the counter is one, i.e. if this is the first time we see that particular date, we write the header to the output file associated with that date. Then we write the current record to the file.

  3. The last block parses the T line. It is assumed that there only is one of these and that it occurs at the end. This simply outputs the accumulated sums and counts for each separate date to the file associated with that date, together with some data from the original T line.

To support arbitrary large numbers (you say elsewhere that you have numbers that would require in excess of 100 bits to store, and that would therefore overflow an integer in awk), we employ the arbitrary precision calculator bc as a "coprocess" (a sort of a computational service). The line saying sum[name] += $4 is replaced by

if (sum[name] == "") sum[name] = 0
printf "%s + %s\n", sum[name], $4 |& "bc"
"bc" |& getline sum[name]

This requires GNU awk (available for most Unix systems, in one way or another).

What this does is to first initialize the sum for the current date to zero, if there is no sum for this date yet. We do this because we need to supply a 0 to bc for the initial sum.

We then print the expression that bc should compute using the GNU awk-specific |& pipe to write to a coprocess. The bc utility, which will be started and running in parallel with our awk script, does the computation, and the following getline reads the output from bc from another |& pipe, directly into sum[name].

As far as I understand, GNU awk will not spawn a separate bc process for each summation, but will maintain a single bc process running as a coprocess. This would thus be slower than doing the computation inside awk natively, but will be much faster than spawning a separate bc for each and every summation.

For the given data, the following two files would be created:

$ cat 2019-03-05.txt
H|~^20200425|~^abcd|~^sum
R|~^abc|~^2019-03-05T12:33:52.27|~^105603.042|~^2018-10-23T12:33:52.27|~^aus
R|~^abc|~^2019-03-05T12:33:52.27|~^2054.026|~^2018-10-24T12:33:52.27|~^usa
R|~^abc|~^2019-03-05T12:33:52.27|~^30.00|~^2018-08-05T12:33:52.27|~^ddd
R|~^abc|~^2019-03-05T12:33:52.27|~^20.00|~^2018-07-23T12:33:52.27|~^audg
T|~^20200425|~^4|~^xxx|~^107707.068
$ cat 2019-03-06.txt
H|~^20200425|~^abcd|~^sum
R|~^abc|~^2019-03-06T12:33:52.27|~^123562388.23456|~^2018-04-12T12:33:52.27|~^hhh
R|~^abc|~^2019-03-06T12:33:52.27|~^10.00|~^2018-09-11T12:33:52.27|~^virginia
R|~^abc|~^2019-03-06T12:33:52.27|~^15.03|~^2018-10-23T12:33:52.27|~^jjj
R|~^abc|~^2019-03-06T12:33:52.27|~^10.04|~^2018-04-08T12:33:52.27|~^jj
T|~^20200425|~^4|~^xxx|~^123562423.30456
| improve this answer | |
  • Thanks a lot for your answer. Its working fine! – hunter May 18 at 7:06
1

I already wrote an awk code to solve this question that runs faster than the code you are presenting here.

You have already asked about the issue of summing many numbers and getting an imprecise answer in the past. This question is very similar to this other question Why is there a difference between these two sum commands?.

The file from that question was 20 Mega Byte with more than 700 thoudsand Lines.
You have stated that your files are in the order of File size is around 500 to 600 mb. That would increase the number of lines to the range of 10 Million lines.

The problem is that the numbers to add:

  • may vary widely: ranging from 3 digits 12.8 to 28 digits 1245637.34526234567299999999.

  • Adding up 28 digit numbers 10 Million times should require 28 + 7 = 35 digits. And that is assuming that the digits are not all decimals or integers. If that could happen, we are talking of 70 digits (35 integers + 35 decimals).

  • The representation in floats will always be an approximation of the exact number, that is a fundamental issue of floats. If you must have an exact sum, you must add all of them as integers.

As a solution to your problem could be to use GNU awk with a longer number of digits. The default floats in awk use 53 bit mantissa, good only for 15 digits.

If you use an a GNU AWK that has been compiled with MPFR (Multiple Precision Floating-Point Reliably) and GMP (GNU Multiple Precision Arithmetic Library) the result of its --version text should include that information (execute awk --version). On that case, you can use more bits. To be able to keep 40 digit floats (35 digits calculated above + some security margin) you will need :

b = ceil(d log2(10)) + 1

b = ceil( 40 * 3.321928 ) + 1 = 133 + 1 = 134 binary digits (bits)

So, the awk invocation should be:

 awk -M -v PREC=134 

Warning: using more digits makes the program slower.

And still use the same awk program

awk -M -v PREC=134 '

     BEGIN  { FS="\\|~\\^"; OFS="|~^" }
     $1=="H"{ header=$0; hdr=$2 }
     $1=="R"{
              t=gensub(/-/, "","g",$3)
              file=gensub(/T.*/,"",1,t);
              sum[file]+=$4
              if(count[file]==0){ print header >file }
              count[file]++
              print $0 >>file
            }
     END    {
              for( i in sum ){
                  printf "T %s %10d xxx %45.25f",hdr,count[i],"xxx",sum[i] >> i;
                  close(i)
                  }
            }
' "inputfile"

For reference: You have been asking almost the same question over and over again:

| improve this answer | |
  • Thaks a ton for the detailed explanation. Actually, I have asked the sum of large numbers in the last question(in split file based on the date) too but didn't get expected answer so posted a new one. But I will take care not posting any repetive questioins Here after. Once agian thanks a lot for detailed explanation on float and large numbers and Thanks for the solution too – hunter May 18 at 7:05

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.