2

I have several columns of data. I always have the same number of rows (say 5). In the 2nd column, I want to multipy the first value by 5, then the second value by 4, the third value by 3, etc. I then want to sum these values, and divide by the sum of the values in the second column. How would I do this in sed and/or awk?

Example:

4 5 7 1 2 3
5 1 2 3 1 2
4 2 3 6 1 2
3 4 1 6 3 3
2 3 1 2 1 6

Answer: (5*5 + 4*1 + 3*2 + 2*4 + 1*3)/(5 + 1 + 2 + 4 + 3) = 3.067

2
  • 1
    Is there some reason you want to do this with sed/awk?
    – derobert
    Commented Feb 19, 2014 at 22:47
  • I just wanted to automate the process without having to resort to a spreadsheet.
    – markn
    Commented Feb 20, 2014 at 0:54

2 Answers 2

4

Replace 6 with total (number of lines + 1) if needed:

awk '{mult+=$2*(6-NR); sum+=$2;} END {print mult/sum;}' yourfile.txt 

Displays: 3.06667

1
  • @markn No problem, you can check the tick (under the downvote arrow) if you consider this answer correct for you.
    – Totor
    Commented Feb 20, 2014 at 9:04
2

If in case total number of lines are not known

$ cat file
4 5 7 1 2 3
5 1 2 3 1 2
4 2 3 6 1 2
3 4 1 6 3 3
2 3 1 2 1 6

$ awk 'FNR==NR{t = NR+1;next}{mult+=$2*(t-FNR);sum+=$2} 
       END{print mult/sum}' file{,}
3.06667

Explanation

awk 'FNR == NR{t=NR+1;next}

FNR variable --> Current line number in the current file

NR variable-->The total number of lines seen so far

The condition FNR == NR can only be true while awk is reading the first file of argument

t=NR+1 --> variable t holds line count +1 extra since it's required in our current context

next--> Stops the processing of the current input record and proceeds with the next input record

mult+=$2*(t-FNR) --> variable mult holds some of (5*(t-FNR) + ..... + 4*1 + 3*2 + 2*4 + 1*3)

sum+=$2 --> variable sum holds sum of column2

END--> AnEND rule is executed, once, after all the input has been read

print mult/sum --> Finally print mult/sum

file{,} --> you can also file file file but i used shortcut this bash trick you can say, try echo file{,} and echo file{,,} on your terminal.

Good Luck

Akshay.

You must log in to answer this question.

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