pandas系列 - (三)关于时点时期数据的处理
实际工作场景中,会遇到需要处理时序表。对于少量的时点时序数据,明细数据+数据透视表,也是很快能处理完成。大量的话,可能会出现有一点慢,同时一些计算字段的每次都要设置,不太方便处理。整理一个思路:将系统的时点时序数据进行汇总整合,并形成时序表。
思路:结构化的数据是很方便处理,表格类的数据不方便程序处理,但是方便计算字段。所有思路是,
将制定指标归并,形成数据数据透视表,再通过列运算形成计算字段,再转回明细数据,最终根据自己
的需要进行处理。
1、数据源读取;
2、数据指标归并,将A1、A2指标,归并为A,归并的参照表以EXCEL的形式储存;
3、数据汇总,用于原始数据是单个地方数据,比如通过汇总关系,汇总出华北地区,华南地区数据;
4、增加计算字段,比如:原始数据中有销售额、销售人数,通过参照表:形成 人均销售额 = 销售额/销售人数。
5、增加相对数据,一般时点数据都是当期值,用于分析的化,还需要知道“比上期”、“比年初”、“同比”等值、
主函数:
%%time # 获取源数据 df = get_src_data(r'data/input/20200930 zonghe3/' ) date_format = "%Y%m%d" stack_drop = False # 不删除指标为空的指标 # 保留基础数据,储存共有多少机构产品 df_base = df.loc[:,['机构名称','产品品种','数据管理机构']].drop_duplicates(subset =['机构名称','产品品种'],keep='first') # 数据预处理 df = pre_handle_data(df) # 读取参照表,参照表存放指标的归并关系,以及计算字段的公式 dfcz = pd.read_excel(r'data/input/cz-zgbgst.xlsx',dtype=object,sheet_name=0) df['指标名称'] = df['行指标名称'] + df['列指标名称'] # 数据归并 df = reduce_data(df) # 数据汇总 df = hz_data(df) # 计算字段,df是处理过后的原始数据源 df = calcu_data(df) # 在原始数据源的基础上,计算出相对数据 df_deal = calcu_relative_data(df)
1、数据源读取:
# 获取数据源 def get_src_data(folder_name): file_list = os.listdir(folder_name) ldf = [] # 先储存在list,再concat比较高效率 if len(file_list) > 0 : # 遍历文件夹下所有文件 for i in range(len(file_list)): # 如果是excel择用这个,如果是csv择用另一个 ldf.append(pd.read_excel(folder_name + str(file_list[i]),dtype=object)) return pd.concat(ldf,ignore_index=True) else: return None
2、归并数据,通过将相关指标替换成对应指标
# 归并数据 def reduce_data(df): # 筛选字段 # 选择需要的数 df = df[df['指标名称'].isin(dfcz.loc[dfcz['是否筛选'] == 1,'指标名称'].values.tolist())].copy() # 内容转换 df['指标名称'] = df['指标名称'].replace(dfcz['指标名称'].values.tolist(),dfcz['对应'].values.tolist()) df.drop(columns=['行指标名称', '列指标名称', '数据表名称','机构产品标识'],inplace=True) return df
3、增加计算字段,遍历参照表中的计算字段名,以及对应公式,使用df.eval进行计算。
# 计算字段,通过现有指标,计算出新的指标 def calcu_data(df): # 补充没有的列名,形成差集,补充新的列,这里是为了避免最后计算时造成的误差 dft = dfcz[(dfcz['对应'].notnull())].drop_duplicates(subset =['对应'],keep='first') for dyl in dft['对应'].values.tolist(): if not dyl in list(df.columns): df[dyl] = 0.0 # 根据计算过程,得出计算字段 df.fillna(0,inplace = True) # 填0,防止影响后面计算 zbmcdf = dfcz[(dfcz['计算字段'].notnull()) & dfcz['计算过程'].notnull()].drop_duplicates(subset =['计算字段'],keep='first') for i,row in zbmcdf.iterrows(): df[ str(row['计算字段'])] = df.eval(str(row['计算过程'])) #将占比的列补充一个(%) dname = {} for c in df.columns: if str(c).find('占比') != -1 or str(c).find('率') != -1 or str(c).find('比率') != -1 or str(c).find('净值') != -1: if str(c).find('%') == -1: dname[str(c)] = str(c) + '(%)' if len(dname) > 0 : df.rename(columns=dname,inplace = True) # 将数据打散为明细 dfout = df.set_index(['数据日期', '产品品种', '机构名称']) dfout.columns.names = ['指标名称'] dfout[dfout == 0] = np.nan dfout.dropna(axis=1,how='all',inplace=True) # 删除空列,减少后面的计算 dfout = dfout.stack(dropna = True).reset_index() # 这里将新形成的指标转置,如果是空的话,择不保留。 dfout.rename(columns={0:'00 当期值'},inplace = True) return dfout
返回数据类型:
4、增加相对数据,使用apply逐行增加比上期,比年初,同比增速,同比增减数据。
# 增加相对计算字段 def calcu_relative_data(df): # 计算相对数 calcu_relative_data # 生成日期范围列表 date_list = list(set(df['数据日期'].values.tolist())) # 构建唯一索引 df['unique'] = df['数据日期'] + ' ' + df['产品品种'] + ' ' + df['机构名称']+ ' ' + df['指标名称'] dftest = df.set_index('unique',drop=False) df.drop(columns=['unique'],inplace=True) dftest.fillna(0,inplace = True) # 填0,防止影响后面计算 # 计算前可以考虑做好,缺失值转换为0 dftest['10 比上期'] = dftest.apply(add_huanbi,axis=1,args=(dftest,date_list,'数据日期','unique','00 当期值')) dftest['11 比上期-同比增减'] = dftest.apply(add_huanbi_onyear,axis=1,args=(dftest,date_list,'数据日期','unique','00 当期值')) dftest['20 比年初'] = dftest.apply(add_binianchu,axis=1,args=(dftest,date_list,'数据日期','unique','00 当期值')) dftest['21 比年初-同比增减'] = dftest.apply(add_binianchu_onyear,axis=1,args=(dftest,date_list,'数据日期','unique','00 当期值')) dsel = dftest['指标名称'].str.contains('%') dftest.loc[~dsel,'30 同比增速'] = dftest[~dsel].apply(add_tongbizengsu,axis=1,args=(dftest,date_list,'数据日期','unique','00 当期值')) # 根据指标名称,包含%只需要增加同比增减,不包含的化计算增速 dftest.loc[dsel,'31 同比增减'] = dftest[dsel].apply(add_tongbi,axis=1,args=(dftest,date_list,'数据日期','unique','00 当期值')) dftest = dftest.reset_index(drop=True) dftest.drop(columns=['unique'],inplace=True) dftest = dftest.set_index(['数据日期', '产品品种', '机构名称', '指标名称']) dftest.columns.names = ['数据类型'] # 这里可以考虑设置 dropna = False ,这样的话,就可以保持空值存在。 dftest[dftest == 0] = np.nan dftest = dftest.stack(dropna = True).reset_index() # 将数据转化为台账类型 dftest.rename(columns={0:'数据值'},inplace = True) #这里把不需要的删除 if stack_drop == False: dftest.drop(index=dftest[ (~(dftest['指标名称'].str.contains('%'))) & (dftest['数据类型'] == '31 同比增减') ].index,inplace=True) dftest.drop(index=dftest[ ((dftest['指标名称'].str.contains('%'))) & (dftest['数据类型'] == '30 同比增速') ].index,inplace=True) return dftest
最终输出样式是:
数据日期 产品品种 机构名称 指标名称 数据类型 数据值
A A A A 当期数 XX
最后,可以通过再处理一次pivot_table数据透视表得到想到的时序数据。后续,只需要修改参照表就可以快速转换成其他数据。