How to set NA values to other row value within same grouping in R Dataframe

Jakob Orel 0
Viewed 33

I have a dataframe containing Parts grouped by Material, Client, CompanyCode, and STOCK_UOM. For each FiscalYear there is an associated column for WAP (weighted average price). FiscalYear 2022 should have the value of WAP_2021 (and associated type) in the WaP column. FiscalYear 2023 should have the value of WAP_2022 (and associated type) in the WaP column. But where FiscalYear is 2023 there can be NA values for WAP_2022, in this case it should give the value of the previous year WaP (WAP_2021).

Material Client CompanyCode STOCK_UOM FiscalYear WAP_2021 WAP_2021_Type WAP_2022 WAP_2022_Type
1234 Cl1 1010 EA 2022 13.1 WAP 2021 NA NA
1234 Cl1 1010 EA 2023 NA NA NA WAP 2022

Desired Result:

Material Client CompanyCode STOCK_UOM FiscalYear WaP WaPType
1234 Cl1 1010 EA 2022 13.1 WAP 2021
1234 Cl1 1010 EA 2023 13.1 WAP 2022

I have added a mutate statement to handle the first part.

df %>% mutate(WaP = ifelse(FiscalYear == 2022, WAP_2021, WAP_2022),
              WaPType = ifelse(FiscalYear == 2022, WAP_2021_Type, WAP_2022_Type))

Now I need to replace the NA values in this WaP column with the associated value for that Material, Client, CompanyCode, UOM from the WAP_2021 column. How do I reference certain rows based on this criteria? I would need the same group (Material, etc) but for FiscalYear 2022.

dataframe r
1 Answers

You could first delete years from the names,

names(dat) <- gsub('_\\d{4}', '', names(dat))

and create a blank with unique names and zero rows, with which we want to merge later on.

blank <- matrix(, nrow=0, ncol=length(unique(names(dat))),
                dimnames=list(NULL, unique(names(dat))))

Next, by fiscal year, we just select columns that are not only NAs and merge it with the blank to globally get the same names. We rbind the result. Finally we zoo::na.locf the WAP column.

by(dat, dat$FiscalYear, \(x) x[colSums(is.na(x)) == 0]) |>
  lapply(merge, blank, all=TRUE) |>
  do.call(what=rbind) |>
  transform(WAP=zoo::na.locf(WAP))
#      Material Client CompanyCode STOCK_UOM FiscalYear  WAP WAP_Type
# 2022     1234    Cl1        1010        EA       2022 13.1 WAP 2021
# 2023     1234    Cl1        1010        EA       2023 13.1 WAP 2022
jay.sf0
Related