R create summary percentages in a dataframe using cells that will become different denominators

user_7147717 0
Viewed 74

Here is my dataframe. It's not very long - only six rows.

enter image description here

df <- structure(list(Send_Month = c("2021-05", "2021-06", "2021-07", 
"2021-05", "2021-06", "2021-07"), Order_Result = c("No", "No", 
"No", "Yes", "Yes", "Yes"), Email_Send = c(135, 495, 475, 7, 
28, 25), Unique_Email_Opens = c(45, 149, 143, 7, 28, 25), Unique_Email_Clicks = c(6, 
21, 10, 7, 28, 25), Total_Orders = c(37, 106, 46, 7, 28, 25)), row.names = c(NA, 
-6L), groups = structure(list(Send_Month = c("2021-05", "2021-06", 
"2021-07"), .rows = structure(list(c(1L, 4L), c(2L, 5L), c(3L, 
6L)), ptype = integer(0), class = c("vctrs_list_of", "vctrs_vctr", 
"list"))), row.names = c(NA, -3L), class = c("tbl_df", "tbl", 
"data.frame"), .drop = TRUE), class = c("grouped_df", "tbl_df", 
"tbl", "data.frame"))

I'm having trouble picturing how I'm going to get summary results that I can graph into a bar chart. I'm trying to make some groupings here:

When the month is the same (for example, "2021-05") and I look at the Email_Send variable, I can see that 7 out of 142 (which is 135+7) emails that were sent led to an order. I can also see that 7 out of 52 (which is 45+7) emails that were opened led to an order. And 7 out of 13 (which is 6+7) emails that were clicked on led to an order. That is for the "2021-05" group.

How can I create these statistics for each grouping so I can see how the percentage would change for each group, where the denominator keeps shifting?

I tried using the janitor package for a second and just to orient myself I first filtered to only include that 2021-05 group:

df_may <- df %>%
  filter(Send_Month == "2021-05")

df_may %>%
  adorn_totals("row")

enter image description here

But I don't know if this method is very flexible for looking at all the groups together and also I don't know if I really want a summary row or a new column. So I don't know if I'm heading in the right direction here.

summary dataframe r
3 Answers

Update

If you don't want to have output in the format of list, you can try

df %>%
  group_by(Send_Month) %>%
  mutate(across(Email_Send:Total_Orders, proportions)) %>%
  ungroup()

which gives

  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks
  <chr>      <chr>             <dbl>              <dbl>               <dbl>
1 2021-05    No               0.951               0.865               0.462
2 2021-06    No               0.946               0.842               0.429
3 2021-07    No               0.95                0.851               0.286
4 2021-05    Yes              0.0493              0.135               0.538
5 2021-06    Yes              0.0535              0.158               0.571
6 2021-07    Yes              0.05                0.149               0.714
# ... with 1 more variable: Total_Orders <dbl>

Maybe you can try the code below

> lapply(split(df, df$Send_Month), function(x) {x[-(1:2)]<-proportions(as.matrix(x[-(1:2)]), 2);x})
$`2021-05`
# A tibble: 2 x 6
# Groups:   Send_Month [1]
  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks
  <chr>      <chr>             <dbl>              <dbl>               <dbl>
1 2021-05    No               0.951               0.865               0.462
2 2021-05    Yes              0.0493              0.135               0.538
# ... with 1 more variable: Total_Orders <dbl>

$`2021-06`
# A tibble: 2 x 6
# Groups:   Send_Month [1]
  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks
  <chr>      <chr>             <dbl>              <dbl>               <dbl>
1 2021-06    No               0.946               0.842               0.429
2 2021-06    Yes              0.0535              0.158               0.571
# ... with 1 more variable: Total_Orders <dbl>

$`2021-07`
# A tibble: 2 x 6
# Groups:   Send_Month [1]
  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks
  <chr>      <chr>             <dbl>              <dbl>               <dbl>
1 2021-07    No                 0.95              0.851               0.286
2 2021-07    Yes                0.05              0.149               0.714
# ... with 1 more variable: Total_Orders <dbl>
user_121587570

Thank you dear @ThomasIsCoding for an excellent tip of using proporstions function in place of .x/sum(.x).

library(dplyr)
library(purrr)

df %>%
  group_by(Send_Month, .add = TRUE) %>%
  group_split() %>%
  map(~ .x %>% 
        mutate(across(!c(1, 2), ~ proportions(.x))))

[[1]]
# A tibble: 2 x 6
  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks Total_Orders
  <chr>      <chr>             <dbl>              <dbl>               <dbl>        <dbl>
1 2021-05    No               0.951               0.865               0.462        0.841
2 2021-05    Yes              0.0493              0.135               0.538        0.159

[[2]]
# A tibble: 2 x 6
  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks Total_Orders
  <chr>      <chr>             <dbl>              <dbl>               <dbl>        <dbl>
1 2021-06    No               0.946               0.842               0.429        0.791
2 2021-06    Yes              0.0535              0.158               0.571        0.209

[[3]]
# A tibble: 2 x 6
  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks Total_Orders
  <chr>      <chr>             <dbl>              <dbl>               <dbl>        <dbl>
1 2021-07    No                 0.95              0.851               0.286        0.648
2 2021-07    Yes                0.05              0.149               0.714        0.352
user_143145200

With janitor, assuming you want to exempt the column Total_Orders from the percentage calculation:

library(janitor)
library(tidyverse)

split(df, df$Send_Month) %>%
  map_df(adorn_percentages, "col", TRUE, -c(1, 2, Total_Orders))

# A tibble: 6 x 6
# Groups:   Send_Month [3]
  Send_Month Order_Result Email_Send Unique_Email_Opens Unique_Email_Clicks Total_Orders
  <chr>      <chr>             <dbl>              <dbl>               <dbl>        <dbl>
1 2021-05    No               0.951               0.865               0.462           37
2 2021-05    Yes              0.0493              0.135               0.538            7
3 2021-06    No               0.946               0.842               0.429          106
4 2021-06    Yes              0.0535              0.158               0.571           28
5 2021-07    No               0.95                0.851               0.286           46
6 2021-07    Yes              0.05                0.149               0.714           25
user_44703650
Related