1

Table 1 and 2

I tried to write code in R to merge data from two excel sheets and order them by a given column (by DRUG_ID).

Here it's "Drug ID". But for a given Drug ID, there is sometimes more than one entry.

Can you help me understand how to merge this? Thank you for your help!

hiuky
  • 19
  • 2
  • Can you be more explicit about your gaol than "organize data and order them"? Do you want to merge them into one table? Based on the drug ID? [Maybe check on the R-FAQ on how to merge/join data](https://stackoverflow.com/q/1299871/903061)... – Gregor Thomas Feb 24 '19 at 21:03
  • This is *slightly* better. Can you show the result you want? The default for `merge` or `dplyr::left_join` will be to use every matching combination. If that's *not* what you want, then what *do* you want? – Gregor Thomas Feb 25 '19 at 02:25

1 Answers1

0

Is this what you want?

I am doing a full join: https://r4ds.had.co.nz/relational-data.html#join-matches. (full_join is from the dplyr package.)

table_1 <- tibble::tribble(
  ~DRUG_ID, ~MEDICINAL_INGREDIENT_E,
  147, 'Abacavir Sulfate',
  148, 'Abacavir Sulfate'
)

table_2 <- tibble::tribble(
  ~DRUG_ID, ~`Doc No.`,
  147, 2216634,
  147, 2289753,
  147, 1340589
)

single_table <-
  dplyr::full_join(table_1, table_2, by = 'DRUG_ID')

single_table
#> # A tibble: 4 x 3
#>   DRUG_ID MEDICINAL_INGREDIENT_E `Doc No.`
#>     <dbl> <chr>                      <dbl>
#> 1     147 Abacavir Sulfate         2216634
#> 2     147 Abacavir Sulfate         2289753
#> 3     147 Abacavir Sulfate         1340589
#> 4     148 Abacavir Sulfate              NA
Ramiro Magno
  • 3,085
  • 15
  • 30