4

I am trying to summarise a table of data that changes everyday. I have already summarised the table to only display rows with entries that are larger than 30.

However, on some days, there are no entries more than 30 in the original table. When that happens, i do not need the entire section that is empty in the summary. How do i then remove the entire header for those sections?

Ideally, if there are no entries in all 5 sections, there should not be any lines printed ( or just a string that says: "None: there is no entry larger than 30" as i was trying to do)

Example of a summarised table with 5 sections, summarised_output.txt:

=========================================================================================================
Month: Jun      

Counter Name                                06/04 18:00     06/04 17:00     06/04 16:00     06/04 15:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45


=========================================================================================================
Month: Jun     

Counter Name                                06/05 14:00     06/05 13:00     06/05 12:00     06/05 11:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45


=========================================================================================================
Month: Jun      

Counter Name                                06/05 10:00     06/05 09:00     06/05 08:00     06/05 07:00
=========================================================================================================


=========================================================================================================
Month: Jun    

Counter Name                                06/05 06:00     06/05 05:00     06/05 04:00     06/05 03:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45


=========================================================================================================
Month: Jun      

Counter Name                                06/04 18:00     06/04 17:00     06/04 16:00     06/04 15:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45


=========================================================================================================

As you can see, the third section is empty because there is no entry in the original_output.txt file higher than 30. But the header is still there.

My summary code( worked):

awk '$1=="Month:"||$1==""||$1=="Counter"||(index($1, "=")!=0)||$3>=30|| $4>=30 || $5>=30||$6>=30' original_output.txt>>summarised_output.txt

My attempt at deleting the header (doesn't work):

touch summarised_output_temp.txt
awk '{if ($1=="Month:"||$1==""||$1=="Counter"||(index($1, "=")!=0)||$3>=30|| $4>=30 || $5>=30||$6>=30) print $0}' original_output.txt >> summarised_output_temp.txt
if (((wc -l < summarised_output_temp.txt)==42))
then
echo "None: there is no entry larger than 30" >> summarised_output.txt
else
cat output_7_temp.txt>>summarised_output.txt
fi

The error received for the attempt:

line 3: ((: (wc -l output_7_temp.txt | awk {print $1})==42: syntax error: invalid arithmetic operator (error token is ".txt | awk {print $1})==42")
  • My gawk version is gawk 3.1.8 – tthhss Jun 5 '18 at 9:51
1

Provided you have GNU awk, You could this script:

awk -v RS='={105}[^=]*={105}' 'NF{print oRT,$0} {oRT=RT}' file

The table header is set as a multiline record separator RS. It is composed of twice 105 = characters with anything inbetween.

The variable RT is catching the record separtor for each record, and is stored in the variable oRT and printed at the next record provided that there is some elements to print (which is catch with NF).

  • Hi! thanks for the help! I am receiving error for this: awk: syntax error near line 1 awk: bailing out near line 1 . I guess i might not have GNU awk, but i'm not sure. i checked man awk but i didn't find anything – tthhss Jun 5 '18 at 9:18
  • @tthhss Try using gawk instead of awk. You can also figure out the version you're using with the command awk --version. – oliv Jun 5 '18 at 9:22
  • gawk worked, but there is no change in the output file. The headers still appeared for empty sections. perhaps there can be a if else way using a temporary file? I was thinkin of using grep, but couldn't figure a way to include the header if i searched for "SYS.SYS" – tthhss Jun 5 '18 at 9:39
  • I'm using GNU awk 4.2.1 and the header including the string 06/05 10:00 doesn't appear with this awk script. – oliv Jun 5 '18 at 9:48
  • oh thats exactly what im trying to do, my gawk version is 3.1.8. I will go look it up and see if there are similar command for this version – tthhss Jun 5 '18 at 9:53
1

Create an executable script test.awk, containing

awk '
  # { print "read " $0 }
  /^SYS/ {
    if (H) {
      if (F) {print F} else { F="\r\n\r\n"}
      print H; H=""}
    print
    next
  }
  /^./ {
    H=$0
    for (i=1; i<5; i++) {
      getline
      H = H "\r\n" $0
    }
    next
  }
' $@

and run

test.awk original_output.txt

"/^SYS/ {" can be replaced by anything that recognizes a data line.

  • Hi! so glad for your help. I dunno why but i tried running it and it returns : test.awk: Command not found. I also added a first line "#!/usr/bin/awk" after doing "which awk", it didn't help. – tthhss Jun 5 '18 at 10:33
  • What is the third word of my answer, in case you skipped that? – Gerard H. Pille Jun 5 '18 at 10:35
  • It works! May i ask what is the (i=1; i<5;i++) referring to? I presume it is the 4 lines of heading? – tthhss Jun 6 '18 at 7:10
  • Almost, since there are 5 lines of heading. My script is very weak, only works when data lines always start with "SYS", and there are always exactly 5 header lines. If you can come up with something better to recognize the data lines, I'll improve both. – Gerard H. Pille Jun 6 '18 at 8:36
1

This problem can be tackled with Perl along these lines.

Code

perl -F'/^=+$/m' -lan -0777e '
   my($dashes) = /^(=+)$/m;
   shift @F; pop @F;
   while ( @F > 1 ) {
      my($h, $s) = splice @F, 0, 2;
      next if $s =~ /^(?:\h*\n)+$/;
      print join $dashes, q(), $h, $s, !@F ? q() : ();
   }
' input.txt

Output

=========================================================================================================
Month: Jun

Counter Name                                06/04 18:00     06/04 17:00     06/04 16:00     06/04 15:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45



=========================================================================================================
Month: Jun

Counter Name                                06/05 14:00     06/05 13:00     06/05 12:00     06/05 11:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45



=========================================================================================================
Month: Jun

Counter Name                                06/05 06:00     06/05 05:00     06/05 04:00     06/05 03:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45



=========================================================================================================
Month: Jun

Counter Name                                06/04 18:00     06/04 17:00     06/04 16:00     06/04 15:00
=========================================================================================================
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45
SYS.SYS.SYS.SYS.SYS.SYS.                  : 45              45              45              45


=========================================================================================================

Explanation

  • Slurp the input in one go: -0777 option to Perl enables that.
  • Split this whole file (now being treated as one long string) along the dashes: /^=+$/m means wherever a given line comprises nothing but repeated equals is where we split the file on.
  • -a option will make perl stuff the the split parts into the array @F.
  • -n option to Perl will disable the autoprinting just before reading the next record AND also does a awk-like looping on records read from input.
  • First off we store the dashes line from the input.
  • Note that the split operation will make the first and last elements to be empty, hence we remove those from the @F array before undertaking the main task.
  • We setup a while loop what consumes 2 elements (splice) at a time from the @F array.First is the header and second the section. The loop continues so long as there are at least 2 elements still in the array.
  • The key point is in taking the decision to skip or not to skip processing the header/section duo. And that gets decided when the section comprises just empty lines or lines comprising only horizontal whitespace (\h). The regex for that is /^(?:\h*\n)+$/
  • Now if the section isn't empty, there remains the task of stitching together the header/section duo with the dashes. We keep in mind that at the last header/section coming together we need to add the dashes to round off.
1

Similar to oliv's answer, perhaps a little simpler:

gawk -v RS='=+\n' '
    NR % 2 == 0 {header = $0; next}
    /[^[:space:]]/ {printf "%s", RT header RT $0}
    END {print RT}
' output.txt 

Using a line of =s as the record separator. The header is then every even numbered record. And the "contents" are not empty if it contains a non-whitespace character.

RT is a GNU awk specific variable containing "The input text that matched the text denoted by RS, the record separator. It is set every time a record is read."

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.