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.
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.
|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:
|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.
Andrew from TX