R：最近365天按名称显示RunningTotal [英] R: RunningTotal in the last 365 days window by Name

问题描述

这就是我的数据。最右边的列是我的所需列。

This is what my data looks like. The rightmost column is my Desired Column.

Name  EventType  EventDate  SalesAmount RunningTotal Runningtotal(prior365Days)
John    Email      1/1/2014      0          0            0
John    Sale       2/1/2014     10          10           10
John    Sale       7/1/2014     20          30           30
John    Sale       4/1/2015     30          60           50 
John    Webinar    5/1/2015      0          60           50
Tom     Email      1/1/2014      0          0            0
Tom     Sale       2/1/2014     15          15           15
Tom     Sale       7/1/2014     10          25           25
Tom     Sale       4/1/2015     25          50           35 
Tom     Webinar    5/1/2015      0          50           35

我只是想在过去365天的窗口中获取每个名称的SalesAmount的运行总计。对于常规的 RunningTotal列，我使用了：

I am just trying to get the running total of SalesAmount for each name in the last 365 days window. For the general "RunningTotal" column I used:

df<- df%>%
   group_by (Name)%>%
    mutate(RunningTotal = cumsum(SalesAmount))

但我不知道如何仅在最近365天的窗口中获取运行总计。请提供帮助。真诚的感谢您的帮助！

But I dont know how to get the running total only in the last 365 day window.Kindly help. Your help is sincerely appreciated!

推荐答案

df$EventDate <- as.Date(df$EventDate, format="%d/%m/%Y")
df <- df %>%
   group_by (Name) %>%
   arrange(EventDate) %>% 
   mutate(day = EventDate - EventDate[1])

f <- Vectorize(function(i)
    sum(df[df$Name[i] == df$Name & df$day[i] - df$day >= 0 & 
             df$day[i] - df$day <= 365, "SalesAmount"]), vec="i")
df$RunningTotal365 <- f(1:nrow(df))

这篇关于R：最近365天按名称显示RunningTotal的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！