记录Pandas及透视功能的代码

最近一直使用pandas的一些功能，这个代码中有自己用到的非常实用的功能，记录备份。
1）pandas 打开excel表时，是需要真正符合xlsx格式的。实际我们碰上的excel表格，很多的格式预先并不知道。xls2xlsx方法就是利用win32的功能将不同格式进行转换。
2）pd.DataFrame 的使用，与数据清洗。
3）这是测试程序，实际代码精简并修改，思路不变。

# -*- coding: utf-8 -*-
"""
Created on Tue Apr 16 09:17:39 2019
反洗钱数据分析透视图集合
@author: yuce_hz
"""

import os
import time
import pandas as pd
import win32com.client as win32

def xls2xlsx(fname):
    #import win32com.client as win32
    #fname='C:\\Users\\yuce_hz.OA\\Desktop\\爬虫\\20190415T47.xls'
    outfname=os.getcwd()+"\\dldata.xlsx"
    excel=win32.gencache.EnsureDispatch('Excel.Application')
    excel.DisplayAlerts=0   #不显示警告,SaveAS的弹框就不出来了。
    wb=excel.Workbooks.Open(fname)
    wb.SaveAs(outfname,FileFormat=51)    #FileFormat=56,pandas也可以读取
    wb.Close()
    excel.Application.Quit()
    os.remove(fname)
    return outfname


        
def pivot(fname):
    """
    fname=dlpath+time.strftime("%Y%m%d")+"T47_tran.xls" 
    下载的文件
    一般给10秒时间，在调用该方法前就可以下载到 fname
    """
    while not (os.path.isfile(fname)):
        time.sleep(2)    #如果文件没有下载完成，给时间等
     
    plst=[]
    if os.access(fname,os.R_OK):     #判断该文件可以读了
        outfname=xls2xlsx(fname)  #转成xlsx格式
        df=pd.read_excel(outfname,skiprows=1)   #读入下载的
        df=df.drop(["系统账户","客户账号","流水号","Unnamed: 1","核心交易码","科目号"],axis=1)   #在列上去掉 axis=1,在行上去掉是axis=0
        df=df.reindex(columns=df.columns.tolist()+['时间2']+['cnt'],fill_value=1)
        df['时间2']=df['交易时间'].map(lambda x :x[11:13])
        df['交易金额']=df['交易金额'].map(lambda x : float(x.replace(',','')))
        df['对方客户号']=df['对方客户号'].map(lambda x: str(x))
        df['对方账号']=df['对方账号'].map(lambda x : str(x))
        df.fillna({'对方户名':'无'},inplace=True,axis=0)   #清洗空数据
        
        pivotDCsum=pd.pivot_table(df,index=["借贷标志"],values=["交易金额"],aggfunc='sum')  #.sort_values(by="借贷标志",ascending=False)   #借贷求和
        drsum=pivotDCsum['交易金额']['借']
        crsum=pivotDCsum['交易金额']['贷']
        sum_str='分析期内借、贷交易金额为:{0:.2f}万元、{1:.2f}万元，'.format(drsum/10000,crsum/10000)
        
        pivotAdd=pd.pivot_table(df,index=["交易去向"],values=["交易金额"],aggfunc='sum').sort_values(by="交易金额",ascending=False)    #去向地
        lens,col=pivotAdd.shape   #返回是一个元组(41,2)=(行，列)
        bb=['{0}{1:.2f}万元'.format(pivotAdd.index[i],pivotAdd.iloc[i,0]/10000) for i in range(lens if lens<=6 else 6)]
        sum_str +='交易总额{0:.2f}万元，去向为：'.format((drsum+crsum)/10000)
        for aa in bb:
            sum_str += (aa+'、')
                
        pivotUse=pd.pivot_table(df,index=["用途"],values=["交易金额"],aggfunc='sum').sort_values(by="交易金额",ascending=False)    #用途
        lens,col=pivotUse.shape   #返回是一个元组(41,2)=(行，列)
        bb=['{0}{1:.2f}万元占{2:.2f}%'.format(pivotUse.index[i],pivotUse.iloc[i,0]/10000,pivotUse.iloc[i,0]*100/(drsum+crsum)) for i in range(lens if lens<=6 else 6)]
        sum_str=sum_str.rstrip("、")+';用途主要分布为：'
        for aa in bb:
            sum_str += (aa+'、')
        sum_str=sum_str.rstrip("、")+"。"
        plst.append(sum_str)  #把交易金额相关串,用途梳理一下
        
        pivotDCcnt=pd.pivot_table(df,index=["借贷标志"],values=["cnt"],aggfunc='count')  #.sort_values(by="借贷标志",ascending=False)    #借贷计数
        drcnt=pivotDCcnt['cnt']['借']
        crcnt=pivotDCcnt['cnt']['贷']
        cnt_str='分析期内借、贷交易笔数为:{0:d}笔、{1:d}笔，'.format(drcnt,crcnt)
                
        pivotDate=pd.pivot_table(df,index=["交易日期"],values=["cnt"],aggfunc='count').sort_values(by="cnt",ascending=False)    #峰值交易日
        lens,col=pivotDate.shape   #返回是一个元组(41,2)=(行，列)
        bb=['{}共{}笔'.format(pivotDate.index[i],pivotDate.iloc[i,0]) for i in range(lens if lens<=6 else 6)]
        cnt_str +='交易笔数共计{}笔，峰值日期集中在：'.format(drcnt+crcnt)
        for aa in bb:
            cnt_str += (aa+'、')
               
        pivotTime=pd.pivot_table(df,index=["时间2"],values=["cnt"],aggfunc='count').sort_values(by="cnt",ascending=False)    #峰值交易时间段
        lens,col=pivotTime.shape   #返回是一个元组(41,2)=(行，列)
        bb=['{}点{}笔'.format(pivotTime.index[i],pivotTime.iloc[i,0]) for i in range(lens if lens<=6 else 6)]
        cnt_str=cnt_str.rstrip("、")+';峰值时间段集中在:'
        for aa in bb:
            cnt_str += (aa+'、')
        cnt_str=cnt_str.rstrip("、")+"。"
        plst.append(cnt_str)  #处理完日期、时间段的交易分析
        #print(plst)

        Dr_df=df[df["借贷标志"]=='借']  #过滤借方
        Cr_df=df[df["借贷标志"]=='贷']  #过滤贷方
        print("Dr"*20)
        print(Dr_df.head(3),Dr_df.columns)
        print("cr"*20)
        print(Cr_df.head(3))
        Dr_detail=pd.pivot_table(Dr_df,index=["对方户名"],values=["交易金额","cnt"],aggfunc="sum").sort_values(by="交易金额",ascending=False)
        Dr_detail=Dr_detail.reindex(columns=Dr_detail.columns.tolist()+['占比'],fill_value=0)
        Dr_detail['占比']=Dr_detail['交易金额'].map(lambda x :x*100/drsum)
        lens,col=Dr_detail.shape   #返回是一个元组(41,2)=(行，列)
        DC_rs='交易对手借方：{}人'.format(lens)
        DC_str='借方主要交易对手有：'
        bb=['{0} {1:.2f}万元 占{2:.2f}% {3:d}笔'.format(Dr_detail.index[i],Dr_detail.iloc[i,1]/10000,Dr_detail.iloc[i,2],Dr_detail.iloc[i,0]) for i in range(lens if lens<=6 else 6)]
        for aa in bb:
            DC_str += (aa+'、')
        Cr_detail=pd.pivot_table(Cr_df,index=["对方户名"],values=["交易金额","cnt"],aggfunc="sum").sort_values(by="交易金额",ascending=False)
        Cr_detail=Cr_detail.reindex(columns=Cr_detail.columns.tolist()+['占比'],fill_value=0)
        Cr_detail['占比']=Cr_detail['交易金额'].map(lambda x :x*100/crsum)
        lens,col=Cr_detail.shape   #返回是一个元组(41,2)=(行，列)
        DC_rs +='，贷方：{}人'.format(lens)
        DC_str=DC_str.rstrip("、")+';贷方主要交易对手有：'
        bb=['{0} {1:.2f}万元 占{2:.2f}% {3:d}笔'.format(Cr_detail.index[i],Cr_detail.iloc[i,1]/10000,Cr_detail.iloc[i,2],Cr_detail.iloc[i,0]) for i in range(lens if lens<=6 else 6)]
        for aa in bb:
            DC_str += (aa+'、')
        DC_str=DC_str.rstrip("、")+"。"
        plst.append(DC_str)
        
        cc=set(Dr_detail.index) & set(Cr_detail.index)  #借贷重合人列表
        DC_rs +='，借贷重合：{}人。'.format(len(cc))
        plst.append(DC_rs)
        #把做好的视图保存一下
        da=[list(Dr_detail.index),list(Cr_detail.index),list(set(Dr_detail.index)&set(Cr_detail.index))]
        dp=pd.DataFrame(da).T
        dp.columns=['借方对手','贷方对手','重合对手']
        df2=df.copy()  #COPY df
        with pd.ExcelWriter('dldata.xlsx') as writer:
            df2.to_excel(writer,sheet_name='原始数据',index=False)   #保存原始数据,index=False去掉行索引
            pivotDCsum.to_excel(writer,sheet_name='借贷求和')
            pivotDCcnt.to_excel(writer,sheet_name='借贷计数')
            Dr_detail.to_excel(writer,sheet_name='借明细')
            Cr_detail.to_excel(writer,sheet_name='贷明细')
            pivotDate.to_excel(writer,sheet_name='日期段')
            pivotTime.to_excel(writer,sheet_name='时间段')
            pivotAdd.to_excel(writer,sheet_name='去向')
            pivotUse.to_excel(writer,sheet_name='用途')
            dp.to_excel(writer,sheet_name='借贷重合对手',index=False)
        
        
    return plst


dlpath="D:\\yuce\\"
fname=dlpath+time.strftime("%Y%m%d")+"T47_tran.xls"
print(fname)
aa_str=pivot(fname)
print(aa_str,type(aa_str))