Help on Looping

General
1

Hi All,

I need help. I am working on a problem is to create a variable which satisfies the below conditions. The code is below. Is there a better and faster way. Thank you for all the help

chk$lessmonth <- as.Date(chk$Date_Created, "%m/%d/%Y") %m-% months(6)
chk$greatmonth <- as.Date(chk$Date_Created_Created, "%m/%d/%Y") %m+% months(6)

for (i in 1:nrow(chk)){
  count <- 0
  for (j in 2:nrow(chk)){
    if((chk[i, "skill"] == chk[j, "skill"])&
       (chk[i, "country"] == chk[j, "country"])&
       (chk[i, "jobgroup"] == chk[j, "jobgroup"] | chk[i, "jobgroup"] +1 == chk[j, "jobgroup"])&
             (chk[i, "Date_Created"] >= chk[j, "lessmonth"]))
      count <- count + 1
  }
  print(count)
  chk[i, "out"] <- count
}

The other version or example which can be replicated is below

library(lubridate)
df1 <- data.frame(Job.cat = c(2L, 3L, 4L, 5L, 6L),
                  skill = as.factor(c("Art", "science", "maths", "maths", "sciencce")),
                  conditions = as.factor(c("L", "E", "L", "L", "L")),
                  work_location = as.factor(c("IND", "NZ", "CHI", "SWT", "IND")),
                  Date.created = as.factor(c("1/30/2016", "2/27/2017", "3/20/2018",
                                             "4/22/2017", "5/26/2018")))

for (i in 1:nrow(df1)){
  count <- 0
  for (j in 1:nrow(df1)){
    if((df1[i, "skill"] == df1[j,"skill"])&
       (df1[i, "job.cat"] == df1[j, "job.cat"] | df1[i, "job.cat"] +1 ==  df1[j, "job.cat"])&
      else if [(df1[i, "work_location"] == df1[j, "work_location])&
       (df1[j, "Date.Available"] >= df1[i, "lessmonth1"] 
    )
      count <- count + 1
  }
  print(count)
  df1[i, "out"] <- count
  
}
2

Your code doesn't work. There are multiple errors.

R is case sensitive, so Job.cat is different from job.cat.

Your data does not have Date.Available.

Use && and || in the if statement.

This works, but I'm not sure what you are trying to do.

library(lubridate)
df1 <- data.frame(job.cat = c(2L, 3L, 4L, 5L, 6L),
				  skill = as.factor(c("Art", "science", "maths", "maths", "sciencce")),
				  conditions = as.factor(c("L", "E", "L", "L", "L")),
				  work_location = as.factor(c("IND", "NZ", "CHI", "SWT", "IND")),
				  Date.created = as.factor(c("1/30/2016", "2/27/2017", "3/20/2018",
				  						   "4/22/2017", "5/26/2018")))

df$out <- 0 # create a new column
for (i in 1:nrow(df1)){
	count <- 0
	for (j in 1:nrow(df1)){
		if ((df1[i, "skill"] == df1[j,"skill"]) &&
		    (df1[i, "job.cat"] == df1[j, "job.cat"]) &&
			(df1[i, "work_location"] == df1[j, "work_location"])){
			count <- count + 1
		}
	}
	print(count)
	df1[i, "out"] <- count
}
1 Like
3

Thanks for the reply. The problem i am currently working on is where

  1. The code should create a new column with the counts (the number of times the filter conditions are satisfied)
  2. It should also calculate the sum of a variable which satisfies the conditions 1.

For e.g. The data has following columns

  1. Id
  2. no
  3. JG
  4. Department
  5. country
  6. Date_Created
    The code has to go through the each row in the datasets and check for following conditions and create a two new columns.

Filter condition 1.
if (JG = =JG) & (Department ==Department) & (country== country) & (Date_Created >= 6 months less)
In the above line the code has to create a new column which is less than the 6 months less of the Date_Created. For eg. if Date_Create is 01/12/2018 then the 6 months less will be 01/06/2018.
So any rows in the data which satisifies the condition above of (JG = =JG) & (Department ==Department) & (country== country) & (Date_Created >= 6 months less) then it should create two variables

  1. Count
  2. Sum of the variable "no" which satisfies the above condition.

Below is the example of the data.

|id|no|JG|Department|Country|Date_Created|Count - of less than date|sum of column B (Satisfying column G)|

id no JG Department Country Date_Created Count - of less than 6 months of Date_Created sum of column B (Satisfying column G)
1 1 2 Fin Australia 12/13/2016
2 16 4 Fin China 1/6/2017
3 7 4 Fin Qatar 1/12/2017
4 4 4 Ops China 1/20/2017
5 1 2 Ops Egypt 1/25/2017
6 2 3 Ops Nigeria 2/8/2017
7 1 3 RE United Kingdom 2/16/2017
8 2 3 RE Nigeria 2/17/2017
9 2 4 RE Canada 2/23/2017
10 5 3 RE Singapore 3/2/2017
11 1 4 Fin United States 3/6/2017

I have written the code based on the above conditions.

The final output will look like this

id no JG Department Country Date_Created Count - of less than 6 months of Date_Created sum of column B (Satisfying column G)
1 1 2 Fin Australia 12/13/2016 0 0
2 16 4 Fin China 1/6/2017 0 0
3 7 4 Fin Qatar 1/12/2017
4 4 4 Ops China 1/20/2017
5 1 2 Ops Egypt 1/25/2017
6 2 3 Ops Nigeria 2/8/2017
7 1 3 RE United Kingdom 2/16/2017
8 2 3 RE Nigeria 2/17/2017
9 2 4 RE Canada 2/23/2017
10 5 3 RE Singapore 3/2/2017
11 1 4 Fin United States 3/6/2017
4

That makes no sense. If you compare X==X it will always be true. You have to compare different things. And "6 months less" is meaningless; what is 6 months less than what?

If you want to provide your data please use the dput(your_dataframe_here) function. It prints some text. Copy the text to here.

5

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

7

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
8

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
9

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
10

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

12

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

13

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

outpu1
outpu1765×468 10.2 KB

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

14

Any suggestions would be really help me moving forward thanks

15

Hi @woodward

Any thoughts or suggestion would be really useful. Thanks

16

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

17

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
18

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,

19

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

20

image
image1280×494 173 KB

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

21

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