R code not working to merge data (unduplicate) in columns based on identical values

user_11628190 0
Viewed 332

I am trying to do some data wrangling on a tibble (dataframe) using dplyr, to unduplicate records, where if an id shows up twice, the resulting record will contain the same values if they are all identical, or an NA if there is a discrepancy in one of the records. For example, if I have df:

id       date        amount     tag
---      ----        ------     ---
1        2018-01-03  10         big
2        2019-01-16  20         small
3        2020-01-05  30         big
3        2001-03-04  30         big
1        2018-01-03  5          big

The result should look like:

id       date        amount     tag
---      ----        ------     ---
1        2018-01-03  NA         big
2        2019-01-16  20         small
3        NA          30         big

Based on other answers I've found on stack overflow, I have tried various methods of using summarise_all including:

new_df <- df %>% group_by(id) %>% summarise_all(function(x) ifelse(all(x[1] == x),x[1],NA))

new_df <- df %>% group_by(id) %>% summarise_all(list(~ if(all(.[1] == .)) .[1] else NA))

new_df <- df %>% group_by(id) %>% summarise_all(funs(if(all(.[1] == .)) .[1] else NA))

Since I was able to use ifelse(all(x[1] == x),x[1],NA) on its own with a vector and it worked fine, I thought that would work with summarise_all. But when I use it with summarise_all or the other variants I show above, I get the error:

Error in summarise_impl(.data, dots): Column `date` can't promote group 2 to character

I suspect I just need to make a small tweak to my code to get it to work, but I've been working on this all day, and I don't know why it isn't working... So any help that the community can provide would be appreciated. This is the first time I've actually asked a question on stack overflow, because I almost always can find the answer from other people's questions :-) Thank you so much for any help!

dplyr r
1 Answers

First, the solution:

d %>% 
  group_by(id) %>% 
  summarise_all(~if(n_distinct(.) == 1) first(.) else c(NA, .)[1])

This is actually a little tricky. You'd think one could write simply:

d %>% 
  group_by(id) %>% 
  summarise_all(~if(n_distinct(.) == 1) first(.) else NA)

Which is just an alternative to your if (all ...) ... else ..., using some more dplyr functions.

However, dplyr doesn't like simply giving NA, but rather you need to be type specific. E.g. you need to provide NA_character_ or NA_integer_ etc to match the correct data type. This is why your code is failing, the error says that group 2 (i.e. id == 2 in this case) is failing to be "promoted" to character. This means that the NA provided there in column Date isn't being coerced to character and a new column is failing to be created.

Since you don't want to code all the correct NA types, I use a little trick here. Using c(NA, .)[1] to combine an NA value with the original variable will coerce that NA to the correct type, which I then use. You can probably use other tricks to get the correct NA too.

user_43414400
Related