Order file based on another file

Question

[Hann]

Hello

I have two files, I want to order the first file based on the second file.

Example here the first file:

0.448032    0.551968    CM04487 Benin   1.383333333 10.5    2
0.53082 0.46918 CM04489 Benin   0.783333333 10.4    1
0.9999  1.00E-04    CM06501 Burkina Faso    -3.083333333    13.36666667 1
0.93288 0.0671198   CM06510 Burkina Faso    -3.9    12.9    1
0.869206    0.130794    CM06511 Burkina Faso    -3.833333333    12.66666667 1
0.985831    0.0141694   CM06513 Burkina Faso    -3.816666667    12.58333333 1
0.269609    0.730391    CM07888 Burkina Faso    -5.083333333    10.55   2
0.713339    0.286661    CH3 Ghana   0.28511 10.13201    1
0.667012    0.332988    CH4 Ghana   0.28771 10.13653    1

and this file2 that I want file1 to be ordered based on this file

CM06513
CM04489
CM04487
CM07234
CH4
CH3
CM06510
CM06501
CM07888

So the results would be like this:

0.985831    0.0141694   CM06513 Burkina Faso    -3.816666667    12.58333333 1
0.53082 0.46918 CM04489 Benin   0.783333333 10.4    1
0.448032    0.551968    CM04487 Benin   1.383333333 10.5    2
0.167236    0.832764    CM07234 Guinea  -9.5    10.86666667 2
0.667012    0.332988    CH4 Ghana   0.28771 10.13653    1
0.713339    0.286661    CH3 Ghana   0.28511 10.13201    1
0.93288 0.0671198   CM06510 Burkina Faso    -3.9    12.9    1
0.9999  1.00E-04    CM06501 Burkina Faso    -3.083333333    13.36666667 1
0.269609    0.730391    CM07888 Burkina Faso    -5.083333333    10.55   2

I tried many different things using R and bash command line, none of them worked

Here is R command, didn't work:

file1[order(ordered(file1, levels=file2))]

I also tried this:

awk 'FNR==NR {x2[$3] = $0; next} $1 in x2 {print x2[$1]}' file1 file2

[Kevin Blighe]

I see that you found the solution. The awk one is probably (I cannot check because I'm away).

awk 'FNR==NR {x2[$3] = $0; next} {if ($1 in x2) {print $1"\t"x2[$1]}}' file1 -F "\t" file2

Answer 1

[Yean]

Have you tried using inner_join function in dplyr package? This function will help to intersect file based on your index column.

Example usage is like Merge_df <- inner_join(file1,file2, by = "ID_column_name")

[Hann]

Thank you! Your hint was helpful!

inner_join didn't produce the same order as in file 2; However, left_join worked !! usage: left_join(data.frame(V1=file2),file1,by="V1")