1

I want to separate the names in the same line into separate individual pairs.

TMPRSS2|pp9284 AADAT Sample1
ERG    TMPRSS2|pp9284 Sample2
TMPRSS2|pp9284 ETV1 Sample3
PDE4A   MIA|MIA-RAB4B|RAB4B|RAB4B-EGLN2|EGLN2 Sample4

This should be

TMPRSS2 AADAT Sample1
pp9284 AADAT Sample1
ERG    TMPRSS2 Sample2
ERG  pp9284 Sample2
TMPRSS2 ETV1 Sample3
pp9284 ETV1 Sample3
PDE4A MIA Sample4
PDE4A MIA-RAB4B Sample4
PDE4A RAB4B Sample4
PDE4A RAB4B-EGLN2 Sample4
PDE4A EGLN2 Sample4

The multiple names are in both the columns so I want individual pairs.I have tried mysql for this as after I have individual pairs I want to compare it with a another table. strsplit in R only works well when the multiple names are in a fixed number.but they vary in each line. Can I do this using sed,awk?

Also there are certain columns,whose values remain same and basically they remain same for the values that were in the same line .How should I print those out .I added the 3rd column,whose values get replicated into new lines.for this I used print $3 inside the for loop to print them out as well,but dos not work.

2
  • Please add an example to illustrate the second requirement (i.e. the lines that should remain the same). Commented Feb 27, 2015 at 20:44
  • I added an example.
    – Ron
    Commented Feb 27, 2015 at 20:51

3 Answers 3

4
$ awk '{split($1,a,"|"); split($2,b,"|"); for (i in a) {for (j in b) print a[i],b[j];}}' file
TMPRSS2 AADAT
pp9284 AADAT
ERG TMPRSS2
ERG pp9284
TMPRSS2 ETV1
pp9284 ETV1
PDE4A RAB4B-EGLN2
PDE4A EGLN2
PDE4A MIA
PDE4A MIA-RAB4B
PDE4A RAB4B

To also print the third (SampleN) field, you can just add $3 to the print statement inside the loops, i.e.

$ awk '{split($1,a,"|"); split($2,b,"|"); for (i in a) {for (j in b) print a[i],b[j],$3;}}' file
TMPRSS2 AADAT Sample1
pp9284 AADAT Sample1
ERG TMPRSS2 Sample2
ERG pp9284 Sample2
TMPRSS2 ETV1 Sample3
pp9284 ETV1 Sample3
PDE4A RAB4B-EGLN2 Sample4
PDE4A EGLN2 Sample4
PDE4A MIA Sample4
PDE4A MIA-RAB4B Sample4
PDE4A RAB4B Sample4
4
  • Also there are certain columns,whose values remain same and basically they duplicate .How should I print those out .I added $3,$4 inside the for loop.
    – Ron
    Commented Feb 27, 2015 at 20:35
  • @ron, don't add requirements as comments to someone's answer -- put them in your question Commented Feb 27, 2015 at 20:36
  • the code for gene names work perfect,its just i forgot to mention this.
    – Ron
    Commented Feb 27, 2015 at 20:49
  • i was trying the same thing but the problem was the txt file was in a classic mac format.I changed to unix format to be readable by awk
    – Ron
    Commented Feb 27, 2015 at 21:05
4

With bash:

# important to use parentheses, not braces, to localize changes to IFS
# the variable is purposefully unquoted
split_pipe() ( IFS='|'; echo $1 )

while read -r first second third; do
  for word1 in $(split_pipe "$first"); do
    for word2 in $(split_pipe "$second"); do
      echo $word1 $word2 $third
    done
  done
done < file
1
  • You need to set -f here or you might glob. The example data doesn't include any glob characters, but you never know.
    – mikeserv
    Commented Feb 27, 2015 at 23:22
1

With GNU sed you can do:

sed -E 's/(\|[^ |]+) /\1| /
        s/(([^|]* )?([^|]*))\|(([^ ]*)(.*))/\1\6\n\2\4/
        /\n/P;D' <infile

...which prints...

TMPRSS2 AADAT Sample1
pp9284 AADAT Sample1
ERG    TMPRSS2 Sample2
ERG    pp9284 Sample2
TMPRSS2 ETV1 Sample3
pp9284 ETV1 Sample3
PDE4A   MIA Sample4
PDE4A   MIA-RAB4B Sample4
PDE4A   RAB4B Sample4
PDE4A   RAB4B-EGLN2 Sample4
PDE4A   EGLN2 Sample4

It works by dividing up and printing then deleting in piecemeal the contents of a line on extra \newline characters. The P command Prints only up to the first occurring \newline in pattern space, so you can fairly easily print only a portion of your edit buffer as you like.

In this case for every pipe-divided section which does not contain spaces sed puts all of the bits at either end of the edit buffer in twice. sed inserts the leftmost and rightmost ends to the left and right of the left-most pipe-divided section and follows that sequence with a \newline, it then puts the same selections to either end of all of the pipe-divided sections that remain on the right-hand side of its inserted \newline. So sed can Print only the bit on the left if there is a \newline in pattern-space at all, then Delete only up to the first occurring \newline in pattern-space and try again.

The very first substitution only ever happens once - it just appends a pipe to the end of the pipe-divided sections so there will always be a pipe to separate on - even for the last occurrence. The rest of the time, sed does the one s///ubstitution, Prints the first line in pattern-space, then Deletes same. When it cannot do that any longer it Deletes all of pattern-space and pulls in the next input line automatically.

A POSIX BRE for doing the same thing could be written:

sed '   s/\(|[^ |]\{1,\}\) /\1| /
        s/^\(\( *[^ |]*  *\)*\([^ |]*\)\)|\(\([^ ]*\)\(.*\)\)/\1\6\
\2\4/;   /\n/P;D' <infile

You must log in to answer this question.

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