2

Using awk, to this table I want to add a column where first row is "INFO" and rest of the rows are all "1".

$ gunzip -c foo.gz | head
SNPID   CHR BP  Allele1 Allele2 Freq1   Effect  StdErr  P.value TotalN
rs1000033   1   226580387   t   g   0.8266  -0.0574 0.0348  0.09867 17310
rs1000050   1   162736463   t   c   0.8545  0.0654  0.0461  0.1564  10864

where

gunzip -c foo.gz | head | cat -A
SNPID^ICHR^IBP^IAllele1^IAllele2^IFreq1^IEffect^IStdErr^IP.value^ITotalN^M$
rs1000033^I1^I226580387^It^Ig^I0.8266^I-0.0574^I0.0348^I0.09867^I17310^M$
rs1000050^I1^I162736463^It^Ic^I0.8545^I0.0654^I0.0461^I0.1564^I10864^M$

Since it's a .gz file I used

gunzip -c foo.gz | \
  awk 'BEGIN {FS="\t"; OFS="\t"} NR == 1 {print $0 OFS "INFO"} NR > 1 {print $0 OFS "1"}' | \
  gzip > foo.V2.gz

For some reason this seems to change me a column name but not the expected column at the end.

$ gunzip -c foo.V2.gz | head   
SNPID   INFO    BP  Allele1 Allele2 Freq1   Effect  StdErr  P.value TotalN
--------^
rs1000031   1   226580387   t   g   0.8266  -0.0574 0.0348  0.09867 17310
rs1000051   1   162736463   t   c   0.8545  0.0654  0.0461  0.1564  10864

Weirdly enough, when I cat -A it the column appears to be where it should be.

$ gunzip -c foo.V2.gz | head | cat -A                                      
SNPID^ICHR^IBP^IAllele1^IAllele2^IFreq1^IEffect^IStdErr^IP.value^ITotalN^M^IINFO$
----------------------------------------------------------------------------^ 
rs1000033^I1^I226580387^It^Ig^I0.8266^I-0.0574^I0.0348^I0.09867^I17310^M^I1$
rs1000050^I1^I162736463^It^Ic^I0.8545^I0.0654^I0.0461^I0.1564^I10864^M^I1$

I'd like to know,

  1. what's happening here?
  2. can I trust gunzip -c foo.V2.gz | head or gunzip -c foo.V2.gz | head | cat -A now??
  3. how to get my expected output using gunzip -c foo.V2.gz | head

SNPID   CHR BP  Allele1 Allele2 Freq1   Effect  StdErr  P.value TotalN INFO
rs1000033   1   226580387   t   g   0.8266  -0.0574 0.0348  0.09867 17310 1
rs1000050   1   162736463   t   c   0.8545  0.0654  0.0461  0.1564  10864 1

Note, I'm using a config script defining SNPID=1; CHR=2; ... where I am depending on the column numbers I'm specifying being correct for the subsequent analyses.

1
  • @steeldriver Just noticed and seded away using sed 's/\r$//', but your solution might be better. Would I add it {FS=OFS="\t"; RS=ORS="\r\n"}?
    – jay.sf
    Nov 30, 2023 at 21:30

4 Answers 4

4

As already mentioned you have DOS line endings. See why-does-my-tool-output-overwrite-itself-and-how-do-i-fix-it for a description of the issue and possible solutions, for example using any awk:

gunzip -c foo.gz |
  awk -v OFS='\t' '{sub(/\r$/,""); print $0, (NR>1 ? 1 : "INFO")}' |
  gzip > foo.V2.gz

You could use RS="\r\n" but a multi-char RS is a GNU awk extension that's recently been adopted by 1 or 2 other awk variants. With any other POSIX-compliant awk setting RS="\r\n" will be treated the same as if you set RS="\r" since per POSIX RS can only be a single literal character. It'll also fail on systems where the underlying C primitives strip the \r from the end of lines before awk sees them so RS="\r?\n" is a bit more robust. With any awk you can leave RS as it's default value of \n and add {sub(\r$/,"")} as the first statement of the script.

I tidied up a couple of other things in your script too, e.g. removing the code setting variables you don't need or already have that value, changing your 2 print statements into 1, using OFS as designed, and getting rid of the unnecessary escapes at the end of lines after a pipe symbol.

1
  • 2
    Thanks for the careful refinement and streamlining of the awk command and the additional hints, from which much can be learned.
    – jay.sf
    Dec 1, 2023 at 9:31
2

Your input seems to be some sort of TSV file with Microsoft line endings.

Then you could use mlr instead of awk that supports 2 sorts of tsv and can specify the record delimiter.

  1. with --tsv values are tab separated, but \\, \t, \r, \n can be used to embed \, TAB, CR and LF characters in the fields.
  2. with --tsvlite values are tab separated and it's not possible to embed line delimiters or tabs in the field values.

Here, since you only want to add an extra column with neither header nor values containing any of those characters, using one or the other is not going to make a difference.

By default, mlr accepts either CRLF (Microsoft) or LF (Unix) line delimiters and outputs lines delimited with LF (Unix). But you can pass --rs crlf for it to output lines delimited with CRLF (Microsoft).

So:

< foo.gz gunzip |
  mlr --tsvlite put '$INFO = 1' |
  gzip > foo.V2.gz

To get a Unix tsv on output and accept Microsoft or Unix tsv on input.

< foo.gz gunzip |
  mlr --tsvlite --rs crlf put '$INFO = 1' |
  gzip > foo.V2.gz

To get a Microsoft tsv on output and accept Microsoft or Unix tsv on input.

Since version 6.0.0, mlr has built-in support to read gzip-compressed files, so you can also do:

mlr --tsvlite put '$INFO = 1' foo.gz |
  gzip > foo.V2.gz

(pass the --gzin option if the file path doesn't end in .gz).

mlr (miller, generally shipped in a miller package) is a tool specialised in processing tabular data. It can take a number of verbs as argument for different actions to perform on the records such as sort, cut, join, filter...

put is the one used to make modifications on the records using a simple domain-specific language not totally unlike that of awk.

In that language, like in awk, $ is used to reference fields in the records except those fields are named¹. With $INFO = 1, we're giving the INFO field a numeric² value of 1 for each record. If that field didn't exist already, it's added and will show up as an extra column.


¹ though can also be numbered like when processing header-less csv/tsv with --implicit-csv-header.

² you'd use $INFO = "1" to set it as a string, which would make a different for output formats such as JSON, but not tsv where there's no indication of type.

3
  • 1
    @EdMorton see edit. Dec 2, 2023 at 6:56
  • @StéphaneChazelas Thanks for showing alternative solution +1. Are there speed differences (don't know how benchmark in bash)? Could both, awk and mlr be set to manipulate in place (probably difficult with a .gz)?
    – jay.sf
    Dec 2, 2023 at 8:54
  • @StéphaneChazelas got it, thanks.
    – Ed Morton
    Dec 2, 2023 at 12:39
1

As @steeldriver noted in comments, the file appeared to be Windows contaminated with carriage return (CR) and line feed (LF) characters, we can remove setting record separators RS="\r\n" but ORS="\n" to not reintroduce the same issue.

gunzip -c foo.gz | \
  awk 'BEGIN {FS=OFS="\t";RS="\r\n";ORS="\n"} NR == 1 {print $0 OFS "INFO"} NR > 1 {print $0 OFS "1"}' | \
  gzip > foo.V2.gz
0
1

To add <tab>INFO on the first line or <tab>1 on subsequent lines just before the line delimiter whether that line-delimiter is LF (Unix) or CR followed by LF (Microsoft), you can do:

<foo.gz gunzip |
  perl -pe 's/(?=\r?$)/$. == 1 ? "\tINFO" : "\t1"/e' |
  gzip > foo.V2.gz

To edit the file in-place, in theory you should be able to use the -i option along with the PerlIO::gzip IO layer (may need to be installed separately):

perl -Mopen=IO,gzip -i -pe 's/(?=\r?$)/$. == 1 ? "\tINFO" : "\t1"/e' foo.gz

Though I find that with my version, it fails with a Can't do inplace edit on foo.gz: Cannot make temp name: Inappropriate ioctl for device. which sounds like a bug to me.

You must log in to answer this question.

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