0

I have to files named file 1 and filed 2 following format:

file 1 with 18000 lines

COL1 COL2 COL3 COL4 COL5

-   .   ID  =   tom_fa_10005086
-   0   Parent  =   tom_fa_10005086
-   0   Parent  =   tom_fa_10005086
-   2   Parent  =   tom_fa_10005086
-   1   Parent  =   tom_fa_10005086
-   0   Parent  =   tom_fa_10005086
-   0   Parent  =   tom_fa_10005086
+   .   ID  =   tom_fa_10013928
+   0   Parent  =   tom_fa_10013928
+   0   Parent  =   tom_fa_10013928
+   0   Parent  =   tom_fa_10013928
+   0   Parent  =   tom_fa_10013928
+   2   Parent  =   tom_fa_10013928
+   2   Parent  =   tom_fa_10013928
+   1   Parent  =   tom_fa_10013928
+   1   Parent  =   tom_fa_10013928
+   2   Parent  =   tom_fa_10013928
+   2   Parent  =   tom_fa_10013928
-   .   ID  =   tom_fa_10000024
-   0   Parent  =   tom_fa_10000024
-   .   ID  =   tom_fa_10004587
-   0   Parent  =   tom_fa_10004587
-   1   Parent  =   tom_fa_10004587
-   1   Parent  =   tom_fa_10004587
+   .   ID  =   tom_fa_10018753
+   0   Parent  =   tom_fa_10018753
+   2   Parent  =   tom_fa_10018753
+   2   Parent  =   tom_fa_10018753
+   0   Parent  =   tom_fa_10018753
+   .   ID  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   1   Parent  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   1   Parent  =   tom_fa_10007441
+   2   Parent  =   tom_fa_10007441

and file 2

COL 1            COL 2

tom_fa_10005086 ABI3VP1
tom_fa_10013928 ABI3VP2
tom_fa_10000024 Alfin-like
tom_fa_10011338 C2C2-Dof
tom_fa_10003474 C2C2-Gata
tom_fa_10003291 C2H2
tom_fa_10007064 C2H2
tom_fa_10008972 C2H2
tom_fa_10009025 C2H2
tom_fa_10005726 C3H
tom_fa_10011317 C3H
tom_fa_10010708 CPP
tom_fa_10002138 E2F-DP
tom_fa_10009443 G2-like
tom_fa_10008970 GRAS

I want to do if colum 5 of (file 1) and colum 1 (file 2) are equal, add at a colum 6 in file 1 corresponding with col 2 (file 2)

so it would look like

-   .   ID  =   tom_fa_10005086   ABI3VP1
-   0   Parent  =   tom_fa_10005086   ABI3VP1
-   0   Parent  =   tom_fa_10005086   ABI3VP1
-   2   Parent  =   tom_fa_10005086   ABI3VP1
-   1   Parent  =   tom_fa_10005086   ABI3VP1
-   0   Parent  =   tom_fa_10005086   ABI3VP1
-   0   Parent  =   tom_fa_10005086   ABI3VP1
+   .   ID  =   tom_fa_10013928   ABI3VP2
+   0   Parent  =   tom_fa_10013928   ABI3VP2
+   0   Parent  =   tom_fa_10013928   ABI3VP2
+   0   Parent  =   tom_fa_10013928   ABI3VP2
+   0   Parent  =   tom_fa_10013928   ABI3VP2
+   2   Parent  =   tom_fa_10013928   ABI3VP2
+   2   Parent  =   tom_fa_10013928   ABI3VP2
+   1   Parent  =   tom_fa_10013928   ABI3VP2
+   1   Parent  =   tom_fa_10013928   ABI3VP2
+   2   Parent  =   tom_fa_10013928   ABI3VP2
+   2   Parent  =   tom_fa_10013928   ABI3VP2
-   .   ID  =   tom_fa_10000024   Alfin-like
-   0   Parent  =   tom_fa_10000024   Alfin-like
-   .   ID  =   tom_fa_10004587
-   0   Parent  =   tom_fa_10004587
-   1   Parent  =   tom_fa_10004587
-   1   Parent  =   tom_fa_10004587
+   .   ID  =   tom_fa_10018753
+   0   Parent  =   tom_fa_10018753
+   2   Parent  =   tom_fa_10018753
+   2   Parent  =   tom_fa_10018753
+   0   Parent  =   tom_fa_10018753
+   .   ID  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   1   Parent  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   1   Parent  =   tom_fa_10007441
+   2   Parent  =   tom_fa_10007441

I tried with fgrep -f file 2 file1 but i don't know how to merge the information of the colum 2 file 2. and the strings are no equal.

  • (1) “add a column 6 in file 2” — but file 2 has only two columns.  Do you want to add columns 3, 4 and 5?  (2) Look at join. – G-Man Apr 24 at 0:03
  • 2
    Have you looked at the join command? It will join lines of two files based on a shared field. – Jeff H. Apr 24 at 0:08
0

If you want to do it in one pass, this sed script will work:

sed '/^[^ ]* [^ ]*$/{H;d;};G;s/ \([^ ]*\)\n.*\n\1 \([[:print:]]*\).*/ \1   \2/;P;d' file2 file1

What does it do? It collects all lines of file2 in the hold space as a kind of lookup table, then appends this table to each line of the pattern space and does add the sixth column if the pattern was found. In detail:

  • /^[^ ]* [^ ]*$/ matches lines with two columns, only for those, the following command (commands inside {} is executed
  • {H;d;} appends the lines to the hold space and stops further processing of the line, thus the rest of the script is only executed for lines of file1
  • G appends the hold space with our lookup table to the pattern space
  • s/ \([^ ]*\)\n.*\n\1 \([[:print:]]*\).*/ \1 \2/ does append the sixth column for a match
  • P prints the line without the appended table
  • d suppresses other output (option -n would have done the same)
-1

Tried with below commands and it worked fine too

Step1

awk 'NR==FNR{a[$5];next}($1 in a){print $0}' file1 file2 > u.txt
count=`awk '{print NR}' u.txt | sort -nr| sed -n '1p'`

step2

for ((i=1;i<=$count;i++)); do h=`awk -v i="$i" 'NR==i{print $1}' u.txt`; j=`awk -v i="$i" 'NR==i{print $2}' u.txt `; awk -v h="$h" -v j="$j" '$5 == h {$6=j;print $0}' file1; done >file3.txt


step3

awk 'NR==FNR{a[$1];next}!($5 in a){print $0}' file2 file1 >file4.txt


Step4(Final output)

awk '{print $0}' file3.txt file4.txt
- . ID = tom_fa_10005086 ABI3VP1
- 0 Parent = tom_fa_10005086 ABI3VP1
- 0 Parent = tom_fa_10005086 ABI3VP1
- 2 Parent = tom_fa_10005086 ABI3VP1
- 1 Parent = tom_fa_10005086 ABI3VP1
- 0 Parent = tom_fa_10005086 ABI3VP1
- 0 Parent = tom_fa_10005086 ABI3VP1
+ . ID = tom_fa_10013928 ABI3VP2
+ 0 Parent = tom_fa_10013928 ABI3VP2
+ 0 Parent = tom_fa_10013928 ABI3VP2
+ 0 Parent = tom_fa_10013928 ABI3VP2
+ 0 Parent = tom_fa_10013928 ABI3VP2
+ 2 Parent = tom_fa_10013928 ABI3VP2
+ 2 Parent = tom_fa_10013928 ABI3VP2
+ 1 Parent = tom_fa_10013928 ABI3VP2
+ 1 Parent = tom_fa_10013928 ABI3VP2
+ 2 Parent = tom_fa_10013928 ABI3VP2
+ 2 Parent = tom_fa_10013928 ABI3VP2
- . ID = tom_fa_10000024 Alfin-like
- 0 Parent = tom_fa_10000024 Alfin-like
-   .   ID  =   tom_fa_10004587
-   0   Parent  =   tom_fa_10004587
-   1   Parent  =   tom_fa_10004587
-   1   Parent  =   tom_fa_10004587
+   .   ID  =   tom_fa_10018753
+   0   Parent  =   tom_fa_10018753
+   2   Parent  =   tom_fa_10018753
+   2   Parent  =   tom_fa_10018753
+   0   Parent  =   tom_fa_10018753
+   .   ID  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   1   Parent  =   tom_fa_10007441
+   0   Parent  =   tom_fa_10007441
+   1   Parent  =   tom_fa_10007441
+   2   Parent  =   tom_fa_10007441

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.