python - Get column values on last transaction date

Question

     customer  date      discount_code product_variant
0    KATIE  2019-05-15  no_discount        X1.1
1    KATIE  2019-05-15  no_discount        X1.2
15   KATIE  2019-06-24  no_discount        X1.1
16   KATIE  2019-06-24  no_discount        X2
141  MAX    2019-11-26  PR19               X1.1
263  OPRAH  2019-12-01  PR19               X1.2
264  OPRAH  2019-12-01  PR19               X2
334  PAUL   2020-01-14  no_discount        X3
1247 PAUL   2019-10-30  CHRISTMAS19        X2

I want to get the discount code and product variant that customers used and bought on their last transaction date.

With date, I can use .groupby('customer_name')['day'].max().reset_index().

Expected Output:

     customer  date      discount_code product_variant
15   KATIE  2019-06-24  no_discount        X1.1, X2
141  MAX    2019-11-26  PR19               X1.1
263  OPRAH  2019-12-01  PR19               X1.2, X2
334  PAUL   2020-01-14  no_discount        X3

标签： pythonpandasdataframepandas-groupby

Answer 1

IIUC you can groupby and transform for latest date of each customer, and then compare and get latest date for another groupby:

s = df.groupby("customer")["date"].transform("max")

print (df[df["date"].eq(s)].groupby("customer").agg(lambda d: ", ".join(d.unique())))

                date discount_code product_variant
customer                                          
KATIE     2019-06-24   no_discount        X1.1, X2
MAX       2019-11-26          PR19            X1.1
OPRAH     2019-12-01          PR19        X1.2, X2
PAUL      2020-01-14   no_discount              X3