R : Opération data.table imbriquée

Question

Voici un mini exemple pour illustrer mon problème : J'ai deux tables de données (objets data.table), dont une très grande, la table A, avec 4 millions de produits qui sont enregistrés à une date donnée. date et qui ont un date d'ex(piration) . Pendant ces deux dates, les produits paient un certain montant si leur période couvre une certaine date_de_paiement les deux dernières variables sont rassemblées dans un second tableau B.

tête du tableau A :

         date     exdate unique_id
1: 1999-01-04 1999-09-18         294
2: 1999-01-04 1999-09-18         295
3: 1999-01-04 1999-09-18         296
4: 1999-01-04 1999-09-18         297
5: 1999-01-04 1999-09-18         298
6: 1999-01-05 1999-09-18         299

Tête de la table B :

   payment_date amount
1:   1998-06-30   4.18
2:   1998-09-30   4.26
3:   1998-12-31   4.00
4:   1999-03-31   4.01
5:   1999-06-30   4.18
6:   1999-09-30   4.45

Il est important que les produits du tableau A aient des durées de période différentes, de sorte que pour certains il y a plusieurs dates de paiement et pour d'autres il n'y en a pas (les dates sont en %Y%M%D).

Je veux maintenant collecter pour chaque produit dans A tous les montants et toutes les dates de paiement jusqu'à sa date d'expiration. Bien sûr, c'est faisable par une boucle for, mais c'est très ennuyeux étant donné la taille de la table. C'est pourquoi je souhaite utiliser les méthodes data.table, beaucoup plus rapides.

Pour l'instant, la formule suivante fait l'affaire pour les montants :

tmp <- B[A,on=.(payment_date>date, payment_date<=exdate),.(amount,unique_id,payment_date),by=.EACHI]
colnames(tmp)<-c("date","exdate","amount","unique_id","payment_date")

La sortie se présente comme suit :

         date     exdate amount unique_id payment_date
1: 1999-01-04 1999-09-18   4.01       294   1999-03-31
2: 1999-01-04 1999-09-18   4.18       294   1999-03-31
3: 1999-01-04 1999-09-18   4.01       295   1999-03-31
4: 1999-01-04 1999-09-18   4.18       295   1999-03-31
5: 1999-01-04 1999-09-18   4.01       296   1999-03-31
6: 1999-01-04 1999-09-18   4.18       296   1999-03-31
7: 1999-01-04 1999-09-18   4.01       297   1999-03-31
8: 1999-01-04 1999-09-18   4.18       297   1999-03-31
9: 1999-01-04 1999-09-18   4.01       298   1999-03-31

Mon problème est que data.table ne me donne que la première date de paiement pour chaque montant, donc bien que ces produits aient clairement deux dates de paiement au cours de leur vie, je n'obtiens que (1999-03-31,1999-03-31), au lieu de (1999-03-31, 1999-06-30).

Quelqu'un sait-il comment je peux dire à data.table de me donner à la fois le vecteur respectif des montants et des dates de paiement au lieu de me donner uniquement les montants et le premier élément du vecteur des dates de paiement pour chaque produit ?

J'espère que cet exemple couvre l'ensemble de mon combat. Si vous avez besoin de plus d'informations pour clarifier les choses, n'hésitez pas à me le faire savoir. Toute aide est très appréciée.

P.S : Bien sûr, j'ai aussi essayé sapply(), mais comme d'habitude, il fonctionne comme une boucle for et ne permet pas de gagner beaucoup de temps par rapport aux méthodes data.table.

Answer 1

C'est étrange mais si l'on ajoute une copie de payment_date à B, cela semble fonctionner.

library(data.table)
library(magrittr)

A <- read.table(header = TRUE, text = "
         date     exdate unique_id
1: 1999-01-04 1999-09-18         294
2: 1999-01-04 1999-09-18         295
3: 1999-01-04 1999-09-18         296
4: 1999-01-04 1999-09-18         297
5: 1999-01-04 1999-09-18         298
6: 1999-01-05 1999-09-18         299
                ") %>%
    setDT() %>%
    .[, date := as.Date(date)] %>%
    .[, exdate := as.Date(exdate)]

B <- read.table(header = TRUE, text = "
   payment_date amount
1:   1998-06-30   4.18
2:   1998-09-30   4.26
3:   1998-12-31   4.00
4:   1999-03-31   4.01
5:   1999-06-30   4.18
6:   1999-09-30   4.45
                ") %>%
    setDT() %>%
    .[, payment_date := as.Date(payment_date)]

B[, payment_date_copy := payment_date][A, on = .(payment_date > date, payment_date <= exdate)] %>%
    setnames(1:5, c("date", "amount", "payment_date", "exdate", "unique_id")) %>%
    print()

Answer 2

Vos données

A <- structure(list(date = structure(c(10595, 10595, 10595, 10595, 
10595, 10596), class = "Date"), exdate = structure(c(10852, 10852, 
10852, 10852, 10852, 10852), class = "Date"), unique_id = 294:299), class = "data.frame", .Names = c("date", 
"exdate", "unique_id"), row.names = c(NA, -6L))

B <- structure(list(payment_date = structure(c(10407, 10499, 10591, 
10681, 10772, 10864), class = "Date"), amount = c(4.18, 4.26, 
4, 4.01, 4.18, 4.45)), class = "data.frame", .Names = c("payment_date", 
"amount"), row.names = c(NA, -6L))

solution tidyverse

Vous pouvez utiliser tidyr:nest y purrr:map pour y parvenir. dplyr:between est une enveloppe pour x >= left_arg & x <= right_arg

library(tidyverse)
A %>% 
  mutate(copy1=date, copy2=exdate) %>%
  nest(copy1, copy2) %>%                         # nest copies of date and exdate
  mutate(data = map(data, ~B %>% filter(between(payment_date, .x$copy1, .x$copy2)))) %>%               # filter B where payment_date is between date and ex_date of A[row,]
  unnest(data) 

Sortie

         date     exdate unique_id payment_date amount
       <date>     <date>     <int>       <date>  <dbl>
 1 1999-01-04 1999-09-18       294   1999-03-31   4.01
 2 1999-01-04 1999-09-18       294   1999-06-30   4.18
 3 1999-01-04 1999-09-18       295   1999-03-31   4.01
 4 1999-01-04 1999-09-18       295   1999-06-30   4.18
 5 1999-01-04 1999-09-18       296   1999-03-31   4.01
 6 1999-01-04 1999-09-18       296   1999-06-30   4.18
 7 1999-01-04 1999-09-18       297   1999-03-31   4.01
 8 1999-01-04 1999-09-18       297   1999-06-30   4.18
 9 1999-01-04 1999-09-18       298   1999-03-31   4.01
10 1999-01-04 1999-09-18       298   1999-06-30   4.18
11 1999-01-05 1999-09-18       299   1999-03-31   4.01
12 1999-01-05 1999-09-18       299   1999-06-30   4.18