this should be my Expected OUTPUT: i need to use this for calculating the rollrate. by this code: i tried the below code but didn't worked out.
test_data$Group <- rep(1:5, nrow(test_data) / 5)
library(dplyr)
new_data <- test_data %>%
group_by(Group) %>%
mutate(D = (bal) /lag(bal, n=2)) %>%
data.frame()
OUTPUT I GOT:
date bal Group D
1 1/31/2013 10 1 NA
2 1/31/2013 11 2 NA
3 1/31/2013 12 3 NA
4 1/31/2013 13 4 NA
5 1/31/2013 14 5 NA
6 2/28/2013 20 1 NA
7 2/28/2013 30 2 NA
8 2/28/2013 40 3 NA
9 2/28/2013 50 4 NA
10 2/28/2013 60 5 NA
11 3/30/2013 10 1 1.000000
12 3/30/2013 11 2 1.000000
13 3/30/2013 12 3 1.000000
14 3/30/2013 13 4 1.000000
15 3/30/2013 15 5 1.071429
expected output:
date bal D
1/31/2013 10 na
1/31/2013 11 na
1/31/2013 12 na
1/31/2013 13 na
1/31/2013 14 na
2/28/2013 20 na
2/28/2013 30 3.00
2/28/2013 40 3.63
2/28/2013 50 4.16
2/28/2013 60 4.61
3/30/2013 10 na
3/30/2013 11 0.55
3/30/2013 12 0.4
3/30/2013 13 0.35
3/30/2013 15 0.25
please suggest: Thanks in Advance
Assuming you always have groups of three rows with the same date, we can use dplyr.
First we create a grouping variable called Group
test_data$Group <- rep(1:3, nrow(test_data) / 3)
Then we use dplyr to group the data, and then create the new column you need. It will save the results in the object new_data.
library(dplyr)
new_data <- test_data %>%
group_by(Case) %>%
mutate(D = lag(Balance) / Balance) %>%
data.frame()