Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Join 2nd column of multiple files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Join 2nd column of multiple files
# 1  
Old 10-28-2015
Join 2nd column of multiple files
Dear All,
I have many files formatted like this:

file1.txt:
Code:
1/2-SBSRNA4	18
A1BG	3
A1BG-AS1	6
A1CF	0
A2LD1	1
A2M	1160

file2.txt
Code:
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

Code:
 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  
Old 10-28-2015
Hello Paolo,

Could you please try following and let me know if this helps you.
Code:
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}' file1 file2

Output will be as follows.
Code:
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
This User Gave Thanks to RavinderSingh13 For This Post:
paolo.kunder
# 3  
Old 10-28-2015
Any attempt from your side?

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

Howsoever, try
Code:
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  
Old 10-28-2015
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..):

Code:
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  
Old 10-28-2015
just add them at the end (file[12] expands to file1 file2). Why don't you try *UCSC* or mayhap a subset of those.
This User Gave Thanks to RudiC For This Post:
paolo.kunder
# 6  
Old 10-28-2015
Many Thansk for your help,
Now it works,
Best Paolo
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join columns across multiple lines in a Text based on common column using BASH

Hello, I have a file with 2 columns ( tableName , ColumnName) delimited by a Pipe like below . File is sorted by ColumnName. Table1|Column1 Table2|Column1 Table5|Column1 Table3|Column2 Table2|Column2 Table4|Column3 Table2|Column3 Table2|Column4 Table5|Column4 Table2|Column5 From... (6 Replies)
Discussion started by: nv186000
6 Replies

2. Shell Programming and Scripting

Difference between 2 files, one with 1 column and 2nd file with multiple columns

Hi, I need to find the difference between 2 files in unix and write the result in the new file File1: A B File2: X 123 hajkd Y 345 adjfka A 123 djafjhd B 678 dsndjks Output file: X 123 hajkd Y 345 adjfka Thanks. (6 Replies)
Discussion started by: nani1984
6 Replies

3. UNIX for Dummies Questions & Answers

Join files by second column

I have file input file1 1/1/2013 A 553.0763397 96 16582 1/1/2013 B 459.8333588 195 11992 1/2/2013 A 844.2973022 306 19555 1/2/2013 B 833.9300537 457 20165 1/3/2013 A 563.6917419 396 13879 1/3/2013 B 632.0749969 169 ... (1 Reply)
Discussion started by: radius
1 Replies

4. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

5. Shell Programming and Scripting

comparing column of two different files and print the column from in order of 2nd file

Hi friends, My file is like: Second file is : I need to print the rows present in file one, but in order present in second file....I used while read gh;do awk ' $1=="' $gh'" {print >> FILENAME"output"} ' cat listoffirstfile done < secondfile but the output I am... (14 Replies)
Discussion started by: CAch
14 Replies

6. Shell Programming and Scripting

Join multiple files based on 1 common column

I have n files (for ex:64 files) with one similar column. Is it possible to combine them all based on that column ? file1 ax100 20 30 40 ax200 22 33 44 file2 ax100 10 20 40 ax200 12 13 44 file2 ax100 0 0 4 ax200 2 3 4 (9 Replies)
Discussion started by: quincyjones
9 Replies

7. Shell Programming and Scripting

Join multiple files by column with awk

Hi all, I searched through the forum but i can't manage to find a solution. I need to join a set of files placed in a directory (~1600) by column, and obtain an output with first and second column common to each file, but following columns are taken from the file in the list (precisely the fourth... (10 Replies)
Discussion started by: macsx82
10 Replies

8. UNIX for Dummies Questions & Answers

Join 2 files using first column

Hi, I'm trying to compare the first column of two files (tab or whitespace delimited, either way's fine, I`ve got both) and print the lines that are identical for the first column of both files. Something like this: File1 AAA 26 49 7 27 36 33 46 75 73 69 AAAAA 4 10 4 7 10 18 21... (2 Replies)
Discussion started by: vanesa1230
2 Replies

9. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

10. Shell Programming and Scripting

Writing out 2nd column into one file from multiple files

I have several files that are being generated every 20 minutes. Each file contains 2 columns. The 1st column is Text, 2nd column is Data. I would like to generate one single file from all these files as follows: One instance of 1st column Text, followed by 2nd column Data separated by... (5 Replies)
Discussion started by: subhap
5 Replies
Login or Register to Ask a Question