process a text file to modify a column value based on maximum value from another column

Question

I have a file that i am looking to modify. There are 100's of files having the same issue. the input file is below

sample1 100A    total   1   1000
sample2 100A    total   1   5584
sample3 100A    total   1   8125
sample4 100A    total   1   59
                
sample1 .   year    1   1000
sample1 .   week1   20  1001
sample1 .   week2   50  1001
                
sample2 .   year    1   5584
sample2 .   week1   20  5585
sample2 .   week2   100 5585
                
sample3 .   year    1   8125
sample3 .   week1   55  8126
sample3 .   week2   100 8126
                
sample4 .   year    1   59
sample4 .   week1   10  59
sample4 .   week2   8   59

In the above file, where third column says "total" is the look up table and the maximum value for it should not exceed in any of the rows below for the respective samples.

For example, "sample1" has maximum value of 1000 but for "week1" and "week2" in third column has "1001" as maximum value. I see this happening for few rows for "week1 and week2" only and always its only 1 greater than maximum value. The output should be for both "week1" and "week2" rows should be updated to maximum value "1000". sample output below

sample1 100A    total   1   1000
sample2 100A    total   1   5584
sample3 100A    total   1   8125
sample4 100A    total   1   59
                
sample1 .   year    1   1000
sample1 .   week1   20  1000
sample1 .   week2   50  1000
                
sample2 .   year    1   5584
sample2 .   week1   20  5584
sample2 .   week2   100 5584
                
sample3 .   year    1   8125
sample3 .   week1   55  8125
sample3 .   week2   100 8125
                
sample4 .   year    1   59
sample4 .   week1   10  59
sample4 .   week2   8   59

Please provide me guidance on how should i proceed. I am a beginner programmer here looking to learn. thank you for your help

Answer 1

Using Raku (formerly known as Perl_6)

~$ raku -e 'my @a = slurp.split("\n\n", 2);  print @a[0] ~ "\n\n"; 
            my %h;  for @a.[0].lines() { \%h.push: .words.[0,4] }; 
            for @a[1].lines {
                S/^ (\S+)(.* \s)(\S+) $/{"$0$1"}{ %h{$0} < $2 ?? "%h{$0}" !! "$2" }/.put };'   file.txt

Raku is a programming language in the Perl-family, and consequently its syntax is decidedly "Perl-ish". Above we slurp the file into memory all at once, splitting the file into 2 parts after the first paragraph. This data is stored in @a array. The first part containing reference values (@a[0]) is immediately printed.

A hash %h is then declared. We iterate through the top summary paragraph (@a[0]) using for ... lines. Here each line is broken into whitespace-separated words, and .[0,4] the first/fifth columns are pushed onto the %h hash as reference key/value pairs.

The remainder of the file (@a[1]) is iterated through using for ... lines, meaning we can analyze/modify each line within the block. Using Raku's S/// non-destructive substitution idiom that returns a modified string, we break the input into $0 first (key) column, $1 middle columns, and $2 final (value) column.

In the replacement, we simply print {"$0$1"} as is. However for the final column we can run code inside the curly braces: using Raku's ternary operator Test ?? True !! False we return %h{$0} (the key's associated value) if it is smaller than $2, otherwise the original value ($2) is returned.

Sample Input:

sample1 100A    total   1   1000
sample2 100A    total   1   5584
sample3 100A    total   1   8125
sample4 100A    total   1   59

sample1 .   year    1   1000
sample1 .   week1   20  1001
sample1 .   week2   50  1001

sample2 .   year    1   5584
sample2 .   week1   20  5585
sample2 .   week2   100 5585

sample3 .   year    1   8125
sample3 .   week1   55  8126
sample3 .   week2   100 8126

sample4 .   year    1   59
sample4 .   week1   10  59
sample4 .   week2   8   59

Sample Output:

sample1 100A    total   1   1000
sample2 100A    total   1   5584
sample3 100A    total   1   8125
sample4 100A    total   1   59

sample1 .   year    1   1000
sample1 .   week1   20  1000
sample1 .   week2   50  1000

sample2 .   year    1   5584
sample2 .   week1   20  5584
sample2 .   week2   100 5584

sample3 .   year    1   8125
sample3 .   week1   55  8125
sample3 .   week2   100 8125

sample4 .   year    1   59
sample4 .   week1   10  59
sample4 .   week2   8   59

---

Finally, it's often instructive to look at intermediate data-structures, so here's what %h hash looks like (using .say for %h.sort;);

sample1 => 1000
sample2 => 5584
sample3 => 8125
sample4 => 59

https://docs.raku.org/syntax/S%2F%2F%2F%20non-destructive%20substitution
https://docs.raku.org/language/operators#infix_??_!!
https://docs.raku.org/language/regexes
https://raku.org

Answer 2

A simple Awk one-liner:

awk -v OFS='\t' '$3 == "total" {maxval[$1] = $5} $1 in maxval && $5 > maxval[$1] {$5 = maxval[$1]} {print}' input.txt

With some line breaks added:

awk -v OFS='\t' '
  $3 == "total" {
    maxval[$1] = $5
  }
  $1 in maxval && $5 > maxval[$1] {
    $5 = maxval[$1]
  }
  {print}' input.txt

Explanation:

Firstly, from the variable spacing in your input file I'm assuming it's tab separated text in the original; it looks to be.

By default Awk will parse based on any number of adjacent tabs or spaces to separate fields. That's fine for this file.

But we would like the output to remain neatly aligned, and by default if we're changing some field values, Awk will turn all field separators for that line into single spaces. So we set the Output Field Separator (OFS) equal to a tab character, with the -v flag for setting variables.

For lines where the third field is the string "total" we save the max value in an associative array (sometimes known as a "hash" depending on the programming language you're most familiar with).

If the first field of the line is present in the associative array, and the fifth field is greater than the value stored in the array as the max value for that key, then we change the fifth field of the current line.

Then we print the current line, whether modified or not.

Output on the input provided:

sample1 100A    total   1       1000
sample2 100A    total   1       5584
sample3 100A    total   1       8125
sample4 100A    total   1       59

sample1 .       year    1       1000
sample1 .       week1   20      1000
sample1 .       week2   50      1000

sample2 .       year    1       5584
sample2 .       week1   20      5584
sample2 .       week2   100     5584

sample3 .       year    1       8125
sample3 .       week1   55      8125
sample3 .       week2   100     8125

sample4 .       year    1       59
sample4 .       week1   10      59
sample4 .       week2   8       59