Calculations based on the date

Question

I have a dataset of my actual observations df1:

Date        Month     Year  Actual
02/12/2017  December    17  4623
12/12/2017  December    17  5111
22/12/2017  December    17  4800
22/12/2017  December    17  4769
02/01/2018  January     18  4711
03/01/2018  January     18  4503
04/01/2018  January     18  4650
05/01/2018  January     18  4598
06/02/2018  February    18  4612
07/02/2018  February    18  4493
08/02/2018  February    18  4515
09/02/2018  February    18  4469

And then my monthly predictions df2:

Month     Year  Prediction
December  17    4874
January   18    4626
February  18    4576

How do I subtract my prediction from my actual value based on each month and year? So I would get the following errors:

Error
-251
237
-74
-105
85
-123
24
-28
36
-83
-61
-107

Kindly provide the reproducible data. – Saurabh Chauhan Jul 20 '18 at 12:43 — Saurabh Chauhan, Jul 20 '18 at 12:43

G. Grothendieck · Answer 1 · 2018-07-20T13:47:44.847

Note that the result in the question is wrong since the years do not match for the 2018 actuals.

1) Base R Left join the data frames and perform the subtraction:

transform(merge(act, pred, all.x = TRUE, sort = FALSE), Diff = Prediction - Actual)

giving:

      Month Year       Date Actual Prediction Diff
1  December   17 02/12/2017   4623       4874  251
2  December   17 12/12/2017   5111       4874 -237
3  December   17 22/12/2017   4800       4874   74
4  December   17 22/12/2017   4769       4874  105
5   January   18 02/01/2018   4711         NA   NA
6   January   18 03/01/2018   4503         NA   NA
7   January   18 04/01/2018   4650         NA   NA
8   January   18 05/01/2018   4598         NA   NA
9  February   18 06/02/2018   4612         NA   NA
10 February   18 07/02/2018   4493         NA   NA
11 February   18 08/02/2018   4515         NA   NA
12 February   18 09/02/2018   4469         NA   NA

sqldf

library(sqldf)
sqldf("select *, Prediction - Actual as Diff  
       from act left join pred using(Year, Month)")

giving:

         Date    Month Year Actual Prediction Diff
1  02/12/2017 December   17   4623       4874  251
2  12/12/2017 December   17   5111       4874 -237
3  22/12/2017 December   17   4800       4874   74
4  22/12/2017 December   17   4769       4874  105
5  02/01/2018  January   18   4711         NA   NA
6  03/01/2018  January   18   4503         NA   NA
7  04/01/2018  January   18   4650         NA   NA
8  05/01/2018  January   18   4598         NA   NA
9  06/02/2018 February   18   4612         NA   NA
10 07/02/2018 February   18   4493         NA   NA
11 08/02/2018 February   18   4515         NA   NA
12 09/02/2018 February   18   4469         NA   NA

Note

The input in reproducible form is:

Lines1 <- "
Date        Month     Year  Actual
02/12/2017  December    17  4623
12/12/2017  December    17  5111
22/12/2017  December    17  4800
22/12/2017  December    17  4769
02/01/2018  January     18  4711
03/01/2018  January     18  4503
04/01/2018  January     18  4650
05/01/2018  January     18  4598
06/02/2018  February    18  4612
07/02/2018  February    18  4493
08/02/2018  February    18  4515
09/02/2018  February    18  4469"
act <- read.table(text = Lines1, header = TRUE, as.is = TRUE)

Lines2 <- "
Month     Year  Prediction
December  17    4874
January   17    4626
February  17    4576"
pred <- read.table(text = Lines2, header = TRUE, as.is = TRUE)

Thank you for this as well as how to make the data reproducible, as I was unsure how to do this. I will use that in the future. — PMc, Jul 20 '18 at 13:31

score 0 · Answer 2 · answered Jul 20 '18 at 13:15

Using the data from G Grothendieck again, including the comment on the year for January and February, you could do this with dplyr:

library(dplyr)
act %>% 
  full_join(pred) %>% 
  mutate(Error = Actual - Prediction) %>% 
  select(-Prediction)



         Date    Month Year Actual Error
1  02/12/2017 December   17   4623  -251
2  12/12/2017 December   17   5111   237
3  22/12/2017 December   17   4800   -74
4  22/12/2017 December   17   4769  -105
5  02/01/2018  January   18   4711    85
6  03/01/2018  January   18   4503  -123
7  04/01/2018  January   18   4650    24
8  05/01/2018  January   18   4598   -28
9  06/02/2018 February   18   4612    36
10 07/02/2018 February   18   4493   -83
11 08/02/2018 February   18   4515   -61
12 09/02/2018 February   18   4469  -107

DATA:

Lines1 <- "
Date        Month     Year  Actual
02/12/2017  December    17  4623
12/12/2017  December    17  5111
22/12/2017  December    17  4800
22/12/2017  December    17  4769
02/01/2018  January     18  4711
03/01/2018  January     18  4503
04/01/2018  January     18  4650
05/01/2018  January     18  4598
06/02/2018  February    18  4612
07/02/2018  February    18  4493
08/02/2018  February    18  4515
09/02/2018  February    18  4469"
act <- read.table(text = Lines1, header = TRUE, as.is = TRUE)

Lines2 <- "
Month     Year  Prediction
December  17    4874
January   18    4626
February  18    4576"
pred <- read.table(text = Lines2, header = TRUE, as.is = TRUE)

Calculations based on the date

2 Answers2

Note