Substract values in a column based on the values of 2 other columns-CodePudding

I have the dataframe below

vol<-structure(list(activity = c("RAAMELK", "RAAMELK", "Separering", 
"Separering", "Sweetmilk Pasteurizer 8331", "Sweetmilk Pasteurizer 8331", 
"9004 - T42 kartong 70x70", "9004 - T42 kartong 70x70", "9006 - T61 BIB", 
"9006 - T61 BIB", "9004 - T41 kartong 70x70", "9004 - T41 kartong 70x70"
), qty = c(0, 31, 31, 9, 31, 31, 6, 6, 3, 3, 28, 28), in_out = c("in", 
"out", "in", "out", "in", "out", "in", "out", "in", "out", "in", 
"out"), qty_scrap = c(0, 0, 0, 0, 0, -270.64, 0, 524, 0, 260, 
0, 0)), 
row.names = c(NA, -12L), case_id = "case_id", activity_id = "activity", 
activity_instance_id = "action", lifecycle_id = "registration_type", 
resource_id = "resource", timestamp = "timestamp", 
class = c("eventlog", "log", "tbl_df", "tbl", "data.frame"))

And I want to subtract the values on column qty based on if they have the same value in column activity and with the logic in-out from the in_out column. The result will be saved in the qty_scrap column.

The result dataframe will then have 4 columns. The activity column with the unique name of the activity, the in column with the in value of the qty, the out column with the out value of the qty and the qty_scrap column with their difference.

CodePudding user response：

You could try this:

vol %>% 
  group_by(activity) %>%
  mutate(qty_scrap = head(qty, 1) - tail(qty, 1)) %>%
  tidyr::pivot_wider(names_from = in_out, values_from = qty) %>%
  select(activity, `in`, out, qty_scrap)

Output

#   activity                    `in`   out qty_scrap
#   <chr>                      <dbl> <dbl>     <dbl>
# 1 RAAMELK                        0    31       -31
# 2 Separering                    31     9        22
# 3 Sweetmilk Pasteurizer 8331    31    31         0
# 4 9004 - T42 kartong 70x70       6     6         0
# 5 9006 - T61 BIB                 3     3         0
# 6 9004 - T41 kartong 70x70      28    28         0

CodePudding user response：

You can reshape your data to a wider format, and then mutate your scrape column by subtracting the in and out columns.

library(dplyr)
library(tidyr)

vol %>% 
  pivot_wider(id_cols = "activity", names_from = "in_out", values_from = "qty") %>% 
  mutate(qty_scrap = `in` - out)

#> # A tibble: 6 x 4
#>   activity                    `in`   out qty_scrap
#>   <chr>                      <dbl> <dbl>     <dbl>
#> 1 RAAMELK                        0    31       -31
#> 2 Separering                    31     9        22
#> 3 Sweetmilk Pasteurizer 8331    31    31         0
#> 4 9004 - T42 kartong 70x70       6     6         0
#> 5 9006 - T61 BIB                 3     3         0
#> 6 9004 - T41 kartong 70x70      28    28         0

^{Created on 2023-01-24 by the reprex package (v2.0.1)}