Answers.Body: <p>You can use the same approach as for two fields, just add the third:</p>
  
  <pre><code>awk 'NR==FNR{a[<span class="math-container">$1,$</span>2,<span class="math-container">$3]=$</span>4;next} (<span class="math-container">$1,$</span>2,<span class="math-container">$3) in a{print $0, a[$1,$2,$</span>3]}' ret lcls
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-10-30T09:21:52.427Z
Answers.LastActivityDate: 2019-10-30T09:21:52.427Z
Answers.UserDisplayName: terdon
Answers.Body: <p>With the 'paste' command you can merge two files. Example :</p>
  
  <pre><code>paste ret lcls | awk '{print <span class="math-container">$1" "$</span>2" "<span class="math-container">$3" "$</span>6" "<span class="math-container">$4" "$</span>8}'
  </code></pre>
  
  <p>Do you want 4 or 5 columns ?
  A sample of the result you want could help understand your needs.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-12T11:48:41.900Z
Answers.LastActivityDate: 2019-11-12T11:48:41.900Z
Answers.UserDisplayName: PierreS
Body: <p>I have two files that look like this:</p>
  
  <pre><code>$ head ret
  chr   pos     gene_id      pval_ret
  chr1 10177 ENSG00000227232 0.381708
  chr1 10352 ENSG00000227232 0.959523
  chr1 11008 ENSG00000227232 0.218132
  chr1 11012 ENSG00000227232 0.218132
  chr1 13110 ENSG00000227232 0.998262
  chr1 13116 ENSG00000227232 0.438572
  chr1 13118 ENSG00000227232 0.438572
  chr1 13273 ENSG00000227232 0.694523
  chr1 14464 ENSG00000227232 0.825113
  </code></pre>
  
  
  
  <pre><code>$ head lcls
  chr  pos     gene_id       pval_nominal
  chr1 13550 ENSG00000227232 0.375614
  chr1 14671 ENSG00000227232 0.474708
  chr1 14677 ENSG00000227232 0.699887
  chr1 16841 ENSG00000227232 0.127895
  chr1 16856 ENSG00000227232 0.627822
  chr1 17005 ENSG00000227232 0.802803
  chr1 17147 ENSG00000227232 0.443987
  chr1 17407 ENSG00000227232 0.397521
  chr1 17408 ENSG00000227232 0.615126
  </code></pre>
  
  <p>How can I merge these two files, <code>ret</code> and <code>lcls</code>, on the first 3 columns: chr, pos, gene_id?</p>
  
  <p>I know how to merge them on the first two columns:</p>
  
  <pre><code>awk 'NR==FNR{a[<span class="math-container">$1,$</span>2]=<span class="math-container">$3;next} ($</span>1,<span class="math-container">$2) in a{print $0, a[$1,$</span>2]}' ret lcls &gt; overlap
  </code></pre>
  
  <p>But how can I do it on the first 3 columns? In the output I want these 4 columns: <code>chr</code>, <code>pos,gene_id</code>, <code>pval_ret</code>, <code>pval_nominal</code>.</p>
  
Tags: <text-processing><awk>
Title: How to merge two files on the first 3 columns?
ThreadText: # How to merge two files on the first 3 columns?
  
  (Asked by: username_0 on 2019-10-29)
  
  I have two files that look like this:
  
  ```
  $ head ret
  chr   pos     gene_id      pval_ret
  chr1 10177 ENSG00000227232 0.381708
  chr1 10352 ENSG00000227232 0.959523
  chr1 11008 ENSG00000227232 0.218132
  chr1 11012 ENSG00000227232 0.218132
  chr1 13110 ENSG00000227232 0.998262
  chr1 13116 ENSG00000227232 0.438572
  chr1 13118 ENSG00000227232 0.438572
  chr1 13273 ENSG00000227232 0.694523
  chr1 14464 ENSG00000227232 0.825113
  
  ```
  
  ```
  $ head lcls
  chr  pos     gene_id       pval_nominal
  chr1 13550 ENSG00000227232 0.375614
  chr1 14671 ENSG00000227232 0.474708
  chr1 14677 ENSG00000227232 0.699887
  chr1 16841 ENSG00000227232 0.127895
  chr1 16856 ENSG00000227232 0.627822
  chr1 17005 ENSG00000227232 0.802803
  chr1 17147 ENSG00000227232 0.443987
  chr1 17407 ENSG00000227232 0.397521
  chr1 17408 ENSG00000227232 0.615126
  
  ```
  
  How can I merge these two files, `ret` and `lcls`, on the first 3 columns: chr, pos, gene\_id?
  
  I know how to merge them on the first two columns:
  
  ```
  awk 'NR==FNR{a[$1,$2]=$3;next} ($1,$2) in a{print $0, a[$1,$2]}' ret lcls > overlap
  
  ```
  
  But how can I do it on the first 3 columns? In the output I want these 4 columns: `chr`, `pos,gene_id`, `pval_ret`, `pval_nominal`.
  
  **Tags:** text-processing, awk
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2019-10-30. Score: 2
  
  You can use the same approach as for two fields, just add the third:
  
  ```
  awk 'NR==FNR{a[$1,$2,$3]=$4;next} ($1,$2,$3) in a{print $0, a[$1,$2,$3]}' ret lcls
  
  ```
  
  
  ---
  
  ### Answer by username_2 on 2019-11-12. Score: 0
  
  With the 'paste' command you can merge two files. Example :
  
  ```
  paste ret lcls | awk '{print $1" "$2" "$3" "$6" "$4" "$8}'
  
  ```
  
  Do you want 4 or 5 columns ?
  A sample of the result you want could help understand your needs.
  
  
  ---
  
  