[R] apply with multiple references and database interactivity

[Steve E.]

Hi R Colleagues,

I have a small R script that relies on two for-loops to pull data from a
database, make some edits to the data returned from the query, then inserts
the updated data back into the database. The script works just fine, no
problems, except that I am striving to get away from loops, and to focus on
the apply family of tools. In this case, though, I did not know quite where
to start with apply. I wonder if someone more adept with apply would not
mind taking a look at this, and suggesting some tips as to how this could
have been accomplished with apply instead of nested loops. More details on
what the script is accomplishing are included below.

Thanks in advance for your help and consideration.


Steve

Here, I have a df that includes a list of keywords that need to be edited,
and the corresponding edit. The script goes through a database of people,
identifies whether any of the keywords associated with each person are in
the list of keywords to edit, and, if so, pulls in the list of keywords and
the person details, swaps the new keyword for the old keyword, then inserts
the updated keywords back into the database for that person (many keywords
are associated with each person, and they are in an array, hence the
somewhat complicated procedure). The if-statement provides a list of
keywords in the df that were not found in the database, and 'm' is just a
counter to help me know how many keywords the script changed.

for(i in 1:nrow(keywords)) {
  pull <- dbGetQuery(conn = con, statement = paste0("SELECT person_id,
expertise FROM people WHERE expertise RLIKE '; ", keywords[i, 2], ";'"))
  pull$expertise <- gsub(keywords[i, 2], keywords[i, 3], pull$expertise)
  if (nrow(pull)==0) {
    sink('~/Desktop/r1', append = TRUE)
    print(keywords[i, ]$keyword)
    sink() } else
    {
    for (j in 1:nrow(pull)) {
    dbSendQuery(conn = con, statement = paste0("UPDATE people SET expertise
= '", pull[j, ]$expertise, "' WHERE person_id = ", pull[j, ]$person_id)) }
      m=m+1
    } }




--
View this message in context: http://r.789695.n4.nabble.com/apply-with-multiple-references-and-database-interactivity-tp4711148.html
Sent from the R help mailing list archive at Nabble.com.