3

I have "N" number of files with the contents shown in the image, and I want to merge and append the 5th column

The files are tab separated.

I have "N" number of files whose contents are as shown in the screenshot above. I want to merge them and append the 5th column. The first 4 columns are the same.

I tried with the awk command below for files 1 and 2:

awk 'NR==FNR{a[NR]=$0;next} {print a[FNR] "\t",$5}' file1 file2

it only appends for 2 files, not more than 2.

How can I do this correctly using awk, paste or other tools?

  • 1
    remove the picture of your data and add your actual data. Make it easy for us to reproduce your code. – glenn jackman Dec 31 '15 at 12:20
  • I did not get your need, you ask to add the 5th column from file2 to file1? – Eng7 Dec 31 '15 at 13:06
  • Please edit your question and i) replace the screenshot with the actual text so we can copy it and test our solutions. ii) explain what you want to happen with the multiple files. If you have 12 files, should the 5th column of all of them be included? iii) Do all the files have the exact same entries and only the 5th column is missing? – terdon Dec 31 '15 at 13:06
  • Hi i tried to add the 2 file content in a tabular format, but the indentation is getting mess as normal text so had to take screen shot. and Yes i want to append the only 5th column – Kiran Jan 1 '16 at 9:44
2

If the first four columns are identical across files you could run something like

set -- file*
fields="-f-5,$(seq -s, 10 5 $((5*$#)))"
paste "$@" | cut ${fields%?} >outfile

This will paste all files then extract fields 1-5 and every 5th field after.

  • Hi, I tried with above command but it gave error as " cut: you must specify a list of bytes, characters, or fields" – Kiran Jan 3 '16 at 16:13
  • Its cut (GNU coreutils) 8.4 – Kiran Jan 4 '16 at 4:31
2

Without testing:

awk -F "\t" '
    { key = $1 FS $2 FS $3 FS $4; values[key] = values[key] FS $5 }
    END { for (key in values) print key values[key] }
' file ...

The header

For each file, you want to extract part of the filename and use that as the header. We'll keep track of the header with a separate string, appending to it for each file.

awk -F "\t" '
    BEGIN { header = "col1" FS "col2" FS "col3" FS "col4" }
    { 
        key = $1 FS $2 FS $3 FS $4
        values[key] = values[key] FS $5 
    }
    FNR == 1 { 
        split(FILENAME, a, /_/)
        header = header FS a[2] 
    }
    END { 
        print header
        for (key in values) 
            print key values[key] 
    }
' file ...

We initialize the header in the BEGIN block. Give the first 4 columns whatever header titles you require.

The variable FNR is the record number of the current file. When FNR == 1 we are on the first line of this file. The awk variable FILENAME holds the name of the file currently being processed.


Sorting

If you use GNU awk then you can do this in the END block (reference):

    END { 
        print header
        # order the array by index, as strings, ascending
        PROCINFO["sorted_in"] = "@ind_str_asc"
        for (key in values) 
            print key values[key] 
    }

If you don't have GNU awk, you can do this:

awk '...' | {
    read header
    echo "$header"
    sort
}
  • I think the OP's column 4 differs from file to file and the key is the first 2 fields. So this would munge the output. Easily fixed. – Otheus Dec 31 '15 at 14:50
  • The fact that im on a mobile and can't read... or count – Otheus Dec 31 '15 at 15:15
  • Hi Glen, thanks for the reply, its working fine, but to add on to it, while appending can we keep header as its file name so as to track from which file its been appended, i have all file names as "P4318_34071_LIB17790_NMPNL.Coverage_AvrgDep.bed" want to split file name with "_", and keep header as 2nd element i.e "34071" Thank you – Kiran Jan 3 '16 at 16:18
  • @Glen Is it also possible to keep the order of the 1st 4 columns same in the output, because 1st 4 columns are identical across all the files as shown in above sample file. Thank you – Kiran Jan 3 '16 at 16:32
  • Yes, both those things are possible. Have you done any research about how to implement them? – glenn jackman Jan 3 '16 at 17:39

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.