R-script for data re-arrangment

Question

[mjoyraj]

I have two: Table-A and Table-B. I want to extract the contents of Table-B matching Table-A something like Table-C for all the Motif_ID of column-1 of Table-A. Can anybody help me with R-script.

Table-A

Motif_ID      Size    Overlap    Overlap    Fold enrichment    P value     FDR          Motif_ID      DBID                  TF_Name        Family_Name
M5301_1.01    181     181        14         16.19              6.81E-18    6.12E-15     M5301_1.01    ENSGALG00000013342    GBX2_CHICK     Homeodomain
M4396_1.01    500     500        16         6.7                1.53E-14    4.57E-12     M4396_1.01    ENSGALG00000005048    SMARCC1        Myb/SANT
M4435_1.01    493     493        16         6.79               1.20E-14    5.38E-12     M4435_1.01    ENSGALG00000010036    FOSL2_CHICK    bZIP

Table-B

Motif         Gene                                  Start    Stop          Log-odds    p-value     Site
M0082_1.01    Chr25_scale5_25_989144_990145_+_-R    380      389    -      11.5048     2.30E-05    AGCCTCAGGG
M0082_1.01    Chr25_scale6_25_989242_990243_+       515      524    +      11.5048     2.30E-05    AGCCTCAGGG
M0082_1.01    Chr25_scale9_25_997105_998106_+_-R    378      387    -      11.5048     2.30E-05    AGCCTCAGGG

Table-C

              Gene                                  Start    Stop          Log-odds    p-value     Site              Size    Overlap    Overlap    Fold enrichment    P value     FDR               Motif_ID      DBID                  TF_Name       Family_Name
M5301_1.01    Chr25_scale1_25_981718_982719_+_-R    516      529    +      10.3933     8.65E-05    TAATTTGCTGATTA    181     181        14         16.19              6.81E-18    6.12E-15          M5301_1.01    ENSGALG00000013342    GBX2_CHICK    Homeodomain
              Chr25_scale2_25_981736_982737_+       455      468    -      10.3933     8.65E-05    TAATTTGCTGATTA
              Chr25_scale3_25_985474_986475_+_-R    758      771    +      12.5796     2.05E-05    TAATTTGCCCATTA
              Chr25_scale3_25_985474_986475_+_-R    758      771    -      13.426      1.11E-05    TAATGGGCAAATTA
              Chr25_scale4_25_985197_986198_+       508      521    +      13.426      1.11E-05    TAATGGGCAAATTA
              Chr25_scale4_25_985197_986198_+       508      521    -      12.5796     2.05E-05    TAATTTGCCCATTA
              Chr25_scale5_25_989144_990145_+_-R    523      536    +      12.5796     2.05E-05    TAATTTGCCCATTA
              Chr25_scale5_25_989144_990145_+_-R    523      536    -      13.426      1.11E-05    TAATGGGCAAATTA

[mjoyraj]

For lack of space, I have shown a small segment of table-B. It actually contains all the motif ID's of table-A, which I have manually extracted from table-B and put in Table-C.

Answer 1

[PoGibas]

merge(Table-A, Table-B, by.x="Motif_ID", by.y="Motif")

This should work, however IDs in Table-A$Motif_ID must be unique.

PS., similar questions were asked here before. Also I don't know how merge will cope with duplicate column names (get rid of one Motif_ID).

[mjoyraj]

It worked thanks....

[PoGibas]

If so, please accept my answer :-)