python - Cumulative Churn rate calculation using Date of a month - Date Issue is major

Question

I need to calculate Daily Churn Rate on cumulative basis but after trying multiple methods for using groupby dates method. I am still unable to do so.

I have already tried multiple things to change the date. I tried this to get the days so that I can use only the day of the date but it doesn't work.

df['Day'] = df['Game_Play_Date'].apply(lambda x: x.days)

error is :- 'Timestamp' object has no attribute 'days' even after using pd.to_datetime method and using dt.days accessor as well.

df_ch = df.groupby('Game_Play_DayofMonth')[['pid1', 'pid2']]

this gives the value of dates in numeric 61882, 122654. like this.

I have data something like this with addtional columns

Date        PID1    PID2
01-06-19    xa  xs
01-06-19    xb  xa
01-06-19    xc  xv
02-06-19    xd  xb
02-06-19    xe  xr
02-06-19    xf  xe

I need result something like this so that I can see returning players count as well.

Date            ID
01-06-19    xa
01-06-19    xb
01-06-19    xc
01-06-19    xs
01-06-19    xv
02-06-19    xd
02-06-19    xe
02-06-19    xf
02-06-19    xr
02-06-19    xb

标签： pythonpython-3.xpandasdatetimepandas-groupby

Answer 1

Use pd.melt:

print(pd.melt(df, 'Date').iloc[:, [0, 2]].sort_values(by='Date').drop_duplicates().reset_index(drop=True))

Or use pd.DataFrame.melt:

print(df.melt('Date').iloc[:, [0, 2]].sort_values(by='Date').drop_duplicates().reset_index(drop=True))

Both output:

       Date value
0  01-06-19    xa
1  01-06-19    xb
2  01-06-19    xc
3  01-06-19    xs
4  01-06-19    xv
5  02-06-19    xd
6  02-06-19    xe
7  02-06-19    xf
8  02-06-19    xb
9  02-06-19    xr