R-script for data re-arrangment
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
1 answer
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).
Log in to answer this question.
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.