Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Joining multiple files based on one column with different and similar values (shell or perl)

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Joining multiple files based on one column with different and similar values (shell or perl)
# 1  
Old 04-27-2011
Joining multiple files based on one column with different and similar values (shell or perl)
Hi,

I have nine files looking similar to file1 & file2 below.

Code:
File1:
  1 ABCA1
   1 ABCC8
   1 ABR:N
   1 ACACB
   1 ACAP2
   1 ACOT1
   1 ACSBG
   1 ACTR1
   1 ACTRT
   1 ADAMT
   1 AEN:N
   1 AKAP1

Code:
File2:
   1 A4GAL
   1 ACTBL
   1 ACTL7
   1 ACTR1
   2 ADAMT
   1 AGPAT
   1 AHNAK
   1 AKAP1
   1 AKR1B
   1 AMT:N
   1 AOX1:
   1 APAF1

I would like to obtain a file like this (with either shell or perl?)

Code:
gene    file1    file2    file3 etc 
A4GAL    0    1    ?
ABCA1    1    0    ?
ABCC8    1    0
ABR:N    1    0
ACACB    1    0
ACAP2    1    0
ACOT1    1    0
ACSBG    1    0
ACTL7    0    1
ACTR1    1    1
etc

Unfortunately, I don't have an idea myself so any help is appreciated!

Best wishes, seqbiologist
# 2  
Old 04-27-2011
See man join. With join, you must use flat files, as it seeks in anticipation of Cartesian products. Files need to be header free, delimited and sorted. If this is a many to one or one to one deal (a simple merge), you could use my m1join.c tool and all piped data for the sort and header removal: https://www.unix.com/shell-programmin...ity-linux.html

Another alternative is the JDBC and unixODBC drivers that treat flat text or CSV files as database tables, so you can express your desire in SQL to jisql or isql (unixODBC).
This User Gave Thanks to DGPickett For This Post:
seqbiologist
# 3  
Old 04-27-2011
Code:
awk '{gene[$2];file[FILENAME];count[$2 FS FILENAME]=$1;}
     END { printf "gene"; for (i in file) printf OFS i; printf RS
           {for (i in gene )
              { printf i;
                 { for (j in file) printf count[i FS j]?OFS count[i FS j]:OFS "0"}
                 printf RS
              }
            }
         }' OFS="\t" file1 file2 file3

This User Gave Thanks to rdcwayx For This Post:
seqbiologist
# 4  
Old 04-27-2011
Hi, DGPickett/
Quote:
Originally Posted by DGPickett
... If this is a many to one or one to one deal (a simple merge), you could use my m1join.c tool and all piped data for the sort and header removal: https://www.unix.com/shell-programmin...ity-linux.html ...
I looked at that thread and I don't see the m1join source. -- or did you mean that you mentioned it there? ... cheers, drl
# 5  
Old 04-28-2011
"join" did the trick - although in a very incompetent way:

join -a1 -a2 -1 2 -2 2 -o 0,1.1,2.1 -e "0" ${genelist001} ${genelist002} | join -a1 -a2 -1 1 -2 2 -o 0,1.2,1.3,2.1 -e "0" - ${genelist003} | join -a1 -a2 -1 1 -2 2 -o 0,1.2,1.3,1.4,2.1 -e "0" - ${genelist004} etc

Thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Paste columns based on common column: multiple files

Hi all, I've multiple files. In this case 5. Space separated columns. Each file has 12 columns. Each file has 300-400K lines. I want to get the output such that if a value in column 2 is present in all the files then get all the columns of that value and print it side by side. Desired output... (15 Replies)
Discussion started by: genome
15 Replies

2. UNIX for Beginners Questions & Answers

Concatenate column values when header is Matching from multiple files

there can be n number of columns but the number of columns and header name will remain same in all 3 files. Files are tab Delimited. a.txt Name 9/1 9/2 X 1 7 y 2 8 z 3 9 a 4 10 b 5 11 c 6 12 b.xt Name 9/1 9/2 X 13 19 y 14 20 z 15 21 a 16 22 b 17 23 c 18 24 c.txt Name 9/1 9/2... (14 Replies)
Discussion started by: Nina2910
14 Replies

3. Shell Programming and Scripting

Extracting values based on line-column numbers from multiple text files

Dear All, I have to solve the following problems with multiple tab-separated text file but I don't know how. Any help would be greatly appreciated. I have access to Linux mint (but not as a professional). I have multiple tab-delimited files with the following structure: file1: 1 44 2 ... (5 Replies)
Discussion started by: Bastami
5 Replies

4. UNIX for Dummies Questions & Answers

Find the average based on similar names in the first column

I have a table, say this: name1 num1 num2 num3 num4 name2 num5 num6 num7 num8 name3 num1 num3 num4 num9 name2 num8 num9 num1 num2 name2 num4 num5 num6 num4 name4 num4 num5 num7 num8 name5 num1 num3 num9 num7 name5 num6 num8 num3 num4 I want a code that will sort my data according... (4 Replies)
Discussion started by: FelipeAd
4 Replies

5. Shell Programming and Scripting

Perl: filtering lines based on duplicate values in a column

Hi I have a file like this. I need to eliminate lines with first column having the same value 10 times. 13 18 1 + chromosome 1, 122638287 AGAGTATGGTCGCGGTTG 13 18 1 + chromosome 1, 128904080 AGAGTATGGTCGCGGTTG 13 18 1 - chromosome 14, 13627938 CAACCGCGACCATACTCT 13 18 1 + chromosome 1,... (5 Replies)
Discussion started by: polsum
5 Replies

6. Shell Programming and Scripting

printing 3 files side by side based on similar values in rows

Hi I'm trying to compare 3 or more files based on similar values and outputting them into 3 columns. For example: file1 ABC DEF GHI file2 DEF DER file3 ABC DER The output should come out like this file1 file2 file3 ABC ABC (4 Replies)
Discussion started by: zerofire123
4 Replies

7. 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

8. Shell Programming and Scripting

PERL - Selecting specific files based on 'date stamp' values

Hi, I've list of files in a directory, which have date stamp value in their names. ex: abc_data_20071102.csv, abc_data_20091221.csv, abc_data_20100110.csv, abc_data_20100222.csv, abc_data_20080620.csv,... etc., I need to select and process only files, within the given date... (4 Replies)
Discussion started by: ganapati
4 Replies

9. Shell Programming and Scripting

joining files based on key column

Hi I have to join two files based on 1st column where 4th column of a2.txt=at and take 2nd column of a1.txt and 3rd column of a2.txt and check against source files ,if matches list those source file names. a1.txt a1|20090809|20090810 a2|20090907|20090908 a2.txt a1|d|file1.txt|at... (9 Replies)
Discussion started by: akil
9 Replies

10. UNIX for Dummies Questions & Answers

Joining files based on multiple keys

I need a script (perl or awk..anything is fine) to join 3 files based on three key columns. The no of non-key columns can vary in each file. The columns are delimited by semicolon. For example, File1 Dim1;Dim2;Dim3;Fact1;Fact2;Fact3;Fact4;Fact5 ---- data delimited by semicolon --- ... (1 Reply)
Discussion started by: Sebben
1 Replies
Login or Register to Ask a Question