CODEWITHSUNDEEP
pythonperlawk
Ramon
Ramon

Reputation: 99

How to combine many text files in a tab-delimited format?

I have hundreds of files with this format (two columns in the files are seperated by space).

file1:

abcd 2
efgh 1
ijkl 3
mnop 4

file2:

abcd 1
efgh 2
mnop 2

file3:

mnop 1

I want all results in a tab-delimited file in this format:

      abcd efgh ijkl mnop
file1   2   1     3    4
file2   1   2     0    2
file3   0   0     0    1

So, first I need to transpose the two-columns files to two-rows. I tried with this awk command but didn't work:

awk '!(NR%2){print}' FS='\n' OFS='\t' RS= file1.txt > file1-tr.txt

The hard part to combine all files after transpose and make a single file with ZERO input where there is no value on certain entry in files. Not sure what program can do this?!

Upvotes: 0

Views: 673

Answers (1)

karakfa
karakfa

Reputation: 67467

awk to the rescue!

$ awk -v OFS='\t' '{f=FILENAME; fs[f]; vs[$1]; a[f,$1]=$2} 
               END {for(v in vs) printf "%s", OFS v; 
                    print ""; 
                    for(f in fs) 
                      {printf "%s", f OFS; 
                       for(v in vs) printf "%s", a[f,v]+0 OFS; 
                       print ""}}' file{1..3} | 
  column -ts'\t'

Upvotes: 2

Related Questions