Convert a character to NA based on a condition in R dplyr

Question

I have a data frame that looks like this

library(tidyverse)
df3 <- tibble(col1 = c("apple",rep("banana",3)),
              col2 = c("aple", "banan","bananb","banat"), 
              count_col1 = c(1,4,4,4), 
              count_col2 = c(4,1,1,1))
df3
#> # A tibble: 4 × 4
#>   col1   col2   count_col1 count_col2
#>   <chr>  <chr>       <dbl>      <dbl>
#> 1 apple  aple            1          4
#> 2 banana banan           4          1
#> 3 banana bananb          4          1
#> 4 banana banat           4          1

^{Created on 2022-02-17 by the reprex package (v2.0.1)}

I want to group_by col1 and when count_col2 > count_col1 the value of col1 to be converted to NA,

and when count_col1 > count_col2 then the value of col2 to be converted to NA.

I want my data to look like this

#> # A tibble: 4 × 4
#>   col1    col2        count_col1 count_col2
#>   <chr>   <chr>          <dbl>      <dbl>
#> 1   NA    aple             1          4
#> 2 banana   NA              4          1
#> 3 banana   NA              4          1
#> 4 banana   NA              4          1

I am not sure if this can be achieved by mutate(case_when...) i fail so far

df3 %>% 
  group_by(col1) %>% 
  mutate(case_when(count_col2 > count_col1 ~ col1==NA,
                   count_col1 > count_col2 ~ col2==NA ))

score 1 · Accepted Answer · answered Feb 17 '22 at 23:07

You can achieve your required output with ifelse(), i.e.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

df3 <- tibble(col1 = c("apple",rep("banana",3)),
              col2 = c("aple", "banan","bananb","banat"), 
              count_col1 = c(1,4,4,4), 
              count_col2 = c(4,1,1,1))
df3
#> # A tibble: 4 × 4
#>   col1   col2   count_col1 count_col2
#>   <chr>  <chr>       <dbl>      <dbl>
#> 1 apple  aple            1          4
#> 2 banana banan           4          1
#> 3 banana bananb          4          1
#> 4 banana banat           4          1

df3 %>% 
  group_by(col1) %>% 
  mutate(col1 = ifelse(count_col2 > count_col1, NA, col1),
         col2 = ifelse(count_col1 > count_col2, NA, col2))
#> # A tibble: 4 × 4
#> # Groups:   col1 [2]
#>   col1   col2  count_col1 count_col2
#>   <chr>  <chr>      <dbl>      <dbl>
#> 1 <NA>   aple           1          4
#> 2 banana <NA>           4          1
#> 3 banana <NA>           4          1
#> 4 banana <NA>           4          1

^{Created on 2022-02-18 by the reprex package (v2.0.1)}

Or with case_when():

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

df3 <- tibble(col1 = c("apple",rep("banana",3)),
              col2 = c("aple", "banan","bananb","banat"), 
              count_col1 = c(1,4,4,4), 
              count_col2 = c(4,1,1,1))
df3
#> # A tibble: 4 × 4
#>   col1   col2   count_col1 count_col2
#>   <chr>  <chr>       <dbl>      <dbl>
#> 1 apple  aple            1          4
#> 2 banana banan           4          1
#> 3 banana bananb          4          1
#> 4 banana banat           4          1

df3 %>% 
  group_by(col1) %>% 
  mutate(col1 = case_when(count_col2 > count_col1 ~ NA_character_,
                          TRUE ~ col1),
         col2 = case_when(count_col1 > count_col2 ~ NA_character_, 
                          TRUE ~ col2))
#> # A tibble: 4 × 4
#> # Groups:   col1 [2]
#>   col1   col2  count_col1 count_col2
#>   <chr>  <chr>      <dbl>      <dbl>
#> 1 <NA>   aple           1          4
#> 2 banana <NA>           4          1
#> 3 banana <NA>           4          1
#> 4 banana <NA>           4          1

^{Created on 2022-02-18 by the reprex package (v2.0.1)}

Does that solve your problem?

score 1 · Answer 2 · answered Feb 18 '22 at 01:59

I am not sure if you really need to group_by here since even with group_by each value of count_col1 is being compared with corresponding value of count_col2. There is nothing which is happening within a "group".

Here's a base R option -

df3$col1[df3$count_col2 > df3$count_col1] <- NA
df3$col2[df3$count_col1 > df3$count_col2] <- NA
df3

#  col1   col2  count_col1 count_col2
#  <chr>  <chr>      <dbl>      <dbl>
#1 NA     aple           1          4
#2 banana NA             4          1
#3 banana NA             4          1
#4 banana NA             4          1

Convert a character to NA based on a condition in R dplyr

2 Answers2