Link clinical studies to application ID

[jayhesselberth]

I am wondering if there could be a link between the clinical trials table the projects table by the application ID (rather than just the project num).

library(nihexporter)
#> Loading required package: jsonlite
#> Loading required package: httr
#> Loading required package: dplyr
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(tidyverse)
#> Loading tidyverse: ggplot2
#> Loading tidyverse: tibble
#> Loading tidyverse: tidyr
#> Loading tidyverse: readr
#> Loading tidyverse: purrr
#> Conflicts with tidy packages ----------------------------------------------
#> filter(): dplyr, stats
#> lag():    dplyr, stats

# this produces several NA values for application.id 
left_join(clinical_studies, projects) %>%
  select(application.id, one_of(names(clinical_studies)))
#> Joining, by = "project.num"
#> # A tibble: 248,786 × 4
#>    application.id                project.num    trial.id
#>             <int>                      <chr>       <chr>
#> 1              NA        261201100031C-0-0-1 NCT01831778
#> 2              NA 261201200042I-0-26100006-1 NCT02772003
#> 3              NA        261201400046C-0-0-1 NCT02464332
#> 4              NA        268200700015C-2-0-0 NCT00534495
#> 5              NA        268200700036C-5-0-1 NCT00556439
#> 6              NA        268200900040C-1-0-1 NCT01206062
#> 7              NA        268201000048C-5-0-1 NCT01322165
#> 8              NA        268201300046C-4-0-1 NCT00005485
#> 9              NA        268201300047C-4-0-1 NCT00005485
#> 10             NA        268201300048C-4-0-1 NCT00005485
#> # ... with 248,776 more rows, and 1 more variables: study.status <fctr>

[datadiva]

Thanks for the quick response and solution.