Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Add new column from another file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Add new column from another file
# 1  
Old 08-02-2017
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 08:23 PM..
# 2  
Old 08-02-2017
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.)
# 3  
Old 08-03-2017
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 02:56 AM..
# 4  
Old 08-03-2017
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.
This User Gave Thanks to Don Cragun For This Post:
freeroute
# 5  
Old 08-03-2017
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.
# 6  
Old 08-03-2017
Try using the script I gave you instead of randomly joining lines and introducing syntax errors!
This User Gave Thanks to Don Cragun For This Post:
freeroute
# 7  
Old 08-03-2017
Quote:
Originally Posted by Don Cragun
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 successSmilie. 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 05:12 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Add new column in the file

Hi Team I have file as below empno,ename,sal 123,smith,1000 124,adams,2000 Required output: Using AWK empno,ename,sal,deptno 123,smith,1000 124,adams,2000 Thanks, Murali (5 Replies)
Discussion started by: bmk
5 Replies

2. UNIX for Dummies Questions & Answers

Add a column to a file

Hi, I have this data file that contains: 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H 9 I I want the results to be: 1 A A 2 B A 3 C A 4 D A 5 E A 6 F A 7 G A 8 H A (8 Replies)
Discussion started by: bobo
8 Replies

3. Shell Programming and Scripting

Add a new column to file

I have file like this b,c 10,20 30,40 50,60 Now I want to add a new column a with fixed values for all the rows a,b,c 60,10,20 60,30,40 60,50,60 Please let me know how can we do this in unix. (4 Replies)
Discussion started by: weknowd
4 Replies

4. Shell Programming and Scripting

Help with add existing file name as new data column in new output file

Input File 1 cat S1.txt MI0043 2731 miR-1 Input File 2 cat S4.txt MI006 310 CiR-1 MI057 10 CiR-24 MI750 5 CiR-24 Desired Output File 1 cat S1.txt.out MI0043 2731 miR-1 S1.txt Desired Output File 2 cat S4.txt.out MI006 310 CiR-1 S4.txt (3 Replies)
Discussion started by: perl_beginner
3 Replies

5. Shell Programming and Scripting

How to add a column from other file?

Dear all, Lets say, I've a file a.txt containing two columns, like a1 b1 .. .. .. .. and another file b.txt containg two columns, like a1 c1 .. .. .. .. I need to put c1 column from b.txt file to the a.txt file. So, the output should be a1 b1 c1 .. .. .. .. ... (4 Replies)
Discussion started by: mkg
4 Replies

6. Shell Programming and Scripting

Add column info from one file to larger second file

Hi, long time reader, first time poster. I've done some searching so please if this is a repeated post excuse the duplicate, but what I have are two files roughly like so: File 1: A W B X C Y D Z File 2: A 1 C 2 D 3 And what I would like to get out is... (4 Replies)
Discussion started by: wallysb01
4 Replies

7. Shell Programming and Scripting

Add column to a file

Hola, How can I add a column to a existing file?? PS: The column which should be added need to be the first column and it will be a parameter from the script. Example: 1 name1 2 name2 3 name3 4 name3 Need to add parameter $file as a first column. $file is a file name with time... (6 Replies)
Discussion started by: Olivia
6 Replies

8. Shell Programming and Scripting

Need to add letters to a column and add in a new column subtracting from another column

So I have this input 1 10327 rs112750067 T C . PASS DP=65;AF=0.208;CB=BC,NCBI 1 10469 rs117577454 C G . PASS DP=2055;AF=0.020;CB=UM,BC,NCBI 1 10492 rs55998931 C T . PASS DP=231;AF=0.167;CB=BC,NCBI 1 10583 rs58108140 G A ... (3 Replies)
Discussion started by: kellywilliams
3 Replies

9. Shell Programming and Scripting

How to add a particular column alone in a file

Hi I have file which contains 5 coulmns i need to add the fifth column value and put it in the desired location in the same column. Here is the sample file.. ashop0004 SQL- 06/14/2009 06/14/2009 00:04:28 SUM ashop0004 SQL- 06/14/2009 06/14/2009 00:00:37 ... (22 Replies)
Discussion started by: cutechaps
22 Replies

10. Shell Programming and Scripting

how to add a new column in an existing file

Hi guys, Please help me if u have some solution. I have a file with three columns separated by ':' - INPUT_FILE C416722_2 : calin Dirigent : Dirigent AC4174_6 : Jac : cal_co TC4260_5 : [no : lin kite BC426302_1 : [no : calin Dirigent lin JC426540_3 : lin Pymo_bin : calin TC428_3 : no7... (4 Replies)
Discussion started by: sam_2921
4 Replies
Login or Register to Ask a Question