Summing rows on certain conditions in R

user_9903566 0
Viewed 72

I have a dataframe of the following kind. This is just a small sample of the dataset. It has a high no of rows.

df <- data.frame(team = c("A", "A", "A", "A", "A", "B"),
                 Skill     = c("TSICS", "TSICS", "TSICS", "TSICS", "COHORT", "COHORT"),
                 Date = c("1/1/2020","7/1/2020", "14/1/2020", "21/1/2020", "7/1/2020", "21/2/2020" ),
                 Value = (10,20,30,5,6,23)
)
>df
   Team    Skill       Date         Value
     A     TSICS       1/1/2020      10
     A     TSICS       7/1/2020      20
     A     TSICS       14/1/2020     30
     A     TSICS       21/1/2020      5
     A     COHORT      7/1/2020       6
     B     COHORT      21/2/2020     23

I want to create a new column Result which is Result = Value[team= A, Skill = TSICS] + Value[team= A, Skill = Cohort] corresponding to that team and Date(1 week previous to the corresponding date).

**Please note: The values for a particular date should be picked from the previous date. For eg: For Team A: Skill = TSICS the value for 21/1/2020 = 30 i.e the previous date value. This the resultant table should be of the form

Team    Skill       Date         Value      Result
     A     TSICS       1/1/2020      10        0 (no previous date values for TSICS(A) & Cohort(A))
     A     TSICS       7/1/2020      20       10 (only previous date value for TSICS(A))
     A     TSICS       14/1/2020     30       26 (sum of previous date values for TSICS(A) & Cohort(A))
     A     TSICS       21/1/2020      5       30 (previous date value of TSICS(A))
     A     COHORT      7/1/2020       6       10 (previous date value of TSICS(A))
     B     COHORT      21/2/2020     23       0  (no previous date value)

Can we use lag function to get the previous date value? The dates are in dd-mm-yyyy format

dplyr data-analysis dataframe r
1 Answers

You may try this:

library(tidyverse)
library(lubridate)

df <- df %>%
  mutate(Date = dmy(Date)) 

df %>%
  group_split(team) %>%
  map(., ~.x %>%
      rowwise() %>%
      mutate(Result = sum(ifelse(.$Date >= (Date - 8) & .$Date < Date, .$Value, 0)))) %>%
  bind_rows()

 A tibble: 6 x 5
# Rowwise: 
  team  Skill  Date       Value Result
  <fct> <fct>  <date>     <dbl>  <dbl>
1 A     TSICS  2020-01-01    10      0
2 A     TSICS  2020-01-07    20     10
3 A     TSICS  2020-01-14    30     26
4 A     TSICS  2020-01-21     5     30
5 A     COHORT 2020-01-07     6     10
6 B     COHORT 2020-02-21    23      0
user_141700970
Related