-2

I am currently working on a project where I need to update variables in my dataframe conditionally. I found the simplest way to do this would be to use sqldf/RSQLite. I have tried several code combinations so far and this is the one that I've got the furthest with (anonymising the dataset)

sqldf("UPDATE dataframe SET Variable = 'New Text Value' WHERE Variable.name.category = 'Text Value'")

From the research that I have done on the topic, I understand that sqldf has issues with the '.' character. Is there a simple workaround for this?

Thanks in advance!

johubb
  • 152
  • 11
  • Unlike R, dot is an operator. in SQL so if you want to use a variable name that contains a dot you must double quote the variable name. This is not an issue with sqldf but is how SQL works. Also note that `update` does not return a value so you need to use `c('update dataframe ...', 'select * from dataframe')` to actually return something. – G. Grothendieck Jan 10 '17 at 22:39

1 Answers1

2

The easiest way to conditionally update something is this:

dataframe$Variable[dataframe$Variable.name.category == "Text Value"] <- "New Text Value"

No need for sqldf at all.

The concept is called logical indexing. You index into the column Variable of your data frame based on the truth value of a logical expression dataframe$Variable.name.category == "Text Value".

Another option is with dplyr:

dataframe <- dataframe %>%
  mutate(Variable = ifelse(
    Variable.name.category == "Text Value", 
    "New Text Value", 
    Variable)
  )
AlexR
  • 2,412
  • 16
  • 26