import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
df=pd.read_csv("CDNOW_MASTER.CSV")
df.head()

df.info()

df.describe()

df['DATE']=pd.to_datetime(df['DATE'],format='%Y%m%d')
df['month']=df.DATE.values.astype('datetime64[M]')
df

grouped_month=df.groupby(by='month')
order_month_amount=grouped_month.TOTAL.sum()
order_month_amount.head()

#可视化显示在页面
%matplotlib inline
#更改设计风格
plt.style.use('ggplot')
order_month_amount.plot()

grouped_month.ID.count().plot()

grouped_month.NUM.sum().plot()

grouped_month.ID.apply(lambda x :len(set(x))).plot()

grouped_user=df.groupby('ID')

grouped_user.sum().describe()

grouped_user.sum().query('TOTAL<4000').plot.scatter(x='TOTAL',y='NUM')

grouped_user.sum().query('NUM<100').TOTAL.plot.hist(bins=20)

grouped_user.sum().query('NUM<100').NUM.plot.hist(bins=20)

user_cumsum=grouped_user.sum().sort_values('TOTAL').apply(lambda x :x.cumsum()/x.sum())
user_cumsum.reset_index().TOTAL.plot()

grouped_user.min().DATE.value_counts().plot()

grouped_user.max().DATE.value_counts().plot()

user_life=grouped_user.DATE.agg(['min','max']).reset_index()
user_life.head()

(user_life['min']==user_life['max']).value_counts()

user_life['regDate']=user_life['min'].values.astype('datetime64[M]')
user_life

user_life_month=df.groupby(['month','ID']).count().reset_index()

user_life_month=user_life_month.merge(user_life,on='ID')

user_life_month

user_life_month['isNew']=user_life_month.apply(lambda x:True if x.month==x.regDate else np.NaN,axis=1)

user_life_month_count=user_life_month.groupby('month').count().reset_index()

user_life_month_count

user_life_month_count['isOld']=pd.DataFrame(user_life_month_count['ID']-user_life_month_count['isNew'])
user_life_month_count.drop(columns=['DATE','NUM','TOTAL','min','max','regDate'],inplace=True)

user_life_month_count.rename(columns={'ID':'all'},inplace=True)

user_life_month_count

rfm=df.pivot_table(index='ID',values=['NUM','TOTAL','DATE'],aggfunc={'NUM':'sum','TOTAL':'sum','DATE':'max'})
rfm

rfm['R']=-(rfm['DATE']-rfm['DATE'].max())/np.timedelta64(1,'D')
rfm

rfm.rename(columns={'NUM':'F','TOTAL':'M'},inplace=True)

rfm.drop(columns=['DATE'],inplace=True)
rfm

def rfm_fumc(x):
    level = x.apply(lambda x:'1' if x>=1 else '0')
    label=level.R+level.F+level.M
    d={
        '111':'重要价值客户',
        '011':'重要保持客户',
        '101':'重要发展客户',
        '001':'重要挽留客户',
        '110':'一般价值客户',
        '010':'一般保持客户',
        '100':'一般发展客户',
        '000':'一般挽留客户',
    }
    result=d[label]
    return result

rfm['label']=rfm[['R','F','M']].apply(lambda x: x-x.mean()).apply(rfm_fumc,axis=1)

rfm.loc[rfm.label=='重要价值客户','color']='g'
rfm.loc[~(rfm.label=='重要价值客户'),'color']='r'
rfm.plot.scatter('F','R',c=rfm.color)

rfm.groupby('label').sum()

pivoted_counts=df.pivot_table(index='ID',columns='month',values='DATE',aggfunc='count').fillna(0)
pivoted_counts

df_purchase=pivoted_counts.applymap(lambda x:1 if x>0 else 0)
df_purchase

def active_status(data):
    status=[]
    for i in range(18):
        if data[i]==0:
            if len(status)>0:
                if status[i-1]=='unreg':
                    status.append('unreg')
                else:
                    status.append('unactive')
            else:
                status.append('unreg')
        else:
            if len(status)==0:
                status.append('new')
            else:
                if status[i-1]=='unactive':
                    status.append('return')                             
                elif status[i-1]=='unreg':
                     status.append('new')
                else:
                     status.append('active')
    return status    
    

puchaser_status=df_purchase.apply(active_status,axis=1)

puchaser_status