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


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers how to join two files using "Join" command with one common field in this problem?
# 1  
Old 04-13-2012
Question how to join two files using "Join" command with one common field in this problem?

file1:
Code:
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
Regina:996194:651036:September 1, 1905:8
Edmonton:3183312:661848:September 1, 1905:9
St.John's:517000:405212:March 31, 1949:1
Yellowknife:42800:1346106:July 15, 1870:11
Whitehorse:31200:482443:June 13, 1898:12
Iqaluit:29300:2093190:April 1, 1999:13

file2:
Code:
Alberta:9:AB
British Comumbia:10:BC
Manitoba:7:MB
New Brunswick:3:NB
Newfoundland and Labrador:1:NL
Northwest Territories:11:NT
Nova Scotia:4:NS
Nunavut:13:NU
Ontario:6:ON
Prince Edward Island:2:PE
Quebec:5:QC
Saskatchewan:8:SK
Yukon Territories:12:YT


here,I want output like this way-->> Toronto:Ontario:6:ON
so,I tried using "join" command this way -->> join -t: -j1 5 -j2 2 -o 1.1 2.1 2.2 2.3 file1 file2
which gives me this output,
"join: file 1 is not in sorted order
join: file 2 is not in sorted order
Edmonton:Alberta:9:AB"

So,i dont understand here why it's giving me only one output line and error that files are not sorted.Do i need to sort the files before joining them?
please help me,I'm a newbie here!
Thanks.

Last edited by radoulov; 04-13-2012 at 06:54 AM.. Reason: Code tags!
# 2  
Old 04-13-2012
join.

Yes, files need to be sorted on the join field(s).

EDIT: e.g.
Code:
bash-3.2$ join -t: -1 5 -2 2 -o 1.1 2.1 2.2 2.3 <(sort -t: -k5 file1) <(sort -t: -k2 file2)
Victoria:British Comumbia:10:BC
Yellowknife:Northwest Territories:11:NT
Whitehorse:Yukon Territories:12:YT
Iqaluit:Nunavut:13:NU
Charlottetown:Prince Edward Island:2:PE
Fredericton:New Brunswick:3:NB
Halifax:Nova Scotia:4:NS
Quebec City:Quebec:5:QC
Toronto:Ontario:6:ON
Winnipeg:Manitoba:7:MB
Regina:Saskatchewan:8:SK
Edmonton:Alberta:9:AB


Last edited by CarloM; 04-13-2012 at 06:47 AM..
This User Gave Thanks to CarloM For This Post:
# 3  
Old 04-13-2012
Thanks..that solves my problem!
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

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

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

4. Shell Programming and Scripting

join files based on a common field

Hi experts, Would you please help me with this? I have several files and I need to join the forth field of them based on the common first field. here's an example... first file: 280346 39.88 -75.08 547.8 280690 39.23 -74.83 538.7 280729 40.83 -75.08 499.2 280907 40.9 -74.4 507.8... (5 Replies)
Discussion started by: GoldenFire
5 Replies

5. Web Development

Perl join two files by "common" column

Hello; I am posting to get any help on my code that I have been struggling for some time. The project is to join two files each with 80k~180k rows. I want to merge them together by the shared common column. The problem of the shared column is partially matching, not exactly the same. File1:... (5 Replies)
Discussion started by: yifangt
5 Replies

6. Shell Programming and Scripting

Remove ":" and join lines in outline file

I have a vim outliner file like this: Title title 2 :Testing now :testing 2 :testing 3 title 3 :testing :ttt :ttg Is there a way to use a script or command to remove... (7 Replies)
Discussion started by: jostber
7 Replies

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

8. UNIX for Dummies Questions & Answers

Explanation of "total" field in "ls -l" command output

When I do a listing in one particular directory (ls -al) I get: total 43456 drwxrwxrwx 2 root root 4096 drwxrwxrwx 3 root root 4096 -rwxrwxr-x 1 nobody nobody 3701594 -rwxrwxr-x 1 nobody nobody 3108510 -rwxrwxr-x 1 nobody nobody 3070580 -rwxrwxr-x 1 nobody nobody 3099733 -rwxrwxr-x 1... (1 Reply)
Discussion started by: proactiveaditya
1 Replies

9. Shell Programming and Scripting

Join file contents via common field

I have 2 files with a common parm - Jobname File 1 0507 1202 JOBA 0507 1302 JOBB 0507 1452 JOBC 0507 1552 JOBA 0507 1553 JOBA File2 JOBA abcdefg server4 JOBB defghij server22 JOBC vwxyz12 server55 I would like to take each line from File1 and match the jobname with the jobname... (8 Replies)
Discussion started by: Northerner
8 Replies

10. Shell Programming and Scripting

join two lines when the second line contains "US DOLLAR"

I want to join two lines together when the second line contains "US DOLLAR". How to do that with sed? Original file: # cat testdata Sep. 24, 2005 Sep. 26, 2005 PHARMA PLUS DRUGMART 1149 $5.85 Sep. 25, 2005 Sep. 27, 2005 99 RESTAURANT #015 $11.00 US DOLLAR @ 1.203636 $13.24 ... (2 Replies)
Discussion started by: powah
2 Replies
Login or Register to Ask a Question