I'm wondering how to replace all Inf and NaN elements in numeric columns in my OUTPUT with NA in a pipe chain?
I tried the following without success:
OUTPUT %>% replace(is.infinite(.)|!is.numeric(.), NA)
library(tidyverse)
DATA <- structure(list(InstID = c(2063L, 2063L, 2063L, 2063L, 2113L,
2113L, 2113L, 2113L, 2440L, 2441L, 2441L, 2441L),
InstNm = c("Adel SD 21", "Adel SD 21", "Adel SD 21", "Adel SD 21", "Adrian SD 61",
"Adrian SD 61","Adrian SD 61", "Adrian SD 61", "Alabama Department of Education",
"Alaska Department of Education", "Alaska Department of Education",
"Alaska Department of Education"),
SchlYr = c(2021L, 2122L, 2223L,2324L, 2021L, 2122L, 2223L,
2324L, 2324L, 2021L, 2122L, 2223L),
Total_Enrollment = c(17L, 13L, 11L, 13L, 262L, 257L, 264L,
275L, 1L, 3L, 4L, 7L),
Total_EL = c(0L, 0L, 0L, 0L, 25L, 24L, 25L, 24L, 0L, 0L, 0L, 0L),
Total_Arriver = c(0L, 0L, 0L, 0L,0L, 0L, 1L, 0L, 0L, 0L, 0L, 0L)),
row.names = c(NA, 12L), class = "data.frame")
OUTPUT <- DATA %>% group_by(InstNm) %>%
mutate(Total_Enroll_change = round(((Total_Enrollment - lag(Total_Enrollment))/ lag(Total_Enrollment))*100, 1),
Total_EL_change = round(((Total_EL - lag(Total_EL))/ lag(Total_EL))*100,1),
Total_Arriver_change = round(((Total_Arriver - lag(Total_Arriver))/ lag(Total_Arriver))*100, 1))
Using dplyr::across and an if_else you could do:
library(dplyr, warn = FALSE)
DATA |>
mutate(
Total_Enroll_change = round(((Total_Enrollment - lag(Total_Enrollment)) / lag(Total_Enrollment)) * 100, 1),
Total_EL_change = round(((Total_EL - lag(Total_EL)) / lag(Total_EL)) * 100, 1),
Total_Arriver_change = round(((Total_Arriver - lag(Total_Arriver)) / lag(Total_Arriver)) * 100, 1),
.by = InstNm
) |>
mutate(
across(
ends_with("_change"),
~ if_else(is.nan(.x) | is.infinite(.x), NA, .x)
)
)
#> InstID InstNm SchlYr Total_Enrollment Total_EL
#> 1 2063 Adel SD 21 2021 17 0
#> 2 2063 Adel SD 21 2122 13 0
#> 3 2063 Adel SD 21 2223 11 0
#> 4 2063 Adel SD 21 2324 13 0
#> 5 2113 Adrian SD 61 2021 262 25
#> 6 2113 Adrian SD 61 2122 257 24
#> 7 2113 Adrian SD 61 2223 264 25
#> 8 2113 Adrian SD 61 2324 275 24
#> 9 2440 Alabama Department of Education 2324 1 0
#> 10 2441 Alaska Department of Education 2021 3 0
#> 11 2441 Alaska Department of Education 2122 4 0
#> 12 2441 Alaska Department of Education 2223 7 0
#> Total_Arriver Total_Enroll_change Total_EL_change Total_Arriver_change
#> 1 0 NA NA NA
#> 2 0 -23.5 NA NA
#> 3 0 -15.4 NA NA
#> 4 0 18.2 NA NA
#> 5 0 NA NA NA
#> 6 0 -1.9 -4.0 NA
#> 7 1 2.7 4.2 NA
#> 8 0 4.2 -4.0 -100
#> 9 0 NA NA NA
#> 10 0 NA NA NA
#> 11 0 33.3 NA NA
#> 12 0 75.0 NA NA