How to group data based on conditional on datetimes (first record or last record) and count of events over time

I have decided to use R as my primary coding language, and I am faced with a challenge. I have been given two data frames:

Data frame A: Has the information of purchases in a clothing store with the variables: name of client, date of purchase, agent and product purchased during a period of time t.

NAME PRODUCT AGENT DATE_PURCHASE
Karen M_14 X_1 8-25-20021 18:21:28
Jean M_78 X_3 8-26-20021 18:11:06
Jean M_71 X_4 8-26-20021 18:21:01
Jean M_64 X_4 8-27-20021 20:21:59
Keith M_57 X_4 8-27-20021 20:21:02
Alba M_50 X_1 8-28-20021 20:21:03
Alba M_43 X_3 8-29-20021 20:21:04
Alex M_36 X_2 8-25-20021 20:21:05

Data frame B: Has the information of clients who have called the CX SERVICE line of the company during a period of time t and stores the variables name of client, date of call, and type of call.

NAME TYPE DATE_OF_CALL DATE_PURCHASE
Karen COMPLAIN 8-26-20021 18:21:28 8-25-20021 18:21:28
Jean CX_SERVICE 8-27-20021 18:11:06 8-26-20021 18:11:06
Jean COMPLAIN 8-28-20021 18:21:01 8-26-20021 18:21:01
Jean CX_SERVICE 8-29-20021 20:21:59 8-27-20021 20:21:59
Keith CX_SERVICE 8-29-20021 20:21:02 8-27-20021 20:21:02
Alba COMPLAIN 8-30-20021 20:21:03 8-28-20021 20:21:03
Alex CX_SERVICE 8-25-20021 21:21:05 8-29-20021 20:21:04

I have to build a table in which It will be shown by NAME what was the very last product purchased by the customer prior to their very last call to the customer service line and it should include the variables: NAME ,LAST_PRODUCT_PURCHASED, AGENT, DATE_PURCHASE, TYPE, DATE_OF_CALL that table should look something like this:

RESULTS

NAME LAST_PRODUCT_PURCHASED AGENT DATE_PURCHASE TYPE DATE_OF_CALL
Karen M_14 X_1 8-25-20021 18:21:28 COMPLAIN 8-26-20021 18:21:28
Jean M_64 X_4 8-27-20021 20:21:59 CX_SERVICE 8-29-20021 20:21:59
Keith M_57 X_4 8-27-20021 20:21:02 CX_SERVICE 8-29-20021 20:21:02
Alba M_43 X_3 8-29-20021 20:21:04 COMPLAIN 8-30-20021 20:21:03
Alex M_36 X_2 8-25-20021 20:21:05 CX_SERVICE 8-25-20021 21:21:05

For example: The second raw shows the desired result as the very last product purchased by Jean was M-78 and her very last call on the line was a TYPE= CX_SERVICE with date 8-29-20021 20:21:59

I have been thinking about doing a group by NAME and DATES or perhaps a join but I cant seen to find a way to deal with the condition of "last" product and "last" date to call on the line

I have been trying to come up with a solution but I finding myself needing of help. I kindly thank you for your assitance or tips, or reference to documentation that can help me achive my results. Thank you so much.

PD: What if we would try to add a column that counts how many time the custumer (NAME) has called prior to their most recent call on the line.

Thank you,

Andrew from TX



Read more here: https://stackoverflow.com/questions/67014728/how-to-group-data-based-on-conditional-on-datetimes-first-record-or-last-record

Content Attribution

This content was originally published by R_Student at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: