0

Here is the text files we have:

file1.txt

A B C D
4 9
5 8

file2.txt:

H I J K L M N O P Q R
1 2 3 5 8 9 5 3 3 2 4
4 7 8 9 5 6 8 9 0 8 7
7 8 0 7 4 7 7 9 0 6 7
2 7 9 5 7 9 0 7 6 5 4

I want extract the two columns (H and N) from file2.txt when A column in file1.txt matches with the L column in file2.txt, and the output text file to be shown should be like this:

output.txt

A B C D
4 9 7 7
5 8 4 8

How can I do that?

closed as unclear what you're asking by Sparhawk, RalfFriedl, Kiwy, jimmij, Romeo Ninov Sep 12 '18 at 6:03

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    always add the input and expected output in text format. everyone cannot see the attached image – Kamaraj Sep 11 '18 at 4:12
  • 2
    How would column A in file1.txt ever match with column L in file2.txt when the columns in file1.txt have three fields and the columns in file2.txt have five fields? Furthermore, your output doesn't show columns H and N extracted extracted from file2.txt but just colums A and B from file1.txt with some numbers just placed into columns C and D from file1.txt. Lastly, what have you tried to get the output that you want? – Nasir Riley Sep 11 '18 at 4:43
  • 1
    This task is rather hard to do using typical Linux command line tools. I would rather recommend writing a script/program. – Hubert Grzeskowiak Sep 11 '18 at 6:01
  • 1
    Could you share with us what you've try ? it look a lot like homework... Question on Unix&Linux should show proof of work... we are not freelancers – Kiwy Sep 11 '18 at 12:52
0

Not possible using grep, but doable using awk:

$ awk 'NR==1   {print $0;next}              # Print header
       NR==FNR {a[$1]=$2;next}              # collect data from file1.txt
               { if($5 in a)                # If column L match
                 { print($5,a[$5],$1,$7) }  # print columns (H and N)
               }
      ' file1.txt file2.txt

As a one-liner:

$ awk 'NR==1{print $0;next} NR==FNR{a[$1]=$2;next} {if($5 in a){print($5,a[$5],$1,$7)}}' file1.txt file2.txt

A B C D
5 8 4 8
4 9 7 7

The order is as the fields appear in the file2.txt.
If you need that sorted, then add a sort step.

  • Thank you so much. I solved the problem already. But I have one more question to ask, if the situation change to column L include some wordings in column A instead of completely matched, how would the code if($5 in a) change? – Owen Sep 12 '18 at 9:36
  • @Owen Situation? Wordings? .... Hmmm .... Those are very generic descriptions, it is impossible for me to make a reasonable guess of what you may mean. I would recommend to open a new question with a clear description of what you need. – Isaac Sep 13 '18 at 1:44
0

I would go with a step by step approach, to explain

  1. Sort the file 1

    sort file1.txt > file1.sorted.txt

  2. Take the interesting portion out from file2 (and sort it too)

    awk '{print $5,$1,$7}' file2.txt | sort > file2_of_interest.txt

  3. Join the two outputs based on the key, which is column 1 in both the files

    join file1.sorted.txt file2_of_interest.txt -1 1 -2 1

You can combine the three steps using indirection - but in the end you would need to complete parse at least one file. There would also be concerns on cases where column L in file2.txt is not unique and/or column 1 in file1.txt is not unique - you would need to resolve those by putting in your business logic to make the key unique.

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