Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Perl join two files by "common" column
Top Forums Web Development Perl join two files by "common" column Post 302494091 by yifangt on Friday 4th of February 2011 11:50:34 PM
Old 02-05-2011
Re: Perl join two files by "common" column
Thanks durben_tyler!

I could not show either file as they are big (180000 rows and 90000rows). That's why I need some help here. What I posted were just part of the files and they do not have common columns at first sight. But, if I search through the rest of the file, for sure there are matches between them. I should have be more careful with the example part. Here I rearranged the first several rows of each file.

File1:
Code:
ID Sequence
GT_Xhyb_CTGSIN-SS-mira_assembly_rep_c3140|919|60    TACATCCTCC...
GT_Specific_CTGSIN-SS-mira_assembly_rep_c24|266|60    TTCTACCTATC...
GT_Specific_CTGSIN-SS-mira_assembly_rep_c3|1386|60_New    TTTTCTT...
GT_Xhyb_SUPCTG-SS-SuperContig_mira_assembly_rep_c29|737|60  CGTTTGA...
GT_Specific_CTGSIN-SS-mira_assembly_rep_c8|392|60    TACTCTTGT...
.......

Note the "mira_assembly_rep_c3140" are the shared part in columns1 of File1.
In File2:
Code:
ID_Part Gene_Symbol Description
mira_assembly_rep_c5    AT4G25140.1    OLEO1 (OLEOSIN 1) 
mira_assembly_rep_c8    AT4G27140.1    2S seed storage protein 1 / 2S albumin storage protein / NWMU1-2S albumin 1 
mira_assembly_rep_c24    AT5G38195.1    protease inhibitor/seed storage/lipid transfer protein (LTP) family protein 
mira_assembly_rep_c29    AT5G39850.1    40S ribosomal protein S9 (RPS9C) 
mira_assembly_rep_c36    AT4G32100.1    galactosyltransferase

In File2, the ID_Part column is partially matching the Column1 (ID) of File1. in my code; I used condition to test the match:
Code:
if ($x =~ m/$F[$col2]\|/)

The merged file contains the full ID, sequence and gene symbol and function description of the gene.
Code:
GT_Xhyb_CTGSIN-SS-mira_assembly_rep_c3140|919|60    TACATCCTCC...   mira_assembly_rep_c3140 AT4G25140.1    OLEO1 (OLEOSIN 1) 
GT_Specific_CTGSIN-SS-mira_assembly_rep_c5|266|60    TTCTACCTATC...  mira_assembly_rep_c5 AT4G10270.1    wound-responsive family protein 
GT_Specific_CTGSIN-SS-mira_assembly_rep_c8|1386|60_New    TTTTCTTTATAAAGAACA...  -mira_assembly_rep_c8 AT2G33830.2    dormancy/auxin associated family protein 
GT_Xhyb_SUPCTG-SS-SuperContig_mira_assembly_rep_c29|737|60    CGTTTGAATGTATGAC...mira_assembly_rep_c29   AT3G49910.1    60S ribosomal protein L26 (RPL26A)
......

The merged file will be appended to another database with the ID as identifier. That's why I need the two files merged.

Quote:
The two files do not seem to match even partially!
The string "mira_assembly_rep_c" is present in line 1 of File 1, and it is present in at the beginning of all lines of File 2.

Other than there, there is nothing in common.
Actually "mira_assembly_rep_c3" instead of "mira_assembly_rep_c" was used for matching test, that's why I append a vertical bar "|" at the end of the string
Code:
$x=~ m/$F[$col2]\|/

Quote:
What's the logic for the merged file then?
The logic is tricky as the match is the middle part of the string.

Quote:
Why is the 2nd line of merged file as follows?
Maybe it is a typo because the row became too long to see, and was wrapped. Anyway, there are five fields:
ID Sequence Part_ID gene_symbol Description.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

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

3. Shell Programming and Scripting

awk command to replace ";" with "|" and ""|" at diferent places in line of file

Hi, I have line in input file as below: 3G_CENTRAL;INDONESIA_(M)_TELKOMSEL;SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL My expected output for line in the file must be : "1-Radon1-cMOC_deg"|"LDIndex"|"3G_CENTRAL|INDONESIA_(M)_TELKOMSEL"|LAST|"SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL" Can someone... (7 Replies)
Discussion started by: shis100
7 Replies

4. UNIX for Dummies Questions & Answers

how to join two files using "Join" command with one common field in this problem?

file1: Toronto:12439755:1076359:July 1, 1867:6 Quebec City:7560592:1542056:July 1, 1867:5 Halifax:938134:55284:July 1, 1867:4 Fredericton:751400:72908:July 1, 1867:3 Winnipeg:1170300:647797:July 15, 1870:7 Victoria:4168123:944735:July 20, 1871:10 Charlottetown:137900:5660:July 1, 1873:2... (2 Replies)
Discussion started by: mindfreak
2 Replies

5. Shell Programming and Scripting

Substituting comma "," for dot "." in a specific column when comma"," is a delimiter

Hi, I'm dealing with an issue and losing a lot of hours figuring out how i would solve this. I have an input file which looks like this: ('BLABLA +200-GRS','Serviço ','TarifaçãoServiço','wap.bla.us.0000000121',2985,0,55,' de conversão em escada','Dia','Domingos') ('BLABLA +200-GRR','Serviço... (6 Replies)
Discussion started by: poliver
6 Replies

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

7. Shell Programming and Scripting

Problem of Perl's "join" function

$ perl -e '@f=("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa","1","911"); print join("\t",@f)."\n";' aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ... (5 Replies)
Discussion started by: carloszhang
5 Replies

8. UNIX for Dummies Questions & Answers

How to join 2 .txt files based on a common column?

Hi all, I'm trying to join two .txt file tab delimitated based on a common column. File 1 transcript_id gene_id length effective_length expected_count TPM FPKM IsoPct comp1000201_c0_seq1 comp1000201_c0 337 183.51 0.00 0.00 0.00 0.00 comp1000297_c0_seq1 ... (1 Reply)
Discussion started by: alisrpp
1 Replies

9. Shell Programming and Scripting

Delete all log files older than 10 day and whose first string of the first line is "MSH" or "<?xml"

Dear Ladies & Gents, I have a requirement to delete all the log files in /var/log/test directory that are older than 10 days and their first line begin with "MSH" or "<?xml" or "FHS". I've put together the following BASH script, but it's erroring out: for filename in $(find /var/log/test... (2 Replies)
Discussion started by: Hiroshi
2 Replies

10. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies
All times are GMT -4. The time now is 04:30 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy