Last active
November 16, 2021 14:57
-
-
Save 1vth1nk3r/5a50fef2e56684af9200786a0e5a0064 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # copying a part of userinfo, filling empty churn_date with the year end, getting months | |
| users2 = users[['user_id','reg_date','churn_date']].copy() | |
| users2['churn_date'] = users2['churn_date'].fillna(pd.to_datetime('2018-12-31', format='%Y-%m-%d') ) | |
| users2['reg_month'] = users2['reg_date'].dt.month | |
| users2['churn_month'] = users2['churn_date'].dt.month | |
| # black magic. making rows with months when the user was subscribed | |
| # 0 1 .. 12 | |
| # true false user_id | |
| def makemonths(row): | |
| months = [True if i in range(row['reg_month'], row['churn_month']+1) else False for i in range(1,12+1)] | |
| months.append(row['user_id']) | |
| return pd.Series(months, index=[list(range(1,14))]) | |
| user_months2 = users2.apply(makemonths, axis=1) # applying this black magic rowwise to users | |
| user_months2.columns = ['1','2','3','4','5','6','7','8','9','10','11','12','user_id'] # changing column names | |
| # converting from wide to long view | |
| user_months3 = user_months2.melt(id_vars=['user_id']) | |
| # deleting rows where False - these are the months without subscription | |
| user_months3 = user_months3[user_months3['value'] == True].reset_index(drop=True) | |
| # getting rid of boolean column and sorting | |
| user_months3 = user_months3[['user_id', 'variable']].sort_values(['user_id','variable']) | |
| # changing column names for future merge on months | |
| user_months3.columns = ['user_id', 'month'] | |
| # on user #491 we could see how we've got only the necessary rows | |
| display(users[users['user_id'] == 1491]) | |
| user_months3[user_months3['user_id'] == 1491] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment