Inserting new values into a data frame using mutate and case_when in dplyr

Question

I have the following data frame of letters with some blank (NA) slots for the lower cases

letters_df <- data.frame(caps = LETTERS[1:10], lows = letters[c(1,2,11,11,11,11,11,11,11,10)])
letters_df[letters_df == "k"] <- NA
letters_df

To fill in some of the blanks I am using this new data frame I constructed

new_letters <- data.frame(caps = c("C", "D", "F", "G", "H"),
                     lows = c("c", "d", "f", "g", "h"))

Following on from a previous question I am using dplyr mutate and case_when as follows

letters_df %>%
  mutate(lows = case_when(
    caps %in% new_letters$caps ~ new_letters$lows,
    TRUE ~ lows))

However, the result does not add in the missing letters and throws an error asking for a vector of the same length as the letters_df column. I thought I had a good handle on the syntax here. Can help me with where I am going wrong?

The mistake is: `caps %in% new_letters$caps ~ new_letters$lows` is more or less: "IF the current value of `caps` is included in `new_letters$caps` THEN take the whole `new_letters$lows` as new value. — Martin Gal, Apr 26 '22 at 14:13
I see. That makes a lot of sense given the results I was getting for other inputs. — rainbird, Apr 26 '22 at 14:18

score 3 · Answer 1 · answered Apr 26 '22 at 14:08

You could consider using a left_join combined with coalesce:

library(dplyr)

letters_df %>% 
  left_join(new_letters, by = "caps") %>% 
  mutate(lows = coalesce(lows.x, lows.y), .keep = "unused")

This returns

   caps lows
1     A    a
2     B    b
3     C    c
4     D    d
5     E <NA>
6     F    f
7     G    g
8     H    h
9     I <NA>
10    J    j

As an alternative that is more similar to your approach, you could transform your new_letters data.frame into a lookup vector returning the same result:

lookup <- tibble::deframe(new_letters)

letters_df %>% 
  mutate(lows = case_when(caps %in% names(lookup) ~ lookup[caps],
                          TRUE ~ lows))

lookup alternative worked for me! – rm1104 May 15 '23 at 18:24 — rm1104, May 15 '23 at 18:24

score 2 · Accepted Answer · answered Apr 26 '22 at 14:48

This is a typical case that rows_* from dplyr can treat:

library(dplyr)

letters_df %>%
  rows_patch(new_letters, by = "caps")

   caps lows
1     A    a
2     B    b
3     C    c
4     D    d
5     E <NA>
6     F    f
7     G    g
8     H    h
9     I <NA>
10    J    j

Inserting new values into a data frame using mutate and case_when in dplyr

2 Answers2