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
# 8  
Old 08-03-2017
How about posting the (curtailed) original files so people get the chance to analyse what might be going wrong? Not everybody's crystal ball can open and read your files remotely...
This User Gave Thanks to RudiC For This Post:
freeroute
# 9  
Old 08-03-2017
Can you show us (each block wrapped in CODE tags):-
  • The (some representative example) of the input
  • The actual code you are running
  • The output & errors you are getting
Hopefully then we can spot the issue and guide you to a correction.

If you want to compress code into a single line, you will have to ensure it is still valid. New lines are important. Consider the two scripts below:-
Code:
printf "Hello "
printf "world\n"

Code:
printf "Hello " printf "world\n"

Would you expect to get the same output?


Kind regards,
Robin
# 10  
Old 08-03-2017
Thank you for your help. Working.
Every solution you said works (awk and join).
# 11  
Old 08-05-2017
Quote:
Originally Posted by rbatte1
Can you show us (each block wrapped in CODE tags):-
  • The (some representative example) of the input
  • The actual code you are running
  • The output & errors you are getting
Hopefully then we can spot the issue and guide you to a correction.

If you want to compress code into a single line, you will have to ensure it is still valid. New lines are important. Consider the two scripts below:-
Code:
printf "Hello "
printf "world\n"

Code:
printf "Hello " printf "world\n"

Would you expect to get the same output?


Kind regards,
Robin
Tried. Not the same output. Thanks for the support.
# 12  
Old 08-13-2017
Quote:
Originally Posted by Don Cragun
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.
I have a little problem with the above join command:

If a hash contain semicolon ":", than the output will be wrong.

Code:
file1.txt format: hash:salt:ID
0276104677c86b442dd63126de7ab5b9:;i:=5:sakura
note: the hash contains ':'

than the output file3.txt (mail:ID)will be wrong:
Code:
icyfury75@yahoo.com:=5

It should be:
Code:
icyfury75@yahoo.com:sakura

Code:
file2.txt format: hash:salt:ID 
icyfury75@yahoo.com:0276104677c86b442dd63126de7ab5b9:;i5

Thanks
# 13  
Old 08-13-2017
Quote:
Originally Posted by freeroute
I have a little problem with the above join command:

If a hash contain semicolon ":", than the output will be wrong.

Code:
file1.txt format: hash:salt:ID
0276104677c86b442dd63126de7ab5b9:;i:=5:sakura
note: the hash contains ':'

than the output file3.txt (mail:ID)will be wrong:
Code:
icyfury75@yahoo.com:=5

It should be:
Code:
icyfury75@yahoo.com:sakura

Code:
file2.txt format: hash:salt:ID 
icyfury75@yahoo.com:0276104677c86b442dd63126de7ab5b9:;i5

Thanks
It is clear that join won't work for you with the data you are feeding it.

If you choose a field separator that also appears as data in a field, how is join supposed to guess at which field separator characters are field separators and which ones aren't?

Your sample input doesn't include any field separators in any of the sample hash fields. Your problem statement doesn't say anything about having to ignore some field separators nor does it describe any mechanism that could be used to determine the lengths of the various fields in your input lines.

It is not clear whether or not you will provide a clear enough description of your data for anyone to use any other tools to process your data. We clearly can't do so with the specifications you have provided us so far.
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