-4

I am trying to merge two columns into one column. My dataset look like this:

RSID1 RSID2
rs7475652 rs7475652
rs7475652 rs7918643
rs7475652 rs3125034
rs7475652 rs3750730
rs7475652 rs883728
rs7475652 rs4881500
rs7475652 rs3853288
rs7475652 rs4881504
rs7475652 rs2242271
rs7475652 rs7099607
rs7475652 rs10904597
rs7475652 rs3207775

As you can see there is some repeat values. I want to first combine column 2 with column 1, then I am planning to use uniq command to remove any duplicates. I want to combine both the columns and then remove the duplicate.

Here is the first part of the expected output for this example:

rs7475652
rs7475652
rs7475652
rs7918643
rs7475652
rs3125034
rs7475652
rs3750730
rs7475652
rs883728 
6
  • 2
    We don't know what your result would be given the data in your question.
    – Kusalananda
    Aug 27 at 22:19
  • rs7475652 rs7475652 rs7475652 rs7918643 rs7475652 rs3125034 rs7475652 rs3750730 rs7475652 rs883728 I just want to merge column 2 to the end of column1. then I will remove duplicates using uniq command
    – priyanka
    Aug 27 at 22:22
  • 1
    Update your question, don't put clarifications in comments.
    – Kusalananda
    Aug 27 at 22:25
  • For your sample data, once you get past the heading, column 1 is constant and all the values in column 2 are already uniq. Are you asking to split the line at the space between the 2 columns?
    – icarus
    Aug 27 at 22:51
  • No. I have more data. This is just a subset. In my data there are two columns 1 and 2. In some places column1 has repetitive values but its not constant.
    – priyanka
    Aug 27 at 23:41
1

Guessing that the question means that the desired output is

RSID1
RSID2
rs7475652
rs7475652
rs7475652
rs7918643
rs7475652
rs3125034
rs7475652
rs3750730
rs7475652
rs883728
rs7475652
rs4881500
rs7475652
rs3853288
rs7475652
rs4881504
rs7475652
rs2242271
rs7475652
rs7099607
rs7475652
rs10904597
rs7475652
rs3207775

then

awk '{print $1 "\n" $2}'

will do it. Awk could also handle the next step that the OP wants to do, make the output unique. The code to do this depends on if the output should be pairwise unique so each line is different to just the previous one, or globally unique where each line is different to all previous ones.

1
  • Yes, This work for me. I first sorted the values after using your command and the use uniq command to get only unique rsid values
    – priyanka
    Aug 27 at 23:59
0

@icarus awk is effectively just replacing with \n and you can achieve the same objective with other tools such as

cat file | tr ' ' '\n'    # Naughty me! See UUOC below.

or

sed "s/ /\n/g" file 

These will reduce a space separated row of 2 or more elements to a single "column" or perhaps more correctly, place each element on it's own line.

As for the additional space noted in the comments ....

sed "s/ /\n/g" file | tr -d ' ' 

Introducing the extra complexity makes awk all the more attractive, but in that case we could also

grep -Po "[^ ]+" file

UUOC Award

I would like to express my gratitude to @edmorton and the team for this wondrous award, and also to my friends and peers who made this all possible. Not forgetting my dog, Spot etc. etc.. It will take pride of place next to my ZX-80 and the collection of used spoons which have been gracefully accumulating in the sink since 1980.

Elevating @edmorton comment from below

tr ' ' '\n' < file
5
  • I know you can use tr or sed. If you look at the data in the question however you will see that there were spaces at the ends of the lines except the last, so I used awk to extract the fields and print them, rather than have blank lines in the output.
    – icarus
    Aug 28 at 16:47
  • Was not clear that the space was intended from the question, but then, you need only break on the first space? sed "s/ /\n/" file
    – bu5hman
    Aug 29 at 17:47
  • I agree it is not clear that the space was intended. Indeed my first thought was tr as it is much faster. However testing my answer before posting it revealed the extra space at the end of the line. Yes you could use sed to convert the first space to a newline, and then add a second clause to remove a space at the end of the line otherwise that space will cause the resulting lines to differ when passed to sort and uniq.
    – icarus
    Aug 30 at 3:30
  • Hence the final | to tr in the updated post ...... in the end though it is just what suits. A "hard coded" 2-column awk approach works for your case, and that's fine. The others will deal with unknown numbers of "columns", outside the strict scope of your question, but may be useful to A.N.Other.
    – bu5hman
    Aug 30 at 4:27
  • 1
    cat file | tr ' ' '\n' = tr ' ' '\n' < file without the UUOC.
    – Ed Morton
    Aug 31 at 21:30
-1

Considering the filename as a.txt and the separator is \t we can use the following command:

cat a.txt | awk -F"\t" '{print $1 "\n" $2}' > b.txt

Now b.txt will contain the expected output

2
  • 2
    What does this add to my answer that was posted 11 hours earlier?
    – icarus
    Aug 28 at 19:27
  • 1
    @icarus Well, it adds a UUOC :-).
    – Ed Morton
    Aug 31 at 21:29

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.