去除Return的訂單、計算RFM

2022-10-16DAC工作坊.py

#%% 讀取資料
import pandas as pd

orders_df = pd.read_excel('Order1.xlsx')
orders_df.columns
#%% 合併訂單產品ID
orders_df["TradeProductID"] = orders_df["TradesID"] + orders_df["ProductID"]


#%% 篩選return
mask_return = orders_df["Status"] == "Return"
return_trades_id = list(orders_df[mask_return]["TradeProductID"])
# %% 去除return同訂單的資料
return_order_mask = orders_df["TradeProductID"].isin(return_trades_id)
orders_keep_df = orders_df[~return_order_mask]
orders_keep_df


#%% 去除重複的訂單
orders_keep_df = orders_keep_df.drop_duplicates(subset="TradesID")
orders_keep_df.to_excel("Orders(去除退貨).xlsx", index=False)
# %% 合併到會員資料


member_df = pd.read_excel("Member.xlsx")

#%% 計算RFM 
def get_frequency(row):
    return orders_keep_df[orders_keep_df["MemberID"] == row["MemberID"]].shape[0]

def get_total_amount(row):
    return orders_keep_df[orders_keep_df["MemberID"] == row["MemberID"]]["Payment"].sum()
def get_recency(row):
    return orders_keep_df[orders_keep_df["MemberID"] == row["MemberID"]]["TradesDateTime"].max()

member_df["Frequency"] = member_df.apply(get_frequency, axis=1)
member_df["TotalAmount"] = member_df.apply(get_total_amount, axis=1)
member_df["Recency"] = member_df.apply(get_recency, axis=1)
member_df

#%% 
member_df.to_excel("Member(加入RFM).xlsx", index=False)
# %%