0

I have a tab-separated file that looks like this:

cg13201342  F   ARNT;ARNT;ARNT;CTSK 3'UTR;3'UTR;3'UTR;TSS1500
cg05269359  F   SCN4B;SCN4B;SCN4B;SCN4B 3'UTR;3'UTR;3'UTR;Body
cg06018296  R   NEK3;NEK3;NEK3;NEK3 3'UTR;3'UTR;3'UTR;Body
cg05172994  F   WDR20;WDR20;WDR20;WDR20 3'UTR;3'UTR;3'UTR;Body

Desired output:

cg13201342  F   ARNT   3'UTR
cg13201342  F   ARNT   3'UTR
cg13201342  F   ARNT   3'UTR
cg13201342  F   CTSK   TSS1500
cg05269359  F   SCN4B  3'UTR
.
.

and so on

I tried

awk 'BEGIN {
       FS = OFS = "\t"
     }
     {
       n = split($3, f, " *;*")
       for (i=1; i<=n; i++)
         print $1, f[i]
     }' probe-genes-regions >chk

but that is only splitting the third column. I want the last column to split together with the second column and form rows with 1st field of 3rd column and 1st field of last column and so on respectively

Improve this question
7
  • 1
    Hi. We're not a free code writing service, but we can help you arrive at a solution on your own. Please describe what you've tried so far. Commented Aug 24, 2022 at 9:49
  • Hi, Please provide more details and the code you wrote to do it, so someone will try to help you Commented Aug 24, 2022 at 9:53
  • Ah, I see now, you want to "explode" each line into multiple ones based on the last field? It would be good if you could say this explicitly in the question itself. Also mention what to do with duplicate lines in the output. Commented Aug 24, 2022 at 9:55
  • I want to explode each line based on second and third and last field. Need output also as tab delimited. I can remove duplicate fields with sort -u later Commented Aug 24, 2022 at 9:59
  • 1
    Note that ;* matches on zero or more ;s so " *;*" also matches one the empty string. Maybe you meant " *; *" instead. Commented Aug 24, 2022 at 10:36

3 Answers 3

Reset to default
0

Split fields 3 and 4 on semi-colon into seperate arrays, then iterate over them, e.g. (assuming field 3 and 4 have the same number of elements):

parse.awk

BEGIN { OFS = "\t" }

{ 
  n = split($3, a, /;/); split($4, b, /;/)
  for(i=1; i<=n; i++)
    print $1, $2, a[i], b[i] 
}

Run it like this:

awk -f parse.awk infile

Output:

cg13201342      F       ARNT    3'UTR
cg13201342      F       ARNT    3'UTR
cg13201342      F       ARNT    3'UTR
cg13201342      F       CTSK    TSS1500
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   Body
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    Body
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   Body
Improve this answer
13
  • Note that this appears to split WDR20 3'UTR into two fields, at least the way the output is written in the answer. Also, when I run this on the original tab-delimited data, I only get three columns of output. Are you assuming the user has first pre-processed the data somehow? Commented Aug 24, 2022 at 10:25
  • Also note that length() generally returns the length of a string. If you use a particular awk that supports returning the length of arrays, then you need to mention which one it is (probably GNU awk, right?). Commented Aug 24, 2022 at 10:32
  • @Kusalananda: Yes I was assuming tsv input, the edit removes that assumption. I tested this script with GNU awk, mawk, busybox awk and nawk. I don't know of any awk that does not support length on arrays. Commented Aug 24, 2022 at 10:38
  • length(array) is not POSIX. bwk added it to his one in 2002, but you can imagine that some BSD or SysV based systems are still based on earlier versions. In any case, you can do n = split(...) like in the OP's code to avoid having to use length(array). Commented Aug 24, 2022 at 10:42
  • 1
    @Kusalananda, the input sample has 4 whitespace delimited fields though. Commented Aug 24, 2022 at 10:46
0

Using Miller (mlr):

sed 's/;/\t/' file | mlr --nidx --fs tab nest --evar ';' -f 4

This first uses sed to replace the first ; on each line with a tab, separating the third tab-delimited field into two separate fields. GNU sed can insert a tab character using \t with the s command, but not all sed implementations may. If your's can't, then type a literal tab character in place of \t by pressing Ctrl+V+Tab.

Miller then reads and writes tab-delimited data (--nidx --fs tab) and will "explode" (or "un-nest") each record based on the ;-delimited sub-fields of the fourth tab-delimited field (nest --evar ';' -f 4).

The output, given the data in the question:

cg13201342      F       ARNT    ARNT
cg13201342      F       ARNT    ARNT
cg13201342      F       ARNT    CTSK 3'UTR
cg13201342      F       ARNT    3'UTR
cg13201342      F       ARNT    3'UTR
cg13201342      F       ARNT    TSS1500
cg05269359      F       SCN4B   SCN4B
cg05269359      F       SCN4B   SCN4B
cg05269359      F       SCN4B   SCN4B 3'UTR
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   Body
cg06018296      R       NEK3    NEK3
cg06018296      R       NEK3    NEK3
cg06018296      R       NEK3    NEK3 3'UTR
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    Body
cg05172994      F       WDR20   WDR20
cg05172994      F       WDR20   WDR20
cg05172994      F       WDR20   WDR20 3'UTR
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   Body

Passing this through uniq would remove duplications of adjacent lines.

Using only awk:

awk -F '\t' 'BEGIN { OFS=FS }
    {
        nf = split($3,a,";")
        for (i = 2; i <= nf; ++i) print $1, $2, a[1], a[i]
    }' file

This splits the third field on ; and then, for the second sub-field on of the third field, proceeds to output the first two fields together with the first sub-field of the original third field.

The output of this is identical to that of the pipeline at the top of this answer.

Improve this answer
0
0

Using any POSIX awk and no matter what the spaces are in your input:

$ awk -F'[[:space:];]+' -v OFS='\t' '{
    n=(NF-2)/2; for (i=1; i<=n; i++) print $1, $2, $(2+i), $(2+i+n)
}' file
cg13201342      F       ARNT    3'UTR
cg13201342      F       ARNT    3'UTR
cg13201342      F       ARNT    3'UTR
cg13201342      F       CTSK    TSS1500
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   3'UTR
cg05269359      F       SCN4B   Body
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    3'UTR
cg06018296      R       NEK3    Body
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   3'UTR
cg05172994      F       WDR20   Body
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.