{"action":"created","issue":{"url":"https://api.github.com/repos/hadley/dplyr/issues/631","repository_url":"https://api.github.com/repos/hadley/dplyr","labels_url":"https://api.github.com/repos/hadley/dplyr/issues/631/labels{/name}","comments_url":"https://api.github.com/repos/hadley/dplyr/issues/631/comments","events_url":"https://api.github.com/repos/hadley/dplyr/issues/631/events","html_url":"https://github.com/hadley/dplyr/issues/631","id":44001266,"number":631,"title":"Equivalent of SQL CASE","user":{"login":"edwindj","id":542492,"avatar_url":"https://avatars.githubusercontent.com/u/542492?v=3","gravatar_id":"","url":"https://api.github.com/users/edwindj","html_url":"https://github.com/edwindj","followers_url":"https://api.github.com/users/edwindj/followers","following_url":"https://api.github.com/users/edwindj/following{/other_user}","gists_url":"https://api.github.com/users/edwindj/gists{/gist_id}","starred_url":"https://api.github.com/users/edwindj/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/edwindj/subscriptions","organizations_url":"https://api.github.com/users/edwindj/orgs","repos_url":"https://api.github.com/users/edwindj/repos","events_url":"https://api.github.com/users/edwindj/events{/privacy}","received_events_url":"https://api.github.com/users/edwindj/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/hadley/dplyr/labels/data%20frame","name":"data frame","color":"207de5"},{"url":"https://api.github.com/repos/hadley/dplyr/labels/enhancement","name":"enhancement","color":"84b6eb"}],"state":"closed","locked":false,"assignee":{"login":"hadley","id":4196,"avatar_url":"https://avatars.githubusercontent.com/u/4196?v=3","gravatar_id":"","url":"https://api.github.com/users/hadley","html_url":"https://github.com/hadley","followers_url":"https://api.github.com/users/hadley/followers","following_url":"https://api.github.com/users/hadley/following{/other_user}","gists_url":"https://api.github.com/users/hadley/gists{/gist_id}","starred_url":"https://api.github.com/users/hadley/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hadley/subscriptions","organizations_url":"https://api.github.com/users/hadley/orgs","repos_url":"https://api.github.com/users/hadley/repos","events_url":"https://api.github.com/users/hadley/events{/privacy}","received_events_url":"https://api.github.com/users/hadley/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/hadley/dplyr/milestones/8","html_url":"https://github.com/hadley/dplyr/milestones/0.5","labels_url":"https://api.github.com/repos/hadley/dplyr/milestones/8/labels","id":766580,"number":8,"title":"0.5","description":"","creator":{"login":"hadley","id":4196,"avatar_url":"https://avatars.githubusercontent.com/u/4196?v=3","gravatar_id":"","url":"https://api.github.com/users/hadley","html_url":"https://github.com/hadley","followers_url":"https://api.github.com/users/hadley/followers","following_url":"https://api.github.com/users/hadley/following{/other_user}","gists_url":"https://api.github.com/users/hadley/gists{/gist_id}","starred_url":"https://api.github.com/users/hadley/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hadley/subscriptions","organizations_url":"https://api.github.com/users/hadley/orgs","repos_url":"https://api.github.com/users/hadley/repos","events_url":"https://api.github.com/users/hadley/events{/privacy}","received_events_url":"https://api.github.com/users/hadley/received_events","type":"User","site_admin":false},"open_issues":28,"closed_issues":217,"state":"open","created_at":"2014-08-26T20:16:29Z","updated_at":"2016-03-14T22:23:08Z","due_on":null,"closed_at":null},"comments":25,"created_at":"2014-09-26T04:49:26Z","updated_at":"2016-03-15T18:53:15Z","closed_at":"2016-03-14T19:49:20Z","body":"In data cleaning data often contains values that one would like to impute/replace.\r\n\r\nFor example: \r\n\r\nIn a survey for smokers, one would not ask a person his or her cigarette consumption when he/she has indicated not to be a smoker. So technically the number of cigarretes is not measured, but it can be deduced to be zero.\r\n\r\n```S\r\nsmoke <- read.csv(textConnection(\r\n\"name,is_smoker,cigarrettes,cigares\r\n John,TRUE,15, 0\r\n Adam,FALSE,NA,NA\r\n Phil,TRUE,0,1\r\n\"\r\n))\r\n\r\n#standard R way\r\nsmoke$cigarrettes[!smoke$is_smoker & is.na(smoke$cigarrettes)] <- 0\r\n```\r\nHow does one do this elegantly in dplyr?\r\n\r\nMy 2 cents:\r\n```S\r\n#dplyr option 1: split the data set, fix one part and combine the two\r\nsmoker_true <- smoke %>% filter(!is_smoker, is.na(smoke$cigarettes))\r\n# complement\r\nsmoker_false <- smoke %>% filter(is_smoker | !is.na(smoke$cigarettes))\r\n\r\nsmoke2 <- rbind( smoker_true %>% mutate(cigarrettes=0)\r\n               , smoker_false\r\n)\r\n```\r\nwhich I don't find elegant dplyr code.\r\n\r\nSo try a little harder\r\n```S\r\n# dplyr option 2: replace the whole variable and do the filtering in the assignment\r\nsmoke2 <- \r\n  smoke %>%\r\n  mutate(cigarrettes=ifelse(!is_smoker & is.na(cigarrettes), 0, cigarrettes))\r\n```\r\nIt works, but this is not efficient/workable for the database backends and I find it unelegant to\r\ndo the filtering in the assignment.\r\n\r\nI suggest a new method:\r\n`mutate_where` that allows for changing values of _existing_ variables of a subset of a `tbl`.\r\nSo something like\r\n```S\r\nsmoke2 <-\r\n   smoke %>% mutate_where(.where=!is_smoker&is.na(cigarrettes), cigarettes=0)\r\n```  \r\nOr another option (in analogy with `group` and `grouped_<tbl>`) to change `filter` or introduce `filtered_<tbl>`/add_filter: setting a filter lets one work on a subset of the data, but one can `remove_filter`.\r\n\r\n```S\r\nsmoke2 <-\r\n   smoke %>% \r\n   add_filter(is_smoker, is.na(cigarrettes) %>%\r\n   mutate(cigarrettes=0) %>%\r\n   remove_filter()\r\n```\r\n\r\nAny better ideas or suggestions?"},"comment":{"url":"https://api.github.com/repos/hadley/dplyr/issues/comments/196971316","html_url":"https://github.com/hadley/dplyr/issues/631#issuecomment-196971316","issue_url":"https://api.github.com/repos/hadley/dplyr/issues/631","id":196971316,"user":{"login":"hadley","id":4196,"avatar_url":"https://avatars.githubusercontent.com/u/4196?v=3","gravatar_id":"","url":"https://api.github.com/users/hadley","html_url":"https://github.com/hadley","followers_url":"https://api.github.com/users/hadley/followers","following_url":"https://api.github.com/users/hadley/following{/other_user}","gists_url":"https://api.github.com/users/hadley/gists{/gist_id}","starred_url":"https://api.github.com/users/hadley/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hadley/subscriptions","organizations_url":"https://api.github.com/users/hadley/orgs","repos_url":"https://api.github.com/users/hadley/repos","events_url":"https://api.github.com/users/hadley/events{/privacy}","received_events_url":"https://api.github.com/users/hadley/received_events","type":"User","site_admin":false},"created_at":"2016-03-15T18:53:15Z","updated_at":"2016-03-15T18:53:15Z","body":"@kevinushey yeah, but I don't see that being a particularly common use case. And I think using `~` means that we'll need to do some work to make `case_when()` work with the hybrid evaluator."}}