Copy values from one row to another based on condition

user_11572263 0
Viewed 620

I have this dataset:

# Data
movmnt_id <- c("101", "601", "105", "321")
plant <- c("FF", "FF", "DO", "BO")
loc <- c("MM", "MM", "KB", "RD")
vendor <- c(123, NA,NA,NA)
customer <- c(456, NA,NA,NA)
check <- c(NA, NA, "defined", "defined")

df <-  data.frame(movmnt_id, plant, loc, vendor,customer,check)

  movmnt_id plant loc vendor customer   check
1       101    FF  MM    123      456    <NA>
2       601    FF  MM     NA       NA    <NA>
3       105    DO  KB     NA       NA defined
4       321    BO  RD     NA       NA defined

I need to get this output (in the second row vendor & customer are copied from the first row):

  movmnt_id plant loc vendor customer   check
1       101    FF  MM    123      456    <NA>
2       601    FF  MM    123      456    <NA>
3       105    DO  KB     NA       NA defined
4       321    BO  RD     NA       NA defined

The condition is following:

If in current row `movmnt_id `== 601 
 -> take row *WHERE* `plant` & `loc` are the same as in the current row
              *AND* `movmnt_id  == 101`
              *AND* is.na(check)
 -> copy from found row `vendor` & `customer` to the current row

I could think about some for loop, but for my dataset it will be too heavy.
I am wondering if there is more elegant solution with less computational cost.

I was trying to adapt solutions from these cases, but did not succeed:

data.table tidyr dplyr dataframe r
2 Answers

To implement your conditions you can try the following -

library(dplyr)

df %>%
  group_by(plant, loc) %>%
  mutate(across(c(vendor, customer), 
              ~ifelse(movmnt_id == '601' & is.na(.), 
                      .[is.na(check) & movmnt_id == 101], .))) %>%
  ungroup

#  movmnt_id plant loc   vendor customer check  
#  <chr>     <chr> <chr>  <dbl>    <dbl> <chr>  
#1 101       FF    MM       123      456 NA     
#2 601       FF    MM       123      456 NA     
#3 105       DO    KB        NA       NA defined
#4 321       BO    RD        NA       NA defined
user_39629140

This solution may help but I assumed that these two values are copied in the second row since they share the same value for loc & plant columns:

library(dplyr)

df %>%
  group_by(plant, loc) %>%
  mutate(across(vendor:customer, ~ first(na.omit(.x))))

  movmnt_id plant loc   vendor customer check  
  <chr>     <chr> <chr>  <dbl>    <dbl> <chr>  
1 101       FF    MM       123      456 NA     
2 601       FF    MM       123      456 NA     
3 105       DO    KB        NA       NA defined
4 321       BO    RD        NA       NA defined
user_143145200
Related