0

I am creating a file via this command:

awk '{print $2 " "$7" "$8}' REACTOME_EXTENSION_OF_TELOMERES.xls |  awk '$8!="No"  {print $1 " " $2}' | awk 'NR>1' | awk 'BEGIN { OFS=", "; print "Name" " " "0" };{ print $0 " " "" }'

The output is this:

    Name 0
WRAP53 0.08495288 
NHP2 0.17606254 
POLA1 0.25320756 
POLD3 0.32372433 
PRIM1 0.38140765 
RFC5 0.44302294 
POLD1 0.497649 
...

I need a command which would subtract every adjacent line in 2nd column and give me this result:

WRAP53 0.0849529 
NHP2 0.0911097 
POLA1 0.077145 
POLD3 0.0705168 
PRIM1 0.0576833 
RFC5 0.0616153 
POLD1 0.0546261 
...

I know how to do it when I just keep the 2nd column, it would be like this:

awk '{print $2 " "$7" "$8}' REACTOME_EXTENSION_OF_TELOMERES.xls |  awk '$8!="No"  {print $1 " " $2}' | awk 'NR>1' | awk 'BEGIN { OFS=", "; print "Name" " " "0" };{ print $0 " " "" }' | awk '{print $NF}' | awk 'NR-1{print $0-p}{p=$0}'

But how to do it so that I preserve the 1st column as shown above?

REACTOME_EXTENSION_OF_TELOMERES.xls file looks like this:

NAME    PROBE   GENE SYMBOL     GENE_TITLE      RANK IN GENE LIST       RANK METRIC SCORE       RUNNING ES      CORE ENRICHMENT
row_0   WRAP53  null    null    163     1.5818238258361816      0.08495288      Yes
row_1   NHP2    null    null    201     1.5055444240570068      0.17606254      Yes
row_2   POLA1   null    null    283     1.3435969352722168      0.25320756      Yes
row_3   POLD3   null    null    367     1.240567684173584       0.32372433      Yes
row_4   PRIM1   null    null    501     1.1049883365631104      0.38140765      Yes
row_5   RFC5    null    null    557     1.0596935749053955      0.44302294      Yes
row_6   POLD1   null    null    653     1.0035457611083984      0.497649        Yes

It would be great if I could have the output of the whole command into: REACTOME_EXTENSION_OF_TELOMERES.y

1

Your entire awk pipeline can be replaced by

awk 'NR > 1 && $8 != "No" {print $2, $7 - prev} {prev = $7}' REACTOME_EXTENSION_OF_TELOMERES.xls

which outputs

WRAP53 0.0849529
NHP2 0.0911097
POLA1 0.077145
POLD3 0.0705168
PRIM1 0.0576833
RFC5 0.0616153
POLD1 0.0546261
  • Perfect, thank you so much! – anikaM Apr 23 at 15:32
1
$ awk 'BEGIN { OFS=FS } $8 == "No" { next } { tmp = $7 } NR > 2 { $7 -= prev } { prev = tmp; print }' inputfile
NAME    PROBE   GENE SYMBOL     GENE_TITLE      RANK IN GENE LIST       RANK METRIC SCORE       RUNNING ES      CORE ENRICHMENT
row_0   WRAP53  null    null    163     1.5818238258361816      0.08495288      Yes
row_1   NHP2    null    null    201     1.5055444240570068      0.0911097       Yes
row_2   POLA1   null    null    283     1.3435969352722168      0.077145        Yes
row_3   POLD3   null    null    367     1.240567684173584       0.0705168       Yes
row_4   PRIM1   null    null    501     1.1049883365631104      0.0576833       Yes
row_5   RFC5    null    null    557     1.0596935749053955      0.0616153       Yes
row_6   POLD1   null    null    653     1.0035457611083984      0.0546261       Yes

The awk program, annotated with comments:

# Set output delimiter to input delimiter (tab, set with -F)
BEGIN { OFS = FS }

# Skip lines whose 8th column is "No"
$8 == "No" { next }  # or { exit } if "No"-lines are sorted at the end.

# Save the original value in column 7.
{ tmp = $7 }

# For any row past both the header and the first data line,
# decrease column 7 by the previous row's column 7 value.
NR > 2 { $7 -= prev }

# Remember the current row's original column 7 value
# in prev and print the (possibly) modified row.
{
    prev = tmp
    print
}

Redirect the output to a new filename to store it:

awk '...as above...' inputfile >outputfile
  • Thanks I had to tweak it a little bit because I have this Yes/No condition on teh 8th column but yes it is along these lines: awk '$8!="No" {print}' ISHIDA_E2F_TARGETS.xls | awk -F ' ' 'BEGIN { OFS=FS } { tmp = $7 } NR > 2 { $7 -= prev } { prev = tmp; print }' | awk '{print $2 " "$7}' > ISHIDA_E2F_TARGETS.GY – anikaM Apr 22 at 20:39
  • @anikaM What should happen if the 8th column says No? It's very seldom necessary to pipeline two awk programs together. – Kusalananda Apr 22 at 20:41
  • @anikaM I've updated. – Kusalananda Apr 22 at 20:44
  • well if it say "No" then I don't want it in my output, I just want rows where it says "Yes" , that is why I put: awk '$8!="No". Also it is space separated file – anikaM Apr 22 at 20:46
  • @anikaM It can't be a space separated file as there are a variable number of spaces between each column. If the columns are separated by multiple spaces, just remove -F '\t'. What should happen with the calculation of column 7 if the previous row was deleted since it contained No in its 8th column? – Kusalananda Apr 22 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.