Delete duplicates after coding response on one variable R

user_17306637 0
Viewed 26

Overall aim: Delete duplicates after coding response on one variable

I have a dataset where an individual can select multiple options on a variable [e.g. Country Code]. I want to delete the duplicates but first I want to make sure that I capture all of the information.

For example Country Code, where options 1-5 is Europe and 6-10 is Africa- I created variables that coded Europe= 1 if Country Code =1:5 and 0 if 6:10 (visa versa for Africa).

ID Country Code Europe Africa
1 1 1 0
1 4 1 0
1 10 0 1
2 3 1 0
2 10 0 1
3 7 0 1

But I want to see if they answered 1 to both, and then delete the duplicate IDs. Hence I want to create a dataset that looks like this:

ID Europe Africa Both
1 1 1 1
2 1 1 1
3 0 1 0

I've tried this:

aggregate(x=Europe,by=list(ID),FUN=sum)) to create a sumEurope (followed by sumAfrica) to then say "if sumEurope*sumAfica >0 = 1" However, the above code has an error that the "Assigned data..must be compatible with existing data." as there are more existing rows than assigned rows.

duplicates r
2 Answers

We could use summarise with max across Europe and Africa and then an ifelse statement:

library(dplyr)

df %>% 
  group_by(ID) %>% 
  summarise(across(c(Europe, Africa), max)) %>% 
  mutate(Both = ifelse(Africa == 1 & Europe == 1, 1, 0))

     ID Europe Africa  Both
  <int>  <int>  <int> <dbl>
1     1      1      1     1
2     2      1      1     1
3     3      0      1     0

data:

structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 3L), CountryCode = c(1L, 
4L, 10L, 3L, 10L, 7L), Europe = c(1L, 1L, 0L, 1L, 0L, 0L), Africa = c(0L, 
0L, 1L, 0L, 1L, 1L)), class = "data.frame", row.names = c(NA, 
-6L))
user_133216470

In BASE R:

transform(+aggregate(cbind(Europe, Africa)~ID, df, any), Both = Europe * Africa)
  ID Europe Africa Both
1  1      1      1    1
2  2      1      1    1
3  3      0      1    0

If you dont mind the logical values:

transform(aggregate(cbind(Europe, Africa)~ID, df, any), Both = Europe * Africa)
  ID Europe Africa Both
1  1   TRUE   TRUE    1
2  2   TRUE   TRUE    1
3  3  FALSE   TRUE    0
user_83802720
Related