分享一个1000块钱的Python私活单，金融Excel数据清洗

分享一个今年一月份接的一个价值1000元的Python数据清洗的金融数据私单，细节确实是接单以来最为复杂的一个案例。废话不多说，给大家分享下整个案例的需求和实现过程。

业务需求

首先是客户提供给我11个表格，这些表格的数据大体相似，但是具体细节每个表格都不一样，因此在具体处理时，需要挨个验证如何实现目标。

最终输出表格样式：

所有的原始数据表格经过处理，都要产出相同的三个表格，格式如下：

表1：

表2：

表3：

代码实现分析

结果表分析：

对于最终输出结果表1，2因为字段固定，因此我的策略就是这两个表格只要在原始数据中提取对应的数据即可，而表3因为数据时灵活多样的，但是它们的共同规律时资产编码均已1103开头，且《单位成本》列不能为0或者空，因此可以通过这两个条件进行定位。

原始数据表分析

以输入1原始数据表为例，表1，2中的汇总数据主要在《科目代码》这列里面，而表3的具体证券名称和金额在《科目名称》和《市值》《市值占比》等数据列。

代码实现

我将所有需要用的数据路径放在了第一个代码块：

#文件路径
final_form = Path(r'D:\Working\私活\数据清洗\21-12-30表格清洗需求\穿透管理输出表v3终版.xlsx')
folder_path =  Path(r'D:\Working\私活\数据清洗\21-12-30表格清洗需求\输入结果')
qiong_form = Path(r'D:\Working\私活\数据清洗\基金穷举v4.xlsx')

由于结果表格式固定，我将它们全部提取出来，并做了相应的预处理，表1，表2保留需要的字段，不需要的数据项全部设置为空值NaN,表3基本只能保留表头：

# 提前读取输出结果的三个表，用于填充数据
df_final1 = pd.read_excel(final_form,header=[0,1]).iloc[:19,:6]
df_final2 = pd.read_excel(final_form,header=[22,23]).iloc[:16,:]
df_final3 = pd.read_excel(final_form,header=[41,42])

然后读取原始数据表,通过pathlib读取文件夹路径下的所有excel表格，然后根据数据开始的行、列将每个原始数据表格限定为一个dataframe对象

#读取各表数据
file_list = folder_path.glob('输入*.xls')
pathlist = list(file_list)
df1 = pd.read_excel(pathlist[2],header=4).iloc[1:,:]
df2 = pd.read_excel(pathlist[3],header=4).iloc[2:,:]
df3 = pd.read_excel(pathlist[4],header=1)
df4 = pd.read_excel(pathlist[5],header=3)
df4 = df4.drop(index=[df4.shape[0]-1,df4.shape[0]-2])
df5 = pd.read_excel(pathlist[6],header=4).iloc[2:,:]
df6 = pd.read_excel(pathlist[7],header=4).iloc[2:,:]
df7 = pd.read_excel(pathlist[8],header=3)
df7 = df7.drop(index=df7.shape[0]-1)
df9 = pd.read_excel(pathlist[9],header=3)
df10 = pd.read_excel(pathlist[0],header=3).iloc[1:,:]
df11 = pd.read_excel(pathlist[1],header=3).iloc[1:,:]

接下来就是根据11个表的共同特征生成4个相同的数据清洗方法，用于处理结果表1，2：
清洗方法1：

def data_wash1(df_form,df_final,ori_col,form_col,form_col_eng,capital1,capitalP,value1,valueP,s_value):
    l = []
    y = []
    j = []
    q = []
    z = []
    for i in range(df_final.shape[0]):
        df2 = df_form[df_form[ori_col]==df_final.loc[i,form_col][form_col_eng]]
        if i<5:
            list1 = df2[capital1].tolist()
            if len(list1)==0:
                list1.append(np.nan)
            list2 = df2[capitalP].tolist()
            if len(list2)==0:
                list2.append(np.nan)
            list3 = df2[value1].tolist()
            if len(list3)==0:
                list3.append(np.nan)
            list4 = df2[valueP].tolist()
            if len(list4)==0:
                list4.append(np.nan)
            l.append(list1[0])
            y.append(list2[0])
            j.append(list3[0])
            q.append(list4[0])
        if i >=5:
            list5 = df2[s_value].tolist()
            if len(list5)==0:
                list5.append(np.nan)
            z.append(list5[0])
    return l,y,j,q,z 

清洗方法2：

def data_wash2(df_form,df_final,ori_col,form_col,form_col_eng,capital1,capitalP,value1,valueP):
    l = []
    y = []
    j = []
    q = []
    for i in range(df_final.shape[0]):
        df2 = df_form[df_form[ori_col]==df_final.loc[i,form_col][form_col_eng]]
        list1 = df2[capital1].tolist()
        if len(list1)