Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Intersection by part of the string

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Intersection by part of the string
# 1  
Old 04-10-2014
Intersection by part of the string
Hi,
I like to intersect two files based on their first columns. Here is the code which does the trick for me:
Code:
awk 'NR==FNR{A[$1];next}$1 in A' file1 file2

However, this only looks for exact matches between the two files in the first column. I need them to be printed even if part of the string matches. Here is an example:
File1
Code:
tamoxifen_citrate	abc	def
Letrozole	ghi	jkl
paclitaxel	mno	pqr
estradiol	stu	vwx

File2
Code:
Tamoxifen	123	456
Letrozole	789	101
paclitaxel_acetate	121	145
benzoat	851	541

My output
Code:
tamoxifen_citrate	abc	def	Tamoxifen	123	456
Letrozole	ghi	jkl	Letrozole	789	101
paclitaxel	mno	pqr	paclitaxel_acetate	121	145

My file is tab delimited.
Thanks in advance
Last edited by a_bahreini; 04-10-2014 at 01:16 PM..
# 2  
Old 04-10-2014
awk -F'\t' -f ab.awk ab1.txt ab2.txt OFS='\t'
where ab.awk is:
Code:
FNR==NR {a[$1]=$0;next}
{
  for (i in a)
    if (tolower(i) ~ tolower($1) || tolower($1) ~ tolower(i)) {
      print a[i], $0
      break
    }
}

# 3  
Old 04-10-2014
Thanks for your response but I don't think it's working. It actually prints everything in the file 2. BTW, I had to replace "space" in my columns with "underline" since the space messes up the column formation. Edited in the first post.
# 4  
Old 04-10-2014
Quote:
Originally Posted by a_bahreini
Thanks for your response but I don't think it's working. It actually prints everything in the file 2. BTW, I had to replace "space" in my columns with "underline" since the space messes up the column formation. Edited in the first post.
strange.... give your original files with tab separation... When I run: awk -F'\t' -f ab.awk ab1.txt ab2.txt OFS='\t' I get:
Code:
tamoxifen citrate       abc     def Tamoxifen   123     456
Letrozole       ghi     jkl Letrozole   789     101
paclitaxel      mno     pqr paclitaxel acetate  121     145

Please make sure your files are as you specified originally.
Also, please post (using code tags) the output of running cat -vet on each of the files.
# 5  
Old 04-10-2014
hmmm, that's weird! There might be something wrong with my files. This is the first ten lines for each file.
File
Code:
PROPRIETARYNAME^IPHARM_CLASSES$
AMYVID^I"Radioactive_Diagnostic_Agent_for_Positron_Emission_Tomography_[EPC],Positron_Emitting_Activity_[MoA]"$
QUINIDINE_GLUCONATE^I"Antiarrhythmic_[EPC],Cytochrome_P450_2D6_Inhibitor_[EPC],Cytochrome_P450_2D6_Inhibitors_[MoA]"$
AXIRON^I"Androgen_[EPC],Androgen_Receptor_Agonists_[MoA],Androstanes_[Chemical/Ingredient]"$
PROZAC^I"Serotonin_Reuptake_Inhibitor_[EPC],Serotonin_Uptake_Inhibitors_[MoA]"$
STRATTERA^I"Norepinephrine_Reuptake_Inhibitor_[EPC],Norepinephrine_Uptake_Inhibitors_[MoA]"$
STRATTERA^I"Norepinephrine_Reuptake_Inhibitor_[EPC],Norepinephrine_Uptake_Inhibitors_[MoA]"$
STRATTERA^I"Norepinephrine_Reuptake_Inhibitor_[EPC],Norepinephrine_Uptake_Inhibitors_[MoA]"$
SYMBYAX^I"Atypical_Antipsychotic_[EPC],Serotonin_Reuptake_Inhibitor_[EPC],Serotonin_Uptake_Inhibitors_[MoA]"$
SYMBYAX^I"Atypical_Antipsychotic_[EPC],Serotonin_Reuptake_Inhibitor_[EPC],Serotonin_Uptake_Inhibitors_[MoA]"$

File2
Code:
DRUG^IInteraction_Type^ISource^IGene_Symbol$
BACITRACIN^Iinhibitor^IDrugBank^IA2M$
BECAPLERMIN^In/a^IDrugBank^IA2M$
GIBBERELLIN_A4^In/a^IDrugBank^IAADACL2$
GIBBERELLIN_A3^In/a^IDrugBank^IAADACL2$
PYRIDOXAL_PHOSPHATE^Icofactor^IDrugBank^IAADAT$
L-GLUTAMIC_ACID^In/a^IDrugBank^IAADAT$
L-ALANINE^In/a^IDrugBank^IAARS$
L-ALANINE^In/a^IDrugBank^IAARS2$
N-(5-AMINO-5-CARBOXYPENTYL)GLUTAMIC_ACID^In/a^IDrugBank^IAASS$

# 6  
Old 04-10-2014
given the above files, there's no match on the FIRST field therefore there's no output from running the script.
Maybe your description of what you're after and the expect results don't jive.
# 7  
Old 04-10-2014
Yeah that's because I'm only showing the first 10 lines. There are matches if I go down. Is there a way I can send you the files? Each of them has thousands lines.
Thanks
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extracting a part of a string

Hi, I needed to extract some specific characters from a string based on user input. For example: After the script executes the user enters the following details: Please enter the string: This is a shell script Please enter the starting position: 11 Please enter the number of characters to be... (4 Replies)
Discussion started by: ChandanN
4 Replies

2. UNIX for Dummies Questions & Answers

Intersection by specific columns

Hi, I'd like to intersect two files by the 4th col of the first file and 6th col of the second file. This is the code I use: awk 'NR==FNR{A;next}$6 File1 File2 However, this is only outputting the second file lines. I'd like to have both lines in a single line separated by a tab. Thanks in... (25 Replies)
Discussion started by: a_bahreini
25 Replies

3. Shell Programming and Scripting

Finding intersection

Hi Friends, I would like to be helped for the following issue I am currently stuck with I have two files like the following tom ram 10 20 hey bye 11 12 bus cat 20 30 put but 25 30 jak mok 11 12 fil don 76 57 bus cat 23 45 pan ban 09 78 put but 45 67 kis mis 23 45 I would like... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

4. Shell Programming and Scripting

Deleting part of a string : string manipulation

i have something like this... echo "teCertificateId" | awk -F'Id' '{ print $1 }' | awk -F'te' '{ print $2 }' Certifica the awk should remove 'te' only if it is present at the start of the string.. anywhere else it should ignore it. expected output is Certificate (7 Replies)
Discussion started by: vivek d r
7 Replies

5. Shell Programming and Scripting

Need to take one part from a string

I have a string something like "/opt/src/default.cfg" OR /opt/src/common/one This whole string stored in an array. The problem is this string is not constant and it will keep on changing as lot of strings are stored in the array and it will be look like :- case 1 /opt/src/default.cfg ... (8 Replies)
Discussion started by: Renjesh
8 Replies

6. Web Development

Intersection and union of array by hash

Hi, A piece of script from Perl-cookbook I do not understand, and post here for explanation. The purpose is to find the element in either array (union), and in both array (intersection). Thank you in advance. @a=qw(1 3 5 6 7 8); @b=qw(2 3 5 7 9); foreach $e (@a, @b) {$union{$e}++ &&... (3 Replies)
Discussion started by: yifangt
3 Replies

7. Shell Programming and Scripting

Part of a string

Hi mates, I am doing a script in ksh. I have the following string: /opt/one/two/four/five/myFile.txt And I have a variable: echo "${variable}" -> /opt/one/two/ I would like to have just the string: four/five/myFile.txt What is the better way to do that? Thanks in... (3 Replies)
Discussion started by: gonzaloron
3 Replies

8. Shell Programming and Scripting

Getting part of a string

Hi, I have a string assinged to a varaible as below. FILE=/var/adm/message If $FILE is the value where it stores the path of message file. I would like to extract the location of the file message as below LOCATION=/var/adm FILE may have value like /var/adm/xxxx/message ... (2 Replies)
Discussion started by: raghu.amilineni
2 Replies

9. Shell Programming and Scripting

Find the intersection between two files

How can find the intersection between files for Example: file1 entry1 entry2 entry3 entry33 file2 entry2 entry4 entry5 . . . . the output should be entry2 (9 Replies)
Discussion started by: makrami
9 Replies

10. Shell Programming and Scripting

how to get the last part of a string followed by a pattern

assuming "cat" is the pattern, string (regardless length) asdadfcat4 I need to get 4 for eirtrjkkkcat678- I'd get 678 (in b-shell) Thanks in advance!!! (4 Replies)
Discussion started by: bluemoon1
4 Replies
Login or Register to Ask a Question