Cfrgtkky

I have a problem in sorting some value from input.txt file, and I still didn't have any idea how to solve it.

Input File (input.txt):

1 5 1 2183 2006 6001 6132

1 6 1 2183 2006 6001 6133

1 7 1 2183 2006 6001 6134

...

1 65 1 2183 2006 6001 6227

1 66 1 2183 2006 6001 6234

1 67 1 2183 2006 6001 6235

1 68 1 2183 2006 6004 6156

1 69 1 2183 2006 6004 6157

1 70 1 2183 2006 6004 6158

...

1 115 1 2183 2006 6004 6227

1 116 1 2183 2006 6004 6234

1 117 1 2183 2006 6004 6235

1 118 1 2183 2006 6007 6120

1 119 1 2183 2006 6007 6146

1 120 1 2183 2006 6007 6147

...

1 182 1 2183 2006 6007 6237

1 183 1 2183 2006 6007 6238

1 184 1 2183 2006 6007 6239

Desired Output File (output.txt):

1 2183 2006 5-67 6001 6132 6235

1 2183 2006 68-117 6004 6156 6235

1 2183 2006 118-184 6007 6120 6239

There are 7 columns/fields in input file. Column 1,3,4,5 should be constant value. While column 2 and column 6 changes are depending on column 7. Column 6 has increment value of 3, and column 7 changes are vary and only change on the last 3 digits; example 6132, 6133, 6134.

The trick is on output column 4, how do I sort and unseq it based on changes of input column 6 and 7?

Let assume if:

input column 6 (6001) and input column 7 (6132) reached the last
value before the next input column 6 (6004) and input column 7 (6235)

then, the final output column 4 should have value 5-67, output column
5 should have the same value 6001, output column 6 should have the
first input column 7 and the output column 7 (6132) should have the
last input column 7 (6235).

Example 1st batch:



Input:

1 5 1 2183 2006 6001 6132

1 6 1 2183 2006 6001 6133

1 7 1 2183 2006 6001 6134

...

1 65 1 2183 2006 6001 6227

1 66 1 2183 2006 6001 6234

1 67 1 2183 2006 6001 6235



Desired Output:

1 2183 2006 5-67 6001 6132 6235





Example 2nd batch:

Input:

1 68 1 2183 2006 6004 6156

1 69 1 2183 2006 6004 6157

1 70 1 2183 2006 6004 6158

...

1 115 1 2183 2006 6004 6227

1 116 1 2183 2006 6004 6234

1 117 1 2183 2006 6004 6235



Desired Output:

1 2183 2006 68-117 6004 6156 6235





Example 3rd batch:



Input:

1 118 1 2183 2006 6007 6120

1 119 1 2183 2006 6007 6146

1 120 1 2183 2006 6007 6147

...

1 181 1 2183 2006 6007 6236

1 182 1 2183 2006 6007 6237

1 183 1 2183 2006 6007 6238

1 184 1 2183 2006 6007 6239



Desired Output:

1 2183 2006 118-184 6007 6120 6239

When the desired output compiled, it should become like the following:

1 2183 2006 5-67 6001 6132 6235

1 2183 2006 68-117 6004 6156 6235

1 2183 2006 118-184 6007 6120 6239

Could you please advise on this?

Rows of columnar data you want to group by and find things like the min and max of columns screams database. So... scripting sqlite3:

#!/bin/sh

sqlite3 -batch -noheader -list -separator ' ' <<EOF

CREATE TABLE data(c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER

                , c5 INTEGER, c6 INTEGER, c7 INTEGER);

.import "$1" data

SELECT c1, c4, c5, min(c2) || '-' || max(c2), c6, min(c7), max(c7)

FROM data GROUP BY c6 ORDER BY c6;

EOF

With your sample input:

$ ./doit.sh input.txt

1 2183 2006 5-67 6001 6132 6235

1 2183 2006 68-117 6004 6156 6235

1 2183 2006 118-184 6007 6120 6239

This awk script yields the output you desired.

$6 != col6 {

    printf fmt, min2, max2, min7, max7

    fmt = $1 " " $4 " " $5 " %d-%d " $6 " %d %dn"



    min2 = max2 = $2

    min7 = max7 = $7



    col6 = $6

}

{

    if ($2 < min2)

        min2 = $2

    else if ($2 > max2)

        max2 = $2



    if ($7 < min7)

        min7 = $7

    else if ($7 > max7)

        max7 = $7

}

END {

    printf fmt, min2, max2, min7, max7

}