R help – how to calculate a new column that sums a column from a different table filtered by two dates?

Asked By: Anonymous

I have two tbls.

workorders:

# A tibble: 6 x 6
  cleaningindex wonumber createddate nextdate  
  <chr>            <int> <date>      <date>    
1 1                 2093 2017-01-11  2017-02-09
2 2                 2514 2017-02-09  2017-03-03
3 3                 2904 2017-03-03  2017-03-24
4 4                 3070 2017-03-24  2017-06-06
5 5                 3669 2017-06-06  2017-07-17
6 6                 3997 2017-07-17  2017-08-24

and batchreceipts:

# A tibble: 6 x 9
  datetimeindex `Batch-Num` `Receipt-Num` `Receipt-Date`      `Receipt-Time` Quantity   datetime  
  <chr>               <int>         <int> <dttm>              <chr>             <dbl>   <chr>    <chr>      
1 1                   99241         88678 2017-01-11 00:00:00 00:57:55          46500   2017-01~ 
2 2                   99322         88689 2017-01-11 00:00:00 05:09:29          45800   2017-01~ 
3 3                   99323         88703 2017-01-11 00:00:00 05:29:51          45000   2017-01~ 
4 4                   99242         88704 2017-01-11 00:00:00 13:04:20          44600   2017-01~ 
5 5                   99243         88711 2017-01-11 00:00:00 13:08:36          45000   2017-01~ 
6 6                   99353         88733 2017-01-12 00:00:00 03:47:23          45225   2017-01~ 

What I need to do is I need to create a new column in the first table called "quantity" that is a sum of the "Quantity" column from the batchreceipts table, filtered so that the quantity column in the workorders table only sums the quantities based on a batchreceipts$`Receipt-Date` >= workorders$`createddate` AND batchreceipts$`Receipt-Date <= workorders$`nextdate`

I’m not finding any resources to suggest a way to structure a new column addition like this. can anyone offer guidance?

** edit to show what my necessary / expected output would be (i added the quantity column and inputted some arbitrary values):

workorders:

# A tibble: 6 x 6
  cleaningindex wonumber createddate nextdate   quantity
  <chr>            <int> <date>      <date>        <int>
1 1                 2093 2017-01-11  2017-02-09   800000
2 2                 2514 2017-02-09  2017-03-03   925000
3 3                 2904 2017-03-03  2017-03-24  1200000 
4 4                 3070 2017-03-24  2017-06-06   715000
5 5                 3669 2017-06-06  2017-07-17   945000
6 6                 3997 2017-07-17  2017-08-24   400000

Solution

Answered By: Anonymous

One solution is to use mapply() with a function that subsets your "Quantities" based on whether they are in a row with a "receipt date" that falls in the range between "createdate and "nextdate":

quantity_sum <- function(date1, date2) {
  rdate <- batchreceipts$`Receipt-Date`
  matching_rows <- (rdate >= date1) & (rdate <= date2)
  sum(batchreceipts$Quantity[matching_rows])
}

workorders$`Quantity Sum` <- mapply(quantity_sum, workorders$createddate, workorders$nextdate)

and this gives us:

# A tibble: 6 x 5
  cleaningindex wonumber createddate nextdate   `Quantity Sum`
          <dbl>    <dbl> <date>      <date>              <dbl>
1             1     2093 2017-01-11  2017-02-09         272125
2             2     2514 2017-02-09  2017-03-03              0
3             3     2904 2017-03-03  2017-03-24              0
4             4     3070 2017-03-24  2017-06-06              0
5             5     3669 2017-06-06  2017-07-17              0
6             6     3997 2017-07-17  2017-08-24              0

Note: In the future, please provide code to reproduce your data. I’m using tribble() here but an even better way is to copy the output from dput(workorders).

library(dplyr)
workorders <- tribble(
~cleaningindex, ~wonumber, ~createddate, ~nextdate, 
1,                 2093, "2017-01-11",  "2017-02-09",
2,                 2514, "2017-02-09",  "2017-03-03",
3,                 2904, "2017-03-03",  "2017-03-24",
4,                 3070, "2017-03-24",  "2017-06-06",
5,                 3669, "2017-06-06",  "2017-07-17",
6,                 3997, "2017-07-17",  "2017-08-24"
)

batchreceipts <- tribble(
~datetimeindex, ~`Batch-Num`, ~`Receipt-Num`, ~`Receipt-Date`, ~`Receipt-Time`, ~Quantity, 
1, 99241, 88678, "2017-01-11 00:00:00", "00:57:55", 46500,
2, 99322, 88689, "2017-01-11 00:00:00", "05:09:29", 45800,
3, 99323, 88703, "2017-01-11 00:00:00", "05:29:51", 45000,
4, 99242, 88704, "2017-01-11 00:00:00", "13:04:20", 44600,
5, 99243, 88711, "2017-01-11 00:00:00", "13:08:36", 45000,
6, 99353, 88733, "2017-01-12 00:00:00", "03:47:23", 45225
)

workorders[[3]] <- as.Date(workorders[[3]])
workorders[[4]] <- as.Date(workorders[[4]])
batchreceipts[[4]] <- as.Date(batchreceipts[[4]])
techinplanet staff

techinplanet staff


Windows 10 Kaufen Windows 10 Pro Office 2019 Kaufen Office 365 Lizenz Windows 10 Home Lizenz Office 2019 Home Business Kaufen windows office 365 satın al follower kaufen instagram follower kaufen porno