Madza Farias-Virgens
Madza Farias-Virgens

Reputation: 1071

Divide each column by another column

I have

V1           V2      V3      V4            sites  var_homorefref  var_homodevdev  var_hetdevdev  var_hetrefdev
NC_044998.1  14582   80739   LOC100221041  596    1256            156             7              319
NC_044998.1  31388   68748   DCBLD2        338    617             88              4              171
NC_044998.1  80874   299341  CMSS1         2873   6246            1183            51             2024
NC_044998.1  112495  297570  FILIP1L       2434   5388            996             31             1736
NC_044998.1  287349  289742  LOC116808959  14     49              0               0              6
NC_044998.1  300404  343805  TBC1D23       364    909             158             3              305
NC_044998.1  333622  344667  NIT2          84     163             44              0              57
NC_044998.1  346168  368957  TOMM70        177    361             85              0              159
NC_044998.1  371654  380427  LNP1          116    308             66              6              126

I would like to divide $6-$9 by $5*11

I am trying

awk 'BEGIN {FS=OFS="\t"} FNR>1 {for(i=6; i<=9; i++) print $1 OFS $2 OFS $3 OFS $4 OFS $5 OFS ($5?$i/($5*11):"0")}' file

but it's calculating only for $6.

it's also not outputting rows where fields in $i is 0, instead of outputting "0" and maintaining the total numbers of rows.

Upvotes: 0

Views: 110

Answers (2)

anubhava
anubhava

Reputation: 786359

You may use this awk script:

awk 'BEGIN {FS=OFS="\t"} NR>1 && $5!=0 {for (i=6; i<=9; ++i) $i /= ($5*11)} 1' file | column -t


V1           V2      V3      V4            sites  var_homorefref  var_homodevdev  var_hetdevdev  var_hetrefdev
NC_044998.1  14582   80739   LOC100221041  596    0.19158         0.023795        0.00106772     0.0486577
NC_044998.1  31388   68748   DCBLD2        338    0.165949        0.0236686       0.00107585     0.0459925
NC_044998.1  80874   299341  CMSS1         2873   0.197639        0.0374332       0.00161377     0.0640446
NC_044998.1  112495  297570  FILIP1L       2434   0.20124         0.0372003       0.00115784     0.064839
NC_044998.1  287349  289742  LOC116808959  14     0.318182        0               0              0.038961
NC_044998.1  300404  343805  TBC1D23       364    0.227023        0.0394605       0.000749251    0.0761738
NC_044998.1  333622  344667  NIT2          84     0.176407        0.047619        0              0.0616883
NC_044998.1  346168  368957  TOMM70        177    0.185413        0.0436569       0              0.0816641
NC_044998.1  371654  380427  LNP1          116    0.241379        0.0517241       0.00470219     0.0987461

Upvotes: 1

RavinderSingh13
RavinderSingh13

Reputation: 133780

it's also not outputting rows where fields in $i is 0, instead of outputting "0" and maintaining the total numbers of rows.

I believe that's happening because your Input_file isn't tab delimited(at least shown samples). To see that let's run following with your samples once.

awk 'BEGIN{FS="\t"} FNR>1{print "First field.." $1}' Input_file
First field..NC_044998.1  14582   80739   LOC100221041  596    1256            156             7              319

You could see we got whole line in place of 1st field, so we need NOT to use BEGIN{FS="\t"} until/unless Input_file is really TAB delimited.



Could you please try following once. This is saving fields 6 to 9 values as per logic of $i/($5*11) and save it in current field itself, so that only 1 line will be printed.

awk '
FNR>1{
  for(i=6; i<=9; i++){
    $i=($5!="" && $5?($i/($5*11)):"0")
  }
}
1' Input_file | column -t

OR with your shown attempts, if you want to print whole line(1st to 5th field) on a separate line along with (6th to 9th fields) then try following.

awk '
FNR>1{
  for(i=6; i<=9; i++){
    print $1 OFS $2 OFS $3 OFS $4 OFS $5 OFS ($5!="" && $5?($i/($5*11)):"0")
  }
}' Input_file

Upvotes: 0

Related Questions