CODEWITHSUNDEEP
rdplyr
Dinho
Dinho

Reputation: 724

Calculate difference based on group and by row in R

Table is broken down by two factors from 'group' column: Customer and Customer Support.

I would like to calculate the ratio between each given the month and total fields.

Reference table df (only goes to April but full table goes all the way to Dec):

Group            | Month  | Total

Customer           Jan       170
Customer Support   Jan       141
Customer           Feb       134
Customer Support   Feb       131
Customer           Mar       162
Customer Support   Mar       136
Customer           Apr       236
Customer Support   Apr       190

What I would like is to create a new field that shows the response ratio between Customer Support (numerator) to Customer (denominator). So a row calculation but from the bottom up.

Desired output:

Group            | Month  | Total | Response Ratio

Customer           Jan       170    0.82
Customer Support   Jan       141    0.82
Customer           Feb       134    0.97
Customer Support   Feb       131    0.97
Customer           Mar       162    0.83
Customer Support   Mar       136    0.83
Customer           Apr       236    0.8
Customer Support   Apr       190    0.8

This will then allow me to calculate the 'Global avg response ratio' across all the data.

Upvotes: 2

Views: 93

Answers (4)

TarJae
TarJae

Reputation: 78917

Another approach:

library(dplyr)
df1 %>% 
    group_by(Month) %>%
    mutate(Response_Ratio = sum(Total) / max(Total) - 1) 

 Group            Month Total Response_Ratio
  <chr>            <chr> <int>          <dbl>
1 Customer         Jan     170          0.829
2 Customer Support Jan     141          0.829
3 Customer         Feb     134          0.978
4 Customer Support Feb     131          0.978
5 Customer         Mar     162          0.840
6 Customer Support Mar     136          0.840
7 Customer         Apr     236          0.805
8 Customer Support Apr     190          0.805
>

Upvotes: 1

Lucas Nesi
Lucas Nesi

Reputation: 403

You can use pivot wider

data %>% pivot_wider(names_from = Group, values_from = Total) %>%
mutate(`Response Ratio`=Customer/`Customer Support`)

Upvotes: 2

Christopher Belanger
Christopher Belanger

Reputation: 631

The easiest way is with a pivot_wider() and then mutate().

library(dplyr)
library(tidyr)
df <- tribble(~Group, ~Month, ~Total,
              "Customer", "Jan", 170,
              "Customer Support","Jan", 141)

df %>%
  pivot_wider(names_from = "Group",
              values_from = "Total") %>%
  mutate(ratio = `Customer Support`/Customer)

Upvotes: 2

akrun
akrun

Reputation: 886938

We can group by 'Month' and divide the values assuming the first element is 'Customer per 'Group'

library(dplyr)
df1 %>%
    group_by(Month) %>%
    mutate(ResponseRatio = Total/first(Total), ResponseRatio = replace(ResponseRatio, 1, ResponseRatio[2])) %>%
    ungroup

-output

# A tibble: 8 x 4
  Group            Month Total ResponseRatio
  <chr>            <chr> <int>         <dbl>
1 Customer         Jan     170         0.829
2 Customer Support Jan     141         0.829
3 Customer         Feb     134         0.978
4 Customer Support Feb     131         0.978
5 Customer         Mar     162         0.840
6 Customer Support Mar     136         0.840
7 Customer         Apr     236         0.805
8 Customer Support Apr     190         0.805

data

df1 <- structure(list(Group = c("Customer", "Customer Support", "Customer", 
"Customer Support", "Customer", "Customer Support", "Customer", 
"Customer Support"), Month = c("Jan", "Jan", "Feb", "Feb", "Mar", 
"Mar", "Apr", "Apr"), Total = c(170L, 141L, 134L, 131L, 162L, 
136L, 236L, 190L)), class = "data.frame", row.names = c(NA, -8L
))

Upvotes: 2

Related Questions