0

Actual OutputI am trying to sum an amount if a column of dates (z) fall between a specified range. Unfortunately my loop doesn't seem to wok and i have a null output. 

 Sd <- as.Date('2017-01-01',tz = "GMT")
 EndDate <- as.Date('2017-01-20',tz = "GMT")
 Ed <- EndDate + 30

 LTV1 <- while (Sd < Ed) {


 Sd <- Sd + 1
 LTV1 <- LTV %>% group_by(InstallationDate)%>% filter(z < Sd) %>% 
 summarize(Amount = sum(USAmount))


 }

 as.data.frame(LTV1)

Apologies everyone. I am quite new to this. Here is a reproducible example:

  Sample <- as.data.frame(seq(as.Date("2017/01/01"), by = "day", length.out 
  = 15))
  Sample$Amount <- c(10,5,3,4,8,65,89,47,74,95,85,63,32,45,32)



  colnames(Sample)[1] <- "date"

  Sd <- as.Date('2017-01-01',tz = "GMT")
  EndDate <- as.Date('2017-01-5',tz = "GMT")
  Ed <- EndDate + 3

  Sample1 <- while (Sd < Ed) {


  Sd <- Sd + 1
  Sample1 <- Sample %>% group_by(date)%>% filter(date < Sd) %>% 
  summarize(Amount = sum(Amount))


  }

  as.data.frame(Sample1)


 Desired Output will be:
 Dates: Day 1    Day 2   Day 3 .......................
 Amount: 25       54      89   .......................
Xristos Solwmou
  • 109
  • 9
  • 1
    It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. `while()` always returns NULL invisibly. You should not be assigning a variable to the result of while, ie don't do `LTV1 <- while() {...}` – MrFlick Apr 22 '19 at 16:30
  • Thank you i have added a screenshot of the LTV table and one of the desired outcome. I would like to sum the US amount when the z column falls between the range of dates specified above – Xristos Solwmou Apr 22 '19 at 16:35
  • 1
    Screenshots of data are not helpful because we can't copy/paste them into R to test. See the link I provided for ways to share your data in a reproducible format. – MrFlick Apr 22 '19 at 16:36
  • Can you please also post your desired output? You have only posted one image, and it looks to be input. – Gregor Thomas Apr 22 '19 at 17:01
  • Hi @Gregor i have update the desired output. Thanks a lot – Xristos Solwmou Apr 22 '19 at 17:18
  • 1
    I don't understand your desired output. The first and second columns are constants, and then the third columns is different in each row? Why? What is the logic? It might make more sense if you showed the actual numeric output you are looking for corresponding to your sample input. Ideally, don't share it as a picture. Pictures make checking harder. – Gregor Thomas Apr 22 '19 at 17:31
  • Sorry wasn't clear. There are not constants but actually variables. Basically i want the amount of the previous date to be added to the next. – Xristos Solwmou Apr 22 '19 at 17:36
  • I understand they are variables. The pattern in the first row: `A1`, `A1 + A2`, `A1 + A2 + A3` makes sense. I don't understand why the pattern is different in the second and third rows, but *only different in the 3rd column*. **I think the best way for you to make this make sense is to post the actual, numeric result corresponding to your sample input**. – Gregor Thomas Apr 22 '19 at 20:32
  • Hi Gregor, i have posted the output when you run the code for 10 days. Now i have the summed amount after 10 days. However i would like for each day to be summed cumulatively, in separate columns. So i would need an extra 9 columns. The 10th column should much the column "Amount" in the screenshot i have posted. – Xristos Solwmou Apr 22 '19 at 21:01
  • You are asking for multiple columns. The "actual output" you show doesn't have multiple columns, thus it is not what you want. Can you please show that output that you want to get from the `Sample` input you have in your question? In text? Just type it into your question--just a few rows or columns would be fine. – Gregor Thomas Apr 22 '19 at 21:10
  • Maybe you want a single row and 10 columns? But the picture before that you had had multiple rows, so I am confused. – Gregor Thomas Apr 22 '19 at 21:11
  • Hi @Gregor. Sorry for troubling you. Basically, imagine that i have two columns of raw data, dates and amounts. Each date indicates the amount paid. Now i want the unique values of the dates to be rows, and another row below, with the sum of the amounts until that particular day. Thus the third day would also contain the amount collected the first and second day as well, and so on. The rows will be days between a date range. Hope its clear now – Xristos Solwmou Apr 23 '19 at 16:54
  • *"imagine that i have two columns of raw data, dates and amounts. Each date indicates the amount paid."* I don't have to imagine this, you shared code to produce `Sample` which shows this, it is nice and clear. *"Now i want the unique values of the dates to be rows,"* Done! The `Sample` you shared already has 15 unique dates, each in its own row. (1/2) – Gregor Thomas Apr 23 '19 at 17:00
  • *"...and another row below, with the sum of the amounts for that particular day"*. I don't understand. Each date is already unique, there is nothing to sum. And I don't understand what this extra row is. *"The rows will be days between a date range"*. I've asked at least 3 times for you to **please post the output that you want corresponding to the `Sample` input you provide.** Actual output please, not psuedo-excel formulas with typos. I don't understand why you haven't done that yet. I don't think I can ask in any other ways. (2/3) – Gregor Thomas Apr 23 '19 at 17:08
  • Hi Gregor, i wiil edit the post now with the desired output – Xristos Solwmou Apr 23 '19 at 17:11
  • Perhaps your `Sample` isn't very good? Maybe your input has multiple rows for each date? And those are needed for the output? If so, make `Sample` better. Also, I'm pretty sure you just have a typo when you said "I want the uniqe values of the dates to be rows", I think you meant columns, but I'm only like 80% sure. The example will be extremely helpful. – Gregor Thomas Apr 23 '19 at 17:13
  • yes you are right, apologies for that. I want the unique values to be columns, and yes my input has indeed multiple rows for each date. This is why i need the unique values. The caveat is that i want the amount summed up to that day. So 54 also contains 25, and 89 contains both, plus the amount of the particular day. – Xristos Solwmou Apr 23 '19 at 17:14

2 Answers2

0

I think this is what you want:

LTV1 = LTV %>% 
   arrange(InstallationDate) %>%
   group_by(InstallationDate) %>%
   summarize(daily_amount = sum(USAmount)) %>%
   ungroup() %>%
   mutate(cumulative_amount = cumsum(daily_amount))

I believe the cumulative_amount column is what you are trying to create---though it's hard to tell and impossible to test since you haven't reproducibly shared your input data. I also can't tell if the arrange, group_by, and summarize are needed---if your data is already in order by date, arrange isn't needed. If your data only has one row per day, the grouping and summarizing aren't needed.

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
  • Many thanks for your reply. Yes this is what i am trying to create, but i need a column for every day in the specified range , which will add the amount of the previous day. – Xristos Solwmou Apr 22 '19 at 16:58
-1

You assign (why?) to LTV1 the result of while, which is always NULL.

Michael M
  • 880
  • 7
  • 10