Unix/Linux Go Back    


UNIX for Beginners Questions & Answers If you're not sure where to post a Unix or Linux question, post it here. All unix and Linux beginners welcome in this forum!

Add new column from another file

UNIX for Beginners Questions & Answers


Tags
awk, join, solved

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 08-02-2017   -   Original Discussion by freeroute
freeroute's Unix or Linux Image
freeroute freeroute is offline
Registered User
 
Join Date: Nov 2016
Last Activity: 22 May 2018, 11:36 AM EDT
Posts: 15
Thanks: 3
Thanked 0 Times in 0 Posts
Add new column from another file

Hi,

I have 2 files.

file1 contains by lines: hash:salt:id
file2 contains by lines: username:hash:salt

I would like to add a new coloumn (id) form file1 to file2.

The new file should contains: username:hash:salt:id
Note: file1 contains less rows than file2.
I tried


Code:
paste -d':' file1 file2

, but this solution not good for me.

Thank you

Last edited by freeroute; 08-02-2017 at 07:23 PM..
Sponsored Links
    #2  
Old Unix and Linux 08-02-2017   -   Original Discussion by freeroute
Don Cragun's Unix or Linux Image
Don Cragun Don Cragun is online now Forum Staff  
Administrator
 
Join Date: Jul 2012
Last Activity: 23 May 2018, 12:33 PM EDT
Location: San Jose, CA, USA
Posts: 11,305
Thanks: 635
Thanked 3,932 Times in 3,365 Posts
What operating system and shell are you using?

Unless both file1 and file2 are sorted by hash and salt and there is a one-to-one correspondence between lines in those two files, there is no chance that the command:


Code:
paste -d':' file1 file2

should be expected to work (and even in that case, those two fields would appear in the output twice).

You could consider using the join utility, but you would need to preprocess both input files so the key field used for joining is a single field (not two fields). If that won't work, awk would be the obvious choice. But, before we can suggest any real solution to your problem, you need to clearly define what should happen if:
  1. there is no match in file1 for a pair of key fields found in file2, or
  2. there a more than one id field value in file1 for a single pair of key fields.
Please show us a representative pair of sample input files and the output that you want to produce from those sample inputs (each in CODE tags). (Please be sure that any cases that require special handling are included in your sample inputs.)
Sponsored Links
    #3  
Old Unix and Linux 08-03-2017   -   Original Discussion by freeroute
freeroute's Unix or Linux Image
freeroute freeroute is offline
Registered User
 
Join Date: Nov 2016
Last Activity: 22 May 2018, 11:36 AM EDT
Posts: 15
Thanks: 3
Thanked 0 Times in 0 Posts
I use Debian GNU Linux, and bash shell.

I tried this command

Code:
join  <(sort -t":" -k 1 file1.txt) <(sort -t":" -k 2 file2.txt) > file3.txt

For your question:
1.) there should be match. (if not, file3.txt should be empty)
2.) there will not be more than 1 id

file1.txt example:


Code:
e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R:biscuit
5356defbc28859a7433a289a3ed7755d:0f#!LWxvi&FnHJdDn?<c{>M74W^[8;:blizzard
558f783143e78fd8df9d8006c993cf5a:v'>#\=c?BESaS^)_j9a7oDh:amigo
f0495d258b379363f560c7aaef658228:SD%!7vo1N;}L,,@]QA"_+-[J1,GBn5:tester
97d3d3227c40e10ea1b8af3f5156051c:4q1!w({=NIuwI32W28$&P["8g8;{bS:gregory
d448d9c1eda2368854a8a6b97d9732b9:6i=!YJ=>?Pza`K+LlY5eu@*ag5=&6O:griffin

file2.txt example:



Code:
Mercenary14:e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R
thiago:8b8eab21b047d9ab19193d90ce0a98e7:=Ug"\^6mVwP*['fy2RGAJs2
uncleslam666:864ea0693e4b69ef8c3abe53fd71f913:k,x"{qla-iPRPR*ome*(!#v
rcmonster112:558f783143e78fd8df9d8006c993cf5a:v'>#\=c?BESaS^)_j9a7oDh

I need this format (so where the hash:salt match, should append username and id coloumns):



Code:
Mercenary14:e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R:biscuit


Last edited by freeroute; 08-03-2017 at 01:56 AM..
    #4  
Old Unix and Linux 08-03-2017   -   Original Discussion by freeroute
Don Cragun's Unix or Linux Image
Don Cragun Don Cragun is online now Forum Staff  
Administrator
 
Join Date: Jul 2012
Last Activity: 23 May 2018, 12:33 PM EDT
Location: San Jose, CA, USA
Posts: 11,305
Thanks: 635
Thanked 3,932 Times in 3,365 Posts
With the sample input files you provided, why is there only one line of output? Why shouldn't the output from those two input files be:


Code:
Mercenary14:e0113c5bccc164169af0bd68e5ecbd88:ca.!f+w;9[_2j^u<e+i6U=R:biscuit
rcmonster112:558f783143e78fd8df9d8006c993cf5a:v'>#\=c?BESaS^)_j9a7oDh:amigo

?

Note that the join command:


Code:
join  -1 1 -2 2 -t: -o2.1,2.2,2.3,1.3  <(sort -t":" -k 1,2 file1.txt) <(sort -t":" -k 2,3 file2.txt) > file3.txt

produces the output above from your sample input, but you ignored my note about preprocessing both input files to produce a single key field. The above command only compares the hash fields in the two files and completely ignores the salt fields when looking for matches in the two input files.

If the above output is acceptable but you need to match both the hash and salt fields, you might try using awk:


Code:
awk -F: '
NR == FNR {
	id[$1, $2] = $3
	next
}
($2, $3) in id {
	print $0, id[$2, $3]
}' OFS=: file1.txt file2.txt > file3.txt

which also produces the output shown above from your sample input files.

If someone else wants to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.
The Following User Says Thank You to Don Cragun For This Useful Post:
freeroute (08-03-2017)
Sponsored Links
    #5  
Old Unix and Linux 08-03-2017   -   Original Discussion by freeroute
freeroute's Unix or Linux Image
freeroute freeroute is offline
Registered User
 
Join Date: Nov 2016
Last Activity: 22 May 2018, 11:36 AM EDT
Posts: 15
Thanks: 3
Thanked 0 Times in 0 Posts


Code:
With the sample input files you provided, why is there only one line of output?

Sorry, I forget to post other matching output lines.

Thanks. I tried, here is the bash error message:


Code:
awk -F: 'NR == FNR {id[$1, $2] = $3 next}($2, $3) in id {print $0, id[$2, $3]}' OFS=: file1.txt file2.txt > file3.txt

awk: line 1: syntax error at or near next



Code:
join  -1 1 -2 2 -t: -o2.1,2.2,2.3,1.3  <(sort -t":" -k 1,2 file1.txt) <(sort -t":" -k 2,3 file2.txt) > file3.txt

In this command the outfile (file3.txt) is empty.
Sponsored Links
    #6  
Old Unix and Linux 08-03-2017   -   Original Discussion by freeroute
Don Cragun's Unix or Linux Image
Don Cragun Don Cragun is online now Forum Staff  
Administrator
 
Join Date: Jul 2012
Last Activity: 23 May 2018, 12:33 PM EDT
Location: San Jose, CA, USA
Posts: 11,305
Thanks: 635
Thanked 3,932 Times in 3,365 Posts
Try using the script I gave you instead of randomly joining lines and introducing syntax errors!
The Following User Says Thank You to Don Cragun For This Useful Post:
freeroute (08-03-2017)
Sponsored Links
    #7  
Old Unix and Linux 08-03-2017   -   Original Discussion by freeroute
freeroute's Unix or Linux Image
freeroute freeroute is offline
Registered User
 
Join Date: Nov 2016
Last Activity: 22 May 2018, 11:36 AM EDT
Posts: 15
Thanks: 3
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by Don Cragun View Post
Try using the script I gave you instead of randomly joining lines and introducing syntax errors!
Okay. Thanks for your help. I try with no successLinux. I have to read man pages of join and awk again...

Edit: I tried with file1.txt, file2.txt. It works, but with the original big found_2711.txt left_txt40k. files not.

found_2711.txt is ASCII
left_txt40k.txt is data

Last edited by freeroute; 08-03-2017 at 04:12 AM..
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
[Solved] Sorting a column in a file based on a column in a second file Homa Shell Programming and Scripting 6 12-14-2012 11:50 AM
Awk: Need help replacing a specific column in a file by part of a column in another file aa2601 Shell Programming and Scripting 1 07-10-2012 09:13 PM
Replace column that matches specific pattern, with column data from another file prashali Shell Programming and Scripting 7 05-15-2012 04:17 PM
Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 rydz00 Shell Programming and Scripting 7 11-09-2010 10:28 AM
Changing one column of delimited file column to fixed width column manneni prakash Shell Programming and Scripting 5 06-22-2009 05:27 AM



All times are GMT -4. The time now is 12:39 PM.