Help on Looping

Thanks Woodward. I think i have to explain much more here.
The filters are based on one condition where Date_Created variables is always greater than equal to less than 6 months of the variable. So first we have to create a new variable called less6month which is derived from Date_Created.
e.g chk$lessmonth <- as.Date(chk$Date_Created, "%m/%d/%Y") %m-% months(6)
Objective of the problem - Create two new variables.
The first variable is Count

  1. Count - Count of all the rows in the datasets which satisfies the below condition. Where
    if(Location==location)&(department==department)& (grade==grade) & Date_Created >= Less than 6 month.
    My code should go through each and every row and see if all the filter conditions are satisfied and the code selects all the rows and give a count where these rows within the 6 months of the Date_Created variable.
    For e.g if Date_Created is 1/10/2018 then the six months less is 1/04/2018. so the code should all the rows which are within this time period. Like that it should do for all the rows and create new less than 6 month for each row.

  2. The second objective is it to create another column called "total" which is the sum of all the values in column "nos" which satisfies the above conditions.
    structure(list(Id = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L,
    11L, 12L, 13L, 14L, 15L, 16L, NA, NA, NA), nos = c(10L, 2L, 3L,
    4L, 1L, 1L, 2L, 2L, 2L, 2L, 4L, 2L, 3L, 2L, 2L, 2L, NA, NA, NA
    ), department = c("A", "A", "A", "B", "B", "A", "C", "A", "B",
    "C", "B", "B", "C", "A", "A", "A", "", "", ""), location = c("delhi",
    "delhi", "london", "london", "london", "delhi", "chennai", "chennai",
    "delhi", "delhi", "delhi", "chennai", "chennai", "london", "delhi",
    "delhi", "", "", ""), grade = c(3L, 3L, 4L, 4L, 4L, 4L, 3L, 3L,
    3L, 4L, 3L, 3L, 4L, 3L, 4L, 4L, NA, NA, NA), Date_Created = c("1/8/2018",
    "1/8/2018", "1/10/2018", "1/15/2018", "1/25/2018", "2/21/2018",
    "2/26/2018", "2/27/2018", "3/19/2018", "5/14/2018", "5/15/2018",
    "5/21/2018", "5/21/2018", "5/25/2018", "6/5/2018", "6/26/2018",
    "", "", ""), X = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
    NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c("1",
    "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13",
    "14", "15", "16", "17", "18", "19"))

Is this what you want? Do you want to compare row i with itself, or only with the other rows?

chk <- structure(list(Id = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L,
11L, 12L, 13L, 14L, 15L, 16L, NA, NA, NA), nos = c(10L, 2L, 3L,
4L, 1L, 1L, 2L, 2L, 2L, 2L, 4L, 2L, 3L, 2L, 2L, 2L, NA, NA, NA
), department = c("A", "A", "A", "B", "B", "A", "C", "A", "B",
"C", "B", "B", "C", "A", "A", "A", "", "", ""), location = c("delhi",
"delhi", "london", "london", "london", "delhi", "chennai", "chennai",
"delhi", "delhi", "delhi", "chennai", "chennai", "london", "delhi",
"delhi", "", "", ""), grade = c(3L, 3L, 4L, 4L, 4L, 4L, 3L, 3L,
3L, 4L, 3L, 3L, 4L, 3L, 4L, 4L, NA, NA, NA), Date_Created = c("1/8/2018",
"1/8/2018", "1/10/2018", "1/15/2018", "1/25/2018", "2/21/2018",
"2/26/2018", "2/27/2018", "3/19/2018", "5/14/2018", "5/15/2018",
"5/21/2018", "5/21/2018", "5/25/2018", "6/5/2018", "6/26/2018",
"", "", ""), X = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c("1",
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13",
"14", "15", "16", "17", "18", "19"))

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

chk$count <- 0 # initialise counter
i <- 1 # for testing
for (i in 1:nrow(chk)){ # loop through rows
	# compare all rows to row i
	compare <- (chk$location==chk$location[i]) & 
		       (chk$department==chk$department[i]) &
		       (chk$grade==chk$grade[i]) &
		       (chk$Date_Created>=chk$less6month[i])
	# count number of TRUE results (subtract 1 for row i)
	chk$count[i] <- sum(compare) - 1
}
1 Like

Thanks Woodward. I will check this. To answer your question.

  1. The code has to go through every row and select all rows which are less than the 6 months of the date_created variable) and create a count variable.
    For eg. it will take first row and select rows in the datasets which satisify filter condition and less than 6 months of the date_created variable.
    Then it will go to the next row and select rows in the datasets which satisfy filter condition and less than 6 months of the date_created variable for that row.

  2. Then it has to create the second variable which is the sum of all the values in column "nos" which satisfy the above filter conditions and less than 6 months of the date_Created variable.

id no JG Department Country Date_Created Count (new variable based on the filter condition and less 6 months less than Date_Created) Sumng column B (Based on column G)
1 1 2 Fin Australia 12/10/2018 0 0
2 16 4 Fin China 12/11/2018 1 17
3 7 4 Fin Qatar 12/11/2018 2 24
4 4 4 Ops China 12/20/2018
5 1 2 Ops Egypt 12/24/2018
6 2 3 Ops Nigeria 12/26/2018
7 1 3 RE United Kingdom 12/26/2018
8 2 3 RE Nigeria 11/29/2018
9 2 4 RE Canada 11/29/2018
10 5 3 RE Singapore 12/6/2018
11 1 4 Fin United States 11/13/2018

Hi Woodward,

After running your code i got the below output. However for the first row the count should be 0 because there is no row in the dataset which has date_created less than 6 months. For e.g 1/08/2018 then 6 months less will be 07/08/2017. But in the dataset there is no row which falls within the 6 month of 1/08/2018. So the similar steps will be for all the other rows.

Id nos department location grade Date_Created X less6month count
1 10 A delhi 3 1/8/2018 NA 7/8/2017 1
2 2 A delhi 3 1/8/2018 NA 7/8/2017 1
3 3 A london 4 1/10/2018 NA 7/10/2017 0
4 4 B london 4 1/15/2018 NA 7/15/2017 1
5 1 B london 4 1/25/2018 NA 7/25/2017 1
6 1 A delhi 4 2/21/2018 NA 8/21/2017 2
7 2 C chennai 3 2/26/2018 NA 8/26/2017 0
8 2 A chennai 3 2/27/2018 NA 8/27/2017 0
9 2 B delhi 3 3/19/2018 NA 9/19/2017 1
10 2 C delhi 4 5/14/2018 NA 11/14/2017 0
11 4 B delhi 3 5/15/2018 NA 11/15/2017 1
12 2 B chennai 3 5/21/2018 NA 11/21/2017 0
13 3 C chennai 4 5/21/2018 NA 11/21/2017 0
14 2 A london 3 5/25/2018 NA 11/25/2017 0
15 2 A delhi 4 6/5/2018 NA 12/5/2017 2
16 2 A delhi 4 6/26/2018 NA 12/26/2017 2

Please find the dataset which i am trying to execute.

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), location = 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"),
grade = 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), 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"), less6month = structure(c(130L,
131L, 135L, 138L, 138L, 4L, 16L, 22L, 23L, 29L, 30L, 35L,
35L, 35L, 35L, 35L, 35L, 37L, 37L, 37L, 37L, 40L, 40L, 40L,
42L, 42L, 2L, 2L, 2L, 53L, 57L, 48L, 49L, 49L, 49L, 49L,
49L, 49L, 49L, 49L, 51L, 55L, 83L, 69L, 69L, 71L, 75L, 80L,
80L, 98L, 90L, 90L, 91L, 92L, 104L, 116L, 109L, 112L, 113L,
128L, 120L, 122L, 124L, 134L, 140L, 141L, 132L, 136L, 139L,
11L, 12L, 5L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 13L, 13L, 26L,
15L, 20L, 20L, 28L, 32L, 45L, 34L, 38L, 38L, 38L, 38L, 38L,
38L, 38L, 38L, 38L, 41L, 3L, 56L, 46L, 47L, 50L, 59L, 65L,
62L, 63L, 82L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L,
67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L,
67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L,
67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L,
67L, 67L, 67L, 67L, 67L, 67L, 67L, 67L, 68L, 72L, 73L, 73L,
73L, 73L, 73L, 73L, 81L, 88L, 88L, 94L, 102L, 105L, 106L,
106L, 106L, 106L, 106L, 106L, 106L, 106L, 106L, 106L, 106L,
117L, 117L, 117L, 108L, 110L, 110L, 111L, 114L, 115L, 127L,
129L, 118L, 119L, 121L, 123L, 125L, 125L, 126L, 133L, 137L,
10L, 8L, 8L, 9L, 27L, 27L, 27L, 14L, 17L, 18L, 19L, 19L,
19L, 19L, 19L, 21L, 21L, 24L, 25L, 33L, 33L, 33L, 33L, 43L,
44L, 44L, 31L, 36L, 39L, 1L, 58L, 58L, 52L, 54L, 54L, 54L,
54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L, 54L,
54L, 64L, 64L, 60L, 61L, 79L, 79L, 79L, 84L, 85L, 85L, 86L,
66L, 70L, 74L, 74L, 76L, 76L, 76L, 77L, 78L, 96L, 96L, 97L,
99L, 100L, 100L, 87L, 89L, 93L, 95L, 95L, 95L, 101L, 103L,
103L, 107L), .Label = c("1/10/2018", "1/29/2016", "1/7/2017",
"10/1/2015", "10/11/2016", "10/13/2016", "10/14/2016", "10/25/2017",
"10/30/2017", "10/4/2017", "10/5/2016", "10/7/2016", "11/1/2016",
"11/11/2017", "11/12/2016", "11/13/2015", "11/14/2017", "11/15/2017",
"11/16/2017", "11/17/2016", "11/21/2017", "11/24/2015", "11/25/2015",
"11/25/2017", "11/29/2017", "11/3/2016", "11/3/2017", "11/30/2016",
"12/1/2015", "12/13/2015", "12/13/2017", "12/2/2016", "12/2/2017",
"12/20/2016", "12/22/2015", "12/22/2017", "12/26/2015", "12/26/2016",
"12/26/2017", "12/28/2015", "12/29/2016", "12/30/2015", "12/4/2017",
"12/5/2017", "12/6/2016", "2/10/2017", "2/11/2017", "2/12/2016",
"2/16/2016", "2/16/2017", "2/17/2016", "2/17/2018", "2/2/2016",
"2/21/2018", "2/25/2016", "2/3/2017", "2/4/2016", "2/6/2018",
"3/1/2017", "3/18/2018", "3/20/2018", "3/21/2017", "3/27/2017",
"3/4/2018", "3/5/2017", "4/10/2018", "4/11/2017", "4/12/2017",
"4/14/2016", "4/15/2018", "4/17/2016", "4/17/2017", "4/20/2017",
"4/22/2018", "4/23/2016", "4/23/2018", "4/24/2018", "4/25/2018",
"4/3/2018", "4/30/2016", "4/30/2017", "4/4/2017", "4/5/2016",
"4/5/2018", "4/8/2018", "4/9/2018", "5/13/2018", "5/15/2017",
"5/15/2018", "5/18/2016", "5/24/2016", "5/25/2016", "5/26/2018",
"5/29/2017", "5/29/2018", "5/5/2018", "5/6/2018", "5/7/2016",
"5/7/2018", "5/8/2018", "6/10/2018", "6/11/2017", "6/11/2018",
"6/13/2016", "6/18/2017", "6/22/2017", "6/26/2018", "7/10/2017",
"7/12/2016", "7/12/2017", "7/15/2017", "7/20/2016", "7/25/2016",
"7/25/2017", "7/29/2017", "7/6/2016", "7/8/2017", "8/12/2017",
"8/14/2017", "8/16/2016", "8/16/2017", "8/17/2016", "8/21/2017",
"8/23/2016", "8/26/2017", "8/27/2017", "8/6/2017", "8/8/2016",
"8/8/2017", "9/1/2015", "9/15/2015", "9/15/2016", "9/19/2017",
"9/2/2016", "9/21/2015", "9/21/2016", "9/28/2017", "9/30/2015",
"9/30/2016", "9/6/2016", "9/8/2016"), class = "factor")), class = "data.frame", row.names = c(NA,
-293L))

I am trying to use the below code to answer first part of the question

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

compare <- (datanew1$location==datanew1$location[i]) &

(datanew1$grade==datanew1$grade[i]) &
(datanew1$Date_Created >=datanew1$less6month[i])&

(datanew1$Date_Created <= datanew1$Date_Created[i])

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

Hi I have a problem and need the help
I am trying to build a for loop in R . but facing the error as the loop is not creating the exact variable and may be double counting. Also if you can suggest efficient way of doing this.
What i trying to do is

  1. I would like to develop a code which can create a variable which can sum up all the values in the column "nos" based on the filter conditions.Below are the conditions. i want to add a counter called sum (nos[i]) to sum all the rows which satisify below conditions but missing something .

The logic for less6 month calculation
library(lubridate)
dataji$Date_Created <- as.Date(dataji$Date_Created, "%m/%d/%Y") # convert to date
dataji$less6month <- dataji$Date_Created %m-% months(6) # subtract 6 months

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))
The final output should like this

There is something similar to this problem but i am unable to replicate it.

Any suggestions would be really help me moving forward thanks

Hi @woodward

Any thoughts or suggestion would be really useful. Thanks

Hi I am trying the below code but its not calculating the sum of the values in the column of nos based on the filter conditions. Its giving me just the same number
i <- 0
for (i in 1:nrow(datanew2)) {
datanew2$sum[i]<- 0
for (j in 1:nrow(datanew2)) {
if((datanew2$location[i]==datanew2$location[i])&
(datanew2$grade[i]==datanew2$grade[i])&
(datanew1$Date_Created >=datanew1$less6month[i])&
(datanew1$Date_Created <= datanew1$Date_Created[i]))
datanew2$sum[i] <- datanew2$nos[i] + datanew2$sum[j]
datanew2$sum[i] <- (datanew2$sum[i]) + datanew2$nos[j]
}
}

To be honest I still don't understand what you are trying to do. "Date_Created is less than 6 months less than Date_Created" makes no sense to me. I think you are talking about two different meanings on "Date_Created", one of them is for an individual row and one of them is for a group of rows? If you mean to discard records that are older than the newest record in each group, this code will do it:

library(dplyr)
library(lubridate)

datasum <- dataji %>% 
	group_by(id, nos, JG, Department, Country) %>% 
	mutate( 
		Date_Created = mdy(as.character(Date_Created)), # convert factor to date
		current_date = max(Date_Created), # find reference date for this grouping?
		earliest_date = current_date %m-% months(6), # 6 months prior to reference date
	) %>% 
	filter(Date_Created >= earliest_date) %>% 
	summarise(
		Count = n(),
		Sumng = sum(nos)
	)

datasum 

# Groups:   id, nos, JG, Department [293]
      id   nos    JG Department Country       Count Sumng
   <int> <int> <int> <fct>      <fct>         <int> <int>
 1     1     4     3 Fin        Netherlands       1     4
 2     2     2     3 Fin        Netherlands       1     2
 3     3     3     3 Fin        Netherlands       1     3
 4     4     4     4 Ops        United States     1     4
 5     5     5     4 Ops        United States     1     5
 6     6    19     4 Ops        United States     1    19
 7     7     7     3 RE         Australia         1     7
 8     8     1     4 RE         Australia         1     1
 9     9     2     3 RE         Netherlands       1     2
10    10     1     2 RE         Nigeria           1     1
# ... with 283 more rows

Hi @woodward
Thanks for the reply. To answer your question the code should create a column called “sum “ which is the sum of all values / rows in the column “nos” which satisfies the below conditions,

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])


The sum column should be summation of the values in column “no” which satisfies the conditions

The logic behind Date_created is - It

  1. My code should go through each and every row and see if all the filter conditions are satisfied and the code selects all the rows and give a count where these rows within the 6 months of the Date_Created variable.
    For e.g if Date_Created is 1/10/2018 then the six months less is 1/04/2018. so the code should all the rows which are within this time period. Like that it should do for all the rows and create new less than 6 month for each row.
  2. The second objective is it to create another column called "total" which is the sum of all the values in column "nos" which satisfies the above conditions.
  1. Ideally the code should take the first row and match the conditions with all other rows and create a count variable which is less than 6 months of Date_created variable for that row.
    Similarly it should go to the second row and match the conditions with all other rows and see which all rows are within the 6 months of the Date_created for that row.
    So in general there will be new 6 month window for every row.

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

  1. 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)

}

  1. 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.

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

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 .

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.