search

oxford-pharmacoepi / MegaStudy

4 stars 2 forks source link

Error while running RunIncidencePrevalence.R #13

Closed rfherrerac closed 8 months ago

rfherrerac commented 8 months ago

After running RunIncidencePrevalence.R, I am getting this error:

Error in cs() at MegaStudy-main_January24/IncidencePrevalence Code/Concepts generation.R:142:1: ! ID: 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 are duplicated in the concept set.

Backtrace: ▆

  1. ├─base::source(here("RunIncidencePrevalence.R"))
  2. │ ├─base::withVisible(eval(ei, envir))
  3. │ └─base::eval(ei, envir)
  4. │ └─base::eval(ei, envir)
  5. ├─base::source(here("Concepts generation.R")) at MegaStudy-main_January24/IncidencePrevalence Code/RunIncidencePrevalence.R:44:1
  6. │ ├─base::withVisible(eval(ei, envir))
  7. │ └─base::eval(ei, envir)
  8. │ └─base::eval(ei, envir)
  9. └─Capr::cs(daunorubicin_ids, name = "daunorubicin_no_combination") at MegaStudy-main_January24/IncidencePrevalence Code/Concepts generation.R:142:1
tiozab commented 8 months ago

@rfherrerac sorry you faced this issue. Have you activated renv? The error occurs in this part :

daunorubicin (single)

daunorubicin_no_combination_ids <- getDrugIngredientCodes(cdm = cdm, name = "daunorubicin", ingredientRange = c(1,1), withConceptDetails = FALSE) daunorubicin_ids <- unlist(daunorubicin_no_combination_ids) concept_objects$daunorubicin_no_combination <- cs(daunorubicin_ids, name = "daunorubicin_no_combination")

getDrugIngredientCodes() is from the package CodelistGenerator (2.1.1) and should create a list of integeres (daunorubicin drug concept ids), however, in your case creates a list with a lot of Zeros inside. cs() is from CapR (2.0.7) which does not allow duplicate integers in the input file, but since the list it gets contains a lot of zeros, there are a lot of duplicates and therefore we see the error.

So I am wondering why the getDrugIngredientCodes() for the search of "daunorubicin" creates those zeros that we see in your case. Can you try again just making sure you have the correct versions and using the function getDrugIngredientCodes() and see what is in the list (also for other drugs?). If it is a general problem we may have to report it to the CodelistGenerator package maintainer. Can you remind us which database management system you are using ? @martapineda

rfherrerac commented 8 months ago

I am redshift. Let me try out your suggestions. I managed to run the renv commands.

rfherrerac commented 8 months ago

Hi @tiozab, When I do daunorubicin_no_combination_ids <- getDrugIngredientCodes(cdm = cdm, name = "daunorubicin", ingredientRange = c(1,1), withConceptDetails = FALSE) I get:

head(daunorubicin_no_combination_ids) $daunorubicin integer64 [1] 36894364 36894171 2065599 2065601 2065609 41097245 36056777 36272220 [9] 41427641 44071671 44071672 36784631 36780913 36879871 2028640 36809551 [17] 36811163 40992743 43275477 36056779 43182903 41384640 43728713 21120652 [25] 41384121 21140382 41372341 784341 784344 42544058 35134194 35151665 [33] 2065604 35761241 40895080 40992741 35606020 41297320 43668063 41383982 [41] 19076077 36258030 41372342 41372343 43613776 43808811 36408638 44108926 [49] 40941145 2027425 40878757 2065598 2065602 40860636 40992742 43286291 [57] 36056776 21022634 41054951 41362577 41371213 41372739 41384162 41361227 [65] 41369310 36233007 783568 783571 40909780 41003463 40863883 35748726 [73] 37499292 36056778 41367751 41054952 21120653 41361450 42658268 1719024 [81] 36780912 36780915 40730667 43032481 35771396 35803530 43197726 35606021 [89] 19124636 41297319 43138871 21052077 41368149 41383921 41364428 41253229 [97] 43638847 41427644 43736909 36784628 40847661 2065608 35749900 41003462 [105] 43032482 43032483 36056781 41054953 41304615 41128580 21169962 41384286 [113] 21110991 41370815 21091240 41371568 41427643 783570 44083105 1719025 [121] 2065607 35603891 43208580 43274539 43204855 43171910 41128582 21140381 [129] 41367423 794042 36409165 42707272 44115440 43032480 43274540 41019541 [137] 43175796 43175797 41191166 43279876 43182901 36271913 36405585 36404384 [145] 44089611 44041819 36784630 36780911 40941146 2027426 43032484 41065704 [153] 43218289 41222271 41364427 43710728 41367422 41371567 21061893 44034862 [161] 784345 36406093 1311825 36780914 36893541 36890964 35748725 41191167 [169] 43286292 19051600 41222270 41222272 21120651 41253230 36217316 36278696 [177] 43818724 783572 36784632 40867990 2065603 21130427 41023703 41368935 [185] 41128581 41384313 43610818 41367424 41370816 43847821 36883684 40867991 [193] 2065606 35745711 43219587 35757054 43193860 41136960 41211722 43182902 [201] 41304614 41304616 21052075 41383830 41315480 43800586 44073754 36784633 [209] 2028641 43160998 2065600 2065605 2065610 2065611 35765311 35148914 [217] 40992740 36056780 41180313 43268999 41373097 41384521 41384558 36278657 [225] 784342 44089612 42705098 1311799 36784634 36893752 40899051 36890785 [233] 35603892 40972098 35771395 40836849 35757053 41047666 2903377 41222273 [241] 41362225 41427642 36408186 783569 784343 44120602 36784629 36809539 [249] 40878756 36885878 40730668 43042715 43215771 43215772 43171909 35752861 [257] 41176092 21022635 41149216 21052076 41363685

Seems like the problem is when using unlist, that is producing the zeros.

daunorubicin_ids <- unlist(daunorubicin_no_combination_ids)

daunorubicin_ids daunorubicin1 daunorubicin2 daunorubicin3 daunorubicin4 daunorubicin5 1.822824e-316 1.822814e-316 1.020542e-317 1.020542e-317 1.020546e-317 daunorubicin6 daunorubicin7 daunorubicin8 daunorubicin9 daunorubicin10 2.030474e-316 1.781441e-316 1.792086e-316 2.046797e-316 2.177430e-316 daunorubicin11 daunorubicin12 daunorubicin13 daunorubicin14 daunorubicin15 2.177430e-316 1.817402e-316 1.817219e-316 1.822108e-316 1.002281e-317 daunorubicin16 daunorubicin17 daunorubicin18 daunorubicin19 daunorubicin20 1.818633e-316 1.818713e-316 2.025311e-316 2.138093e-316 1.781442e-316 daunorubicin21 daunorubicin22 daunorubicin23 daunorubicin24 daunorubicin25 2.133519e-316 2.044673e-316 2.160485e-316 1.043499e-316 2.044647e-316 daunorubicin26 daunorubicin27 daunorubicin28 daunorubicin29 daunorubicin30 1.044474e-316 2.044065e-316 3.875159e-318 3.875174e-318 2.101956e-316 daunorubicin31 daunorubicin32 daunorubicin33 daunorubicin34 daunorubicin35 1.735860e-316 1.736723e-316 1.020544e-317 1.766840e-316 2.020485e-316 daunorubicin36 daunorubicin37 daunorubicin38 daunorubicin39 daunorubicin40 2.025311e-316 1.759171e-316 2.040359e-316 2.157489e-316 2.044640e-316 daunorubicin41 daunorubicin42 daunorubicin43 daunorubicin44 daunorubicin45 9.424834e-317 1.791385e-316 2.044065e-316 2.044065e-316 2.154807e-316 daunorubicin46 daunorubicin47 daunorubicin48 daunorubicin49 daunorubicin50 2.164443e-316 1.798826e-316 2.179271e-316 2.022761e-316 1.001681e-317 daunorubicin51 daunorubicin52 daunorubicin53 daunorubicin54 daunorubicin55 2.019679e-316 1.020541e-317 1.020543e-317 2.018784e-316 2.025311e-316 daunorubicin56 daunorubicin57 daunorubicin58 daunorubicin59 daunorubicin60 2.138627e-316 1.781441e-316 1.038656e-316 2.028384e-316 2.043583e-316 daunorubicin61 daunorubicin62 daunorubicin63 daunorubicin64 daunorubicin65 2.044010e-316 2.044085e-316 2.044649e-316 2.043516e-316 2.043915e-316 daunorubicin66 daunorubicin67 daunorubicin68 daunorubicin69 daunorubicin70 1.790148e-316 3.871340e-318 3.871355e-318 2.021212e-316 2.025840e-316 daunorubicin71 daunorubicin72 daunorubicin73 daunorubicin74 daunorubicin75 2.018944e-316 1.766222e-316 1.852711e-316 1.781442e-316 2.043838e-316 daunorubicin76 daunorubicin77 daunorubicin78 daunorubicin79 daunorubicin80 2.028384e-316 1.043499e-316 2.043527e-316 2.107598e-316 8.493107e-318 daunorubicin81 daunorubicin82 daunorubicin83 daunorubicin84 daunorubicin85 1.817219e-316 1.817219e-316 2.012362e-316 2.126087e-316 1.767342e-316 daunorubicin86 daunorubicin87 daunorubicin88 daunorubicin89 daunorubicin90 1.768929e-316 2.134251e-316 1.759171e-316 9.448826e-317 2.040359e-316 daunorubicin91 daunorubicin92 daunorubicin93 daunorubicin94 daunorubicin95 2.131343e-316 1.040111e-316 2.043858e-316 2.044637e-316 2.043674e-316 daunorubicin96 daunorubicin97 daunorubicin98 daunorubicin99 daunorubicin100 2.038180e-316 2.156046e-316 2.046798e-316 2.160890e-316 1.817402e-316 daunorubicin101 daunorubicin102 daunorubicin103 daunorubicin104 daunorubicin105 2.018143e-316 1.020546e-317 1.766280e-316 2.025840e-316 2.126087e-316 daunorubicin106 daunorubicin107 daunorubicin108 daunorubicin109 daunorubicin110 2.126087e-316 1.781442e-316 2.028384e-316 2.040719e-316 2.032022e-316 daunorubicin111 daunorubicin112 daunorubicin113 daunorubicin114 daunorubicin115 1.045935e-316 2.044655e-316 1.043022e-316 2.043990e-316 1.042046e-316 daunorubicin116 daunorubicin117 daunorubicin118 daunorubicin119 daunorubicin120 2.044027e-316 2.046798e-316 3.871350e-318 2.177995e-316 8.493112e-318 daunorubicin121 daunorubicin122 daunorubicin123 daunorubicin124 daunorubicin125 1.020545e-317 1.759066e-316 2.134787e-316 2.138046e-316 2.134603e-316 daunorubicin126 daunorubicin127 daunorubicin128 daunorubicin129 daunorubicin130 2.132976e-316 2.032022e-316 1.044474e-316 2.043822e-316 3.923089e-318 daunorubicin131 daunorubicin132 daunorubicin133 daunorubicin134 daunorubicin135 1.798852e-316 2.110020e-316 2.179592e-316 2.126087e-316 2.138046e-316 daunorubicin136 daunorubicin137 daunorubicin138 daunorubicin139 daunorubicin140 2.026635e-316 2.133168e-316 2.133168e-316 2.035114e-316 2.138310e-316 daunorubicin141 daunorubicin142 daunorubicin143 daunorubicin144 daunorubicin145 2.133519e-316 1.792071e-316 1.798675e-316 1.798616e-316 2.178316e-316 daunorubicin146 daunorubicin147 daunorubicin148 daunorubicin149 daunorubicin150 2.175955e-316 1.817402e-316 1.817218e-316 2.022761e-316 1.001682e-317 daunorubicin151 daunorubicin152 daunorubicin153 daunorubicin154 daunorubicin155 2.126087e-316 2.028915e-316 2.135267e-316 2.036651e-316 2.043674e-316 daunorubicin156 daunorubicin157 daunorubicin158 daunorubicin159 daunorubicin160 2.159597e-316 2.043822e-316 2.044027e-316 1.040596e-316 2.175611e-316 daunorubicin161 daunorubicin162 daunorubicin163 daunorubicin164 daunorubicin165 3.875179e-318 1.798700e-316 6.481277e-318 1.817219e-316 1.822783e-316 daunorubicin166 daunorubicin167 daunorubicin168 daunorubicin169 daunorubicin170 1.822656e-316 1.766222e-316 2.035114e-316 2.138627e-316 9.412741e-317 daunorubicin171 daunorubicin172 daunorubicin173 daunorubicin174 daunorubicin175 2.036651e-316 2.036651e-316 1.043499e-316 2.038180e-316 1.789373e-316 daunorubicin176 daunorubicin177 daunorubicin178 daunorubicin179 daunorubicin180 1.792406e-316 2.164933e-316 3.871360e-318 1.817402e-316 2.019147e-316 daunorubicin181 daunorubicin182 daunorubicin183 daunorubicin184 daunorubicin185 1.020543e-317 1.043982e-316 2.026840e-316 2.043897e-316 2.032022e-316 daunorubicin186 daunorubicin187 daunorubicin188 daunorubicin189 daunorubicin190 2.044657e-316 2.154661e-316 2.043822e-316 2.043990e-316 2.166370e-316 daunorubicin191 daunorubicin192 daunorubicin193 daunorubicin194 daunorubicin195 1.822296e-316 2.019147e-316 1.020545e-317 1.766073e-316 2.135331e-316 daunorubicin196 daunorubicin197 daunorubicin198 daunorubicin199 daunorubicin200 1.766633e-316 2.134060e-316 2.032436e-316 2.036130e-316 2.133519e-316 daunorubicin201 daunorubicin202 daunorubicin203 daunorubicin204 daunorubicin205 2.040719e-316 2.040719e-316 1.040111e-316 2.044633e-316 2.041256e-316 daunorubicin206 daunorubicin207 daunorubicin208 daunorubicin209 daunorubicin210 2.164036e-316 2.177533e-316 1.817402e-316 1.002282e-317 2.132437e-316 daunorubicin211 daunorubicin212 daunorubicin213 daunorubicin214 daunorubicin215 1.020542e-317 1.020544e-317 1.020547e-317 1.020547e-317 1.767041e-316 daunorubicin216 daunorubicin217 daunorubicin218 daunorubicin219 daunorubicin220 1.736587e-316 2.025310e-316 1.781442e-316 2.034578e-316 2.137773e-316 daunorubicin221 daunorubicin222 daunorubicin223 daunorubicin224 daunorubicin225 2.044103e-316 2.044667e-316 2.044669e-316 1.792404e-316 3.875164e-318 daunorubicin226 daunorubicin227 daunorubicin228 daunorubicin229 daunorubicin230 2.178316e-316 2.109912e-316 6.481148e-318 1.817402e-316 1.822794e-316 daunorubicin231 daunorubicin232 daunorubicin233 daunorubicin234 daunorubicin235 2.020682e-316 1.822647e-316 1.759066e-316 2.024291e-316 1.767342e-316 daunorubicin236 daunorubicin237 daunorubicin238 daunorubicin239 daunorubicin240 2.017608e-316 1.766633e-316 2.028024e-316 1.434459e-317 2.036651e-316 daunorubicin241 daunorubicin242 daunorubicin243 daunorubicin244 daunorubicin245 2.043565e-316 2.046797e-316 1.798803e-316 3.871345e-318 3.875169e-318 daunorubicin246 daunorubicin247 daunorubicin248 daunorubicin249 daunorubicin250 2.179847e-316 1.817402e-316 1.818633e-316 2.019679e-316 1.822405e-316 daunorubicin251 daunorubicin252 daunorubicin253 daunorubicin254 daunorubicin255 2.012362e-316 2.126593e-316 2.135143e-316 2.135143e-316 2.132976e-316 daunorubicin256 daunorubicin257 daunorubicin258 daunorubicin259 daunorubicin260 1.766426e-316 2.034369e-316 1.038656e-316 2.033041e-316 1.040111e-316 daunorubicin261 2.043638e-316

tiozab commented 8 months ago

There is another way to unlist() please run this example again with do.call(c, ...) see below

daunorubicin_no_combination_ids <- getDrugIngredientCodes(cdm = cdm, name = "daunorubicin", ingredientRange = c(1,1), withConceptDetails = FALSE) daunorubicin_ids <- do.call(c, daunorubicin_no_combination_ids) concept_objects$daunorubicin_no_combination <- cs(daunorubicin_ids, name = "daunorubicin_no_combination")

let me know if it works, so I will change the code for everybody

rfherrerac commented 8 months ago

Hi @tiozab it worked partially, I think the object daunorubicin_ids must enter to cs as numeric / integer, when it enters as integer64 cs returns zeros.

daunorubicin_no_combination_ids <- getDrugIngredientCodes(cdm = cdm, name = "daunorubicin", ingredientRange = c(1,1), withConceptDetails = FALSE) daunorubicin_ids <- do.call(c, daunorubicin_no_combination_ids) daunorubicin_ids_num <- as.numeric(daunorubicin_ids) concept_objects$daunorubicin_no_combination <- cs(daunorubicin_ids_num, name = "daunorubicin_no_combination") ── daunorubicin_no_combination ──────────────────────────────────

A tibble: 261 × 9

conceptId conceptCode conceptName domainId vocabularyId standardConcept

1 43800586 "" "" "" "" "" 2 44073754 "" "" "" "" "" 3 36784633 "" "" "" "" "" 4 35148914 "" "" "" "" "" 5 2065600 "" "" "" "" "" 6 2065605 "" "" "" "" "" 7 2065610 "" "" "" "" "" 8 2065611 "" "" "" "" "" 9 40992740 "" "" "" "" "" 10 2028641 "" "" "" "" "" # ℹ 251 more rows # ℹ 3 more variables: includeDescendants , isExcluded , # includeMapped # ℹ Use `print(n = ...)` to see more rows
tiozab commented 8 months ago

This is perfect, that is what it looks like, only the first column is filled, all good! ── daunorubicin_no_combination ────────────────────────────────────────────────────────────────────────────────────────────────────────────

A tibble: 260 × 9

conceptId conceptCode conceptName domainId vocabularyId standardConcept includeDescendants isExcluded includeMapped

1 783568 "" "" "" "" "" FALSE FALSE FALSE 2 783569 "" "" "" "" "" FALSE FALSE FALSE 3 783570 "" "" "" "" "" FALSE FALSE FALSE 4 783571 "" "" "" "" "" FALSE FALSE FALSE 5 783572 "" "" "" "" "" FALSE FALSE FALSE 6 784341 "" "" "" "" "" FALSE FALSE FALSE 7 784342 "" "" "" "" "" FALSE FALSE FALSE 8 784343 "" "" "" "" "" FALSE FALSE FALSE 9 784344 "" "" "" "" "" FALSE FALSE FALSE 10 784345 "" "" "" "" "" FALSE FALSE FALSE # ℹ 250 more rows # ℹ Use `print(n = ...)` to see more rows I have just updated the code and pushed it, so the newest version should run for you now! in case you do not want to download the repository again, you can just replace the file "Concepts generation.R" in your files, that is where I made the changes
rfherrerac commented 8 months ago

@tiozab for me did not work, still you need to transform the object to numeric, because it is integer64 that is the reason the cs function returns zeros and do the error.

tiozab commented 8 months ago

sorry did not see the line with "as.numeric" will amend the code.

rfherrerac commented 8 months ago

After doing cdm <- generateConceptCohortSet( cdm = cdm, conceptSet = concept_objects, name = "outcomes", limit = "all", requiredObservation = c(0, 0), end = "observation_period_end_date", overwrite = TRUE ) I am getting

Error: Failed to fetch row: ERROR: relation "bidataoutcomes" already exists

tiozab commented 8 months ago

Hi @rfherrerac I see that the generateConceptCohortSets is still throwing errors despite you using a new CDM right? Do you still have the error only when using the whole list of concept sets?

@edward-burn any idea?

a few screenshots from our previous conversation :

image

We thought we could fix this by either changing the name of the outcome (not so nice, because will have to changed downstream), or just use a new CDM instance (where the temporary tables will be dropped). However, it seems that the error persists even with a new CDM instance. The database management system is redshift

tiozab commented 8 months ago

@rfherrerac we have made some changes to the code, and also use the most recent packages out now from CDMConnector etc. Can you please try again using the most recent code and let me know how it goes?

rfherrerac commented 8 months ago

Hi @tiozab Hi, I got an error in doing cdm <- generateDrugUtilisationCohortSet( cdm = cdm, name = "drug_cohorts", conceptSet = concept_drugs, cohortDateRange = as.Date(c("2010-01-01", NA)), limit = "all" )

Error in db_copy_to(): ! Can't copy to table "dbplyr_033". Caused by error in dplyr::db_write_table(): ! Can't write table table "dbplyr_033". Caused by error: ! Failed to prepare query: ERROR: "4.46007880470268e-318" is out of range for type double precision Run rlang::last_trace() to see where the error occurred.

rlang::last_trace() <error/rlang_error> Error in db_copy_to(): ! Can't copy to table "dbplyr_033". Caused by error in dplyr::db_write_table(): ! Can't write table table "dbplyr_033". Caused by error: ! Failed to prepare query: ERROR: "4.46007880470268e-318" is out of range for type double precision

Backtrace: ▆

  1. ├─DrugUtilisation::generateDrugUtilisationCohortSet(...)
  2. │ └─DrugUtilisation:::subsetTables(cdm, conceptSet, "Drug")
  3. │ └─... %>% computeTable(cdm)
  4. ├─DrugUtilisation:::computeTable(., cdm)
  5. │ └─x %>% ...
  6. ├─CDMConnector::computeQuery(...)
  7. │ └─base::is.data.frame(x)
  8. ├─dplyr::right_join(...)
  9. └─dbplyr:::right_join.tbl_lazy(...)
  10. └─dbplyr:::add_join(...)
  11. ├─dplyr::auto_copy(x, y, copy = copy, indexes = if (auto_index) list(by$y))
  12. └─dbplyr:::auto_copy.tbl_sql(x, y, copy = copy, indexes = if (auto_index) list(by$y))
  13. ├─dplyr::copy_to(...)
  14. └─dbplyr:::copy_to.src_sql(...)
  15. ├─dbplyr::db_copy_to(...)
  16. └─dbplyr:::db_copy_to.DBIConnection(...)
  17. ├─dbplyr:::with_transaction(...)
  18. ├─base::tryCatch(...)
  19. │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
  20. │ └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
  21. │ └─base (local) doTryCatch(return(expr), name, parentenv, handler)
  22. ├─dplyr::db_write_table(...)
  23. └─dbplyr:::db_write_table.DBIConnection(...)
tiozab commented 8 months ago

@catalamarti can you help. we changed to the DUS function generateDrugUtilisationCohortSet(...) (away from CDM connector function) and it does not work in redshift yet @edward-burn @daniprietoalhambra wants this to work for redshift because many EFPIA partners have redshift. it seems that @rfherrerac does not have writing permission to add tables to the database?

catalamarti commented 8 months ago

Hi @tiozab Ive triend in my redshift test database and it worked fine.

So I would start to check whether the problem is the writing permissions: @rfherrerac could you please try to run sth like:

cdm$person|>
  head(1) |>
  CDMConnector::computeQuerry(
    name = "test_table", temporary = FALSE, schema = attr(cdm, "write_schema")
  )

If that work you can drop the table like:

dropTable(cdm = cdm, name = "test_table")
rfherrerac commented 8 months ago

Hi, I got

Source: table [1 x 18] /# Database: postgres [ronald@10.8.19.50:5439/cegedim_thinbe_omop] / person_id gender_concept_id year_of_birth month_of_birth day_of_birth birth_datetime

/# ℹ 12 more variables: race_concept_id , ethnicity_concept_id , /# location_id , provider_id , care_site_id , /# person_source_value , gender_source_value , gender_source_concept_id , /# race_source_value , race_source_concept_id , ethnicity_source_value , /# ethnicity_source_concept_id
catalamarti commented 8 months ago

@rfherrerac nice so this means you have permissions to write permanent tables.

could we check which is the format that you get if you run the following lines:

DrugUtilisation:::conceptSetFromConceptSetList(concept_drugs)
rfherrerac commented 8 months ago

Hi @catalamarti I am getting a tibble table DrugUtilisation:::conceptSetFromConceptSetList(concept_drugs)

A tibble: 48,979 × 3

      x concept_id cohort_definition_id
<int64>      <dbl>                <int>
catalamarti commented 8 months ago

Just to make sure I think the error is here, can you confirm?

conceptSet <- DrugUtilisation:::conceptSetFromConceptSetList(concept_drugs)
cohort <- DrugUtilisation:::subsetTables(cdm, conceptSet, "Drug")

If it crashes we can run the subsetTables functions line by line so we find where is the error, thanks for the help @rfherrerac

rfherrerac commented 8 months ago

Hi @catalamarti seems like the problem is in the subset

conceptSet <- DrugUtilisation:::conceptSetFromConceptSetList(concept_drugs) cohort <- DrugUtilisation:::subsetTables(cdm, conceptSet, "Drug") Error in db_copy_to(): ! Can't copy to table "dbplyr_035". Caused by error in dplyr::db_write_table(): ! Can't write table table "dbplyr_035". Caused by error: ! Failed to prepare query: ERROR: "4.46007880470268e-318" is out of range for type double precision Run rlang::last_trace() to see where the error occurred.

rfherrerac commented 8 months ago

@catalamarti @tiozab It seems like this error typically occurs when you're trying to write a table to a database using dplyr's db_write_table() function, and one of the columns in your data frame has a value that's too small for the database to handle, I search for that value and it is in the list cytarabine_liposomal.

concept_drugs$cytarabine_liposomal [1] 2.103880e+07 2.107810e+07 2.111726e+07 2.112343e+07 2.113330e+07 2.113330e+07 [7] 2.113330e+07 2.115671e+07 3.560602e+07 3.578026e+07 3.578044e+07 3.627923e+07 [13] 3.749929e+07 4.017546e+07 4.017546e+07 4.068461e+07 4.071616e+07 4.071616e+07 [19] 4.071616e+07 4.071616e+07 4.071616e+07 4.071616e+07 4.074449e+07 4.074449e+07 [25] 4.074449e+07 4.074449e+07 4.074449e+07 4.074449e+07 4.074449e+07 4.074449e+07 [31] 4.086389e+07 4.103458e+07 4.119118e+07 4.119118e+07 4.119118e+07 4.123857e+07 [37] 4.135275e+07 4.135421e+07 4.135473e+07 4.135758e+07 4.135959e+07 4.136013e+07 [43] 4.304039e+07 4.314480e+07 4.315285e+07 4.315598e+07 4.318581e+07 4.318890e+07 [49] 4.321071e+07 4.321870e+07 4.406406e+07 4.460079e-318

catalamarti commented 8 months ago

Could we investigate this? How the concept_drugs is created @tiozab ? It should only contain integers, how this value appears there?

tiozab commented 8 months ago

The weird transformation of values only happens in @rfherrerac's version. We had this issue before, see first comments in this thread. then we had the problem with the unlist() We fixed this problem, however, as a whole, we had to change the code entirely when going away from Capr concepts and into integer lists for generateDrugUtilisationCohortSet() I made an issue about this that different functions need different input (but that is a different matter https://github.com/darwin-eu-dev/DrugUtilisation/issues/393)

Mine looks like this

concept_drugs[["cytarabine_liposomal"]] [1] 21038797 21078096 21117261 21123431 21133296 21133298 ...

Furthermore, the code before seems fine because the error only occurs at the cytarabine_liposomal, I put this code here.

both <- c(list(cytarabine_liposomal = as.numeric(readLines(here::here("drug_vectors", "Athena_searches_depocyte_cytarabine_liposomal_ids.txt")))), getDrugIngredientCodes( cdm, name = "Cytarabine liposomal", doseForm = NULL, ingredientRange = c(1, 1), withConceptDetails = FALSE ) ) concept_drugs[["cytarabine_liposomal"]] <- purrr::list_c(both)

So it is either the list again, or the read in from drug_vectors. To find out which one, @rfherrerac can you run this code please?

daunorubicin + cytarabine combination drugs

cytarabine_daunorubicin <- list(as.numeric(readLines(here::here("drug_vectors", "Athena_search_cytarabine_daunorubicin_ids.txt")))) concept_drugs[["cytarabine_daunorubicin"]] <- purrr::list_c(cytarabine_daunorubicin)

rfherrerac commented 8 months ago

Hi @tiozab I ran it and no error. cytarabine_daunorubicin [[1]] [1] 794045 794047 794050 36810413 36812614 36812796 36812867 36812917 [9] 36813065 36814141 36814234 36814630 36940619 36956681 37499290 37499291 [17] 43030157 43030158 43030159 43042700 43042701 43042702

tiozab commented 8 months ago

great, now we only have to get that other expression working: can you try the following using append() instead of c() ? @rfherrerac

both <- append(list(cytarabine_liposomal = as.numeric(readLines(here::here("drug_vectors", "Athena_searches_depocyte_cytarabine_liposomal_ids.txt")))), getDrugIngredientCodes( cdm, name = "Cytarabine liposomal", doseForm = NULL, ingredientRange = c(1, 1), withConceptDetails = FALSE ) )

concept_drugs[["cytarabine_liposomal"]] <- purrr::list_c(both)

tiozab commented 8 months ago

@rfherrerac you can even remove the naming of the list in the first line

both <- append(list(as.numeric(readLines(here::here("drug_vectors", "Athena_searches_depocyte_cytarabine_liposomal_ids.txt")))), getDrugIngredientCodes( cdm, name = "Cytarabine liposomal", doseForm = NULL, ingredientRange = c(1, 1), withConceptDetails = FALSE ) )

concept_drugs[["cytarabine_liposomal"]] <- purrr::list_c(both)

lets hope either works :-)

rfherrerac commented 8 months ago

Hi @tiozab concept_drugs[["cytarabine_liposomal"]] <- purrr::list_c(both) Error in purrr::list_c(): ! Can't combine x[[1]] and x[[2]] .

tiozab commented 8 months ago

@rfherrerac have you tried both versions. what do x[[1]] and x[[2]] look like?

rfherrerac commented 8 months ago

Both give the same error:

both[[1]] both[[1]] [1] 21038797 21078096 21117261 21123431 21133296 21133298 21133299 21156712 [9] 35606025 35780257 35780445 36279233 37499294 40175463 40175464 40684609 [17] 40716159 40716160 40716161 40716162 40716163 40716164 40744487 40744488 [25] 40744489 40744490 40744491 40744492 40744493 40744494 40863886 41034585 [33] 41191180 41191181 41191182 41238573 41352747 41354212 41354730 41357579 [41] 41359593 41360133 43040392 43144799 43152848 43155980 43185809 43188899 [49] 43210713 43218702 44064063

both[[2]] integer64 [1] 902730

Seems like getDrugIngredientCodes is returning the integer64

tiozab commented 8 months ago

anyway, lets not make it too complicated, getDrugIngredientCodes( cdm, name = "Cytarabine liposomal", doseForm = NULL, ingredientRange = c(1, 1), withConceptDetails = FALSE )

this code does not produce any descendants, so I just added the ingredient to the other list and we will be fine ;-)

I just pushed the new code and the cytarabine is now like the daunorubicin that you tried earlier and it worked :-)

rfherrerac commented 8 months ago

Fantastic. It worked!

daniprietoalhambra commented 8 months ago

[celebrate] Daniel Prieto Alhambra reacted to your message:

From: Ronald Herrera @.> Sent: Thursday, February 1, 2024 9:49:00 AM To: oxford-pharmacoepi/MegaStudy @.> Cc: Daniel Prieto Alhambra @.>; Mention @.> Subject: Re: [oxford-pharmacoepi/MegaStudy] Error while running RunIncidencePrevalence.R (Issue #13)

Fantastic. It worked!

— Reply to this email directly, view it on GitHubhttps://github.com/oxford-pharmacoepi/MegaStudy/issues/13#issuecomment-1920924485, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJXXJRSETAWRNDCTFKKL6ELYRNQIZAVCNFSM6AAAAABB3PN2AOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMRQHEZDINBYGU. You are receiving this because you were mentioned.Message ID: @.***>