This is a test version of Biostars. For the public version, visit https://www.biostars.org.
awk command merge files on one common column

Hi,

I have two files

file 1

A
B
B
C
D
E
E
E

file 2

A P Q 
B R S
C W X
D T U
E G H

I want to map the 1st column of file 1 with 1st column of file2 and I want the result as

A P Q
B R S
B R S
C W X
D T U
E G H
E G H
E G H

result will give me multiple occurrence of the row according to the 1st file.

I have used awk command for this, but it is giving me uniq entry not multiple.

Command used:

awk -F'\t' 'NR==FNR{a[$1];next} ($1) in a' file1 file2

EDIT: OP added biological context:

file1

Chrysiogenetes
Chrysiogenetes
Chrysiogenetes
Coprothermobacterota
Coprothermobacterota
Abditibacteriota
Abditibacteriota
Dictyoglomi
Dictyoglomi

File2

Chrysiogenetes ABCD
Coprothermobacterota WXYZ
Abditibacteriota TUVM
Dictyoglomi  OPLK

output :

Chrysiogenetes ABCD
Chrysiogenetes ABCD
Chrysiogenetes ABCD
Coprothermobacterota WXYZ
Coprothermobacterota WXYZ
Abditibacteriota TUVM
Abditibacteriota  TUVM
Dictyoglomi  OPLK
Dictyoglomi  OPLK

I want to match taxonomy details with my organism name.

linux shell-scripting awk
• 2,007 views • link
updated by zx8754 zx8754 • written by Confused_human • Confused_human

This post does not fit the theme of this forum. For simple awk commands, search StackOverflow.

If there is no biological context, this post will be deleted.

• 0 views • link
written by Ram Ram

file1

Chrysiogenetes

Chrysiogenetes

Chrysiogenetes

Coprothermobacterota

Coprothermobacterota

Abditibacteriota

Abditibacteriota

Dictyoglomi

Dictyoglomi

File2

Chrysiogenetes ABCD

Coprothermobacterota WXYZ

Abditibacteriota TUVM

Dictyoglomi OPLK

output :

Chrysiogenetes ABCD

Chrysiogenetes ABCD

Chrysiogenetes ABCD

Coprothermobacterota WXYZ

Coprothermobacterota WXYZ

Abditibacteriota TUVM

Abditibacteriota TUVM

Dictyoglomi OPLK

Dictyoglomi OPLK

I want to match taxonomy details with my organism name .

• 0 views • link
written by Confused_human • Confused_human

I've moved this content to your question. Next time, please as the question with proper biological context.

Side note: I corrected some typos in your post. The word column is spelled with just one o - it's not coloumn.

• 0 views • link
written by Ram Ram 
$ join -t $'\t' -1 1 -2 1 file1.txt file2.txt

Chrysiogenetes  ABCD
Chrysiogenetes  ABCD
Chrysiogenetes  ABCD
Coprothermobacterota    WXYZ
Coprothermobacterota    WXYZ
Abditibacteriota    TUVM
Abditibacteriota    TUVM
Dictyoglomi OPLK
Dictyoglomi OPLK
• 0 views • link
written by cpad0112 cpad0112

Hi, thank you for your suggestion.

I tried using this. I have sorted both the files but still I am getting error showing file 2 one entry not sorted .

• 0 views • link
written by Confused_human • Confused_human

If your copy paste operation was accurate, you have a white space in front of Abditibacteriota in file1 but not in file2.

• 0 views • link
written by Ram Ram 
 join -t $'\t' -1 1 -2 1 <( tr " " "\t" < file1.txt | tr -d '\r' | sort -t $'\t' -k1,1) <( tr " " "\t" < file2.txt | tr -d '\r' |  sort -t $'\t' -k1,1)
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

0 answers

No answers yet.

Log in to answer this question.

More posts like this