In a data.frame, I would like to automatically remove a row with 'NA' on Column_E if there is another row with the same info in all the other columns, such that:
Column_A Column_B Column_C Column_D Column_E
A121 NAME1 A321 2019-01-01 NA
A121 NAME1 A321 2019-01-01 2020-02-01
A123 NAME2 A322 2019-01-01 2020-01-01
A123 NAME2 A322 2019-01-01 NA
A124 NAME3 A323 2019-01-01 2019-01-01
A124 NAME4 A324 2019-01-01 NA
The output should be:
Column_A Column_B Column_C Column_D Column_E
A121 NAME1 A321 2019-01-01 2020-02-01
A123 NAME2 A322 2019-01-01 2020-01-01
A124 NAME3 A323 2019-01-01 2019-01-01
A124 NAME4 A324 2019-01-01 NA
any ideas?
You could select rows where there is no NA value or there is only 1 row in the group.
library(dplyr)
df %>%
group_by(across(Column_A:Column_D)) %>%
filter(!is.na(Column_E) | n() == 1)
# Column_A Column_B Column_C Column_D Column_E
# <chr> <chr> <chr> <chr> <chr>
#1 A121 NAME1 A321 2019-01-01 2020-02-01
#2 A123 NAME2 A322 2019-01-01 2020-01-01
#3 A124 NAME3 A323 2019-01-01 2019-01-01
#4 A124 NAME4 A324 2019-01-01 NA
Same logic in data.table :
library(data.table)
setDT(df)
df[, .SD[!is.na(Column_E) | .N == 1], .(Column_A, Column_B, Column_C, Column_D)]
and base R :
subset(df, ave(!is.na(Column_E),Column_A, Column_B, Column_C, Column_D,
FUN = function(x) x | length(x) == 1))
data
df <- structure(list(Column_A = c("A121", "A121", "A123", "A123", "A124",
"A124"), Column_B = c("NAME1", "NAME1", "NAME2", "NAME2", "NAME3",
"NAME4"), Column_C = c("A321", "A321", "A322", "A322", "A323",
"A324"), Column_D = c("2019-01-01", "2019-01-01", "2019-01-01",
"2019-01-01", "2019-01-01", "2019-01-01"), Column_E = c(NA, "2020-02-01",
"2020-01-01", NA, "2019-01-01", NA)), class = "data.frame",
row.names = c(NA, -6L))