RStudio实现客户新发票创建日前已付/逾期发票计数

Hey there! As a new RStudio user, figuring out rolling counts per customer can feel tricky at first, but we can solve this cleanly with the dplyr package (part of the tidyverse). Let's walk through how to calculate your two metrics step by step.

The Approach #

We need to:

1. Group the data by each customer (names) so calculations are independent per person.
2. Sort each customer's invoices by their creation date (startdate), since we need counts of invoices before the current one.
3. Calculate cumulative counts of paid and delayed invoices, but exclude the current invoice itself (since we want counts prior to its creation date).

Solution Code #

First, make sure you have the tidyverse installed (if not, run install.packages("tidyverse") once):

library(tidyverse)

# Use your existing dataset (you already created this part)
set.seed(123)
names<- rep(LETTERS[1:2], each = 16)
id<- seq(1,32)
daysp<- runif(1:32,1,32)
startdate <-c("20-02-2018","01-03-2018","13-03-2018","20-03-2018","28-03-2018","05-04-2018","10-04-2018","13-04-2018", "16-04-2018","19-04-2018","04-05-2018","14-05-2018","23-05-2018","04-06-2018","12-06-2018","19-06-2018", "26-04-2018","02-05-2018","07-05-2018","07-05-2018","07-05-2018","14-05-2018","29-05-2018","12-06-2018", "12-06-2018","18-06-2018","11-07-2018","11-07-2018","17-07-2018","30-07-2018","03-08-2018","07-08-2018")
startdate<-as.Date(startdate,"%d-%m-%Y" )
paydate<- startdate + daysp
class <- c("Payed", "Payed","Payed", "Delayed","Payed", "Delayed","Delayed", "Delayed","Payed", "Delayed", "Payed", "Delayed","Payed", "Delayed","Payed", "Delayed","Payed", "Delayed","Payed", "Delayed", "Payed", "Delayed","Payed", "Delayed","Payed", "Delayed","Delayed", "Delayed","Payed", "Delayed", "Payed", "Delayed")
df<-data.frame(names,id,daysp,startdate,paydate,class)

# Calculate the two metrics
df_final <- df %>%
  group_by(names) %>%
  # Sort invoices by creation date per customer
  arrange(startdate, .by_group = TRUE) %>%
  mutate(
    # Create binary flags for paid/delayed status
    is_payed = if_else(class == "Payed", 1, 0),
    is_delayed = if_else(class == "Delayed", 1, 0),
    # Use lag() to exclude current invoice, then cumulative sum for prior counts
    nopip = cumsum(lag(is_payed, default = 0)),
    nopip_delayed = cumsum(lag(is_delayed, default = 0))
  ) %>%
  # Clean up temporary flag columns
  select(-is_payed, -is_delayed) %>%
  ungroup()

Verify the Results #

To check if this matches your expected values, run:

# Your expected values
expected_nopip <- c(0,0,1,1,3,3,4,4,4,5,7,10,10,12,12,14,0,0,2,2,2,2,3,6,6,6,9,9,10,12,13,14)
expected_delayed <- c(0,0,0,0,0,0,1,1,1,2,3,5,5,6,6,6,0,0,1,1,1,1,1,3,3,3,4,4,5,6,7,8)

# Check if results match
all.equal(df_final$nopip, expected_nopip)
all.equal(df_final$nopip_delayed, expected_delayed)

Both should return TRUE if everything works correctly.

How It Works #

• group_by(names): Ensures we only calculate counts within each customer's invoices.
• arrange(startdate): Sorts invoices so we're counting earlier invoices first.
• lag(is_payed, default = 0): Shifts the paid flag column down by one row, so the first row uses 0 (no prior invoices). This ensures we don't count the current invoice.
• cumsum(): Adds up the lagged flags to get a running total of prior paid/delayed invoices.

内容的提问来源于stack exchange，提问作者Fenico Alarcon