将年报数据汇总至xlsx文件中

import os
import pdfdocx
import warnings
warnings.filterwarnings('ignore')
import pandas as pd
import re

def clean(text):
    return re.sub('\s', ' ', text)

#文件夹列表
dirs = [d for d in os.listdir('reports') if 'DS' not in d]
for di in dirs:
    datas = []
    files = [f for f in os.listdir('reports/{d}'.format(d=di)) if 'z' in f]
    for file in files:
        try:
            file = 'reports/{di}/{f}'.format(di=di, f=file)
            code = file.split('/')[-1].split('_')[0]
            year = re.findall('_(\d{4})_', file.split('/')[-1])[0]
            text = clean(pdfdocx.read_pdf(file))
            data = {'code': code, 'text': text, 'year':year}
            datas.append(data)
        except:
            pass
    df = pd.DataFrame(datas, columns=['code', 'year', 'text'])
    #将每家公司的年报导出到csv中
    df.to_csv('data.csv', index=False, mode='a+', encoding='utf-8')
    
    
#读取
ndf = pd.read_csv('data.csv')
#去重
ndf.drop_duplicates(inplace=True)
#清洗
ndf = ndf[ndf.code=!='code']
#导出到xlsx
ndf.to_excel('data.xlsx', index=False)

import pandas as pd

df = pd.read_excel('data.xlsx')
df.head()

len(df)

df.code.nunique()

sorted(df.year.unique())

avg = len(df)/df.code.nunique()
round(avg, 2)