Average of lines with different conditions at header and tail of files

Question

Edit: edited it for clarity and made the example files minimal and more reproducible for easier help. Thanks!

I have files that gave 1000+lines. Each file is formatted with the same amount of lines. The format of the has 3 "header lines", 1000+ lines of values (positive and negative with 6 trailing decimal places), followed by 13 "tail lines". The format of the lines can be seen below. within my real files, on certain lines I would like to different commands such as print text from lines, take averages on the actual data, lines to copy text and average of data and take average of dates and times.

This is an outline or sorts of the long files with some comment about the goal for each line.

The outline below is an dbriavated example. The lines containing data (lines 4-9 in the example) are actually line 4-1436 in the real files. Then line 10 in the outline is line 1437 in the actual file. (hopefully this makes sense). The data lines could contain negative numbers or positive numbers and be in the range from -100 to +5000.

ABCDEFGH               # Line 1... print text into output file (same on across all files)
1                      # Line 2... Take average of values across all the files in this line
2048                   # Line 3... Take average of values across all the files in this line
8.123456               # Line 4... Take average of values across all the files in this line (could be positive or negative)
5.123456               # Line 5... Take average of values across all the files in this line (could be positive or negative)
5.654321               # Line 6... Take average of values across all the files in this line (could be positive or negative)
4.654321               # Line 7... Take average of values across all the files in this line (could be positive or negative)
9.654321               # Line 8... Take average of values across all the files in this line (could be positive or negative)
1.654321               # Line 9... Take average of values across all the files in this line (could be positive or negative)
90.00                  # Line 10... Check and make sure value in this line across print if same
Sprite                 # Line 11... check and see if text is same across all values and print if same
cats10                 # Line 12... check and see if text is same across all values and print if same
07/02/20               # Line 13... See below for explantion on next 3 lines
08:32                  # Line 14...
08:32                  # Line 15...
290.000000             # Line 16... average across all files on this line
10.750000              # Line 17... average across all files on this line
SCANS23                # Line 18... output should be SCANS "average of values"
INT_TIME57500          # Line 19... output should be INT_TIME "sum of values"
SITE northpole         # Line 20...Check if all lines are same if so print line
LONGITUDE -147.850037  # Line 21... Output should be LONGITUDE "average"
LATITUDE 64.859375     # Line 22... Output should be LONGITUDE "average"

Line 13 is the date the data is from, line 14 is the time if started and the time it ended. Probably using someway date to decimal command.. is there a way to take the average of the date? If one data was taken on 07/02/20 and the other from 07/02/18 can the output be 07/02/19? the average of the times would be taken into account too.

I thought some extended ternary operator could be a path but using so many different cases didn't work at all.

awk -F: '
  FNR==1     { c++ };
  /^LATITUDE/    { a[FNR] += $6 };
  /^LONGITUDE/    { a[FNR] += $5 };
  /^SITE/    { a[FNR] += $4 };
  /^INT_TIME/    { a[FNR] += $3 };
  /^SCANS/    { a[FNR] += $2 };
  /^[+-]?([0-9]*[.])?[0-9]+$/ { a[FNR] += $1 };

  END {
    for (i in a) {
      printf (i==22 ? "LATITUDE%f": 
              i==21 ? "LONGITUDE%2.3f": 
              i==20 ? "SITE%2.3f": 
              i==19 ? "INT_TIME%2.3f": 
              i==18 ? "SCANS%2.3f": "%f") "\n", a[i] / c 
    }
  }' /home/test/test1.* > /home/average

All sample files are assumed to be in /home/test/aaaaaa-bbbb-cc10dddd-L1-2020070119*-01.std and want the "average" file output to be in /home/dir/aaaaaa-bbbb-cc10dddd-L1-2020070119-01.std which is formatted as /aaaaaa-bbbb-cc10-dddd-L1-"year""month""day""hour"-"elevation number".std

input files of taken on 01/07/2020 at 19 hours at elevation 1:

/home/dir/dir2/aaaaaa-bbbb-cc10dddd-L1-202007011918-01.std
/home/dir/dir2/aaaaaa-bbbb-cc10dddd-L1-202007011929-01.std
/home/dir/dir2/aaaaaa-bbbb-cc10dddd-L1-202007011941-01.std
/home/dir/dir2/aaaaaa-bbbb-cc10dddd-L1-202007011953-01.std

Output file would be

/home/dir/aaaaaa-bbbb-cc10dddd-L1-2020070119-01.std

/home/dir/dir2/aaaaaa-bbbb-cc10dddd-L1-202007011918-01.std

ABCDEFGH
1
2048
-3.249389
-4.544701
5.822962
2.372011
-17.937092
20.000408
5.00
Sprite
cats10
07/01/20
19:18
19:18
290.000000
10.690000
SCANS23
INT_TIME57500
SITE northpole
LONGITUDE -147.850037
LATITUDE 64.859375

/home/dir/dir2/aaaaaa-bbbb-cc10dddd-L1-202007011929-01.std

ABCDEFGH
1
2048
-6.369022
-4.957337
-2.715081
1.766033
-20.002853
21.522350
5.00
Avantes
buoy10
07/01/20
19:29
19:29
290.000000
10.310000
SCANS23
INT_TIME57500
SITE giroof
LONGITUDE -147.850037
LATITUDE 64.859375

/home/dir/dir2/aaaaaa-bbbb-cc10dddd-L1-202007011926-01.std

ABCDEFGH
1
2048
2.961413
-14.236549
19.784035
2.711583
-18.305300
9.369226
5.00
Avantes
buoy10
07/02/20
19:26
19:26
290.000000
10.310000
SCANS23
INT_TIME57500
SITE giroof
LONGITUDE -147.850037
LATITUDE 64.859375

Please create and edit your question to show a minimal-reproducible-example that demonstrates the problem you want help with. Not an "outline" of a file but an minimal example of a couple of files and the associated expected output given that input that would demonstrate what you need. Such an example would have no ellipses, would not need 13 lines of whatever, it'd only need 2 or 3, and it wouldn't need 10 lines of types of data, it'd again only need 2 or 3, etc. — Ed Morton, Jan 29, 2022 at 11:26
Your request could be clearer. You're talking of "both files", then "3 files", then show a directory listing of four files. What are lines 13, 14, etc. that you refer to? Counted from where? "print if same" - what to do if "not same"? Just drop? — RudiC, Jan 29, 2022 at 16:35
sorry, I made this question really late and I had it got really messy. I've edited it to hopefully be more clear what I'm trying to create! — Lucas Djeu, Jan 30, 2022 at 5:14
I'm noticing that this is your third question, and that you have not yet accepted any of the answers to your older questions. unix.stackexchange.com/help/someone-answers — Kusalananda, Jan 30, 2022 at 7:00
We can't test a potential solution for creating averages across multiple files if you don't provide multiple input files and the expected output given those input files for us to test with. You posted the contents of 3 files at the bottom of your question. Are those the sample input files you want us to use? Their names don't match the list of 4 file names you provided above them under input files of taken on 01/07/2020 at 19 hours at elevation 1:, please fix that so the input's consistent and add the expected output given that input (not just the name of the output file you want to create). — Ed Morton, Jan 30, 2022 at 15:17

RudiC · Accepted Answer · 2022-01-30 16:39:14Z

This might come close to what you need, pasteing (hopefully not too many) input files into awk, switching off any locale influence:

paste file[1-3] | LC_ALL=C awk -v"LNCT=$(wc -l <file1)" '

function avg(  sum)     {for (i=1; i<=NF; i++) sum += $i
                         return sum/NF
                        }

function same()         {for (i=2; i<=NF; i++) if ($1 != $i) return 0
                         return 1
                        }

NR == 1                 {print $1
                         next
                        }
NR <= (LNCT-13) ||
NR >= (LNCT-6)  &&
NR <= (LNCT-5)          {print avg()
                         next
                        }

NR >  (LNCT-13) &&
NR <= (LNCT-10)         {print (same()?$1:"") 
                        }
NR >= (LNCT-9) &&
NR <= (LNCT-7)          {if (NR == (LNCT-9))    FMT = "%m/%d/%y"
                           else                 FMT = "%H:%M"

                         for (i=1; i<=NF; i++)  {CMD = "date +%s -d\"" $i"\""
                                                 CMD | getline  $i
                                                 close (CMD)
                                                }
                         CMD = "date +" FMT " -d\"@" avg() "\""
                         CMD | getline ITEM
                         close (CMD)
                         print ITEM
                        }

                        {ITEM = $1
                         gsub (/[0-9]*/, "", ITEM)
                         if (gsub (/SCANS|INT_TIME|LONGITUDE|LATITUDE/, ""))    {print ITEM, avg()
                                                                                }
                         if (gsub (/SITE/, ""))         print ITEM, (same()?$1:"") 
                        }
'
ABCDEFGH
1
2048
-2.219
-7.91286
7.63064
2.28321
-18.7484
16.964
5.00


07/01/20
19:24
19:24
290
10.4367
SCANS 23
INT_TIME 57500
SITE 
LONGITUDE -147.85
LATITUDE 64.8594

It's a bit clumsy as it detects the "special treatment " lines by their line No., esp. the date / time ones, but it seems to do what was requested. We need the line count upfront and pass the wc - l output via an awk variable assuming all file have equal length. There may be other / better approaches. For the date / time calculations: that is quite resource hungry to do with running an external date command for every occurrence which, on top, is not available on all OS versions. It works for my linux system, but I'd be open for better ideas.

This worked great thanks! I do have to do it on alot of files within a directory. I'd like to run this on 24 directories at the same time with about 4-5 files in each directory. Theoretically I should be able to write this code into a loop for each directory correct? are you able to provide some syntax or starting point for this in a loop if possible? — Lucas Djeu, Feb 16, 2022 at 4:20
Use two nested for loops - one for the directories, one for the files in each. The files MUST be of identical structure. Show the tree (in your question) if you need some help. — RudiC, Feb 16, 2022 at 9:52

Ed Morton · Accepted Answer · 2022-01-30 16:51:25Z

This might be what you're looking for, using GNU awk for time functions in the date average calculation and assuming your timezone is UTC and all of your dates are this century and you don't have any empty input lines:

$ cat tst.sh
#!/usr/bin/env bash

paste "$@" |
awk '
    BEGIN { FS="\t"; CONVFMT="%0.6f" }
    ( 1 <= NR) && (NR <=  1) { print chkSameStrnums() }
    ( 2 <= NR) && (NR <=  9) { print getTagAveNr() }
    (10 <= NR) && (NR <= 12) { print chkSameStrnums() }
    (13 <= NR) && (NR <= 13) { print getAveDate() }
    (14 <= NR) && (NR <= 15) { print getAveTime() }
    (16 <= NR) && (NR <= 17) { print getTagAveNr() }
    (18 <= NR) && (NR <= 18) { print getTagAveNr() }
    (19 <= NR) && (NR <= 19) { print getTagSumNr() }
    (20 <= NR) && (NR <= 20) { print chkSameStrnums() }
    (21 <= NR) && (NR <= 22) { print getTagAveNr() }

    function sumNrFlds(         i,sum,val) {
        for (i=1; i<=NF; i++) {
            val = $i
            sub(/^[^0-9-]+/,"",val)
            sum += val
        }
        return sum
    }

    function getTagAveNr(       tag) {
        tag = $1
        sub(/[0-9.-]+$/,"",tag)
        return tag (sumNrFlds() / NF)
    }

    function getTagSumNr(       tag) {
        tag = $1
        sub(/[0-9.-]+$/,"",tag)
        return tag sumNrFlds()
    }

    function getAveDate(        i,sum,d,secs) {
        for (i=1; i<=NF; i++) {
            split($i,d,"/")
            secs = mktime("20"d[3] " " d[1] " " d[2] " 12 00 00", 1)
            sum += secs
        }
        return strftime("%m/%d/%y",int(sum/NF))
    }

    function getAveTime(        i,sum,t,ave,hrs,mins) {
        for (i=1; i<=NF; i++) {
            split($i,t,":")
            mins = (t[1] * 60) + t[2]
            sum += mins
        }
        ave = sum/NF
        hrs = int(ave/60)
        mins = int(ave - (hrs * 60))
        return (hrs ":" mins)
    }

    function chkSameStrnums(    i,diff) {
        for (i=2; i<=NF; i++) {
            if ($i != $1) {
                diff = 1
                break
            }
        }
        return (diff ? "different" : $1)
    }
'

$ ./tst.sh file?
ABCDEFGH
1
2048
-2.218999
-7.912862
7.630639
2.283209
-18.748415
16.963995
5.00
different
different
07/01/20
19:24
19:24
290
10.436667
SCANS23
INT_TIME172500
different
LONGITUDE -147.850037
LATITUDE 64.859375

The time calculation would become more interesting if the date changed between the 2 times but you don't have a way to represent that in your data in general so I left it as an exercise (hint: if the end time is less than the start time and your interval can never exceed 24 hours then you know you've crossed a day and so can add 24 hours to the end time - if the interval CAN exceed 24 hours then you're out of luck).

Thanks for the time of writing a code i could try. When I tried to run the code, I seem to get 2 errors asking me to define functions written awk: line 66: function strftime never defined awk: line 66: function mktime never defined You were able to get an output so it must work. were their functions you defined that aren't in your response? — Lucas Djeu, Feb 1, 2022 at 0:53
You're not using GNU awk as I said in my answer is required. Get gawk or figure out some other way to get the date averages or just don't do the date average calculation. — Ed Morton, Feb 1, 2022 at 0:54

Stack Exchange Network

Average of lines with different conditions at header and tail of files

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
bash
awk
printf
.

Hot Network Questions

Average of lines with different conditions at header and tail of files

2 Answers 2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged bashawkprintf.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
bash
awk
printf
.