R – Convert columns of type list to type str

May 16, 2023

I Am using a API call to Clinicaltrail.gov to pull some studies of interest using the code below. I am able to create a dataframe(studylist) with the columns i need , however each of these columns are type List, how can convert all the columns from list to str

library(httr)
library(jsonlite)

res <- httr::GET(
  paste0("https://clinicaltrials.gov/api/query/study_fields?",
         "expr=AREA[Condition](lyme)",
         "&fields=NCTId,",
         "Condition,OfficialTitle,phase,StartDate,EnrollmentCount",
         "&min_rnk=1&max_rnk=10&fmt=json"))
data <- fromJSON(rawToChar(res$content))
studylist <- as.data.frame(data$StudyFieldsResponse$StudyFields)

I tried using unnest from tidyr as follows , this seems to work however i end up with 6 rows instaed of expected 10 rows.

unnest(studylist,c(Phase,NCTId,Condition,OfficialTitle,StartDate,EnrollmentCount))

>Solution :

You lose 4 rows because 4 of your Phases are length-0.

sapply(studylist, lengths)
#       Rank NCTId Condition OfficialTitle Phase StartDate EnrollmentCount
#  [1,]    1     1         1             1     0         1               1
#  [2,]    1     1         1             1     1         1               1
#  [3,]    1     1         1             1     1         1               1
#  [4,]    1     1         1             1     1         1               1
#  [5,]    1     1         1             1     1         1               1
#  [6,]    1     1         1             1     0         1               1
#  [7,]    1     1         1             1     1         1               1
#  [8,]    1     1         1             1     0         1               1
#  [9,]    1     1         1             1     0         1               1
# [10,]    1     1         1             1     1         1               1

We can fix this with:

studylist[] <- lapply(studylist, function(z) { z[lengths(z) == 0] <- NA; z; })
unnest(studylist, everything())
# # A tibble: 10 × 7
#     Rank NCTId       Condition    OfficialTitle  Phase StartDate EnrollmentCount
#    <int> <chr>       <chr>        <chr>          <chr> <chr>     <chr>          
#  1     1 NCT05678478 Lyme Disease Perceptions,S… NA    February… 45             
#  2     2 NCT05634811 Lyme Disease A PHASE 3, RA… Phas… December… 3000           
#  3     3 NCT05477524 Lyme Disease A Phase 3, Mu… Phas… August 4… 18000          
#  4     4 NCT04867473 Lyme Disease Teleyoga for … Not … October … 13             
#  5     5 NCT04863287 Lyme Disease A Phase 1 Stu… Phas… February… 44             
#  6     6 NCT04835792 Lyme Disease Biomarker Stu… NA    March 21… 1000           
#  7     7 NCT04719962 Lyme Disease Diagnosis of … Not … July 7, … 10             
#  8     8 NCT04653558 Lyme Disease Clinical Mani… NA    January … 311            
#  9     9 NCT04422314 Lyme Disease ImmuneSense L… NA    July 9, … 893            
# 10    10 NCT03981874 Lyme Disease Comparative S… Not … May 29, … 50