0

Hey guys I have 2 DBs:

BaseDB:

"id", "Main_event_date"

*id*  *Main_event_date*
1       01/01/2017
2       01/07/2018
3       01/11/2017

DB_2

"id","event_date"

*id*   *event_date*
1       01/02/2017
1       19/12/2017
1       19/01/2018
2       01/10/2018
2       10/01/2019

DB_2 might be duplicated in "id": every time an "id" has an event, I have a new row with a new "event_date".

I reshaped the DB_2 from long to wide using "reshape", so that now I have: DB_2_wide

"id", "event_date0", "event_date1"...."event_date100".

I would like to join the BaseDB with DB_2_Wide considering only the first 30 events after the main_event_date for every "id" having a final table of the type:

Final_Table

id, t1, ... t30

*id* *t1* *t2* *t3*   ..... *t30*
1      x   NA    NA
2      NA  NA    x
3      NA  NA    NA


Considering also that t1 for 1 is Feb2017, 
                      t1 for 2 is August 2018
                      t1 for 3 is December 2017

Not sure how to do this because simply using left_join (or merge) I would get a table like:

"id", "event_date0", "event_date1"...."event_date100",

hence for every id I would also get a column for events before the main_event_date, which would be NA.

Not sure this is clear so let me know if have any questions.

Roberto92
  • 11
  • 2
  • 1
    Welcome! This is not clear. Could you edit your question to provide reproducible sample data of the two databases, and the desired final output? – jpsmith Jan 12 '23 at 14:16
  • 1
    Hi Roberto, welcome to SO! It’ll be much easier to help if we have a copy-pasteable sample of your data. The easiest way to provide this is running `dput(head(BaseDB, 10))` and pasting the result into your question, and the same for `DB_2`. See [How to make a great R reproducible example](https://stackoverflow.com/q/5963269/17303805) for more info. Thanks! – zephryl Jan 12 '23 at 14:18
  • What I am thinking is that I could do a wide-type table having *id*, *Main_event_date*, *t1*....*t30*, and then fill the cells based on *tx*=*event_date* for every *id*. I don't exactly know how to code this but it might work... – Roberto92 Jan 12 '23 at 14:45

0 Answers0