Removing rows that include NA in specific column only if the rows are duplicate (Based on two other columns)

The summarise after grouping can be used here as dplyr version >= 1.0 can return more than one row per group. Here, we can use the grouping columns, and then do the summarise across the numeric columns to return the non-NA elements if at least one of them is non-NA or else return the NA

library(dplyr)
df1 %>%
   group_by(`P.I.D..`, Status, List.year) %>%
   summarise(across(where(is.numeric),  
     ~ if(all(is.na(.))) NA_real_ else .[complete.cases(.)]), .groups = 'drop')

-output

# A tibble: 10 x 5
#   P.I.D..     Status List.year CURRENT_LAND_VALUE CURRENT_IMPROVEMENT_VALUE
#   <chr>       <chr>      <int>              <dbl>                     <dbl>
# 1 003-913-627 S           2002                 NA                        NA
# 2 003-913-627 S           2008             464000                    391000
# 3 003-913-627 S           2008             339000                    246000
# 4 003-913-627 S           2018            2814000                    901000
# 5 003-913-627 T           2007             390000                    282000
# 6 003-913-627 T           2007             295000                    180000
# 7 003-913-627 T           2010            1578000                   1201000
# 8 003-913-627 X           2000                 NA                        NA
# 9 003-913-627 X           2009             339000                    246000
#10 003-913-627 X           2009             464000                    391000

Details - across loops over multiple columns. Inside it, the first expression can be columns of interest. We could use everything() if all the rest of the columns needs to be loop or a expression to check the type of the column and if it meets only do the looping (where(is.numeric)), then we create a lamdba expression (~ - function(x) equivalent), and use some condition if/else. It may not be needed, but it is just an option to prevent it from failing when some columns have only NA. In the else, we subset the column with non-NA elements (.[complete.cases(.)]).

An assumption is that it will return the same length for each column, or else can be wrapped in a list

data

df1 <- structure(list(P.I.D.. = c("003-913-627", "003-913-627", "003-913-627", 
"003-913-627", "003-913-627", "003-913-627", "003-913-627", "003-913-627", 
"003-913-627", "003-913-627", "003-913-627"), Status = c("X", 
"T", "S", "S", "S", "T", "T", "S", "S", "X", "X"), List.year = c(2000L, 
2010L, 2018L, 2018L, 2002L, 2007L, 2007L, 2008L, 2008L, 2009L, 
2009L), CURRENT_LAND_VALUE = c(NA, 1578000L, NA, 2814000L, NA, 
390000L, 295000L, 464000L, 339000L, 339000L, 464000L), 
CURRENT_IMPROVEMENT_VALUE = c(NA, 
1201000L, NA, 901000L, NA, 282000L, 180000L, 391000L, 246000L, 
246000L, 391000L)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11"))

I suspect this is due to the way you are joining the two data frames, but without being able to produce it, my first thought is that you could do something like:

df %>% 
  distinct(`P.I.D.`, CURRENT_LAND_VALUE, .keep_all = TRUE) %>% 
  filter(!is.na(CURRENT_LAND_VALUE))

The distinct() would give you the distinct inputs with the combinations of the P.I.D column and the CURRENT_LAND_VALUE column (thus if its duplicated, youd get the NA and the actual value as distinct rows) and then from there you could safely remove any NA values (assuming the NAs are only from the duplication and it isn't else where in the data).

If there are NAs naturally occurring then it would get more tricky and I would focus on shaping the full_join into a different type of join.

EDIT: I just noticed naturally occurring NAs do exist. My solution is less helpful then.

Base R solution:

subset(within(df, {
  pk <- as.integer(as.factor(paste0(P.I.D.., List.year)))
  pk_cnt <- ave(pk, pk, FUN = length)
}), pk_cnt == 1 | !(is.na(CURRENT_LAND_VALUE) & is.na(CURRENT_IMPROVEMENT_VALUE)),
select = names(df))

Data:

   df <-  structure(list(P.I.D.. = c("003-913-627", "003-913-627", "003-913-627", 
    "003-913-627", "003-913-627", "003-913-627", "003-913-627", "003-913-627", 
    "003-913-627", "003-913-627", "003-913-627"), Status = c("X", 
    "T", "S", "S", "S", "T", "T", "S", "S", "X", "X"), List.year = c(2000L, 
    2010L, 2018L, 2018L, 2002L, 2007L, 2007L, 2008L, 2008L, 2009L, 
    2009L), CURRENT_LAND_VALUE = c(NA, 1578000L, NA, 2814000L, NA, 
    390000L, 295000L, 464000L, 339000L, 339000L, 464000L), CURRENT_IMPROVEMENT_VALUE = c(NA, 
    1201000L, NA, 901000L, NA, 282000L, 180000L, 391000L, 246000L, 
    246000L, 391000L)), row.names = c(NA, -11L), class = "data.frame")

You can keep those rows that have only one row in a group or do not have NA value in both CURRENT_LAND_VALUE and CURRENT_IMPROVEMENT_VALUE.

library(dplyr)

df %>%
  group_by(P.I.D.., List.year) %>%
  filter(n() == 1 | !(is.na(CURRENT_LAND_VALUE) & is.na(CURRENT_IMPROVEMENT_VALUE)))

#    P.I.D..     Status List.year CURRENT_LAND_VALUE CURRENT_IMPROVEMENT_VALUE
#   <chr>       <chr>      <int>              <int>                     <int>
# 1 003-913-627 X           2000                 NA                        NA
# 2 003-913-627 T           2010            1578000                   1201000
# 3 003-913-627 S           2018            2814000                    901000
# 4 003-913-627 S           2002                 NA                        NA
# 5 003-913-627 T           2007             390000                    282000
# 6 003-913-627 T           2007             295000                    180000
# 7 003-913-627 S           2008             464000                    391000
# 8 003-913-627 S           2008             339000                    246000
# 9 003-913-627 X           2009             339000                    246000
#10 003-913-627 X           2009             464000                    391000