1

I have two files:

1_file.txt:
ChrX 129759713 A G
ChrX 129760010 C T
ChrX 129762238 C G
ChrX 129762448 A G
ChrX 129762803 A C
ChrX 129763441 C A
ChrX 129764931 T C
ChrX 129767696 C T
ChrX 129818213 C T
ChrX 129841336 T C

2_files.txt:
Chr29 129841336 T C
Chr29 129845233 A G
Chr29 129852688 T C
Chr29 129871602 G T
Chr29 129872683 T C
ChrX 129875545 C A
ChrX 129876975 A G
ChrX 129879796 G A
ChrX 129880521 T C
ChrX 129759713 A G
ChrX 129760010 C T
ChrX 129762238 C G
ChrX 129762448 A G
ChrX 129762803 A C
ChrX 129763441 C A

I want to join both files into one by field ONE and TWO

The answer
ChrX 129759713 A G
ChrX 129760010 C T
ChrX 129762238 C G
ChrX 129762448 A G
ChrX 129762803 A C
ChrX 129763441 C A

Any ideas how can i do it using join or awk?

  • Please provide a testable sample of inputs + desired output. There don't appear to be any joinable cases in the input you have provided. – steeldriver Dec 11 '19 at 1:16
  • Sure, it should be clearer. Here it is – Krzyztopher Dec 11 '19 at 23:09
  • Do you actually want to join or do you just want common lines from both files based on the first and second columns? – muru Dec 12 '19 at 4:23
  • I want some kind of INNER JOIN, but finally, we want to get lines which are on both files – Krzyztopher Dec 12 '19 at 16:06
1

Are you missing rows in the second file? I don't see one with "T G" or "A T". To my knowledge, join doesn't accept two fields, you simply join two fields using sed. Here's an example for your specific example:

join -j 1 <(cat 1_file.txt | sed "s/ /_/") <(cat 2_files.txt | sed "s/ /_/")

| improve this answer | |
  • 1
    Thanks for answer. I was looking for an example with join with two fields...but ineffectively. But if u tell that join isn't works, I trust you :D – Krzyztopher Dec 11 '19 at 23:08
1

With awk, you could use

awk '
    NR == FNR          {f1[$1,$2] = $0; next}
    $1 SUBSEP $2 in f1 {print f1[$1,$2], $3, $4}
' 1_file.txt 2_files.txt
ChrX 129759713 A G A G
ChrX 129760010 C T C T
ChrX 129762238 C G C G
ChrX 129762448 A G A G
ChrX 129762803 A C A C
ChrX 129763441 C A C A

For details about awk syntax and generally how it works, see the awk info page on Stack Overflow

Here:

  • NR == FNR is that condition that is true only for the lines of the first file being processed. In the block, we are storing each line in an array, keyed by the first 2 fields. See the gawk manual for the meaning of the variables.
  • in the array key, using a comma joins the fields using the builtin SUBSEP variable (reference)
  • the $1 SUBSEP $2 in f1 condition is true if, for a line in the second file, the first and second fields also appeared in the first file.
| improve this answer | |
  • Really nice answer. Could you explaint it? – Krzyztopher Dec 12 '19 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.