how to sum conditional functions to grouped rows in R

Question

I so have the following data frame

customerid	payment_month	payment_date	bill_month	charges
1	January	22	January	30
1	February	15	February	21
1	March	2	March	33
1	May	4	April	43
1	May	4	May	23
1	June	13	June	32
2	January	12	January	45
2	February	15	February	56
2	March	2	March	67
2	April	4	April	65
2	May	4	May	54
2	June	13	June	68
3	January	25	January	45
3	February	26	February	56
3	March	30	March	67
3	April	1	April	65
3	June	1	May	54
3	June	1	June	68

(the id data is much larger) I want to calculate payment efficiency using the following function,

efficiency = (amount paid not late / total bill amount)*100

not late is paying no later than the 21st day of the bill's month. (paying January's bill on the 22nd of January is considered as late)

I want to calculate the efficiency of each customer with the expected output of

customerid	effectivity
1	59.90
2	100
3	37.46

I have tried using the following code to calculate for one id and it works. but I want to apply and assign it to the entire group id and summarize it into 1 column (effectivity) and 1 row per ID. I have tried using group by, aggregate and ifelse functions but nothing works. What should I do?

df1 <- filter(df, (payment_month!=bill_month & id==1) | (payment_month==bill_month & payment_date > 21 & id==1) )
df2 <-filter(df, id==1001)
x <- sum(df1$charges)
x <- sum(df2$charges)
100-(x/y)*100

Maurits Evers · Accepted Answer · 2021-05-18 05:23:33Z

1

An option using dplyr


library(dplyr)
df %>%
    group_by(customerid) %>%
    summarise(
        effectivity = sum(
            charges[payment_date <= 21 & payment_month == bill_month]) / sum(charges) * 100,
        .groups = "drop")
## A tibble: 3 x 2
#customerid effectivity
#       <int>       <dbl>
#1          1        59.9
#2          2       100  
#3          3        37.5

answered May 18, 2021 at 5:23

Maurits Evers

51k4 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tiar Salsabila Fitriazaki Over a year ago

thanks! it worked. I changed the condition to payment_data >= 21 & payment_mont!=bill_month and subtract the effectivity with 100 to get the desired number and make it more precise.

TarJae · Accepted Answer · 2021-05-18 05:30:01Z

0


df %>% 
  group_by(customerid) %>% 
  mutate(totalperid = sum(charges)) %>% 
  mutate(pay_month_number = match(payment_month , month.name),
         bill_month_number = match(bill_month , month.name)) %>% 
  mutate(nolate = ifelse(pay_month_number > bill_month_number, TRUE, FALSE)) %>% 
  summarise(efficiency = case_when(nolate = TRUE ~ (charges/totalperid)*100))

answered May 18, 2021 at 5:30

TarJae

80.2k6 gold badges30 silver badges94 bronze badges

Collectives™ on Stack Overflow

how to sum conditional functions to grouped rows in R

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related