<p>Using awk, this can be done in the following way:</p>

<pre><code>awk 'NR==FNR{a[<span class="math-container">$1]=$</span>2; next}<span class="math-container">$1 in a{print $1, $2, a[$</span>1]}' file1.txt file2.txt &gt; same.txt
</code></pre>

<p>Explanation:</p>

<pre><code>NR==FNR (along with the next command) 
</code></pre>

<p>Will go through the first file only and create an associative array of of the geneIDs corresponding with the geneNames. </p>

<p>Then if the 1st column in the 2nd file is present in the array, a, it will print both columns from the file plus the geneName.</p>

CC BY-SA 4.0
2019-06-27T18:04:57.560Z
2019-06-27T18:21:10.383Z
2019-06-27T18:21:10.383Z
h3ab74
<p>I like to use the <code>join</code> command for this. For your example above, you can simply use the following:</p>

<pre><code>$ join file2 file1
ENST00000000412 0.779142 M6PR
ENST00000001008 0.738143 FKBP4
ENST00000002501 0.715315 DBNDD1
ENST00000002596 0.713664 HS3ST1
</code></pre>

<p>This assumes your files are sorted. If they aren't sorted, you can use input redirection if your shell supports it (bash and other modern shells do):</p>

<pre><code>join &lt;(sort -k 1b,1 file2) &lt;(sort -k 1b,1 file1)
</code></pre>

<p>If your shell doesn't support <code>&lt;()</code>, sort the files first:</p>

<pre><code>sort file1 &gt; file1.sorted; sort file2 &gt; file2.sorted
join &lt;(sort -k 1b,1 file2.sorted) &lt;(sort -k 1b,1 file1.sorted)
</code></pre>

CC BY-SA 4.0
2019-06-26T23:21:32.700Z
But the number of lines in file 2 and file 1 are not same. In an output file, I need a 1st and 2nd column of file 2 and 3rd column values to be extracted from file1
Megha
CC BY-SA 4.0
2019-06-26T23:31:02.680Z
The result matches your desired output - can you clarify what you want?
Scot
CC BY-SA 4.0
2019-06-26T23:58:17.743Z
Note that the `join` command does not simply `paste` files together, it matches two files based on a common column (by default column 1), and then tacks on the additional columns from the files in the specified order. I think it is exactly what you want, given what you've written. One caveat is that the files must be sorted by the matching column, though in your example above, they already are.
Scot
CC BY-SA 4.0
2019-06-27T05:05:29.510Z
If they're not sorted, this might work: ``join <(sort -k 1b,1 file1.txt) <(sort -k 1b,1 file2.txt)``
gringer
CC BY-SA 4.0
2019-06-27T09:01:46.967Z
@Scot Thank you for the answer, I figured out my file (completed one) was not sorted that's why it didn't work initially
Megha
CC BY-SA 4.0
2019-06-26T22:54:06.377Z
2019-06-27T15:48:46.800Z
2019-06-27T15:48:46.800Z
Scot
<p>File1:</p>

<pre><code>ENST00000000233 ARF5
ENST00000000412 M6PR
ENST00000001008 FKBP4
ENST00000001146 CYP26B1
ENST00000002501 DBNDD1
ENST00000002596 HS3ST1
</code></pre>

<p>File 2:</p>

<pre><code>ENST00000000412 0.779142
ENST00000001008 0.738143
ENST00000002501 0.715315
ENST00000002596 0.713664
</code></pre>

<p>Desired Output</p>

<pre><code>ENST00000000412 0.779142    M6PR
ENST00000001008 0.738143    FKBP4
ENST00000002501 0.715315    DBNDD1
ENST00000002596 0.713664    HS3ST1
</code></pre>

<bash><awk>
How to extract values from second file on the basis common first column?
# How to extract values from second file on the basis common first column?

(Asked by: username_0 on 2019-06-26)

File1:
ENST00000000233 ARF5
ENST00000000412 M6PR
ENST00000001008 FKBP4
ENST00000001146 CYP26B1
ENST00000002501 DBNDD1
ENST00000002596 HS3ST1

File 2:
ENST00000000412 0.779142
ENST00000001008 0.738143
ENST00000002501 0.715315
ENST00000002596 0.713664

Desired Output
ENST00000000412 0.779142    M6PR
ENST00000001008 0.738143    FKBP4
ENST00000002501 0.715315    DBNDD1
ENST00000002596 0.713664    HS3ST1

**Tags:** bash, awk

---

## 2 answers

### Answer by username_2 on 2019-06-26. Score: 6 | Accepted answer

I like to use the join command for this. For your example above, you can simply use the following:
$ join file2 file1
ENST00000000412 0.779142 M6PR
ENST00000001008 0.738143 FKBP4
ENST00000002501 0.715315 DBNDD1
ENST00000002596 0.713664 HS3ST1

This assumes your files are sorted. If they aren't sorted, you can use input redirection if your shell supports it (bash and other modern shells do):
join <(sort -k 1b,1 file2) <(sort -k 1b,1 file1)

If your shell doesn't support <(), sort the files first:
sort file1 > file1.sorted; sort file2 > file2.sorted
join <(sort -k 1b,1 file2.sorted) <(sort -k 1b,1 file1.sorted)

> **Comments:**
> **username_0**: But the number of lines in file 2 and file 1 are not same. In an output file, I need a 1st and 2nd column of file 2 and 3rd column values to be extracted from file1  
> **username_2**: The result matches your desired output - can you clarify what you want?  
> **username_2**: Note that the `join` command does not simply `paste` files together, it matches two files based on a common column (by default column 1), and then tacks on the additional columns from the files in the specified order. I think it is exactly what you want, given what you've written. One caveat is that the files must be sorted by the matching column, though in your example above, they already are.  
> **username_3**: If they're not sorted, this might work: ``join <(sort -k 1b,1 file1.txt) <(sort -k 1b,1 file2.txt)``  
> **username_0**: @Scot Thank you for the answer, I figured out my file (completed one) was not sorted that's why it didn't work initially  

---

### Answer by username_1 on 2019-06-27. Score: 1

Using awk, this can be done in the following way:
awk 'NR==FNR{a[$1]=$2; next}$1 in a{print $1, $2, a[$1]}' file1.txt file2.txt > same.txt

Explanation:
NR==FNR (along with the next command) 

Will go through the first file only and create an associative array of of the geneIDs corresponding with the geneNames. 
Then if the 1st column in the 2nd file is present in the array, a, it will print both columns from the file plus the geneName.


---

