Replacing NA value in dataframe by first or last value of other columns within group

user_14011731 0
Viewed 112

I have the following dataframe:

Group<-c(A,A,A,B,B,B)
Dates<-(c("01-01-2000","02-01-2000","03-01-2000","01-05-2020","02-05-2020","03-05-2020"))
Departure<-c("01-01-2000","01-01-2000","01-01-2000",NA,NA,NA)
Arrival<-c(NA,NA,NA,"03-02-2020","03-02-2020","03-02-2020")
Dates<-data.frame(Dates,Departure,Arrival)
Dates

 Group  Dates      Departure    Arrival
     1  01-01-2000 02-01-2000       <NA>
     1  02-01-2000 02-01-2000       <NA>
     1  03-01-2000 02-01-2000       <NA>
     2  01-05-2000       <NA> 31-12-2020
     2  02-05-2000       <NA> 31-12-2020
     2  03-05-2000       <NA> 31-12-2020

Here is what I want to do:

  • For the "Departure" column: if the value is NOT NA, leave as is. If the value is NA, then replace with the FIRST value of the "Dates" column within each group.
  • For the "Arrival" column: if the value is NOT NA, leave as is. If the value is NA, then replace with the LAST value of the "Dates" column within each group.

I would then obtain the following dataframe:

 Group  Dates      Departure    Arrival
     1  01-01-2000 02-01-2000   03-01-2000
     1  02-01-2000 02-01-2000   03-01-2000
     1  03-01-2000 02-01-2000   03-01-2000
     2  01-05-2000 01-05-2000   31-12-2020
     2  02-05-2000 01-05-2000   31-12-2020
     2  03-05-2000 01-05-2000   31-12-2020

I'm thinking of using a combination of if else and group_by from dplyr, but beyond that I'm stuck. Any suggestions would be appreciated!!

na dataframe r replace
3 Answers

An option is to use replace_na (from tidyr) after grouping by 'Group' to replace the NA elements with either the first or last values of 'Dates' column

library(dplyr)
library(tidyr)
df1 %>% 
   group_by(Group) %>% 
   mutate(Departure = replace_na(Departure, first(Dates)), 
          Arrival = replace_na(Arrival, last(Dates))) %>% 
   ungroup

NOTE: Here we assume that 'Dates' are already ordered. If not, take the min and max after converting to Date class

library(lubridate)
df1 %>% 
   mutate(across(-Group, dmy)) %>%
   group_by(Group) %>% 
   mutate(Departure = replace_na(Departure, min(Dates)), 
          Arrival = replace_na(Arrival, max(Dates))) %>% 
   ungroup
user_37322710

A data.table option

setDT(Dates)[
  ,
  .(
    Dates = Dates,
    Departure = replace(Departure, is.na(Departure), min(Dates)),
    Arrival = replace(Arrival, is.na(Arrival), max(Dates))
  ),
  Group
]

gives

   Group      Dates  Departure    Arrival
1:     A 01-01-2000 01-01-2000 03-01-2000
2:     A 02-01-2000 01-01-2000 03-01-2000
3:     A 03-01-2000 01-01-2000 03-01-2000
4:     B 01-05-2020 01-05-2020 03-02-2020
5:     B 02-05-2020 01-05-2020 03-02-2020
6:     B 03-05-2020 01-05-2020 03-02-2020
user_121587570

The OP has asked to replace NA values in a data.frame.

One of data.table's strong points is the ability to update by reference, i.e., to replace values without copying the whole dataset.

In addition, data.table's fcoalesce() function is used together with Map().

library(data.table)
cols <- c("Departure", "Arrival")
setDT(df_Dates)[, (cols) := Map(fcoalesce, .SD, Dates[c(1L, .N)]), .SDcols = cols, by = Group]
df_Dates

   Group      Dates  Departure    Arrival
1:     A 01-01-2000 01-01-2000 03-01-2000
2:     A 02-01-2000 01-01-2000 03-01-2000
3:     A 03-01-2000 01-01-2000 03-01-2000
4:     B 01-05-2020 01-05-2020 03-02-2020
5:     B 02-05-2020 01-05-2020 03-02-2020
6:     B 03-05-2020 01-05-2020 03-02-2020

Map() picks the first value of Dates in each groups for the first column Departures and the last value Dates[.N] for the second column Arrival when calling fcoalesce().

Please, note that the original dataset has been changed in place which can be verified by calling address() before and after.

Using min(Dates) and max(Dates) instead of first(Dates) and last(Dates), or Dates[1L] and Dates[.N], resp., may lead to unexpected results with other datasets as Dates are given as character dates in the format DD-MM-YYYY which would be sorted on day of month, first.

Data

df_Dates <- data.frame(
  Group = c("A", "A", "A", "B", "B", "B"), 
  Dates = c("01-01-2000", "02-01-2000", "03-01-2000", "01-05-2020", "02-05-2020", "03-05-2020"), 
  Departure = c("01-01-2000", "01-01-2000", "01-01-2000", NA, NA, NA), 
  Arrival = c(NA, NA, NA, "03-02-2020", "03-02-2020", "03-02-2020"))
user_38170040
Related