<jupyter_start><jupyter_text>1. Order table features:
   * **order_id**: Unique order indentifier in-app.
   * **order_date**: Date when to user paid for an audio.
   * **user_id**: Unique user indentifier.
   * **order_type**: Type of order, include VIP/Coin/9K.
   * **order_amount**: The money value of an order.
   * **age**: Age of user.
   * **gender**: Gender of user.
   * **register_date**: User registration date.
   * **platform**: User device platform (only ios/android).
2. User table features:
   * **user_id**: Unique user indentifier.
   * **user_type**: Type of user, include VIP/Free.
   * **gender**: Gender of user.
   * **age**: Age of user.
   * **region**: Region that user come from (only local/cb).
   * **platform**: User device platform (only ios/android).
   * **register_date**: User registration date.
   * **total_listening_cnt**: Total listening count of a user.
   * **total_listening_amt**: Total listening of time in minutes.
3. Listening table features:
   * **playlist_id**: Unique audio indentifier.
   * **playlist_name**: Audio name.
   * **level1_category**: The category of audio (Level1).
   * **level2_category**: The category of audio (Level2).
   * **vip**: Type of audio, include VIP/free.
   * **playlist_duration**: Total duration of audio (in minutes).
   * **playlist_listening_amt**: Total time listening of a user on the audio.
   * **listening_timestamp**: The time user start listeing the audio (24h format).
   * **listening_date**: The time user start listeing the audio.  **Summary:**
**RFM Clustering**
 - R: Recency - Day since last listening_date (if user hasn't listened yet, change to day since last order_date)
 - F: Frequency - (Total_listening_cnt/Time_window) (Time_window is calculated as last_listening_date - first_listening_date, if user hasn't listened yet it will be NaN)
 - M: Monetary - (Total_order_amt/Time_window) (Time_window is calculated as last_listening_date - first_listening_date, if time_window is NaN monetary will equal to itself.  1. Calculate Recency Score (R): How many days past from last listening date?<jupyter_code># find the last listening_date, then calculate the recency first
df_recency = listening_df #make a coppy of order to make recency
df_recency['listening_date'] = pd.to_datetime(df_recency['listening_date'])
reference_date = pd.to_datetime('2020-12-31')
df_recency = df_recency.groupby(by='user_id', as_index=False)['listening_date'].max()

df_recency.columns = ['user_id', 'max_date']

df_recency['recency'] = df_recency['max_date'].apply(lambda row: (reference_date - row).days)
df_recency = df_recency[['user_id', 'recency']]
df_recency.head(10)
# find the last order_date in case of user hasn't listened yet
order_recency = order_df.groupby(by='user_id', as_index=False)['order_date'].max()
order_recency['order_date'] = pd.to_datetime(order_recency['order_date'])
order_recency['recency_order'] = order_recency['order_date'].apply(lambda row: (reference_date - row).days)
order_recency.head()<jupyter_output><empty_output><jupyter_text>2. Calculating Frequency score (F): How frequently users listen everday?<jupyter_code># find the total listening cnt of whole duration
listening_df['listening_date'] = pd.to_datetime(listening_df['listening_date'])

df_frequency = listening_df
df_frequency = df_frequency.groupby(by='user_id')['playlist_id'].count()
df_frequency = df_frequency.reset_index()
df_frequency.columns = ['user_id', 'frequency']
df_frequency.head()
# find the time window (the reason of using time window is to check the consecutive frequency)
time_df = listening_df
time_df_max = time_df.groupby(by='user_id')['listening_date'].max()
time_df_min = time_df.groupby(by='user_id')['listening_date'].min()
df_frequency = pd.merge(df_frequency, time_df_max, how='left', on ='user_id')
df_frequency = pd.merge(df_frequency, time_df_min, how='left', on ='user_id')
df_frequency.columns = ['user_id', 'frequency', 'latest_listening', 'first_listening']
df_frequency['latest_listening'] = pd.to_datetime(df_frequency['latest_listening'])
df_frequency['first_listening'] = pd.to_datetime(df_frequency['first_listening'])

df_frequency['time_range'] = (df_frequency['latest_listening'] - df_frequency['first_listening']).dt.days + 1
df_frequency.head(10)<jupyter_output><empty_output><jupyter_text>3. Calculating Moneytary Score (M): How much user spent a day in terms of consecutive frequency?<jupyter_code># find the total monetary of the whole duration
order_df['order_date'] = pd.to_datetime(order_df['order_date'])

# make a copy of order_df
df_monetary = order_df
df_monetary = df_monetary.groupby(by='user_id')['order_amount'].sum()

df_monetary = df_monetary.reset_index()
df_monetary.columns = ['user_id', 'monetary']
df_monetary.head()<jupyter_output><empty_output><jupyter_text>4. Concat all RFM into one table<jupyter_code>rfm_df = pd.merge(df_monetary, df_frequency[['user_id', 'frequency', 'time_range']], how='left', on='user_id')
rfm_df = pd.merge(rfm_df, df_recency, how='left', on='user_id')
rfm_df = pd.merge(rfm_df, order_recency[['user_id', 'recency_order']], how='left', on='user_id')
# rfm_df['monetary'] = rfm_df.apply(lambda x: x['monetary'] if math.isnan(x['time_range']) == True else x['monetary'] / x['time_range'], axis = 1)
rfm_df['frequency'] = rfm_df['frequency'] / rfm_df['time_range']
rfm_df['frequency'] = rfm_df['frequency'].fillna(value=float(0.0))
rfm_df['recency'] = rfm_df.apply(lambda x: x['recency_order'] if math.isnan(x['recency']) == True else x['recency'], axis = 1)
rfm_df = rfm_df[['user_id', 'recency', 'frequency', 'monetary']]
rfm_df.head()
# rfm_df.to_csv('rfm_draft.csv', encoding='utf-8', index=False)
def visualize_numerical(df, column, target = None):

    fig, (ax1,ax2) = plt.subplots(nrows = 1, ncols = 2, figsize = (14, 4));

    sns.histplot(df[column], ax=ax1, kde=True);
    ax1.set_xlabel(column);
    ax1.set_ylabel('Number');
    ax1.set_title(f'{column}  Distribution');
    
    if(target == None):
        sns.boxplot(y=column, data=df, showmeans=True, ax=ax2);
        ax2.set_ylabel(column);
    else:
        sns.boxplot(x=target, y=column, data=df, showmeans=True, ax=ax2);
        ax2.set_xlabel('Target');
        ax2.set_ylabel(column);
    
    plt.show()

def visualize_numerical_lst(df, numerical = ['age', 'duration', 'campaign', 'pdays', 'previous', 'emp.var.rate',\
                            'cons.price.idx', 'cons.conf.idx', 'euribor3m', 'nr.employed'], target = None):
    for column in numerical:
        visualize_numerical(df,column, target)
        print();
visualize_numerical_lst(rfm_df,["recency","frequency","monetary"])
## Thay thế Density của các trường hợp >quantile95 bằng chính nó
q95 = rfm_df["monetary"].quantile(0.995)
rfm_df["monetary"]=rfm_df["monetary"].map(lambda x: q95 if x>q95 else x)

q95 = rfm_df["frequency"].quantile(0.995)
rfm_df["frequency"]=rfm_df["frequency"].map(lambda x: q95 if x>q95 else x)
visualize_numerical_lst(rfm_df,["recency","frequency","monetary"])
rfm_df['recency'].describe()
def plot_sse(sse):
#     sse = find_sse(df.loc[:,[col]], max_k = 10) 
    plt.figure()
    plt.plot(list(sse.keys()),list(sse.values()))
    plt.xlabel("Number of clusters")
    plt.show() 

# for Ebowl
def find_sse(df, max_k):
    sse = {}
    for k in range(1, max_k):
        kmeans = KMeans(n_clusters=k, max_iter=1000)
        kmeans.fit(df)
        df['clusters'] = kmeans.labels_
        sse[k] = kmeans.inertia_
    return sse

#function for ordering cluster numbers
def order_cluster(cluster_field_name, target_field_name,df,ascending):
    new_cluster_field_name = 'new_' + cluster_field_name
    df_new = df.groupby(cluster_field_name)[target_field_name].mean().reset_index()
    df_new = df_new.sort_values(by=target_field_name,ascending=ascending).reset_index(drop=True)
    df_new['index'] = df_new.index
    df_final = pd.merge(df,df_new[[cluster_field_name,'index']], on=cluster_field_name)
    df_final = df_final.drop([cluster_field_name],axis=1)
    df_final = df_final.rename(columns={"index":cluster_field_name})
    return df_final

def run_kmean_clustering(df,cluster_cols,k_cluster,k_iter = 1000):
    kmeans = KMeans(n_clusters=k_cluster, max_iter = k_iter )
    kmeans.fit(df[cluster_cols])
    prefix = cluster_cols[0]
    df[str(prefix)+"Cluster"]= kmeans.predict((df[cluster_cols]))
    return df

plt.figure(figsize=(8,4))
sns.displot(rfm_df.recency, kde=False)
plt.figure(figsize=(8,4))
sns.displot(rfm_df.frequency, kde=False)
plt.figure(figsize=(8,4))
sns.displot(rfm_df.monetary, kde=False)
## Elbow Method to determine K
sse = find_sse(rfm_df.loc[:,['recency']], max_k = 10) 
plot_sse(sse)
# ## RecencyCluster_k = 3
k_clus = 3
rfm_df = run_kmean_clustering(rfm_df,["recency"],k_cluster=k_clus)

## Sắp theo thứ tự R càng thấp thì Cluster càng cao
rfm_df = order_cluster('recencyCluster', 'recency',rfm_df,False)
rfm_df.groupby("recencyCluster")['recency'].describe()

# ## see details of each cluster - Recency<jupyter_output><empty_output><jupyter_text>**Recency** chia làm 3 nhóm:
1. Nhóm 0: Hibernate (2-3 tháng)
2. Nhóm 1: Sleep (1-2 tháng)
3. Nhóm 2: Active (< 1 tháng)<jupyter_code>sse = find_sse(rfm_df.loc[:,['frequency']], max_k = 10) 
plot_sse(sse) 
# ## FrequencyCluster_k = 4
k_clus = 4

rfm_df = run_kmean_clustering(rfm_df,["frequency"],k_cluster=k_clus)

## Sắp theo thứ tự R càng thấp thì Cluster càng cao
rfm_df = order_cluster('frequencyCluster', 'frequency',rfm_df,True)
rfm_df['frequency']=round(rfm_df['frequency'],0)
rfm_df.groupby("frequencyCluster")['frequency'].describe()

# ## see details of each cluster - Frequency
sse = find_sse(rfm_df.loc[:,['monetary']], max_k = 10) 
plot_sse(sse) 
k_clus = 3
rfm_df = run_kmean_clustering(rfm_df,["monetary"],k_cluster=k_clus)

## Sắp theo thứ tự M càng cao thì Cluster càng cao
rfm_df = order_cluster('monetaryCluster', 'monetary',rfm_df,True)
rfm_df.groupby("monetaryCluster")['monetary'].describe()
def frequency_score(x):
    if x['frequency'] >= 14:
        frequency = 4
    elif x['frequency'] < 14 and x['frequency'] >= 6:
        frequency = 3
    elif x['frequency'] < 6 and x['frequency'] >= 2:
        frequency = 2
    else:
        frequency = 1
    return frequency
def recency_score(x):
    if x['recency'] <= 27:
        recency = 3
    elif x['recency'] > 27 and x['recency'] < 76:
        recency = 2
    else:
        recency = 1
    return recency
def monetary_score(x):
    if x['monetary'] >= 380000:
        monetary = 3
    elif x['monetary'] < 380000 and x['monetary'] >= 110000:
        monetary = 2
    else:
        monetary = 1
    return monetary
rfm_df['R'] = rfm_df.apply(recency_score, axis=1)
rfm_df['F'] = rfm_df.apply(frequency_score, axis=1)
rfm_df['M'] = rfm_df.apply(monetary_score, axis=1)
rfm_df.head()
rfm_df.to_csv('rfm_output.csv', encoding='utf-8', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14766134880348078, 'fraction_numerical': 0.014412617839013778, 'mean_word_length': 4.896846606092998, 'pattern_counts': {'":': 1, '<': 27, '<?xml version=': 0, '>': 30, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '543863', 'license': 'MIT', 'n_tokens_mistral': 4139, 'n_tokens_neox': 4010, 'n_words': 846, 'path': 'SCALA/RFM Clustering.ipynb', 'repo_name': 'interphuoc/atom-assignments', 'size': '11032'}