Delete NAs, based on duplicated ID variables

user_10359295 0
Viewed 29

My data looks something like this

zz <- 'wb_iso3c                   country year wbclass
1:      YUG "Serbia and Montenegro (former)" 1990    NA
2:      YUG            "Yugoslavia (former)" 1990      UM
3:      YUG "Yugoslavia (former)" 1991    NA
4:      YUG            "Serbia and Montenegro (former)" 1991      UM
5:      YUG "Serbia and Montenegro (former)" 1992      NA
6:      YUG            "Yugoslavia (former)" 1992    NA'
Data <- read.table(text=zz, header = TRUE)

I would like to find a way to systematically and efficiently drop observations when: My observation is duplicated only considering wb_iso3c and year. (hence I do not care if another variable such as country has different values). Among the "duplicated" observations, I would like to keep the observation where wbclass is not NA. If wbclass is NA for both observations, it is indifferent which row to keep.

The final dataset should look something like this

   wb_iso3c                        country year wbclass
1:      YUG            Yugoslavia (former) 1990      UM
2:      YUG Serbia and Montenegro (former) 1991      UM
3:      YUG Serbia and Montenegro (former) 1992    <NA>

Thanks a lot in advance for your help. If you can use data.table of dyplr it would be great.

missing-data data-manipulation r
2 Answers

With dplyr you could try the following.

First group_by both wb_iso3c and year, with intent on obtaining one observation for each distinct combination of these two columns.

Then arrange (sort order) the wbclass column within the group. With arrange, the NA values will always be sorted to the end.

Then slice(1) will keep the first row of data in each group.

library(dplyr)

Data %>%
  group_by(wb_iso3c, year) %>%
  arrange(wbclass) %>%
  slice(1)

Output

  wb_iso3c country                         year wbclass
  <chr>    <chr>                          <int> <chr>  
1 YUG      Yugoslavia (former)             1990 UM     
2 YUG      Serbia and Montenegro (former)  1991 UM     
3 YUG      Serbia and Montenegro (former)  1992 NA
user_34606700

A very long base R solution can be based on testing for duplicated values and then computing a variable to store dupes and NA. After that you can filter and get the expected result. Here the code:

#Code
Data$i1 <- duplicated(paste(Data$wb_iso3c,Data$year),fromLast = T)
Data$i2 <- ifelse(Data$i1 & is.na(Data$wbclass),1,0)
Data2 <- Data[Data$i2==0,]
Data2$i1 <- NULL
Data2$i2 <- NULL

Output:

   wb_iso3c                        country year wbclass
2:      YUG            Yugoslavia (former) 1990      UM
4:      YUG Serbia and Montenegro (former) 1991      UM
6:      YUG            Yugoslavia (former) 1992    <NA>
user_20808480
Related