Join 2nd column of multiple files

Shell Programming and Scripting
1

Dear All,
I have many files formatted like this:

file1.txt:

1/2-SBSRNA4	18
A1BG	3
A1BG-AS1	6
A1CF	0
A2LD1	1
A2M	1160

file2.txt

1/2-SBSRNA4	53
A1BG	1
A1BG-AS1	7
A1CF	0
A2LD1	3
A2M	2780

I would like to produce a file like this, keep only once the first column and cat the second column of each file, with the column header the filename

 file1.txt	file2.txt
1/2-SBSRNA4	18	53
A1BG	3	1
A1BG-AS1	6	7
A1CF	0	0
A2LD1	1	3
A2M	1160	2780

Any help?
Many thanks,
paolo

2

Hello Paolo,

Could you please try following and let me know if this helps you.

awk 'BEGIN{for(i=1;i<=ARGC;i++){O=O?O OFS ARGV:ARGV};print O} FNR==NR{A[$1]=$0;next} ($1 in A){print A[$1] OFS $NF}' file1 file2

Output will be as follows.

file1 file2
1/2-SBSRNA4 18 53
A1BG 3 1
A1BG-AS1  6 7
A1CF 0 0
A2LD1 1 3
A2M 1160 2780

Thanks,
R. Singh

1 Like
3

Any attempt from your side?

---------- Post updated at 10:24 ---------- Previous update was at 10:21 ----------

Howsoever, try

awk 'FNR==1{TTL=TTL OFS FILENAME} {VAL[$1]=VAL[$1] OFS $2} END {print TTL; for (v in VAL) print v, VAL[v]}' OFS="\t" file[12]
4

Hi R. Singh

I tried yor script with awk 'BEGIN{for(i=1;i<=ARGC;i++){O=O?O OFS ARGV[i]:ARGV[i]};print O} FNR==NR{A[$1]=$0;next} ($1 in A){print A[$1] OFS $NF}' *filename*

actually the header of all fiels are present, but only columns of two files are reported ( Ihave more than 1000 files..):

file1   file2   file3   file4   
1/2-SBSRNA4	187 102
A1BG	114 203
A1BG-AS1	552 772

---------- Post updated at 04:33 AM ---------- Previous update was at 04:28 AM ----------

Dear RudiC,
Yes I tried, otherwise I won't be here, I am a bioinformatic and I trust in you guys,
I have thousands file, where should I put in your script my filenames ( I would like to select those that are named *UCSC*)

thanks
Paolo

5

just add them at the end (file[12] expands to file1 file2). Why don't you try *UCSC* or mayhap a subset of those.

1 Like
6

Many Thansk for your help,
Now it works,
Best Paolo