1
\$\begingroup\$

Currently, my code works perfectly well but i would like to make it cleaner for other users by removing the duplicate and similar lines of code into functions or for loops. Because I am still new learning Python, I still did not get a hang of functions and for loops. My data frame rfm includes 5 columns:

  • Max Date (latest transaction)
  • Id (unique identifier)
  • Recency (today's date minus Latest Transaction Date)
  • Frequency (total # of transactions per Id since its subscription)
  • Monetary (total amount of $ spent by Id since its subscription)

Separating the main data frame into 3 different df because the sort differs for each cumulative sum column. Frequency and Monetary dfs have identical calculations:

rfm_recency = rfm[['Max_Date', 'Id', 'Member_id', 'Recency']].copy()
rfm_recency = rfm_recency.sort_values(['Recency'], ascending=True)

rfm_frequency = rfm[['Id', 'Member_id', 'Frequency']].copy()
rfm_frequency = rfm_frequency.sort_values(['Frequency'], ascending=False)
rfm_frequency['cum_sum'] = rfm_frequency['Frequency'].cumsum()
rfm_frequency['cum_sum_perc'] = rfm_frequency['cum_sum'] / rfm_frequency['Frequency'].sum()

rfm_monetary = rfm[['Id', 'Member_id', 'Monetary']].copy()
rfm_monetary = rfm_monetary.sort_values(['Monetary'], ascending=False)
rfm_monetary['cum_sum'] = rfm_monetary['Monetary'].cumsum()
rfm_monetary['cum_sum_perc'] = rfm_monetary['cum_sum'] / rfm_monetary['Monetary'].sum()

def scorefm(x):
    """Function for separating data into 5 bins for Frequency & Monetary df """
    if x <= 0.20:
        return 5
    elif x <= 0.40:
        return 4
    elif x <= 0.60:
        return 3
    elif x <= 0.80:
        return 2
    else:
        return 1


# Divide the Recency df into equal quantiles
rfm_recency['r_score'] = 5 - pd.qcut(rfm_recency['Recency'], q=5, labels=False)

# Create scores from cum_sum_perc for Frequency and Monetary
rfm_frequency['f_score'] = rfm_frequency['cum_sum_perc'].apply(scorefm)
rfm_monetary['m_score'] = rfm_monetary['cum_sum_perc'].apply(scorefm)

# Resorting data frames by ID to merge
rfm_recency = rfm_recency.sort_values('Id')
rfm_frequency = rfm_frequency.sort_values('Id')
rfm_monetary = rfm_monetary.sort_values('Id')

# Merging data frames together
result = rfm_recency.copy(['Recency', 'r_score'])
result = result.join(rfm_frequency[['Frequency', 'f_score']])
result = result.join(rfm_monetary[['Monetary', 'm_score']])

# Create an FM and RFM score based on the individual R, F, M scores.
result['FM'] = (result['f_score'] + result['m_score']) / 2
result['RFM_Score'] = result['r_score'] * 10 + result['FM']

Fiddle Data:

import pandas as pd

col_names = ['Max_Date', 'Id', 'Member_id', 'Recency', 'Frequency', 'Monetary'] # 6 columns
data =[['2019-01-21',456,'dwfv84',23,261,4221],
['2019-02-10',123,'qwbe78',3,83,9251],
['2019-01-25',789,'adqw87',19,478,19195],
['2018-01-04',988,'afdi25',40,321,3753],
['2018-03-19',784,'asdf48',331,413,8551],
['2018-04-15',445,'asfv41',304,246,10215],
['2018-04-10',589,'sdqw88',309,80,19569],
['2018-05-20',741,'dsdg46',269,282,3108],
['2018-06-30',852,'cvgo87',228,261,5975],
['2019-01-19',963,'ewgs45',25,357,4405],
['2019-01-12',369,'fbbr54',32,197,1019],
['2019-01-18',258,'fwgs77',26,132,18100],
['2019-02-10',147,'jkyu87',3,32,8678],
['2019-02-05',753,'yukh20',8,132,19871]]

rfm = pd.DataFrame(data=data, columns=col_names,)
rfm ['Max_Date'] = pd.to_datetime(rfm ['Max_Date'])
\$\endgroup\$

closed as off-topic by IEatBagels, Sᴀᴍ Onᴇᴌᴀ, Donald.McLean, vnp, Toby Speight Feb 15 at 9:00

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – IEatBagels, Sᴀᴍ Onᴇᴌᴀ, Donald.McLean, vnp, Toby Speight
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    \$\begingroup\$ What is type of rfm? Could you provide a sample for initialization this variable? \$\endgroup\$ – belkka Feb 6 at 8:05
  • 1
    \$\begingroup\$ What does single row mean? Is 'Id' unique identifier of row or user? What is meaning of cumsum() for frequency and monetary? \$\endgroup\$ – belkka Feb 6 at 8:40
  • 1
    \$\begingroup\$ 1. rfm is <class 'pandas.core.frame.DataFrame'> 2. cumsum() helps me get the pareto principle (20-80). Basically 20% of Ids will account for 80% of revenue. So I am sorting Frequency and Monetary DESC to get the highest values first. All the values under 0.2 will be in bin 5 which represents the best bin. Ids are what defines a user so yes its unique in the sense that there isn't more than one in the the df. \$\endgroup\$ – Roger Steinberg Feb 6 at 15:39
  • 2
    \$\begingroup\$ If you want help, it might help to copy a small set of dummy data (in the form of csv or so), so we can test it with actual data \$\endgroup\$ – Maarten Fabré Feb 12 at 12:59
  • 1
    \$\begingroup\$ @belkka updated my question with sample data \$\endgroup\$ – Roger Steinberg Feb 13 at 20:52
2
+50
\$\begingroup\$

This can be done a lot simpler.

Since all of rfm returns in result, you can do a copy. To make sure that even if you reorder things, they appear at the correct place, you use an index. Since Id is unique in the DataFrame, let's use this

result2 = datas.set_index("Id")

r_score

There is no use for the intermediate rfm_recency, or it's sorting, so including the r_score can be as simple as:

result2["r_score"] = 5 - pd.qcut(result2["Recency"], q=5, labels=False)

Doing this immediately in this result2 DataFrame instead of joining several intermediary DataFrames together.

f_score and m_score

The f_score and m_score are both linear interpolations, so can be done with this formula, instead of applying the scorefm method on each row individually

def fm_score(series):
    return (
        6 - series.sort_values(ascending=False).cumsum() / series.sum() * 5
    ).astype(int)

result2["f_score"] = fm_score(result2["Frequency"])
result2["m_score"] = fm_score(result2["Monetary"])

FM and RFM_Score

These are simple calculations, so can be done like this:

result2["FM"] = (result2["f_score"] + result2["m_score"]) / 2
result2["RFM_Score"] = result2["r_score"] * 10 + result2["FM"]

This can be all you need. If the shape needs to be exactly as the original result, a reset_index, sort and reindex can help:

result2 = result2.sort_index().reset_index().reindex(columns=result.columns)

where instead of result.columns you manually give a list of the columns in order

    Max_Date    Id  Member_id   Recency r_score Frequency   f_score Monetary    m_score FM  RFM_Score
0   2019-02-10  123 qwbe78  3   5   83  1   9251    2   1.5 51.5
1   2019-02-10  147 jkyu87  3   5   32  1   8678    2   1.5 51.5
2   2019-01-18  258 fwgs77  26  3   132 1   18100   3   2.0 32.0
3   2019-01-12  369 fbbr54  32  3   197 1   1019    1   1.0 31.0
4   2018-04-15  445 asfv41  304 1   246 2   10215   2   2.0 12.0
5   2019-01-21  456 dwfv84  23  4   261 2   4221    1   1.5 41.5
6   2018-04-10  589 sdqw88  309 1   80  1   19569   4   2.5 12.5
7   2018-05-20  741 dsdg46  269 2   282 3   3108    1   2.0 22.0
8   2019-02-05  753 yukh20  8   5   132 1   19871   5   3.0 53.0
9   2018-03-19  784 asdf48  331 1   413 4   8551    1   2.5 12.5
10  2019-01-25  789 adqw87  19  4   478 5   19195   3   4.0 44.0
11  2018-06-30  852 cvgo87  228 2   261 2   5975    1   1.5 21.5
12  2019-01-19  963 ewgs45  25  4   357 4   4405    1   2.5 42.5
13  2018-01-04  988 afdi25  40  2   321 3   3753    1   2.0 22.0
\$\endgroup\$
  • \$\begingroup\$ It is probably better to move expression (6 - 5 * column.sort_values(ascending=False).cumsum() / column.sum()).astype(int) into separate function. Shouldn't something like "floor" be applied before .astype(int)? \$\endgroup\$ – belkka Feb 14 at 11:10
  • \$\begingroup\$ the .astype(int) drops everything behind the ., so it does the flooring there. I checked and this gives the same results as the OP's code \$\endgroup\$ – Maarten Fabré Feb 14 at 12:16
  • \$\begingroup\$ @MaartenFabré Do you mind explaining how the following works: return function is substracted by 6 and multiplied by 5. Greatly appreciated thank you \$\endgroup\$ – Roger Steinberg Feb 18 at 1:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.