-2

I'm currently working on a script for automating the creation of a pivot table from raw data file and I'm quite happy with my current work.

But I would like to add another step after creating the pivot table. I need to import the data into Excel and currently I would need to group values manually in Excel, so it would be great to have this grouping action within my script.

My current pivot table looks kinda like this (the original is too big for posting it here):

    10 20 30 40 50
0.2  1  0  0  1  2
1.4  0  2  1  0  0
2.0  2  0  1  0  1
2.3  0  0  2  2  0
3.6  3  0  1  1  0
4.1  1  0  2  1  0

The result should look like this:

    10 20 30 40 50
0-2  3  2  2  1  3
2-4  3  0  3  3  0
> 4  1  0  2  1  0

So, the values in column 1 need to be grouped and their values in the table need to be added to each other.

My original table has field separators (;) and the groups I need are 0-2, 2-4, 4-6, 6-8, 8-10 and > 10. So, like 0.2, 1.3, 1.9, 2.0 need to be grouped to "0-2".
2.2, 3.1, 3.6 to "2-4" and so on.

The values 2.0, 4.0, 6.0, 8.0 should be in the previous group, like 2.0 in "0-2", while the first value of "2-4" needs to be 2.1.

Originally my example had a comma as decimal separator, I can change the decimal separator to dot if needed; it's just for Excel which needs a comma when importing the table.

Any ideas how to manage this? Maybe using awk as I did in my script to create this pivot?

Here’s the complete data:

Row Labels;10;20;30;40;50;60;70;80;90;100;110;120;130;140;150;160;170;180;190;200;210;220;230;240;250;260;270;280;290;300;310;320;330;340;350;360
0.2;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;1
0.3;;;;;;;;;;;1;;;;;;;1;;;;;;;;;;1;;;;;;;;
0.4;;;;;1;;1;;;;;;;;1;;;;;;;;;;;;1;;;;1;;;;;
0.5;;;;;;;1;;;;1;;;;1;1;;1;;;;;;;;1;;;;;;;;2;;
0.6;1;1;1;;1;;;;1;1;;;;;;;;;;;;;;2;1;;;;1;;1;1;;2;;
0.7;1;;;;4;;;;1;;;;;;;;;;;;;;;1;;;;1;2;;;1;1;1;1;
0.8;;1;1;2;;;2;;1;;;2;;;;;;1;;;;;;1;;1;;1;;;;1;;1;1;1
0.9;1;;;;1;3;2;;1;;1;;1;;;;;;;;;;;;;;;;1;;;1;2;1;;1
1.0;;;2;;;;3;2;1;;;;;;1;;;;;;;;;;;1;1;;;;2;1;;;1;1
1.1;;1;;1;;2;3;2;;;;2;;;1;;;;;;;1;;;;;1;;1;;;;2;;;
1.2;;;1;;2;4;;;;;;;3;;;;;;;;;;;;1;;;;;;1;;2;;;
1.3;1;;1;2;;3;2;1;1;;;;;1;1;;;;;;;;;1;;;1;1;1;1;1;;1;1;1;
1.4;;;1;1;1;2;4;;;;;;;1;1;1;;;;;;1;;;;;;;;;;1;;;;
1.5;;;;1;;2;2;1;2;1;;;;;1;;;;;;;;;;;;;;;1;;;;;;
1.6;;1;;;4;4;2;1;;;1;;;;;;1;;;;;1;;;;;;;;;;;1;;;
1.7;;;;;;1;2;;1;1;;1;1;;;;;1;;1;;;;;;1;1;;;;;;;;;
1.8;1;;;1;2;1;1;;1;1;1;1;;;1;1;;;1;;;;;;1;;;1;2;;1;;1;;;2
1.9;3;;;1;1;3;;;;1;2;;;;;;;;1;;;1;2;;;;1;;1;;;;;;1;1
2.0;;2;;1;;;1;;;;3;1;1;1;;;;;;;;;;;;;;;;;2;1;;;;
2.1;1;;;;1;;;;1;;1;1;2;;;;1;;;;;;;;1;;;;;;;;1;;;
2.2;;;;;;3;2;;;2;1;1;1;;;1;;;;;1;;1;;;;;;;;;;;;;
2.3;;;;;;;1;1;;;3;;1;;;;;;;;;;;;1;1;;;1;;;;;;;
2.4;;1;;1;2;3;;;1;1;;;1;;3;;;;;;;;;;;;;;;1;;;;;;
2.5;;;;1;1;2;;;;;;2;;;;;;;;;;;1;;;;;1;;;;;;;;
2.6;;;;1;;1;;;;;1;2;;;;;;;;;1;;1;;;;;;;;1;;;;;
2.7;;;;;1;4;1;;;1;1;;1;1;;;;;;;;;;;;;;1;;;;;;;;1
2.8;;;;;;2;;3;;2;1;1;;;;2;;;;;;;;;2;1;;;2;;;1;;;;
2.9;;;;;4;1;;1;;1;1;2;;;;;;;;;;;;;;;;;;;;;;;;
3.0;1;;;2;;1;1;;;1;1;;;;;1;1;;;;;;;;;;1;;;;;;1;;;
3.1;;1;;;1;;;1;;;1;1;;;;1;1;;;;;;;;1;;;;1;;;1;;;;
3.2;;1;;1;1;1;1;;;1;;1;;1;1;;;;;;;;;;;;;;;;1;1;;;;
3.3;;;;;1;;;;;;3;4;;;1;1;;;;;;;;;;2;;;;;;;;;;1
3.4;;;;;;1;;;1;2;2;1;;;;;;;;;;;;;;;;;1;1;;1;;;1;
3.5;;;;;;;;;2;;;2;1;;;;;;;;;;;;;;;;1;;1;;;;;1
3.6;;;1;;;;1;;;1;2;;;;;1;;;;;;;;;;;;2;;;;;;1;;
3.7;;;;;;1;2;1;;;1;;;;;;;;;;;;;;;;1;1;;1;;;;;;
3.8;;;;;1;;1;;;;;;;;2;;1;;;;;;;;;;;;1;2;;;;;;
3.9;;;;;;1;;;;;1;1;1;;;;;;;;;;1;;;;;;3;2;;;;;;
4.0;;;;;;2;1;;;;1;3;;;;;;;;;;;;;;;1;;2;2;;;;;;
4.1;;;;;;;2;;;;1;2;1;;;;;;;;;;;;;;;1;1;1;1;;;;;
4.2;;;;;;1;1;;;1;;1;;;;;;;;;;;;;;;;;1;;;;;;;
4.3;;;;;;2;;;;;;3;;;;;;;;;;;;;;;;;1;2;;;;;;
4.4;;;;;1;;;;1;;3;3;;1;1;1;;;1;;;;;;;;;;1;2;1;;;;;
4.5;;;;;;1;1;;;1;1;;;;;;;;;;;;;;;;1;;1;;;;;;;
4.6;;;;;;1;;1;;;;;1;;1;;;;;;;;;;;;1;;1;1;1;;;;;
4.7;;;;;1;1;1;;;1;3;1;;;;;;;;;;;1;;;;;1;;2;;;;;;
4.8;;;;;1;1;;;;1;;;2;1;1;;;;;;;;;;1;;;;;1;;;;;;
4.9;;;;;;2;;;;;;;;;2;;;;;;;;;;;;;;;;;;;;;
5.0;;;;;;;;1;;;;;1;1;;1;;;;;;;;;;;;1;;1;;;;;;
5.1;;;;;;;1;;;;;1;;1;;;;;;;;;;;;;;;1;;;;;;;
5.2;;;;;1;;1;;;2;1;1;;;1;;;;;;;;;;;;;;;;;;;;;
5.3;;;;;;;2;;;;;2;;2;2;;;;;;;;;;;;;;;;;;;;;
5.4;;;;;;2;1;1;;;;1;1;;1;;;;;;;;;;;;;1;1;1;;;;;;
5.5;;;;;1;;;;;;;1;1;;;;;;;;;;;;;;;;1;1;;;;;;
5.7;;;;;;;3;1;;1;1;2;;1;;;1;;;;;;;;;;;1;1;1;;;;;;
5.8;;;;;;;;;;;;1;;;;;;;;;;;;;;;;1;1;;;;;;;
5.9;;;;;;;;;;;;1;;1;;1;;;;;;;;;;;;1;1;1;;;;;;
6.0;;;;;;1;1;;;;;2;2;1;;;;;;;;;;;;;;1;;2;;;;;;
6.1;;;;;;;;1;1;;;2;2;2;1;;;;;;;;;;;;;;;1;;;;;;
6.2;;;;;;;1;;;;1;3;;;;;;;;;;;;;;;;1;1;;;;;;;
6.3;;;;;;1;1;;;;;1;;;;;;;;;;;;;;;;1;;1;;;;;;
6.4;;;;;;;2;;;;;3;2;;;;;;;;;;;;;;;;;;;;;;;
6.5;;;;;;;;;;;;3;1;3;;;;;;;;;;;;;;;1;1;;;;;;
6.6;;;;;;;;;;;;2;;;;;;;;;;;;;;;;;;2;;;;;;
6.7;;;;;;;2;;;;;1;1;;;;;;;;;;;;;;;;1;1;;;;;;
6.8;;;;;;;;;;;1;1;;;;;;;;;;;;;;;;;;;;;;;;
6.9;;;;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;
7.0;;;;;;;;;;;;;2;1;;;;;;;;;;;;;;1;;;;;;;;
7.1;;;;;;;1;;;;;1;1;1;;;;;;;;;;;;;;;;1;;;;;;
7.2;;;;;;1;;;;;;2;;;;;;;;;;;;;;;;;2;1;;;;;;
7.3;;;;;;1;;;;;;2;1;1;1;;;;;;;;;;;;;;;;;;;;;
7.4;;;;;;;1;;;;;;1;1;;;;;;;;;;;;;;;;;;;;;;
7.5;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;;;;;
7.6;;;;;1;1;;;;;;1;2;;;;;;;;;;;;;;;;;;;;;;;
7.7;;;;;1;;;;;1;;1;1;1;1;;;;;;;;;;;;;;;;;;;;;
7.8;;;;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;
7.9;;;;;;;;1;;;;1;;1;;;;;;;;;;;;;;;;;;;;;;
8.0;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
8.2;;;;;;;;;;;;1;;1;;;;;;;;;;;;;;;;;;;;;;
8.3;;;;;;;;1;;;;;;2;;;;;;;;;;;;;;;;;;;;;;
8.4;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;
8.5;;;;;;;1;;;1;;;;;;;;;;;;;;;;;;;;;;;;;;
8.6;;;;;;;;1;;1;;1;;;;;;;;;;;;;;;;;;;;;;;;
8.7;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
8.8;;;;;;;;;;;;1;1;;;;;;;;;;;;;;;;;;;;;;;
8.9;;;;;;;;;;;;;1;1;;;;;;;;;;;;;;;;;;;;;;
9.0;;;;;;;1;;;;;1;1;;;;;;;;;;;;;;;;;;;;;;;
9.1;;;;;;;;;;1;;1;;;;;;;;;;;;;;;;;;;;;;;;
9.3;;;;;;;;;;;;2;1;;;;;;;;;;;;;;;;;;;;;;;
9.5;;;;;;;;;;;;2;1;;;;;;;;;;;;;;;;;;;;;;;
9.6;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;
9.8;;;;;;;;;;;;1;1;;;;;;;;;;;;;;;;;;;;;;;
9.9;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;
10.0;;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;
10.3;;;;;;;;;;;;3;;;;;;;;;;;;;;;;;;;;;;;;
10.5;;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;
10.9;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;
11.3;;;;;;;;;;;;1;;;;;;;;;;;;;;;;;;;;;;;;
tripleee
  • 175,061
  • 34
  • 275
  • 318
Jannomag
  • 3
  • 4
  • Does this answer your question? [Awk/Unix group by](https://stackoverflow.com/questions/14916826/awk-unix-group-by) – Foxfire And Burns And Burns Apr 08 '22 at 12:29
  • not really, I found this and several similar posts before posting my issue. I can't figure out how to group values within a range. – Jannomag Apr 08 '22 at 12:42
  • 1
    What did you try and what was wrong with it? – Renaud Pacalet Apr 08 '22 at 12:43
  • @RenaudPacalet I edited my question after your comment. The first group should be 0-2 and not 1-2. I tried several things I really can't relate since awk is very complicated to me and I'm very happy to get to this point where I am. Those groups are needed for creating a graphical diagram using excel. Without these groups there would be too many values in column 1 for the diagram. (@EdMorton) – Jannomag Apr 08 '22 at 12:46
  • Still, why do you group the 3 first lines? According what criteria? Why not the 10 first lines? And why `0-2` for the merge of `0-2`, `1-4` and `2-0`? – Renaud Pacalet Apr 08 '22 at 12:48
  • @RenaudPacalet As I've written in my question, I need to create groups in the range of 2. I need the sum of the values in the table for creating a diagram. Without those groups I would have too many values for my graphical diagram. – Jannomag Apr 08 '22 at 12:51
  • 1
    Is your file sorted in increasing order of first column? – Renaud Pacalet Apr 08 '22 at 13:03
  • 1
    your 1st column is actually a float/real number? so `0,2` is the same as `0.2`, right? (it's a bit confusing that your data shows a comma but in your description you use periods); so a range of `0-2` means to combine all lines where the value in the 1st column is `>0.0 and <=2.0`, the range `2-4` refers to 1st column values `>2.0 and <=4.0`, range `4-6` refers to 1st column values `>4.0 and <=6.0` ... correct? and continue to do this by increasing the range (1st column values) by `2` until end of file – markp-fuso Apr 08 '22 at 13:21
  • 1
    is there a possibility of finding an emtpy range (eg, for range `10-12` there are no rows with 1st column values `>10.0 and <=12.0`) and if so, should that empty range be shown in the output as all 0's, especially if there are follow-on rows that fit into the `12-14` range? I'll repeat Renaud's question ... is the data is already sorted by the 1st column? (this will determine if we can accumulate/print ranges as we read the input rows or if we'll have to store all range info in memory and then print the final result after having read the entire input file) – markp-fuso Apr 08 '22 at 13:25
  • @markp-fuso the first column are floating numbers, they just name the rows. It doesn’t matter for me if they’re have a dot or a comma as decimal separator (in German it’s a comma and since the finished diagram is in German I used commas for my table), but it doesn’t matter since it doesn’t show up within the groups. So periods are fine. And it can happen, that there aren’t values >=10. In those cases it it can be ignored at all. Groups are from „0-2“ to „>10“ – Jannomag Apr 09 '22 at 05:57

2 Answers2

2
$ cat tst.awk
BEGIN { beg=0; range=2; end=beg+range; max=4 }
NR==1 { print; next }
$1 > end {
    prt()
    delete sum
    beg = end
    end += range
}
{
    for (i=1; i<=NF; i++) {
        sum[i] += $i
    }
}
END { prt() }

function prt() {
    if ( beg == max ) {
        printf "> %d", beg
    }
    else {
        printf "%d-%d", beg, end
    }
    for (i=2; i<=NF; i++) {
        printf "%3d", sum[i]
    }
    print ""
}


$ awk -f tst.awk file
    10 20 30 40 50
0-2  3  2  2  1  3
2-4  3  0  3  3  0
> 4  1  0  2  1  0

There may be an issue if you have values in your input greater than 4 but since those don't exist in the example you provided I wasn't able to test with it and I assume you can figure out how to handle it yourself given the above as a starting point.

Ed Morton
  • 188,023
  • 17
  • 78
  • 185
  • I'm expecting the OP to change their question to replace the `,`s with `.`s and there are other missing requirements/use cases not present in the example so I don't want to put much thought/effort into this answer just yet, we'll see where this goes... – Ed Morton Apr 08 '22 at 13:24
  • It doesn’t matter if it’s a comma or a dot for me. It’s just a local thing (commas are the decimal aperster in Germany, where I live). For the final result it doesn’t matter, so I edited my example. – Jannomag Apr 09 '22 at 06:07
1

Assumptions/understandings:

  • objective is to collapse the input table into a series of summations based on what range the 1st column's value falls within
  • the 1st column's value is a float/real number; my locale uses the period as the decimal point so I'll convert commas to periods (I'll also assume that the real input file may contain floats/reals in other columns so I'll also convert commas to periods ... and hope there are no commas in use as the 1000's delimiter)
  • the 1st column's value falls within the range X-Y if value > X and value <= Y
  • ranges of interest: 0-2, 2-4, 4-6, etc
  • within a given range we sum the values in each of the columns #2 - #N
  • if an intermediate range does not have any rows from the input then we print said range with all 0's (eg, all input data falls within ranges 0-2 or 4-6 => we need to generate range 2-4 and set all sums=0)
  • input data may not be sorted by the 1st column so we'll need to accumlate the range/sums in memory (eg, an array)
  • all lines contain the same number of columns

One GNU awk idea:

awk '
BEGIN  { FS=OFS=";" }
FNR==1 { print; next }
       { gsub(/,/,".")                                                # convert commas to periods for my locale
                                                                      # remove the gsub() line if locale handles whatever is used as a decimal point in the input    

         endrange= int($1) - int($1)%2                                # calculate upper range that $1 fits into
         if (endrange < $1) endrange+=2

         maxrange=(endrange > maxrange ? endrange : maxrange)         # keep track of the max upper range that we have seen

         for (col=2; col<=NF; col++)                                  # loop through list of columns ...
             sums[endrange][col]+=$col+0                              # summing up the values
       }

END    { for (endrange=2; endrange<=maxrange; endrange=endrange+2) {
             printf "%s-%s", (endrange-2), endrange
             for (col=2; col<=NF; col++)
                 printf "%s%s", OFS, sums[endrange][col]+0            # if this is an invalid array reference we can force the output==0 with the "+0"
             print ""
         }
       }
' pivot.dat

NOTES:

  • this requires GNU awk for multi-dimensional array support
  • OP hasn't provided the expected output for the latest data edit so I've assumed input and output field delimiters should be the same (ie, FS=OFS=";"); OP can modify as needed
  • OP has mentioned the input data was generated from a separate awk script; OP can decide if they wish to maintain 2 sets of awk scripts or attempt to consolidate into a single awk script

This generates:

Row Labels;10;20;30;40;50;60;70;80;90;100;110;120;130;140;150;160;170;180;190;200;210;220;230;240;250;260;270;280;290;300;310;320;330;340;350;360
0-2;8;6;7;10;17;25;26;7;10;5;10;7;6;3;8;3;1;4;2;1;0;4;2;5;3;4;6;5;9;2;9;7;10;8;5;7
2-4;2;3;1;6;13;23;11;7;5;12;21;22;8;2;7;7;4;0;0;0;2;0;4;0;5;4;3;5;12;9;3;4;2;1;1;3
4-6;0;0;0;0;5;12;14;4;1;7;10;22;9;9;9;3;1;0;1;0;0;0;1;0;1;0;2;8;12;16;3;0;0;0;0;0
6-8;0;0;0;0;2;4;9;3;1;1;2;24;14;11;5;0;0;0;0;0;0;0;0;0;0;0;0;3;5;8;0;0;0;0;0;0
8-10;0;0;0;0;0;0;3;2;0;3;0;13;7;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
10-12;0;0;0;0;0;0;0;0;0;0;0;5;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0

If the 1st column of the last row of output should be > {maxrange} then we can change the END{...} block as follows:

END    { for (endrange=2; endrange<=maxrange; endrange=endrange+2) {
             if (endrange == maxrange)
                 printf "> %s", (endrange-2)
             else
                 printf "%s-%s", (endrange-2), endrange

             for (col=2; col<=NF; col++)
                 printf "%s%s", OFS, sums[endrange][col]+0
             print ""
         }
       }

This generates:

Row Labels;10;20;30;40;50;60;70;80;90;100;110;120;130;140;150;160;170;180;190;200;210;220;230;240;250;260;270;280;290;300;310;320;330;340;350;360
0-2;8;6;7;10;17;25;26;7;10;5;10;7;6;3;8;3;1;4;2;1;0;4;2;5;3;4;6;5;9;2;9;7;10;8;5;7
2-4;2;3;1;6;13;23;11;7;5;12;21;22;8;2;7;7;4;0;0;0;2;0;4;0;5;4;3;5;12;9;3;4;2;1;1;3
4-6;0;0;0;0;5;12;14;4;1;7;10;22;9;9;9;3;1;0;1;0;0;0;1;0;1;0;2;8;12;16;3;0;0;0;0;0
6-8;0;0;0;0;2;4;9;3;1;1;2;24;14;11;5;0;0;0;0;0;0;0;0;0;0;0;0;3;5;8;0;0;0;0;0;0
8-10;0;0;0;0;0;0;3;2;0;3;0;13;7;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
> 10;0;0;0;0;0;0;0;0;0;0;0;5;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
markp-fuso
  • 28,790
  • 4
  • 16
  • 36
  • This works great! Awesome. I understand what you've written in that script but I would've never find this solution on my own, eventhough it looks so simple. Next thing I'll try to do it by myself is to add columns, if they're missing. In the example I've given there are no missing columns (10-360 degree for wind direction), but sometimes there will be missing directions which needs to be added just with 0 as values for each wind speed. – Jannomag Apr 11 '22 at 07:20
  • Just tried it with older data. In very rare cases there are values >12. With your code it will add groups like 12-14. But I just need >10, even if there are cases with >12 (14.1). – Jannomag Apr 11 '22 at 09:04
  • right now `maxrange` is dynamically determined based on the value of `endrange`; a quick fix would be to limit `endrange` to `12` (in this case), eg, after `if (endrange...)` add `endrange=(endrange>12 ? 12 : endrange)` – markp-fuso Apr 11 '22 at 12:44