AWK sort array by splitting string on particular column to match another array in bash

Question

I have an array a that the following lines

rs6605071   chr1:962943 C   ENSG00000188976 ENST00000487214
rs6605071   chr1:962943 C   ENSG00000187961 ENST00000622660
rs6605071   chr1:962943 C   84069   NM_001160184.1
rs6605071   chr1:962943 C   339451  NC_006462594.2
rs6605071   chr1:962943 C   339451  XR_001737138.1
rs6605071   chr1:962943 C   339451  XM_006710600.3

and another ordered array b that has the following lines:

NC
NG
NM
NP
NR
XM
XP
XR
WP

I would like to order the lines in array a to match the order of array b on column 5 to obtain to desired output:

rs6605071   chr1:962943 C   339451  NC_006462594.2
rs6605071   chr1:962943 C   84069   NM_001160184.1
rs6605071   chr1:962943 C   339451  XM_006710600.3
rs6605071   chr1:962943 C   339451  XR_001737138.1
rs6605071   chr1:962943 C   ENSG00000188976 ENST00000487214
rs6605071   chr1:962943 C   ENSG00000187961 ENST00000622660

I tried to do the following command by splitting on column 5 but it is printing blank lines:

awk -F '\t' -v OFS='\t' 'FNR==NR{split(a[$5],t,"_"); t[1]=$0;next}
{print a[$1]}' <(printf '%s\n' "${a[@]}") <(printf '%s\n' "${b[@]}")

Could you please tell me why my command is not working ? Would a partial match by regex work ?

EDIT 1: changing array a to include lines that can have multiple codes from array b

rs6605071   chr1:962943 C   ENSG00000188976 ENST00000487214
rs6605071   chr1:962943 C   ENSG00000187961 ENST00000622660
rs6605071   chr1:962943 C   84069   NM_001160184.1
rs6605071   chr1:962943 C   339451  NC_006462594.2
rs6605071   chr1:962943 C   ENSG00000135234 ENST00000624144
rs6605071   chr1:962943 C   339451  XR_001737138.1
rs6605071   chr1:962943 C   334324  NC_006462632.2
rs6605071   chr1:962943 C   84333   NM_004353462.1
rs6605071   chr1:962943 C   339451  XM_006710600.3

Expected output:

rs6605071   chr1:962943 C   334324  NC_006462632.2
rs6605071   chr1:962943 C   339451  NC_006462594.2
rs6605071   chr1:962943 C   84069   NM_001160184.1
rs6605071   chr1:962943 C   84333   NM_004353462.1
rs6605071   chr1:962943 C   339451  XM_006710600.3
rs6605071   chr1:962943 C   339451  XR_001737138.1
rs6605071   chr1:962943 C   ENSG00000188976 ENST00000487214
rs6605071   chr1:962943 C   ENSG00000187961 ENST00000622660
rs6605071   chr1:962943 C   ENSG00000135234 ENST00000624144

EDIT 2: Since the answer provided by RavinderSingh13 below did not fully answer my question, I will re-ask the question on how to perform such task with AWK.

Thanks in advance.

Why did you put the lines where $5 does not contain a _ at the end of the desired output? — hek2mgl
– hek2mgl, Commented Feb 7, 2019 at 18:54
@hek2mgl I used $5 because I though it would take 5th column which contains string like XR_001737138.1 and it would split them on _ ... — user324810
– user324810, Commented Feb 7, 2019 at 20:34

RavinderSingh13 · Accepted Answer · 2019-02-07 19:36:24Z

2

I am assuming here that you want to print matching fields of both arrays in order and then remaining non-matched items from array a also you want to print too, if that is the case then following may help you here.

Creating arrays here:

declare -a a=("rs6605071   chr1:962943 C   ENSG00000188976 ENST00000487214
rs6605071   chr1:962943 C   ENSG00000187961 ENST00000622660
rs6605071   chr1:962943 C   84069   NM_001160184.1
rs6605071   chr1:962943 C   339451  NC_006462594.2
rs6605071   chr1:962943 C   339451  XR_001737138.1
rs6605071   chr1:962943 C   339451  XM_006710600.3")
declare -a b=("NC
NG
NM
NP
NR
XM
XP
XR
WP")

Now running following code:

awk -v OFS='\t' '
FNR==NR{
  split($5,a,"_")
  array[a[1]]=$0
  next
}
($1 in array) {
  print array[$0]
  b[$1]
}
END{
  for(i in b){
    delete array[i]
  }
  for(j in array){
    print array[j]
  }
}' <(printf '%s\n' "${a[@]}") <(printf '%s\n' "${b[@]}")

Output will be as follows.

rs6605071   chr1:962943 C   339451  NC_006462594.2
rs6605071   chr1:962943 C   84069   NM_001160184.1
rs6605071   chr1:962943 C   339451  XM_006710600.3
rs6605071   chr1:962943 C   339451  XR_001737138.1
rs6605071   chr1:962943 C   ENSG00000188976 ENST00000487214
rs6605071   chr1:962943 C   ENSG00000187961 ENST00000622660

answered Feb 7, 2019 at 19:36

RavinderSingh13

135k14 gold badges61 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user324810 Over a year ago

@RavinderSingh13 when using this code, it outputs the correct order but only one line, for example, the line NM in array a. I only gave a small subset but there can be multiple lines having the same code of array b like so rs6605071 chr1:962943 C 84069 NM_001160184.1 rs6605071 chr1:962943 C 84062 NM_004420144.5

RavinderSingh13 Over a year ago

I am riding bike as of now. If your actual input_file is not same as shown samples then please do add correct samples. Will look into it once I am on system

user324810 Over a year ago

@RavinderSingh13 yes I will edit my post, it's slightly different since I only wanted to keep it as simple as possible

user324810 Over a year ago

Bump on this post to have some feedback on the newly edited code ... sorry

Collectives™ on Stack Overflow

AWK sort array by splitting string on particular column to match another array in bash

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related