昨天朋友发我一个基金募集资金表格,格式大概这样。
每个基金里面股东名称、出资比例、认缴金额这些并没有拆分为单行的数据,而是合并在一起的。
他们需要拆分开,然后和另一张表通过vlookup核对。
如果只有一个字段是多行数据,那么用Excel插件方方格子就可以完成。
但是这里多个字段都是多行的,拆分的时候需要根据换行符一一对应。
所以用python写了30几行代码解决这个问题。
代码
import pandas as pddef get_data(file,sheet): df=pd.read_excel(file,sheet, index_col=None, na_values=['NA']) return dfdef split_row(df,row): owners=str(row['股东名称']).split('\r\n') ratios=str(row['出资比例']).split('\r\n') capitals=str(row['认缴金额']).split('\r\n') units=str(row['单位']).split('\r\n') for index,owner in enumerate(owners): if owner!='nan': if index ratio=ratios[index] else: ratio='' if index capital=capitals[index] else: capital='' if index unit=units[index] else: unit='' s=pd.Series([row['基金简称'],row['基金全称'],row['成立时间'],row['所在地'],row['基金类型'],row['资本来源'],row['募集目标规模'],row['单位'],row['基金业协会备案'],row['管理公司'],row['开始募集时间'],row['募集完成时间'],row['募集状态'],owner,ratio,capital,unit]) #print(s) df=df.append(s,ignore_index=True) #print(df) return dfif __name__ == '__main__': df=get_data('注册在成都市的基金列表.xlsx', '基金') df_new=pd.DataFrame() for index,row in df.iterrows(): df_new=split_row(df_new,row) df_new.columns=['基金简称','基金全称','成立时间','所在地','基金类型','资本来源','募集目标规模','单位','基金业协会备案','管理公司','开始募集时间','募集完成时间','募集状态','股东名称','出资比例','认缴金额','单位2'] df_new.to_excel('output.xlsx','基金',index=False)
知识点
其实整个问题解决也不难。
主要思路可以分成下面几步:
1. 读取Excel数据
这里我们用pandas库能够非常简单的读取Excel。
df=pd.read_excel(file,sheet, index_col=None, na_values=['NA'])
这样一个语句就能将Excel数据存放到df变量里。
- file:Excel文件路径
- sheet:工作表名称
- index_col:是否要索引号(如果为true就会多加一列0,1,2,3。。。)
2.循环每一行的数据
整个文档我们有500多行数据,不管多少行,程序就是可以把相同逻辑的用循环处理。
通过循环,我们把处理500多行数据,简化为处理一行数据的逻辑。
pandas的循环可以用下面这条语句:
for index,row in df.iterrows():
这里的df就是第一步我们将Excel数据放入到的变量。
- index:是行数-1(比如第1行的index就是0,第2行的index就是1)
- row: 是每一行的数据。
这样我们就可以遍历每一行的数据,我们后面只需要关心一行里数据怎么处理。
3.拆分
我们以股东名称为基准进行拆分,拆分方法就是根据换行符,在windows中换行符就是'\r\n'.
owners=str(row['股东名称']).split('\r\n')
我们将股东名称拆分成了owners的列表,比如,如果有三个股东,就会拆分成:['股东A','股东B','股东C']
下面我们要做的就是循环这个owners,每一个owner补全完整一行数据
列表的循环方法:
for index,owner in enumerate(owners):
因为出资比例等字段也是多行并和股东名称是一一对应的,所以我们同样对其用split('\r\n')
拆分后,只需要用owner循环中的index就可以补全对应的数据。
4.补全新的二维表
s=pd.Series([row['基金简称'],row['基金全称'],row['成立时间'],row['所在地'],row['基金类型'],row['资本来源'],row['募集目标规模'],row['单位'],row['基金业协会备案'],row['管理公司'],row['开始募集时间'],row['募集完成时间'],row['募集状态'],owner,ratio,capital,unit])df=df.append(s,ignore_index=True)
变量s是每一个股东对应补全的新的一行数据。
df.append(s,ignore_index=True)
将每一行数据添加到新的二维表df中。
5.数据输出到Excel
df_new.to_excel('output.xlsx','基金',index=False)
将数据存储在新的工作簿output.xlsx的基金表里。
最后拆分出来的表格就是这样一种格式。
我和林铖编写的审计效率手册