交易流水指标统计——pandas

1、根据交易流水，计算每个交易交易对象的如下指标，
总收入笔数、总收入月数、最大月收入笔数、最大月收入笔数所在日期（年月日格式）

import pandas as pd
path = r"C:\Users\xxx\Desktop\科技数据.xlsx"

df = pd.read_excel(path)                  #读取excel数据
df['_COL0']= pd.to_datetime(df['_COL0'])  #日期数据格式转换
res = df[df['_COL4 '] == '收入']          #查询收入数据
res.reset_index(inplace=True, drop=True)

res.info() 
res.head() #查看数据

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2134 entries, 0 to 2133
Data columns (total 6 columns):
 #   Column  Non-Null Count  Dtype         
---  ------  --------------  -----         
 0   id      2134 non-null   object        
 1   _COL0   2134 non-null   datetime64[ns]
 2   x       2134 non-null   object        
 3   _COL4   2134 non-null   object        
 4   _col5   2134 non-null   float64       
 5   交易对方    2133 non-null   object        
dtypes: datetime64[ns](1), float64(1), object(4)
memory usage: 100.2+ KB

 # 计算每个交易对象的指标
def max_count_date(x):
    idx = x.groupby(x['_COL0'].dt.month)['_col5'].transform('count').idxmax()
    return  res.iloc[idx,1].strftime('%Y%m%d')

grouped = res.groupby('交易对方')
summary = grouped.agg(总收入笔数=('交易对方', 'count'), 总收入月数=('_COL0',lambda x: x.dt.month.nunique()) )
max_monthly_income = grouped.apply(lambda x: x['_col5'].groupby(x['_COL0'].dt.month).count().max())
summary['最大月收入笔数'] = max_monthly_income.astype('int')
summary['最大月收入笔数所在日期'] = grouped.apply(max_count_date)
summary                  #查看指标结果

502 rows × 4 columns

2、将上面计算的指标匹配到原始数据表中，按交易对象（人名）匹配，原始表新增上面几个指标列。

# 将指标匹配到原始数据表中
df = df.merge(summary, on='交易对方', how='left')
df[df.duplicated('交易对方')].sort_values('交易对方') #查看指标是否正确

2597 rows × 10 columns

3、将交易月份数大于等于2的交易对象的前两个月定义为1，2，交易月份数小于2的不定义。并匹配到原始表中，即，原始表新增一列，first_twoM。

df['first_twoM'] = np.nan
def get_first_two_m(x):
    months = x.dt.month.drop_duplicates()
    if len(months) >=2:
        first_t = months[0:2].values
        arr1 = x.dt.month.values == first_t[0]
        idx1 = np.where(arr1 == True)
        arr2 = x.dt.month.values == first_t[1]
        arr2 = np.where(arr2, 2, np.nan)
        arr2[idx1]  = 1
        return arr2
    
df.sort_values(['交易对方', '_COL0'], inplace=True)
df['first_twoM'] = df.groupby('交易对方')['_COL0'].transform(get_first_two_m)
df        

3283 rows × 12 columns

4、根据交易日期，将连续的月份定义为1，不连续定义为0。例如一个人交易月份
202201、202202、202205、202206 、202208、202210、202211、202302

def transaction_date(x):
    x.sort_values(inplace=True)
    month = x.dt.month.drop_duplicates()
    if len(month) <=1 :
        return           #0 只有一个月份数的可以在这里设置返回零
    dif1 = abs(month.diff(periods=-1))
    dif1 = dict(zip(month.values,dif1.values))
    dif2 = abs(month.diff(periods=1))
    dif2 =dict(zip(month.values,dif2.values))
    transaction_month = set([i for i,j in dif2.items() if j in [1,11]] + [i for i,j in dif1.items() if j in [1,11]])
    return x.dt.month.isin(transaction_month).astype('int')

df['交易月份是否连续'] = df.groupby('交易对方')['_COL0'].transform(transaction_date)
df

3283 rows × 12 columns

• 自定义excel表格式导出

class writer():
    def __init__(self, df: list, sheet_name: list, key: list, path: str):
        self.df = df                 #dataframe数据
        self.sheet_name = sheet_name #sheet名称
        self.key = key               #需要上色字段
        self.path = path             #excel保存地址
    def save(self):
        path = "C:/Users/15028/Desktop/{}{}（导出时间{}）.xlsx".format(pd.Timestamp.now().strftime("%y%m%d"),self.path,pd.Timestamp.now().strftime("%H时%M分%S秒"))
        writer = pd.ExcelWriter(path,engine='xlsxwriter') #创建pandas.ExcelWriter实例，赋值给writer
        for j in range(len(self.sheet_name)):            #遍历sheet名称列表
            #创建sheet表并写入dataframe数据
            self.df[j].to_excel(writer, sheet_name=self.sheet_name[j], index=False #不写入索引index=False
                                ,freeze_panes=(1,2))      #设置固定1、2列（固定列无法左右移动）
            workbook = writer.book                        #工作蒲格式方法
            worksheet = writer.sheets[self.sheet_name[j]] #读取sheet表
            #  计算每列的合适字符宽度，放到元组中
            widths = (self.df[j].astype(str).applymap(lambda x: 40 if len(x)>61 else len(x)+9 if len(x)<9 else len(x)).agg(max).values)
            #设置每列宽度大小
            [worksheet.set_column(i, i, width) for i, width in enumerate(widths)]  #计算的宽度，设置列宽
            color = ['#03A89E','#00C78C','#FFFFCD','#FFC0CB','#808A87','#FFE384','#ED9121','#40E0D0','#FFFFCD']*5 #颜色列表
            (worksheet.set_column(i,i,widths[i],workbook.add_format({'fg_color': color[i-11],
                                                        'valign': 'vcenter',# 垂直对齐方式
                                                        'font_size': 10,    #字体大小
                                                        'border': 4,        #单元格边框宽度
                                                        'align': 'left'    # 水平对齐方式
                                                                    })) for 
            i,x in enumerate(self.df[j].columns) if self.df[j].columns[i] in self.key[0])
            #设置首行宽度 
            worksheet.set_row(0,20,workbook.add_format({'fg_color':'#40E0D0'#背景颜色
                                                        ,'bold': True,#字体加粗
                                                        'valign': 'vcenter',# 垂直对齐方式
                                                        'font_size': 16, #字体大小
                                                        'border': 10, #单元格边框宽度
                                                        'align': 'left' # 水平对齐方式
                                                       }))
#         worksheet.set_column("A:B",None,None,{'hidden':1})  #A至B列，隐藏
        writer.close()
writer1 = writer(df=[df],path='交易数据',key=[df.columns[-6:]],sheet_name=['交易表'])
writer1.save()