How to count values between empty cells

user_10950470 0
Viewed 166

I'm facing one problem which is bigger than me. I have 18 relative large text files (ca 30k lines each) and I need to count the values between the empty cells in the second column. Here is a simple example of my file:

Metabolism
line_1    10.2
line_2    10.1
line_3    10.3
TCA_cycle
line_4    10.7
line_5    10.8
Pyruvate_metab
line_6   100.8

In reality, I have circa 500 description lines (Metabolism, TCA_cycle, etc.) and the range of lines is between zero to a few hundred.

I would like to count values for each block (block starts with a description and corresponding lines are always below), e.g.

Metabolism  30.6
line_1    10.2
line_2    10.1
line_3    10.3
TCA_cycle   21.5
line_4    10.7
line_5    10.8
Pyruvate_metab 100.8
line_6   100.8

Or just

30.3
21.5
100.8

It won't be a problem if results will be printed line by line into an additional file... Or another alternative way.

There is one tricky thing and it's descriptions without lines with numbers.

Transport
line_1000   100.1
line_1001   100.2
Cell_signal
Motility
Processing
Translation
line_1002   500.1
line_1003   200.2

And even for those lines and would like to get 0 value.

Transport     200.3
line_1000   100.1
line_1001   100.2
Cell_signal   0
Motility      0
Processing    0
Translation   700.3
line_1002   500.1
line_1003   200.2

The rest of the file looks same and it's consistent - 2 columns, tab separators, descriptions in the first column, values in the second, no spaces (only underlines).

Actually I have no experience with more sophisticated coding so I really don't know how to solve it in the command line. I've already tried some Excel ways but it was painful and unsuccessful.

awk
3 Answers
user_37768580

Could you please try following, written and tested with shown samples in GNU awk.

awk '
FNR==NR{
  if($0!~/line/){  a[$0]; prev=$0 }
  else          {  a[prev]+=$NF   }
  next
}
!/line/{
  $0=$0 OFS (a[$0]?a[$0]:0)
}
1'  Input_file  Input_file

OR in case you want output in good looking form add column -t to above command like as follows:

awk '
FNR==NR{
  if($0!~/line/){  a[$0]; prev=$0 }
  else          {  a[prev]+=$NF   }
  next
}
!/line/{
  $0=$0 OFS (a[$0]?a[$0]:0)
}
1'  Input_file  Input_file  |  column -t

Explanation: Adding detailed explanation for above code.

awk '                                           ##Starting awk program from here.
FNR==NR{                                        ##Checking FNR==NR which will be TRUE when Input_file is being read first time.
  if($0!~/line/){  a[$0]; prev=$0 }             ##checking condition if line contains string line and setting index of current line in a and setting prev value to current line.
  else          { a[prev]+=$NF    }             ##Else if line not starting from line then creating array a with index prev variable and keep on adding last field value to same index of array.
  next                                          ##next will skip all further statements from here.
}
!/line/{                                        ##Checking if current line doesnot have line keyword in it then do following.
  $0=$0 OFS (a[$0]?a[$0]:0)                     ##Re-creating current line with its current value then OFS(which is space by default) then either add value of a[$0] or 0 based on current line value is NOT NULL here.
}
1                                               ##Printing current line here.
' Input_file  Input_file                        ##Mentioning Input_file names here.
user_58665800

In plain awk:

awk '{
    if (NF == 1) {
        if (blockname)
            printf("%s\t%.2f\n%s", blockname, sum, lines)
        blockname = $0
        sum = 0
        lines=""
    } else if (NF == 2) {
        sum += $2 
        lines = lines $0 "\n"
    }
    next
}
END { printf("%s\t%.2f\n%s", blockname, sum, lines) }
' input.txt
user_138090010
Related