select and order columns in linux join

Posted on 2011-05-09
Last Modified: 2012-05-11

I can use this command
 join -1 x -2 y file1 file2

to join 2 files on the xth column of file1 and the yth column of file2.

But is there a way to only retrieve certain columns from file1 and file2 and to order these columns in the new file

e.g. file 1 has cols 1 2 3 and file 2 has cols 1 2 3 4
I want to join on col1 in both files and retrieve all cols from file 1 and only col 4 from file 2

in this example cols 1 2 3 are the same in both files so i am effectively merging them to add col4 but I have more complicated examples than this where the 2 files only have a couple of columns in common and I want to merge them into a unifed file containing one copy of each column

Question by:andieje
    LVL 31

    Accepted Solution

    Would you consider something like
    join -1 1 -2 1 file1 file2 | awk '{print $1,$2,$3,$7}'
    LVL 38

    Assisted Solution

    > order these columns in the new file
    Assume you want to sort the col1
    Use farzanj's script and pipe to sort
    join -1 1 -2 1 file1 file2 | awk '{print $1,$2,$3,$7}' | sort -k1,1 > new_file


    Author Closing Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I am a long time windows user and for me it is normal to have spaces in directory and file names. Changing to Linux I found myself frustrated when I moved my windows data over to my new Linux computer. The problem occurs when at the command line.…
    Introduction We as admins face situation where we need to redirect websites to another. This may be required as a part of an upgrade keeping the old URL but website should be served from new URL. This document would brief you on different ways ca…
    Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
    Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now