How can I use awk to calculate sum and replace column in file

user_14498029 0
Viewed 329

I'm new to the site and to the programming world and I hope you have time to help me. My problem is as follows: I have a file with several columns. In the 2nd column there are values. I'm tring to calculate the sum of each values to a given number and to replace the second column with a new column containing the results of the sum. Here an example of my input:

A B C
x 1 t
y 2 u
z 3 v

I want to sum values in B column to 5 and obtain an output like the one below:

A B C
x 6 t
y 7 u
z 8 v

The code I tried unsucesfully is

zcat my_file.vcf.gz| tail -n +49 | awk 'BEGIN{FS=OFS="\t"} {print $0, $2+5}'>my.output.vcf

Thanks in advance

awk
6 Answers

We could avoid using tail since printing of lines from 49th line could be handled within awk itself. Also you need to add value in 2nd field and then you could print the whole line itself by print command.

Important point, as per OP's sample if 2nd field is having alphabets then need NOT to add 5 in it, so taken care of that condition too here.

zcat my_file.vcf.gz | 
awk '
  BEGIN{ FS=OFS="\t" }
  FNR>=49{
    $2=($2~/[a-zA-Z]/?$2:$2+5)
    print
  }
' > my.output.vcf
user_58665800

You can use

awk 'BEGIN{FS=OFS="\t"} {$2+=5}1'

Here, $2+=5 will add 5 to Filed 2 value, and 1 will trigger the display of the record (row, line, same as print $0).

See an online awk demo:

#!/bin/bash
s='A    B   C
x   1   t
y   2   u
z   3   v'
awk 'BEGIN{FS=OFS="\t"} {$2+=5}1' <<< "$s"

Output:

A   5   C
x   6   t
y   7   u
z   8   v
user_38329700

Another form for clarity:

awk 'BEGIN{FS=OFS="\t"} {print $1, $2+5, $3}'
user_41623560

you can use:

awk 'BEGIN {FS=OFS="\t"} NR == 1 {print $0}   NR > 1 {print $1,($2+5),$3;}'

output:

A       B       C
x       6       t
y       7       u
z       8       v
user_9833250

Maube this can help you:

cat file | awk '{if (NR > 1 && $2 = ($2+5)) 
                     print $0;
                else print $0;}'

Answer apply to your code:

zcat my_file.vcf.gz| tail -n +49 | awk '{if (NR > 1 && $2 = ($2+5)) print $0; else print $0;}' > my.output.vcf
user_94971260 
cat boo
A       B       C
x       1       t
y       2       u
z       3       v

cat boo | awk 'BEGIN{FS=OFS="\t"} $2 ~ /^[0-9]+$/ {print $1, $2+5, $3}  $2 !~ /^[0-9]+$/ {print} '
A       B       C
x       6       t
y       7       u
z       8       v
user_36380810
Related