数据统计流程

数据统计流程
1、

#总的流程
#1、读取数据
df1=pd.read_excel(r'E:\anju\待审核.xlsx')
df2=pd.read_excel(r'E:\anju\待放款.xlsx')
df3=pd.read_excel(r'E:\anju\已放款.xlsx')
df4=pd.read_excel(r'E:\anju\拒贷.xlsx')
df5=pd.read_excel(r'E:\anju\补充资料.xlsx')
res=df1.append([df2,df3,df4,df5],ignore_index=False)
res=res[res["项目地址"].str.contains('^贵州省')]
res

#订单状态
order_status_sq=['已提交 ','已批核','已放款','拒贷','补充资料']
order_status_sh=['已批核','已放款']
order_status_fk=['已放款']
pattern1='^2019-10-15'
pattern2='^2019-10'

#申请审核数据处理
def use_data1(df,order_status,pattern):
    df = df[df['受理日期'].str.contains(pattern)]
    df= df[df["订单状态"].isin(order_status)]
    ll = df[['申请编号', '申请金额', '项目名称']].groupby(['项目名称']).agg({'申请金额': 'sum', '申请编号': 'count'})
    return ll

#累计申请审核数据处理
def use_data11(df,order_status):
    df= df[df["订单状态"].isin(order_status)]
    ll = df[['申请编号', '申请金额', '项目名称']].groupby(['项目名称']).agg({'申请金额': 'sum', '申请编号': 'count'})
    return ll

#放款数据
def use_data2(df,order_status,pattern):
    df = df[df['放款日期'].str.contains(pattern)]
    df= df[df["订单状态"].isin(order_status)]
    ll = df[['申请编号', '申请金额', '项目名称']].groupby(['项目名称']).agg({'申请金额': 'sum', '申请编号': 'count'})
    return ll

#累计放款数据处理
def use_data22(df,order_status):
    df= df[(df["订单状态"].isin(order_status))&(df['放款日期'].notnull())]
    ll = df[['申请编号', '申请金额', '项目名称']].groupby(['项目名称']).agg({'申请金额': 'sum', '申请编号': 'count'})
    return ll

#生成的结果重新构造一个DataFrame
def create_dataframe_1(df):
    df_1=pd.DataFrame()
    index=df.index.tolist()
    df_1["今日申请"]=df['申请编号'].tolist()
    df_1["今日申请金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_2(df):
    df_1=pd.DataFrame()
    index=df.index.tolist()
    df_1["今日审批"]=df['申请编号'].tolist()
    df_1["今日审批金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_3(df):
    index=df.index.tolist()
    df_1=pd.DataFrame()
    df_1["今日放款"]=df['申请编号'].tolist()
    df_1["今日放款金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_11(df):
    df_1=pd.DataFrame()
    index=df.index.tolist()
    df_1["本月申请"]=df['申请编号'].tolist()
    df_1["本月申请金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_21(df):
    df_1=pd.DataFrame()
    index=df.index.tolist()
    df_1["本月审批"]=df['申请编号'].tolist()
    df_1["本月审批金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_31(df):
    index=df.index.tolist()
    df_1=pd.DataFrame()
    df_1["本月放款"]=df['申请编号'].tolist()
    df_1["本月放款金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_12(df):
    df_1=pd.DataFrame()
    index=df.index.tolist()
    df_1["累计申请"]=df['申请编号'].tolist()
    df_1["累计申请金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_22(df):
    df_1=pd.DataFrame()
    index=df.index.tolist()
    df_1["累计审批"]=df['申请编号'].tolist()
    df_1["累计审批金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1
def create_dataframe_32(df):
    index=df.index.tolist()
    df_1=pd.DataFrame()
    df_1["累计放款"]=df['申请编号'].tolist()
    df_1["累计放款金额"]=df['申请金额'].tolist()
    df_1["项目"]=index
    return df_1

#今日申请数量及金额
#今日审核数量及金额
#今日回款数量及金额
def daily():
    sq=use_data1(df=res,order_status=order_status_sq,pattern=pattern1)
    sq=create_dataframe_1(sq)
    sh=use_data1(df=res,order_status=order_status_sh,pattern=pattern1)
    sh=create_dataframe_2(sh)
    #df需要改成df3
    fk=use_data2(df=df3,order_status=order_status_fk,pattern=pattern1)
    fk=create_dataframe_3(fk)
    return sq,sh,fk

def month():
    sq=use_data1(df=res,order_status=order_status_sq,pattern=pattern2)
    sq=create_dataframe_11(sq)
    sh=use_data1(df=res,order_status=order_status_sh,pattern=pattern2)
    sh=create_dataframe_21(sh)
    #df需要改成df3
    fk=use_data2(df=df3,order_status=order_status_fk,pattern=pattern2)
    fk=create_dataframe_31(fk)
    return sq,sh,fk

def consum():
    sq = use_data11(df=res, order_status=order_status_sq)
    sq=create_dataframe_12(sq)
    sh = use_data11(df=res, order_status=order_status_sh)
    sh=create_dataframe_22(sh)
    # df需要改成df3
    fk = use_data22(df=df3, order_status=order_status_fk)
    fk=create_dataframe_32(fk)
    return sq, sh, fk

#原生表
yb=pd.read_excel(r'C:\Users\kyrie\Downloads\碧桂园贵州区域安居分期每日回款情况跟进表(1).xlsx')
yb

#今日数据生成表
sq,sh,fk=daily()
# sq,sh,fk=month()
# sq,sh,fk=consum()
for k in [sq,sh,fk]:
    for i in yb["项目"]:
        for j in sq["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'今日申请']=sq[(sq['项目']==j)]['今日申请'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'今日申请金额']=sq[(sq['项目']==j)]['今日申请金额'].tolist()[0]/10000
        for j in sh["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'今日审批']=sh[(sh['项目']==j)]['今日审批'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'今日审批金额']=sh[(sh['项目']==j)]['今日审批金额'].tolist()[0]/10000
        for j in fk["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'今日放款']=fk[(fk['项目']==j)]['今日放款'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'今日放款金额']=fk[(fk['项目']==j)]['今日放款金额'].tolist()[0]/10000
yb

#本月数据生成表
sq,sh,fk=month()
for k in [sq,sh,fk]:
    for i in yb["项目"]:
        for j in sq["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'本月申请']=sq[(sq['项目']==j)]['本月申请'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'本月申请金额']=sq[(sq['项目']==j)]['本月申请金额'].tolist()[0]/10000
        for j in sh["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'本月审批']=sh[(sh['项目']==j)]['本月审批'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'本月审批金额']=sh[(sh['项目']==j)]['本月审批金额'].tolist()[0]/10000
        for j in fk["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'本月放款']=fk[(fk['项目']==j)]['本月放款'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'本月放款金额']=fk[(fk['项目']==j)]['本月放款金额'].tolist()[0]/10000
yb

#累计生成表
sq,sh,fk=consum()
sq
for k in [sq,sh,fk]:
    for i in yb["项目"]:
        for j in sq["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'累计申请']=sq[(sq['项目']==j)]['累计申请'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'累计申请金额']=sq[(sq['项目']==j)]['累计申请金额'].tolist()[0]/10000
        for j in sh["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'累计审批']=sh[(sh['项目']==j)]['累计审批'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'累计审批金额']=sh[(sh['项目']==j)]['累计审批金额'].tolist()[0]/10000
        for j in fk["项目"]:
            if i==j:
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'累计放款']=fk[(fk['项目']==j)]['累计放款'].tolist()[0]
                yb.loc[yb[yb['项目']==j].index.tolist()[0],'累计放款金额']=fk[(fk['项目']==j)]['累计放款金额'].tolist()[0]/10000
yb

yb.to_excel('llll.xlsx')