Help on Looping

srini · July 4, 2019, 7:39am

Hi @woodward
There are two parts to the problem 1) Creating a count variable 2) Create a sum variable.

Creating a count variable - Please find below the for loop which works for the first part of creating a count variable. Is it a way to replicate that in vectorize format as my data size is huge.

library(lubridate)
datanew1$Date_Created <- as.Date(datanew1$Date_Created, "%m/%d/%Y") # convert to date
datanew1$less6month <- datanew1$Date_Created %m-% months(6) # subtract 6 months

datanew1$count <- 0 # initialise counter
i <- 1 # for testing
for (i in 1:nrow(datanew1)){ # loop through rows

compare all rows to row i

compare <- (datanew1$location==datanew1$location[i]) &
#(datanew1$department==datanew1$department[i]) &
(datanew1$grade==datanew1$grade[i]) &
(datanew1$Date_Created >=datanew1$less6month[i])&
(datanew1$Date_Created <= datanew1$Date_Created[i])

count number of TRUE results (subtract 1 for row i)

datanew1$count[i] <- sum(compare)

}

Need help on second part - Creating a sum variable - This is the summation of all the values in the column "nos" which satisifies the above conditions in part 1.

woodward · July 4, 2019, 4:18pm

That's what I did in my reply, if you bothered to read it.

srini · July 4, 2019, 4:35pm

Thanks @woodward. I did read your comment and ran the code However the code is not summing the values in the “no” column based on the condition . It’s just returning the same result. Ideally it should all the values in the column which satisfies the conditions .

srini · July 4, 2019, 4:41pm

Please find below the code which creates the counter for "count". Along with this i also need another variable "sum" which is the sum of all values /rows of "nos" which satisifies the below filter conditions. ideally adding one more summing counter. but its not working. Need your suggestion
dataji$count <- 0 # initialise counter
i <- 1
for (i in 1:nrow(datanew1)){
compare <- (dataji$country==dataji$country) &
(dataji$grade==dataji$grade ) &
(dataji$department==dataji$department ) &
(dataji$Date_Created >=dataji$less6month[i])&
(dataji$Date_Created <= dataji$Date_Created[i])
dataji$count[i] <- sum(compare)

}
Please find below the data

structure(list(id = 1:293, nos = c(4L, 2L, 3L, 4L, 5L, 19L, 7L,
1L, 2L, 1L, 9L, 2L, 2L, 3L, 2L, 1L, 2L, 1L, 6L, 1L, 3L, 17L,
16L, 12L, 7L, 4L, 3L, 2L, 2L, 1L, 3L, 3L, 12L, 5L, 4L, 7L, 3L,
9L, 10L, 11L, 4L, 13L, 12L, 17L, 1L, 3L, 2L, 1L, 5L, 4L, 1L,
8L, 1L, 1L, 1L, 16L, 7L, 4L, 1L, 2L, 1L, 2L, 2L, 5L, 1L, 1L,
8L, 1L, 1L, 9L, 2L, 9L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 4L, 1L, 1L,
1L, 4L, 3L, 2L, 9L, 1L, 1L, 1L, 4L, 4L, 7L, 3L, 5L, 5L, 4L, 9L,
3L, 1L, 6L, 1L, 7L, 3L, 10L, 19L, 1L, 2L, 3L, 1L, 7L, 3L, 5L,
5L, 7L, 4L, 10L, 4L, 1L, 1L, 3L, 5L, 4L, 5L, 4L, 8L, 2L, 5L,
9L, 6L, 5L, 4L, 8L, 6L, 4L, 9L, 5L, 5L, 6L, 9L, 8L, 6L, 4L, 1L,
7L, 7L, 8L, 2L, 9L, 4L, 9L, 7L, 7L, 4L, 8L, 2L, 1L, 6L, 2L, 8L,
1L, 4L, 1L, 4L, 1L, 1L, 4L, 6L, 1L, 5L, 1L, 4L, 2L, 1L, 7L, 1L,
3L, 7L, 6L, 3L, 7L, 3L, 1L, 6L, 8L, 14L, 17L, 5L, 15L, 4L, 4L,
9L, 1L, 1L, 5L, 3L, 2L, 3L, 9L, 1L, 2L, 2L, 1L, 4L, 3L, 2L, 5L,
4L, 1L, 1L, 4L, 6L, 1L, 2L, 5L, 5L, 2L, 6L, 4L, 7L, 6L, 7L, 7L,
1L, 3L, 1L, 1L, 6L, 3L, 5L, 8L, 1L, 5L, 3L, 3L, 2L, 1L, 3L, 1L,
1L, 2L, 1L, 5L, 2L, 2L, 2L, 6L, 3L, 3L, 2L, 2L, 1L, 3L, 1L, 2L,
6L, 1L, 1L, 3L, 4L, 2L, 13L, 5L, 3L, 6L, 1L, 3L, 2L, 3L, 1L,
5L, 2L, 2L, 2L, 3L, 1L, 3L, 4L, 1L, 1L, 1L, 3L, 2L, 5L, 2L, 2L,
4L, 2L, 1L, 9L, 2L, 1L, 2L), JG = c(3L, 3L, 3L, 4L, 4L, 4L, 3L,
4L, 3L, 2L, 4L, 2L, 2L, 2L, 2L, 2L, 2L, 4L, 4L, 4L, 3L, 4L, 4L,
3L, 4L, 2L, 3L, 2L, 2L, 3L, 4L, 2L, 3L, 3L, 3L, 3L, 3L, 4L, 4L,
4L, 2L, 3L, 3L, 4L, 4L, 4L, 2L, 4L, 3L, 4L, 4L, 2L, 3L, 3L, 2L,
4L, 4L, 4L, 2L, 3L, 3L, 3L, 4L, 3L, 4L, 2L, 3L, 4L, 4L, 2L, 4L,
3L, 3L, 4L, 3L, 3L, 4L, 4L, 4L, 3L, 4L, 3L, 2L, 3L, 3L, 3L, 4L,
4L, 4L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 2L, 3L, 4L,
3L, 4L, 3L, 2L, 4L, 4L, 4L, 4L, 3L, 3L, 3L, 4L, 4L, 3L, 3L, 2L,
4L, 4L, 2L, 2L, 2L, 3L, 4L, 2L, 2L, 2L, 2L, 2L, 4L, 2L, 2L, 3L,
3L, 2L, 2L, 2L, 2L, 4L, 2L, 3L, 3L, 4L, 3L, 3L, 2L, 3L, 3L, 4L,
4L, 4L, 2L, 3L, 2L, 2L, 3L, 2L, 3L, 3L, 3L, 4L, 3L, 3L, 3L, 4L,
3L, 4L, 4L, 4L, 4L, 4L, 2L, 4L, 3L, 4L, 4L, 4L, 2L, 4L, 3L, 3L,
4L, 4L, 4L, 4L, 4L, 3L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 3L, 4L,
4L, 4L, 4L, 3L, 4L, 4L, 4L, 3L, 4L, 3L, 3L, 3L, 4L, 4L, 4L, 4L,
4L, 3L, 4L, 4L, 4L, 4L, 3L, 4L, 4L, 3L, 4L, 4L, 2L, 3L, 3L, 4L,
4L, 3L, 2L, 4L, 4L, 4L, 3L, 3L, 3L, 4L, 3L, 4L, 2L, 3L, 4L, 3L,
4L, 4L, 4L, 2L, 3L, 3L, 3L, 4L, 3L, 4L, 3L, 3L, 4L, 4L, 4L, 4L,
3L, 4L, 4L, 3L, 4L, 4L, 3L, 2L, 3L, 3L, 3L, 4L, 3L, 4L, 2L, 4L,
4L, 4L, 4L, 4L, 4L, 3L, 2L, 3L, 4L, 4L, 3L, 4L, 4L, 4L), Department = structure(c(1L,
1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L,
2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L,
2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L,
3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L,
3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L,
3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L,
2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L,
3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L,
3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L,
2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L,
3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L,
3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L,
3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L,
2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L,
3L, 3L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L,
3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L,
2L, 3L, 3L, 3L, 3L, 1L, 3L, 1L, 2L, 3L, 3L, 3L, 3L, 1L, 2L, 3L,
3L, 3L, 3L, 1L), .Label = c("Fin", "Ops", "RE"), class = "factor"),
Country = structure(c(10L, 10L, 10L, 18L, 18L, 18L, 2L, 2L,
10L, 11L, 18L, 17L, 17L, 17L, 17L, 17L, 17L, 3L, 3L, 3L,
3L, 4L, 4L, 4L, 8L, 17L, 10L, 10L, 10L, 8L, 10L, 9L, 9L,
9L, 9L, 9L, 9L, 9L, 9L, 9L, 18L, 9L, 18L, 13L, 10L, 18L,
12L, 18L, 10L, 15L, 16L, 9L, 5L, 5L, 2L, 5L, 15L, 5L, 6L,
11L, 17L, 11L, 4L, 16L, 18L, 18L, 18L, 8L, 17L, 18L, 18L,
8L, 10L, 10L, 10L, 10L, 2L, 2L, 2L, 2L, 17L, 17L, 18L, 11L,
11L, 11L, 4L, 1L, 1L, 11L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 10L, 9L, 10L, 9L, 2L, 15L, 18L, 11L, 10L,
9L, 8L, 4L, 4L, 4L, 18L, 18L, 18L, 18L, 18L, 8L, 8L, 8L,
18L, 10L, 10L, 18L, 18L, 17L, 10L, 8L, 8L, 10L, 8L, 18L,
18L, 18L, 18L, 18L, 18L, 10L, 17L, 8L, 8L, 8L, 8L, 18L, 18L,
18L, 10L, 18L, 10L, 18L, 18L, 18L, 17L, 17L, 17L, 17L, 17L,
17L, 17L, 17L, 17L, 17L, 10L, 9L, 2L, 16L, 16L, 18L, 18L,
16L, 11L, 10L, 18L, 2L, 7L, 2L, 8L, 8L, 10L, 8L, 8L, 18L,
18L, 8L, 8L, 8L, 8L, 9L, 18L, 18L, 18L, 8L, 8L, 18L, 18L,
2L, 3L, 4L, 10L, 14L, 8L, 18L, 9L, 18L, 18L, 18L, 8L, 9L,
17L, 4L, 11L, 9L, 11L, 11L, 8L, 18L, 18L, 4L, 4L, 4L, 4L,
4L, 18L, 18L, 18L, 10L, 2L, 2L, 2L, 2L, 16L, 18L, 10L, 10L,
16L, 18L, 9L, 8L, 10L, 16L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 10L, 18L,
18L, 18L, 9L, 9L, 18L, 9L, 18L, 18L, 5L, 18L, 10L, 2L, 10L,
2L, 5L, 2L, 18L, 8L, 18L, 17L, 10L, 17L, 11L, 10L, 9L, 11L,
18L, 17L, 17L, 17L, 11L, 10L, 11L, 8L), .Label = c("Argentina",
"Australia", "Brazil", "Canada", "China", "Egypt", "Germany",
"India", "Malaysia", "Netherlands", "Nigeria", "Norway",
"Oman", "Philippines", "Qatar", "Singapore", "United Kingdom",
"United States"), class = "factor"), Date_Created = structure(c(66L,
67L, 71L, 74L, 74L, 78L, 90L, 96L, 97L, 103L, 104L, 109L,
109L, 109L, 109L, 109L, 109L, 111L, 111L, 111L, 111L, 114L,
114L, 114L, 116L, 116L, 121L, 121L, 121L, 130L, 134L, 125L,
126L, 126L, 126L, 126L, 126L, 126L, 126L, 126L, 128L, 132L,
29L, 14L, 14L, 16L, 20L, 25L, 26L, 44L, 36L, 36L, 37L, 38L,
50L, 9L, 2L, 5L, 6L, 64L, 56L, 58L, 60L, 70L, 76L, 77L, 68L,
72L, 75L, 85L, 86L, 79L, 80L, 80L, 80L, 80L, 80L, 80L, 80L,
81L, 87L, 87L, 100L, 89L, 94L, 94L, 102L, 106L, 119L, 108L,
112L, 112L, 112L, 112L, 112L, 112L, 112L, 112L, 112L, 115L,
122L, 133L, 123L, 124L, 127L, 136L, 142L, 139L, 140L, 28L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 13L, 17L, 18L, 18L, 18L, 18L, 18L,
18L, 27L, 34L, 34L, 40L, 48L, 51L, 52L, 52L, 52L, 52L, 52L,
52L, 52L, 52L, 52L, 52L, 52L, 10L, 10L, 10L, 1L, 3L, 3L,
4L, 7L, 8L, 63L, 65L, 54L, 55L, 57L, 59L, 61L, 61L, 62L,
69L, 73L, 84L, 82L, 82L, 83L, 101L, 101L, 101L, 88L, 91L,
92L, 93L, 93L, 93L, 93L, 93L, 95L, 95L, 98L, 99L, 107L, 107L,
107L, 107L, 117L, 118L, 118L, 105L, 110L, 113L, 120L, 135L,
135L, 129L, 131L, 131L, 131L, 131L, 131L, 131L, 131L, 131L,
131L, 131L, 131L, 131L, 131L, 131L, 131L, 131L, 141L, 141L,
137L, 138L, 24L, 24L, 24L, 30L, 31L, 31L, 32L, 11L, 15L,
19L, 19L, 21L, 21L, 21L, 22L, 23L, 42L, 42L, 43L, 45L, 46L,
46L, 33L, 35L, 39L, 41L, 41L, 41L, 47L, 49L, 49L, 53L), .Label = c("1/10/2018",
"1/12/2017", "1/12/2018", "1/15/2018", "1/20/2017", "1/25/2017",
"1/25/2018", "1/29/2018", "1/6/2017", "1/8/2018", "10/10/2018",
"10/11/2017", "10/12/2017", "10/14/2016", "10/15/2018", "10/17/2016",
"10/17/2017", "10/20/2017", "10/22/2018", "10/23/2016", "10/23/2018",
"10/24/2018", "10/25/2018", "10/3/2018", "10/30/2016", "10/31/2016",
"10/31/2017", "10/4/2017", "10/5/2016", "10/5/2018", "10/8/2018",
"10/9/2018", "11/13/2018", "11/15/2017", "11/15/2018", "11/18/2016",
"11/24/2016", "11/25/2016", "11/26/2018", "11/29/2017", "11/29/2018",
"11/5/2018", "11/6/2018", "11/7/2016", "11/7/2018", "11/8/2018",
"12/10/2018", "12/11/2017", "12/11/2018", "12/13/2016", "12/18/2017",
"12/22/2017", "12/26/2018", "2/12/2018", "2/14/2018", "2/16/2017",
"2/16/2018", "2/17/2017", "2/21/2018", "2/23/2017", "2/26/2018",
"2/27/2018", "2/6/2018", "2/8/2017", "2/8/2018", "3/1/2016",
"3/15/2016", "3/15/2017", "3/19/2018", "3/2/2017", "3/21/2016",
"3/21/2017", "3/28/2018", "3/31/2016", "3/31/2017", "3/6/2017",
"3/8/2017", "4/1/2016", "4/11/2017", "4/13/2017", "4/14/2017",
"4/25/2018", "4/30/2018", "4/4/2018", "4/5/2017", "4/7/2017",
"5/1/2017", "5/11/2018", "5/12/2017", "5/13/2016", "5/14/2018",
"5/15/2018", "5/16/2018", "5/17/2017", "5/21/2018", "5/24/2016",
"5/25/2016", "5/25/2018", "5/29/2018", "5/3/2017", "5/3/2018",
"5/31/2017", "6/1/2016", "6/13/2016", "6/13/2018", "6/2/2017",
"6/2/2018", "6/20/2017", "6/22/2016", "6/22/2018", "6/26/2016",
"6/26/2017", "6/26/2018", "6/28/2016", "6/29/2017", "6/30/2016",
"6/4/2018", "6/5/2018", "6/6/2017", "7/10/2018", "7/29/2016",
"7/7/2017", "8/10/2017", "8/11/2017", "8/12/2016", "8/16/2016",
"8/16/2017", "8/17/2016", "8/17/2018", "8/2/2016", "8/21/2018",
"8/25/2016", "8/3/2017", "8/4/2016", "8/6/2018", "9/1/2017",
"9/18/2018", "9/20/2018", "9/21/2017", "9/27/2017", "9/4/2018",
"9/5/2017"), class = "factor")), class = "data.frame", row.names = c(NA,
-293L))
[/quote]

The final output will be like this creating a count and a sum variable.
For eg. the second row since there is only two rows which satisifies the condition and it falls less than 6 month of Date_Created variable i.e 3/15/2016 and 3/1/2016 it added "4" and "2" from the "nos" column and given 6.

woodward · July 4, 2019, 5:08pm

I think it is working, it's just that there is only one item that satisfies your condition. As I have said, your condition doesn't make sense.

srini · July 4, 2019, 5:11pm

okey. But earlier you have provided the code for count the below which satisifies for multiple conditions. But only when creating a "sum" variable based out of "nos" it's not working. This was the code which you provided earlier and its working for "count" variable.
dataji$count <- 0 # initialise counter
i <- 1
for (i in 1:nrow(datanew1)){
compare <- (dataji$country==dataji$country) &
(dataji$grade==dataji$grade ) &
(dataji$department==dataji$department ) &
(dataji$Date_Created >=dataji$less6month[i])&
(dataji$Date_Created <= dataji$Date_Created[i])
dataji$count[i] <- sum(compare)

}

srini · July 4, 2019, 5:12pm

Actually we are in the HR space and for them condition is look for all the candidates who all within the 6 month of the Date_Created. So that they can tag them. In this case they want to "sum" the Column "nos" which is the no of candidates falls within the 6 months period.

srini · July 4, 2019, 5:22pm

Please find below the input and the output (coming out of the code you shared). Ideally for the 2nd row it will be 6 and for 3rd row it the sum should be 9 because there are 3 rows which are within the 6 months of the 3/21/2016. The 6 months less of 3/21/2016 is 9/21/2015. All the top 2 rows are within this range. I.e. 3/15/2016 and 3/1/2016. So the code should sum the "nos" which is 4 ,2 and 3. input1 output1

srini · July 5, 2019, 9:24am

This is what ia m trying but not working

datanew12 = datanew2 %>% group_by(location,grade,Date_Created) %>%
mutate(earliest_date = Date_Created %m-% months(6)) %>%
filter((Date_Created >= earliest_date & Date_Created <= Date_Created)) %>%
summarise(cars_cumulative = sum(nos))

srini · July 5, 2019, 2:45pm

Any suggestion please? thanks

andresrcs · July 5, 2019, 2:56pm

My suggestion is, check the logic of your filtering conditions, have in minde that this works rowwise, so comparing Date_Created <= Date_Created makes no sense since it will always be TRUE.

srini · July 5, 2019, 2:58pm

Thanks. I am trying to replicate the below for loop into the vectorize form which works fine for creating a "count" variable. but it's not working when i transform it into a vectorize form
dataji$count <- 0 # initialise counter
i <- 1
for (i in 1:nrow(datanew1)){
compare <- (dataji$country==dataji$country) &
(dataji$grade==dataji$grade ) &
(dataji$department==dataji$department ) &
(dataji$Date_Created >=dataji$less6month[i])&
(dataji$Date_Created <= dataji$Date_Created[i])
dataji$count[i] <- sum(compare)

}

andresrcs · July 5, 2019, 3:18pm

These variable names don't match the ones in the sample data you have posted before, to help us help you, could you please prepare a self-contained minimal reproducible example (reprex) illustrating your issue? A reprex makes it much easier for others to understand your issue and figure out how to help.
Please have a look at this guide, to see how to create one:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners Guides & FAQs

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

srini · July 5, 2019, 3:22pm

Please find below the data and the code
structure(list(Id = 1:15, nos = c(4L, 2L, 3L, 2L, 3L, 2L, 2L,
3L, 1L, 5L, 2L, 1L, 2L, 1L, 1L), location = structure(c(1L, 1L,
1L, 2L, 2L, 2L, 5L, 5L, 1L, 2L, 2L, 6L, 3L, 4L, 4L), .Label = c("Aus",
"Ind", "KA", "LA", "ML", "PA"), class = "factor"), grade = c(3L,
3L, 3L, 3L, 3L, 2L, 2L, 4L, 4L, 3L, 3L, 4L, 3L, 3L, 4L), Date_Created = structure(c(3L,
4L, 5L, 7L, 9L, 9L, 9L, 10L, 1L, 2L, 6L, 6L, 6L, 6L, 8L), .Label = c("10/14/2016",
"10/31/2016", "3/1/2016", "3/15/2016", "3/21/2016", "4/13/2017",
"5/25/2016", "6/29/2017", "7/29/2016", "8/4/2016"), class = "factor")), class = "data.frame", row.names = c(NA,
-15L))

library(lubridate)
data$Date_Created <- as.Date(data$Date_Created, "%m/%d/%Y") # convert to date
data$less6month <- data$Date_Created %m-% months(6) # subtract 6 months

data$count <- 0 # initialise counter
i <- 1 # for testing
for (i in 1:nrow(data)){ # loop through rows

compare all rows to row i

compare <- (data$location==data$location[i]) &
(data$grade==data$grade[i]) &
(data$Date_Created >=data$less6month[i])&
(data$Date_Created <= data$Date_Created[i])

count number of TRUE results

data$count[i] <- sum(compare)

}

srini · July 5, 2019, 3:26pm

Problem : The for loop is running but not working when converted to vectorize form. Also the final output

The code has to create two variables
a) count - how many rows are there in the dataset which satisifies the filter conditions and are within the 6 months of Date_Created variable for that row. I have provided the explanation in the post already and the picture of the final output.
b) Sum - The code has create a new variable called "sum" based on the "nos" column which satisifies the filter conditions. one of the conditions is Given this Date_Created, I want to find the other rows with Date_Created that are within the 6 months of the Date_Created for every row and then sum the "nos"[![enter image description here][1]][1] for those rows. I have provided the picture and the final out put.

srini · July 5, 2019, 3:30pm

The final output should look like
finaloutput

andresrcs · July 5, 2019, 4:51pm

This produces the sum

data <- data.frame(
    Id = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L,
           14L, 15L),
    nos = c(4L, 2L, 3L, 2L, 3L, 2L, 2L, 3L, 1L, 5L, 2L, 1L, 2L, 1L, 1L),
    grade = c(3L, 3L, 3L, 3L, 3L, 2L, 2L, 4L, 4L, 3L, 3L, 4L, 3L, 3L, 4L),
    Date_Created = c("2016-03-01", "2016-03-15", "2016-03-21", "2016-05-25",
                     "2016-07-29", "2016-07-29", "2016-07-29", "2016-08-04",
                     "2016-10-14", "2016-10-31", "2017-04-13", "2017-04-13",
                     "2017-04-13", "2017-04-13", "2017-06-29"),
    location = as.factor(c("Aus", "Aus", "Aus", "Ind", "Ind", "Ind", "ML",
                           "ML", "Aus", "Ind", "Ind", "PA", "KA", "LA",
                           "LA"))
)

library(tidyverse)
library(lubridate)

data$Date_Created = ymd(data$Date_Created)

data %>%
    mutate(earliest_date = Date_Created %m-% months(6),
           sum_nos = map2_dbl(.x = earliest_date,
                              .y = Date_Created,
                              ~ data %>%
                                  filter(Date_Created >= .x & Date_Created <= .y) %>%
                                  summarise(sum_nos = sum(nos)) %>% 
                                  .$sum_nos
                              ))
#>    Id nos grade Date_Created location earliest_date sum_nos
#> 1   1   4     3   2016-03-01      Aus    2015-09-01       4
#> 2   2   2     3   2016-03-15      Aus    2015-09-15       6
#> 3   3   3     3   2016-03-21      Aus    2015-09-21       9
#> 4   4   2     3   2016-05-25      Ind    2015-11-25      11
#> 5   5   3     3   2016-07-29      Ind    2016-01-29      18
#> 6   6   2     2   2016-07-29      Ind    2016-01-29      18
#> 7   7   2     2   2016-07-29       ML    2016-01-29      18
#> 8   8   3     4   2016-08-04       ML    2016-02-04      21
#> 9   9   1     4   2016-10-14      Aus    2016-04-14      13
#> 10 10   5     3   2016-10-31      Ind    2016-04-30      18
#> 11 11   2     3   2017-04-13      Ind    2016-10-13      12
#> 12 12   1     4   2017-04-13       PA    2016-10-13      12
#> 13 13   2     3   2017-04-13       KA    2016-10-13      12
#> 14 14   1     3   2017-04-13       LA    2016-10-13      12
#> 15 15   1     4   2017-06-29       LA    2016-12-29       7

^{Created on 2019-07-05 by the reprex package (v0.3.0)}

srini · July 5, 2019, 4:59pm

Thank you. May be i was not clear in my communication. For the 4th row the sum should be 2. Because for the location is Ind and there are no rows which falls in the 6 month of the Date_Created.
For e.g for the 4th row the Date_Created is 25-05-2016 and the 6 month earliest is 25-11-2015. But there are now rows in the data which falls satisifies location is India and falls in between.. So the value in the sum column will be just 2.
For e.g for the 5th row the Date_Created is 29-07-2016 and the 6 month earlies is 29-01-2016. In this case 4th and 5th row satisifies the conditions. So the sum will be 2+3 which is 5.

srini · July 5, 2019, 5:00pm

finaloutput

andresrcs · July 5, 2019, 5:13pm

Sorry, you are right, you are not being clear enough and I can't understand what you are saying, I believe I have gave you an example of how to replace your for loop using purrr package, so you should be able to adapt it to fit your conditions (which I can't understand).