1

I have an input file looks like this

N1518    AMP,AUG,AZM,CHL 
N1520    AZM,NAL
N1524    AMP,NAL,STR
......

I'm trying to convert it to

N1518    AMP
N1518    AUG
N1518    AZM
N1518    CHL
N1520    AZM
N1520    NAL
N1524    AMP
N1524    NAL
N1524    STR
....

Is there an one liner to do this?

0

You can use awk with multiple separators, and then iterate over fields to print the columns.

awk -F'[ ,]+' '{for (i=2;i<=NF;i++) {printf "%s %s\n",$1,$i;}}' file

Explanation:

-F'[ ,]+' : This tells awk to use both space and comma as the field separator. It also tells it to consider consecutive separators as a single separator.

for (i=2;i<=NF;i++) : loops over all columns starting from the second one, because we have to print the first column repeatedly.

printf "%s %s\n",$1,$i; : prints a line with the first column and the ith column

  • Works. Just replaced the space with tab since it is tab separated file. Nice explanation. – palc Jun 10 '19 at 4:58
0

Try this,

awk -F '[\t,]' '{for (i=2;i<NF;i++) print $1"\t"$i}' file
N1518 AMP
N1518 AUG
N1518 AZM
N1518 CHL
N1520 AZM
N1524 AMP
N1524 NAL
  • 1
    Thanks. It works perfectly. It is tab separated so I replaced the space with a tab ` '[\t ,]'` – palc Jun 10 '19 at 4:55
0

Tested with below script and it worked fine too

count_line=awk '{print NR}' filename| sort -nr| sed -n '1p'

for ((i=1;i<=$count_line;i++)); do fi=`awk -v i="$i" 'NR==i{print $1}' filename`; h=`awk -v i="$i"  'NR==i{print $2}' filename|awk -F "," '{print NF}'|  sort -nr | sed -n '1p'`; for ((j=1;j<=$h;j++)); do echo $fi;awk -v i="$i" -v j="$j" 'NR==i{print $2}' filename| awk -v j="$j" -F ","  '{print $j}' ; done; done|sed "N;s/\n/ /g"

output

N1518 AMP
N1518 AUG
N1518 AZM
N1518 CHL
N1520 AZM
N1520 NAL
N1524 AMP
N1524 NAL
N1524 STR
0

With GNU sed in extended regex mode -E option, your problem is doable as:

$ sed -Ee 's/^((\S+\t)[^,]+),/\1\n\2/;P;D' inp

Output:

N1518   AMP
N1518   AUG
N1518   AZM
N1518   CHL
N1520   AZM
N1520   NAL
N1524   AMP
N1524   NAL
N1524   STR

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.