Recode observation in column depending on different column

Question

I have a dataset called 'survey' with rows of individual ID's, and columns with many questions. I need to recode the value in 1 column as NA and move the observation to the other column.

For example:

ID    Food    Vegetable 
aaa   NA       NA 
bbb   NA       lemon
ccc   NA       sprout
ddd   fruit    NA
eee   fruit    NA
fff   NA       watermelon

I want to change the lemon and watermelon observations, belonging to ID bbb and fff to put them into the Food column and rename them fruit (survey respondents put them in the wrong column) and leave NA behind in the vegetable column.

To look like:

   ID    Food        Vegetable 
    aaa   NA         NA 
    bbb   fruit      NA
    ccc   NA         sprout
    ddd   fruit      NA
    eee   fruit      NA
    fff   fruit      NA

I've used:

survey<- survey %>%
    mutate(food = if_else(str_detect(Vegetable,"(lemon)|(watermelon)"),"fruit", Food))

Which works to convert NA to fruit in the food column, but it doesn't concert to NA in the vegetable column, it also turns all the other fruits in the food column to NA!

DATA:

    structure(list(ID = c("aaa", "bbb", "ccc", "ddd", "eee", "fff"
), Food = c(NA, NA, NA, "fruit", "fruit", NA), Vegetable = c(NA, 
"lemon", "sprout", NA, NA, "watermelon")), class = "data.frame", row.names = c(NA, 
-6L))

P.S.: This is a follow up to a previous question I asked which was answered. This isn't exactly the same question as before, which is why I started a new one.

dplyr version (1.0.2)

After the step, you can do `mutate(Vegetable = NA_character_)` — akrun, Dec 20 '20 at 21:23

andrew_reece · Accepted Answer · 2020-12-20T21:41:06.853

1

One option is to update Food and Vegetable based on whether Vegetable values are %in% a given list, not_vegetables:

not_vegetables <- c("grape", "tomato")

df %>%
  mutate(Food = if_else(Vegetable %in% not_vegetables, "fruit", Food),
         Vegetable = if_else(Vegetable %in% not_vegetables, NA_character_, Vegetable))

Another way is to replace, across both columns, and do the if_else inside:

df %>%
  mutate(across(
    c(Food, Vegetable), 
    ~replace(., 
             Vegetable %in% not_vegetables, 
             if_else(cur_column() == "Food", 'fruit', NA_character_))
    ))

edited Dec 20 '20 at 21:41

answered Dec 20 '20 at 21:33

andrew_reece

20,390
3
33
58

Unfortunately, neither of these seem to have worked? The first solution ran, but no change to data. the second solution gave an error: ```Error: Problem with `mutate()` input `..1`. x Formula shorthand must be wrapped in `where()`. # Bad data %>% select(~...) # Good data %>% select(where(~...)) ℹ Input `..1` is `across(...)`. Backtrace: >``` – Gabriella Dec 21 '20 at 11:58
1

I tested both locally before posting, sounds like a version issue, and/or something about how your real data is set up. What version of dplyr do you have? `across` was introduced in April. Can you edit your post to include `dput` of your data, or of your toy df? That will make it easier to know what's going on. – andrew_reece Dec 21 '20 at 14:29
Weirdly, when I do this in a new R file, it works...is it possible `%in% ` is masked by another package? – Gabriella Dec 21 '20 at 18:21
1

Glad it worked. Re masking, maybe? You can look at `conflicts(detail=TRUE)` to see which functions have conflicts in your environment. Note that `%in%` is a base R function, I don't think anything in tidyverse overwrites it. – andrew_reece Dec 21 '20 at 18:32

score 1 · Answer 2 · answered Dec 21 '20 at 12:33

Using base R could you try this:

#Conditional
values <- c('grape','tomato')
df$Food <- ifelse(df$Vegetable %in% values,'fruit',df$Food)
df$Vegetable <- ifelse(df$Vegetable %in% values,NA,df$Vegetable)

Output:

df
   ID  Food Vegetable
1 aaa fruit      <NA>
2 bbb fruit      <NA>
3 ccc fruit      <NA>
4 ddd fruit      <NA>

Data

df <- structure(list(ID = c("aaa", "bbb", "ccc", "ddd"), Food = c(NA, 
NA, "fruit", "fruit"), Vegetable = c("grape", "tomato", NA, NA
)), class = "data.frame", row.names = c(NA, -4L))

Recode observation in column depending on different column

2 Answers2