I am trying to deduct values across a data frame of elements.
structure(list(Date = structure(c(20095, 20095, 20095, 20095, 20123, 20123, 20123, 20123), class = "Date"), Type = c("Drip", "Drain", "Drip", "Drain", "Drip", "Drain", "Drip", "Drain"), Valve = c(73, 73, 74, 74, 73, 73, 74, 74), `Nitrate_N_(ppm)` = c(26.5, 17.5, 21.9, 16.2, 30.1, 47.9, 29.1, 63.8), `Ammonium_N_(ppm)` = c(36, 49, 39, 51, 29, 109, 36, 99), `Calcium_(ppm)` = c(22, 32.5, 18, 31.2, 24.2, 81.7, 22.4, 78.7)), row.names = c(NA, -8L), class = c("tbl_df", "tbl", "data.frame"))For each Date and Valve, I need to deduct values of Nitrate, Ammonium and Calcium in the Drain sample from the value in the Drip sample.
Example: deduct calcium levels in drain of valve 73, 07/01/2025 from levels in drip of valve 73 07/01/2025.
I have tried grouping by Date and Valve but can't come up with the next step.
Any ideas?
2 Answers2
One approach could be to reshape, like:
df |> pivot_longer(cols = -c(Date, Type, Valve)) |> pivot_wider(names_from = Type, values_from = value) |> mutate(Drip_after = Drip - Drain)Result
Date Valve name Drip Drain Drip_after <date> <dbl> <chr> <dbl> <dbl> <dbl> 1 2025-01-07 73 Nitrate_N_(ppm) 26.5 17.5 9 2 2025-01-07 73 Ammonium_N_(ppm) 36 49 -13 3 2025-01-07 73 Calcium_(ppm) 22 32.5 -10.5 4 2025-01-07 74 Nitrate_N_(ppm) 21.9 16.2 5.7 5 2025-01-07 74 Ammonium_N_(ppm) 39 51 -12 6 2025-01-07 74 Calcium_(ppm) 18 31.2 -13.2 7 2025-02-04 73 Nitrate_N_(ppm) 30.1 47.9 -17.8 8 2025-02-04 73 Ammonium_N_(ppm) 29 109 -80 9 2025-02-04 73 Calcium_(ppm) 24.2 81.7 -57.510 2025-02-04 74 Nitrate_N_(ppm) 29.1 63.8 -34.711 2025-02-04 74 Ammonium_N_(ppm) 36 99 -63 12 2025-02-04 74 Calcium_(ppm) 22.4 78.7 -56.3
Sign up to request clarification or add additional context in comments.
2 Comments
r2evans
I recommend against column indices when pivoting (it is fragile), suggest
pivot_longer(dat, cols = -c(Date, Type, Valve)) instead.
Jon Spring
Good point, edited.
Answer recommended byR Language Collective
> summarise(df, across(2:4, \(x) x[Type=="Drip"] - x[Type=="Drain"]), .by=c(Date, Valve))# A tibble: 4 × 5 Date Valve `Nitrate_N_(ppm)` `Ammonium_N_(ppm)` `Calcium_(ppm)` <date> <dbl> <dbl> <dbl> <dbl>1 2025-01-07 73 9 -13 -10.52 2025-01-07 74 5.7 -12 -13.23 2025-02-04 73 -17.8 -80 -57.54 2025-02-04 74 -34.7 -63 -56.3
Comments
Explore related questions
See similar questions with these tags.