How to summarize/aggregate by group with dplyr::summarise() when some columns are constants that depend on values from other columns?

user_6105259 0
Viewed 30

I want to summmarize a data frame using dplyr::summarise(), where one column is in fact a constant that depends on value from a different column.

Below the code simulates a data frame for example.

# you can skip the function definition; it's just a helper
simulate_df <- function() {
  person_ids    <- sample(1:10, size = 2)
  product_ids   <- sample(1:999, size = 2)
  product_score <- sample(1:9, size = 2)
  
  get_weights <- function() {
    first_num <- sample(1:9, 1)
    c(first_num, 10 - first_num)
  } 
  
  data.frame(
    person_id = rep(person_ids, times = get_weights()),
    product_id = sample(product_ids, 5, replace = TRUE)
  ) |> 
    transform(product_score = ifelse(product_id == product_ids[1], product_score[1], product_score[2]))
}

set.seed(12345)

my_df <- simulate_df()
my_df
#>    person_id product_id product_score
#> 1          3        826             8
#> 2          3        605             2
#> 3          3        826             8
#> 4          3        605             2
#> 5          3        605             2
#> 6          3        826             8
#> 7          8        605             2
#> 8          8        826             8
#> 9          8        605             2
#> 10         8        605             2

In my_df we have data about two persons, each with person_id, and multiple occasions of purchasing products. Each person bought any of two products. Each product has a product_id, and each product_id has its own product_score. Please pay attention that product_score is constant per each product_id.

My Problem

I want to aggregate/summarize the data by person, to have:

  • one column for row-counts of product_a
  • one column for row-counts of product_b
  • one column for the difference between the count of product_a and the product_score of product a
  • one column for the difference between the count of product_b and the product_score of product b

Thus, the Expected Output would be:

## # A tibble: 2 × 5
##   person_id n_product_a n_product_b diff_nproduct_a_product_score diff_nproduct_b_product_score
##       <int>       <int>       <int>                         <int>                         <int>
## 1         3           3           3                            -5                             1
## 2         8           1           3                            -7                             1

So I know how to get the first two columns for counts:

library(dplyr, warn.conflicts = FALSE)

my_df |> 
  group_by(person_id) |> 
  summarise(n_product_a = sum(product_id == 826),
            n_product_b = sum(product_id == 605))
#> # A tibble: 2 × 3
#>   person_id n_product_a n_product_b
#>       <int>       <int>       <int>
#> 1         3           3           3
#> 2         8           1           3

But how can I get the "diff" columns, given that product_score values are constants that depend on the values of product_id?

dplyr dataframe r
1 Answers

You can do this calculation as follows:

  1. Compute the four summaries you are interested in: two n_products and two diff_product_scores.
  2. "Pivot" the n_product and diff_product_score columns.

The final column names aren't exactly as requested but still readable. It's trivial to rename() if necessary.

my_df %>%
  mutate(
    product_id = recode(product_id,
      `605` = "b",
      `826` = "a"
    )
  ) %>%
  count(
    person_id, product_id, product_score,
    name = "n_product"
  ) %>%
  mutate(
    diff_product_score = n_product - product_score
  ) %>%
  pivot_wider(
    id_cols = person_id,
    names_from = product_id,
    names_glue = "{.value}_{product_id}",
    values_from = c(n_product, diff_product_score)
  )
#> # A tibble: 2 × 5
#>   person_id n_product_a n_product_b diff_product_score_a diff_product_score_b
#>       <int>       <int>       <int>                <int>                <int>
#> 1         3           3           3                   -5                    1
#> 2         8           1           3                   -7                    1

Created on 2022-07-27 by the reprex package (v2.0.1)

user_95664840
Related