Unix/Linux Go Back    


Shell Programming and Scripting BSD, Linux, and UNIX shell scripting — Post awk, bash, csh, ksh, perl, php, python, sed, sh, shell scripts, and other shell scripting languages questions here.

Problem with Join Command

Shell Programming and Scripting


Tags
join, solved, sort, unix

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 08-12-2016   -   Original Discussion by Varshha
Varshha's Unix or Linux Image
Varshha Varshha is offline
Registered User
 
Join Date: Jun 2014
Last Activity: 18 October 2016, 1:48 AM EDT
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
Problem with Join Command

I have 2 files. File 1 is a daily file with only a bunch of IDs and a date column. File 2 has all the dump of IDs and their respective cost. I basically want an inner join. When I am picking a few rows from these files and joining, they work perfectly fine. But when I join the full files together, I get no response. Below are the sample files



Code:
File 1 :
01223610248 , 04/07/2009
01223612562 , 08/19/2003
01223617098 , 02/01/2005
01223618661 , 12/13/2005
01223619159 , 05/29/2007
01223620423 , 02/06/2007
01256957092 , 04/22/2003
01256959417 , 12/19/2006
01256959597 , 02/08/2005
01256970382 , 10/12/2004
01256970722 , 03/15/2005
01256972774 , 10/11/2005
01256975064 , 05/02/2006
01256976030 , 03/21/2006
933604300140 , 10/27/2008
933736900872 , 05/06/2016
933604300128 , 03/14/2008



Code:
File 2:
933604300140 ,20.64
933736900872 ,18.56
933604300128 ,20.64
67119603398 ,0.64
67261704102 ,0.65
75072313652 ,0.65
02454397033 ,0.70
02454397537 ,0.70
03139824387 ,0.70
03139824388 ,0.70
76218230730 ,0.70
77898802256 ,0.70
88843006240 ,0.70
63410597392 ,0.81
84315600053 ,0.82
63447926391 ,0.97
06004450461 ,0.98
60161702121 ,0.98
79862230382 ,0.98
79862230662 ,0.98



A simple join command works for this. But when I join the original File 2 which is around 19 mb on the server with the original file 1 (96 kb) , I get no output at all.

using

Code:
join file 2 file1

works for the above sample files.

I have tried the following commands for the full files:


Code:
join -t, file2 file1



Code:
 join -t, -1 1 -1 2 file2 file1

nothing seems to be working with the original files.

I also tried the following code lines:



Code:
awk 'NR=FNR{check[$0];next} $2 in check' File2 File1

and




Code:
cat File2 | while read line; do  grep $line File1; done

What am I missing here? Please help!

Thanks
Sponsored Links
    #2  
Old Unix and Linux 08-12-2016   -   Original Discussion by Varshha
Don Cragun's Unix or Linux Image
Don Cragun Don Cragun is offline Forum Staff  
Administrator
 
Join Date: Jul 2012
Last Activity: 21 May 2018, 5:26 AM EDT
Location: San Jose, CA, USA
Posts: 11,295
Thanks: 633
Thanked 3,932 Times in 3,365 Posts
You say nothing seems to be working with the original files. What does that mean? Are the commands completing with no output? Are you killing the commands because no output is produced in an hour (or some other fixed time)?

Your sample data in File 1 (or maybe file1 as it is referenced in your sample code) uses <space><comma><space> as the field separator. Your sample data in File 2 (or maybe file2 or file 2 as it is referenced in your sample code) uses <space><comma> as the field separator. But your code just uses <comma> as the field separator. Are you sure that your real data ALWAYS has exactly one <space> character after the number in the first field in both input files before the <comma> on every line?
Sponsored Links
    #3  
Old Unix and Linux 08-15-2016   -   Original Discussion by Varshha
Varshha's Unix or Linux Image
Varshha Varshha is offline
Registered User
 
Join Date: Jun 2014
Last Activity: 18 October 2016, 1:48 AM EDT
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
I posted the reply yesterday but I am not sure why it is not reflecting. So here it is again :

The files were originally tab delimited but I made them comma delimited to help me with the join command. I am now trying again with tab delimited files. I also tried some additions in my join command and this is what I gave :



Code:
join -t"  "  -a 2 -a 1 -e 'NULL' -o '0,1.1,1.2,2.1,2.2' File1 File2 | head -100

I am getting a result out of this which unfortunately means that UNIX is not finding a common key for the files to join and it is surprising because there ARE common values between the files. This is how the sample of the result looks like :



Code:
01635158332	09/09/2016 01635158332	09/09/2016 NULL NULL NULL
01635163349	11/24/2009 01635163349	11/24/2009 NULL NULL NULL
16.11	01635163339 NULL NULL 16.11	01635163339 NULL
16.11	01635163349 NULL NULL 16.11	01635163349 NULL


As you can see above, 01635163349 is a common key between File 1 that has dates and file 2 that has the cost. So ideally the result should be



Code:
01635163349  11/24/2009  16.11

The command

Code:
join -1 1 -2 1 File 1 File 2

does not give me any result as in no output on the console at all.

This is how file 1 looks:



Code:
00033492482     04/11/2006
00033492682     07/14/2009
00033492702     02/09/2010
00076848302     08/10/2010
00881123792     11/07/2000
01130162424     06/12/2007
01130164254     01/29/2008
01130165543     05/16/2011
01130168864     07/14/2009
01635163349     11/24/2009

File 2:



Code:
0.00    03139822826
0.00    49246820001
0.00    7621830148
0.00    822004599003
0.11    73379268872
0.64    67119603398
0.65    67261704102
16.11   01635163349


Can there be any other way to achieve an inner join between these files?
    #4  
Old Unix and Linux 08-15-2016   -   Original Discussion by Varshha
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 21 May 2018, 6:55 AM EDT
Location: Aachen, Germany
Posts: 12,716
Thanks: 412
Thanked 3,910 Times in 3,596 Posts
Shouldn't you use column 2 in file2? Try


Code:
join  -11 -22 file1 file2
01635163349 11/24/2009 16.11

And, moving targets rarely help. Why didn't you post representative samples in the first place?
Sponsored Links
    #5  
Old Unix and Linux 08-15-2016   -   Original Discussion by Varshha
Varshha's Unix or Linux Image
Varshha Varshha is offline
Registered User
 
Join Date: Jun 2014
Last Activity: 18 October 2016, 1:48 AM EDT
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
Sorry, I did



Code:
join -11 -22  File1 File2

Came out as a typo .... but this is not working either Linux
Sponsored Links
    #6  
Old Unix and Linux 08-15-2016   -   Original Discussion by Varshha
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 21 May 2018, 6:55 AM EDT
Location: Aachen, Germany
Posts: 12,716
Thanks: 412
Thanked 3,910 Times in 3,596 Posts
DOS <CR> (0x0D, \r, ^M) line terminators? Where and how did you produce the files?
Sponsored Links
    #7  
Old Unix and Linux 08-15-2016   -   Original Discussion by Varshha
Varshha's Unix or Linux Image
Varshha Varshha is offline
Registered User
 
Join Date: Jun 2014
Last Activity: 18 October 2016, 1:48 AM EDT
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
These files are being sent by the source. There are many other columns in these files. I have manipulated them to remove the unrequired columns and the header using AWK and SED.

---------- Post updated at 04:24 AM ---------- Previous update was at 04:19 AM ----------

These files are being sent by the source. There are many other columns in these files. I have manipulated them to remove the unrequired columns and the header using AWK and SED. When I am viewing the file in vi, I do not see ant ^M charachters.
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Weird problem with join command dbiggied UNIX for Dummies Questions & Answers 3 03-18-2014 07:40 AM
How to use the the join command to join multiple files by a common column evelibertine UNIX for Dummies Questions & Answers 5 07-05-2012 04:15 PM
how to join two files using "Join" command with one common field in this problem? mindfreak UNIX for Dummies Questions & Answers 2 04-13-2012 05:55 AM
Problem when using join command forevertl UNIX for Dummies Questions & Answers 2 10-31-2011 02:23 PM
Problem with Join command repinementer Shell Programming and Scripting 5 05-10-2009 02:58 AM



All times are GMT -4. The time now is 02:55 PM.