1

I recently make my move to use sqldf package so may be i'm missing the point here.
I'm trying to make a "select case" instead of nested ifelse(). I first made one and it works. 

    cohort$activite = 
sqldf("select case activite when 1 then 'actif a plein temps'  
                            when 2 then 'actif a temps partiel'  
                            when 3 then 'actif en milieu'  
                            ELSE 'Données manquantes' END   
        from cohort")

Then running another one just after in my script, i've received this error : 

    cohort$motifs_ini=
sqldf("select case motifs_ini when 1 then 'contre indication medicale'  
                              when 2 then 'refus du patient'   
                              when 4 then 'bilan en cours'   
                              when 3 then 'autre motif'  
                              when 99 then 'Non precise'  
                              ELSE 'Données Manquantes' END  
      from cohort") 

Erreur dans sqliteExecStatement(con, statement, bind.data) :   
RS-DBI driver: (error in statement: no such table: cohort)  
De plus : Message d'avis :  
In value[[3L]](cond) :  
RAW() can only be applied to a 'raw', not a 'character'

I don't understand why i've got this error. The dataframe exists and i've already modified a viariable with the same type of command. Is it because i'm trying to attack the same data.frame ?
Any help will be appriciate.

Edit : Here are the 6 first lines of my 9213 rows dataframe. 

structure(list(dure_ttt = c(1402L, 556L, 778L, 30L, 123L, 241L
), sexe = c(1L, 2L, 1L, 1L, 2L, 1L), nephro_ini1 = structure(c(68L, 
68L, 68L, 68L, 68L, 68L), .Label = c("0108NL", "0401NL", "0404NL", 
"0405NL", "0406NL", "0407NL", "0408NL", "0409NL", "0410NL", "0411NL", 
"0412NL", "0413NL", "0415NL", "0416NL", "0417NL", "0418NL", "0419NL", 
"0420NL", "0421NL", "0422NL", "0423NL", "0424NL", "0425NL", "0441NL", 
"0442NL", "0443NL", "0446NL", "0447NL", "0448NL", "0449NL", "0450NL", 
"0455NL", "0456NL", "0457NL", "0458NL", "0502NL", "0521NL", "0522NL", 
"0523NL", "0524NL", "0525NL", "0526NL", "0527NL", "0528NL", "0529NL", 
"0530NL", "0531NL", "0532NL", "0533NL", "0534NL", "0535NL", "0536NL", 
"0537NL", "0601NL", "0602NL", "0603NL", "0604NL", "0605NL", "0606NL", 
"0607NL", "0608NL", "0701NL", "0702NL", "0703NL", "0801NL", "0802NL", 
"0804NL", "0NL", "1107NL", "1108NL", "1141NL", "1145NL", "1308NL", 
"1337NL", "1352NL", "1414NL", "1415NL", "1416NL", "1417NL", "1435NL", 
"1501NL", "1502NL", "1503NL", "1505NL", "1506NL", "1507NL", "1521NL", 
"1522NL", "1523NL", "1531NL", "1535NL", "1536NL", "1540NL", "1542NL", 
"1551NL", "1552NL", "1554NL", "1558NL", "1749NL", "1801NL", "1804NL", 
"1805NL", "1806NL", "1812NL", "1813NL", "1814NL", "1817NL", "1823NL", 
"2213NL", "2215NL", "2216NL", "2307NL", "2311NL", "2314NL", "2318NL", 
"3202NL"), class = "factor"), catheter = c(1L, 1L, 1L, 0L, 1L, 
0L), urgence = c(0L, 0L, 1L, 0L, 0L, 0L), activite = c(8L, 4L, 
4L, 10L, 10L, 0L), nb_comorbidite_ini = c(2L, 6L, 6L, 3L, 2L, 
1L), presence_comorbidite_ini = c(1L, 1L, 1L, 1L, 1L, 1L), nb_acv_ini = c(1L, 
1L, 3L, 3L, 0L, 0L), presence_acv_ini = c(1L, 1L, 1L, 1L, 0L, 
0L), diabete_ini = c(0L, 0L, 0L, 0L, 0L, 0L), type_diabete_ini = c(99L, 
99L, 99L, 99L, 99L, 99L), insuline_ini = c(0L, 0L, 0L, 0L, 0L, 
0L), hta_ini = c(1L, 1L, 1L, 0L, 0L, 1L), hypercholesterolemie_ini = c(0L, 
1L, 0L, 0L, 1L, 0L), fumeur_ini = c(0L, 1L, 1L, 0L, 0L, 0L), 
    ex_fumeur_ini = c(0L, 0L, 0L, 0L, 0L, 0L), insuff_respiratoire_ini = c(0L, 
    1L, 0L, 0L, 0L, 0L), oxygenotherapie_ini = c(0L, 0L, 0L, 
    0L, 0L, 0L), insuffisance_cardiaque_ini = c(0L, 0L, 1L, 1L, 
    0L, 0L), stade_IC_ini = c(99L, 99L, 1L, 3L, 99L, 99L), infarctus_ini = c(0L, 
    0L, 1L, 1L, 0L, 0L), angor_ini = c(0L, 0L, 0L, 0L, 0L, 0L
    ), arythmie_ini = c(1L, 0L, 0L, 1L, 0L, 0L), arterite_MI_ini = c(0L, 
    1L, 1L, 0L, 0L, 0L), stade_arterite_MI_ini = c(99L, 1L, 3L, 
    99L, 99L, 99L), avc_ini = c(0L, 0L, 0L, 0L, 0L, 0L), ait_ini = c(0L, 
    0L, 0L, 0L, 0L, 0L), cancer_ini = c(0L, 0L, 0L, 0L, 1L, 0L
    ), cirrhose_ini = c(0L, 0L, 0L, 0L, 0L, 0L), cirrhose_A_ini = c(0L, 
    0L, 0L, 0L, 0L, 0L), cirrhose_BC_ini = c(0L, 0L, 0L, 0L, 
    0L, 0L), hepatite_ini = c(0L, 0L, 0L, 0L, 0L, 0L), hepatite_B_ini = c(0L, 
    0L, 0L, 0L, 0L, 0L), hepatite_C_ini = c(0L, 0L, 0L, 0L, 0L, 
    0L), VIH_ini = c(0L, 0L, 0L, 0L, 0L, 0L), sida_ini = c(0L, 
    0L, 0L, 0L, 0L, 0L), transpl_ini = c(0L, 0L, 0L, 0L, 0L, 
    0L), nb_handicap_ini = c(0L, 1L, 1L, 0L, 0L, 0L), amputat_ini = c(0L, 
    0L, 0L, 0L, 0L, 0L), hemipl_ini = c(0L, 0L, 0L, 0L, 0L, 0L
    ), cecite_ini = c(0L, 0L, 0L, 0L, 0L, 0L), compor_ini = c(0L, 
    1L, 1L, 0L, 0L, 0L), inscri_ini = c(0L, 0L, 0L, 0L, 0L, 0L
    ), motifs_ini = c(1L, 1L, 1L, 1L, 3L, 99L), hospit_ini = c(0L, 
    1L, 0L, 1L, 1L, 0L), nbr_hospit_ini = c(0, 2, 0, 1, 1, 0), 
    duree_hospit_ini = c(99L, 2L, 99L, 2L, 2L, 99L), marche_ini = c(3L, 
    3L, 99L, 1L, 3L, 99L), structure_ini = c(1L, 1L, 1L, 1L, 
    1L, 1L), methode_ini = c(1L, 1L, 1L, 1L, 1L, 1L), voie_abord_ini = c(1L, 
    3L, NA, 4L, 3L, 1L), nb_seances_ini = c(3L, 3L, NA, 3L, 3L, 
    3L), erythropoietine_ini = c(0L, 1L, 0L, 0L, 0L, 0L)), .Names = c("dure_ttt", 
"sexe", "nephro_ini1", "catheter", "urgence", "activite", "nb_comorbidite_ini", 
"presence_comorbidite_ini", "nb_acv_ini", "presence_acv_ini", 
"diabete_ini", "type_diabete_ini", "insuline_ini", "hta_ini", 
"hypercholesterolemie_ini", "fumeur_ini", "ex_fumeur_ini", "insuff_respiratoire_ini", 
"oxygenotherapie_ini", "insuffisance_cardiaque_ini", "stade_IC_ini", 
"infarctus_ini", "angor_ini", "arythmie_ini", "arterite_MI_ini", 
"stade_arterite_MI_ini", "avc_ini", "ait_ini", "cancer_ini", 
"cirrhose_ini", "cirrhose_A_ini", "cirrhose_BC_ini", "hepatite_ini", 
"hepatite_B_ini", "hepatite_C_ini", "VIH_ini", "sida_ini", "transpl_ini", 
"nb_handicap_ini", "amputat_ini", "hemipl_ini", "cecite_ini", 
"compor_ini", "inscri_ini", "motifs_ini", "hospit_ini", "nbr_hospit_ini", 
"duree_hospit_ini", "marche_ini", "structure_ini", "methode_ini", 
"voie_abord_ini", "nb_seances_ini", "erythropoietine_ini"), row.names = c(NA, 
6L), class = "data.frame")
Improve this question
1
  • 2
    Use dput to post enough of your data to reproduce the problem. Commented Feb 12, 2013 at 13:26

2 Answers 2

Reset to default
4

Uhm, I've never used sqldf but as long as it's returning a dataframe assigning the results of the first query to a column of your original dataframe is corrupting its structure and not allowing other queries. I tried not assigning the whole result but only the first column and it worked:

df2$activite<- sqldf("SELECT CASE activite 
                         WHEN 1 TEHN 'actif a plein temps'  
                         WHEN 2 TEHN 'actif a temps partiel'  
                         WHEN 3 TEHN 'actif en milieu'  
                         ELSE 'Données manquantes' END   
                      from df2")[[1]]

(where df2 is your cohort) and then other queries work. Maybe there is a more canonical way to obtain what you need...

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@vodka Thanks. It seems working but I still have a warning : In value[[3L]](cond) : RS-DBI driver: (error in statement: table 'df2' already exists) ...The mystery still remains but, as long as it works...
@Aman Gast, Please put this new issue in reproducible form. If cohort is the dput output in the post then after correcting the spelling of THEN the code in the answer, namely df2 <- cohort; df2$activite<- sqldf("SELECT CASE activite WHEN 1 THEN 'actif a plein temps' WHEN 2 THEN 'actif a temps partiel' WHEN 3 THEN 'actif en milieu' ELSE 'Données manquantes' END from df2")[[1]] ` works without error or warning for me.
Mh, yes, is there a special reason (or just a mistyping?) for the editing with "TEHN"? (also "CASE" does not work as long as the column name is lowercase).
@vodka, THEN must be spelled as in this sentence. What do you mean that CASE does not work as long as the column name is lowercase? The column name after CASE in my last comment is lower case and the code there works for me.
I was simply wondering why the original then became TEHN for some policy about code that't I'm unaware of or if it was simply a mispelling :) (about upper/lowercase my bad, sorry, too many Rsessions around here).
3

@vodka answer is the good answer. sqldf returns a data.frame. So assign it directly will corrput your initial data.frame (becomes list of list).

Here just a way to return a prettier result. You can use AS to assign the new column name, and then use it to assign the old column.

cohort <- data.frame(motifs_ini=c(1,2,3,4,99,100))
cohort$motifs_ini <- sqldf("SELECT CASE  motifs_ini
                               WHEN 1 THEN 'contre indication medicale'  
                               WHEN 2 THEN 'refus du patient'   
                               WHEN 4 THEN 'bilan en cours'   
                               WHEN 3 THEN 'autre motif'  
                               WHEN 99 THEN 'Non precise'  
                               ELSE 'Données Manquantes' 
                          END AS newcol
                          FROM cohort")$newcol


cohort
                  motifs_ini
1 contre indication medicale
2           refus du patient
3                autre motif
4             bilan en cours
5                Non precise
6         Données Manquantes
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.