Merging columns from multiple files and adding headers

Question

I have over 50 files that have the following format:

File1.tab

A 25
B 56
C 87
D 82
E 91
F 34

File2.tab

A 89
B 55
C 92
D 36
E 28
F 19

File3.tab

A 98
B 76
C 82
D 76
E 49
F 42

etc.

I would like the following:

• The columns merged together based on the first column.
• each column to have the file name as a header.
• The values separated by a tab not space so when I open the merged file in excel it will have multiple columns (i.e column1= "row1 is empty, row2 is A and so on"; column2= "row1 is File1.tab, row2 is 25 and so on"; column3= "row1 is File2.tab, row2 is 89 and so on"; etc).

final.tab

  File1.tab File2.tab File3.tab
A    25        89        98
B    56        55        76
C    87        92        82
D    82        36        76
E    91        28        49
F    34        19        42

I tried searching for answers and followed the following command: from Merge multiple files by first column

Print with headers: for the multiple files version:

$ hdr() { awk 'FNR==1{ print "\0", FILENAME }1' "$1"; }
$ join -a1 -a2 -e 1 -o auto <(hdr file1) <(hdr file2) >join.tmp
$ for file in rest_files*; do
     join -a1 -a2 -e 1 -o auto join.tmp <(hdr "$file") >join.tmp.1
     mv join.tmp.1 join.tmp
  done
$ tr -d '\0' <join.tmp >final.file

When I opened the output in excel, I had the following problems:

• The headers were assigned twice in two rows
• Columns from the first and second files were added twice and then the files was added again at the end.
• All the values and headers were separated by a space and just assigned to one column in excel.

Here is what it looked like:

final.tab

 File1.tab File2.tab File3.tab join.temp
 File1.tab File2.tab File3.tab File1.tab
A 25 89 25 89 98 25
B 56 55 56 55 76 56
C 87 92 87 92 82 87
D 82 36 82 36 76 82
E 91 28 91 28 49 91
F 34 19 34 19 42 34

Can you fix the current code or provide me with a new one that will do I need?

Answer 1

I come with a full awk solution (since you are using awk).

BEGIN {
    fname=""
    f=0
}

NR == FNR {
    col[NR]=$1
    colmax=NR
}

 { if ( FILENAME != fname ) {
     f++
     fname=FILENAME
     colfname[f]=fname
     }
     col2[FNR "-" f]=$2
 }

END {
    for ( j=1 ; j<= f; j++ ) printf "\t%s",colfname[j]
    printf "\n"
    for ( i=1 ; i<=colmax ; i++ ) {
    printf "%s",col[i]
    for ( j=1 ; j<= f; j++ ) printf "\t%s",col2[i "-" j]
    printf "\n"
    }
}

to be called as

awk -f merge.awk f?.tab

gives

    f1.tab  f2.tab  f3.tab
A   25      89      98
B   56      55      76
C   87      92      82
D   82      36      76
E   91      28      49
F   34      19      42

---

initialize variable

BEGIN {
    fname=""
    f=0
}

on first file, collect first column name

NR == FNR {
    col[NR]=$1
    colmax=NR
}

when FILENAME change, collect it (after), and remember it

 { if ( FILENAME != fname ) {
     f++
     fname=FILENAME
     colfname[f]=fname
     }

collect value of secund column

     col2[FNR "-" f]=$2
 }


END {

dump filenames

    for ( j=1 ; j<= f; j++ ) printf "\t%s",colfname[j]
    printf "\n"

dump values

    for ( i=1 ; i<=colmax ; i++ ) {
    printf "%s",col[i]
    for ( j=1 ; j<= f; j++ ) printf "\t%s",col2[i "-" j]
    printf "\n"
    }
}

Answer 2

You can try using python

file_list = ['File1.tab', 'File2.tab', 'File3.tab']
df = pd.DataFrame()
for file in file_list:
    temp_df = pd.read_csv(file, header=None, names=[file[:-4]])
    df = pd.concat([df, temp_df], axis=1)
    
print(df)

     File1     File2     File3
A    25        89        98
B    56        55        76
C    87        92        82
D    82        36        76
E    91        28        49
F    34        19        42