step1:处理end_date中重复的数据,并清除没有意义的几列
import pandas as pd
import seaborn as sns
from datetime import datetime
import os
import warnings
import math
warnings.filterwarnings("ignore")
mpl.rcParams['font.sans-serif'] = ['FangSong'] # 指定默认字体
mpl.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题
########################第一步 清洗数据######################################################
######1.处理END_DATE重复的数据,并清除表中无用的几列
#读取数据 DF1、DF2、DF3分别为banlanceSheet incomeSheet cashFlowSheet
dfFile = "E:\\阿里云大数据竞赛\\金融算法大赛\\data\\[Add April May] FDDC_financial_data_20180613\\[New] Financial Data_20180613\\Balance Sheet.xls"
pwd = os.getcwd()
os.chdir(os.path.dirname(dfFile))
DF1 = pd.read_excel(os.path.basename(dfFile),sheetname=None)
dfFile = "E:\\阿里云大数据竞赛\\金融算法大赛\\data\\[Add April May] FDDC_financial_data_20180613\\[New] Financial Data_20180613\\Income Statement.xls"
os.chdir(os.path.dirname(dfFile))
DF2 = pd.read_excel(os.path.basename(dfFile),sheetname=None)
dfFile = "E:\\阿里云大数据竞赛\\金融算法大赛\\data\\[Add April May] FDDC_financial_data_20180613\\[New] Financial Data_20180613\\Cash Flow Statement.xls"
os.chdir(os.path.dirname(dfFile))
DF3 = pd.read_excel(os.path.basename(dfFile),sheetname=None)
#sheet=4 ['General Business' 'Insurance' 'Securities' 'Bank']
os.chdir(pwd)
#sheetNames存储四个表的名字['General Business' 'Insurance' 'Securities' 'Bank']
sheetNames=[]
for x in DF1:
sheetNames.append(x)
#函数1:clearEndDate ########函数用作清除表内对于同一个股票存在的多个END_DATE重复的问题(新的报表修改老的报表的数据),保留最新的报表数据
def clearEndDate(dataframe,columnNames): #columnNames[1] 对应股票代码 columnNames[5]对应END_DATE
rows=dataframe[columnNames[1]].size
temp=dataframe.groupby(columnNames[1])[columnNames[5]].apply(lambda x:x.sort_values(ascending=True)).reset_index()
#如果不存在重复的END_DATE,temp这个dataframe对象中不会出现level_1这个列
if 'level_1' not in temp.columns:
dataframe.sort_index(by=[columnNames[1]],ascending=True,inplace=True)
dataframe.reset_index(drop=True,inplace=True)
return dataframe
#如果存在重复的END_DATE,temp这个dataframe对象中会出现level_1这个列,level_1为原表里对应行的index
ind=list(temp['level_1'])
temp=dataframe.loc[ind]
temp.reset_index(drop=True,inplace=True)
dropIndex=[] #存储要被删除的数据行的index
for i in range(1,rows):
if(temp[columnNames[1]][i-1]==temp[columnNames[1]][i] and temp[columnNames[5]][i-1]==temp[columnNames[5]][i]): #如果两行数据的ID和截止日期相同,保留最后一行的数据
dropIndex.append(i-1)
temp.drop(dropIndex,axis=0,inplace=True)
temp.reset_index(drop=True,inplace=True)
return temp
for x in sheetNames:
df_balance=DF1[x]
df_income=DF2[x]
df_cashflow=DF3[x]
columnNames=list(df_balance.columns) #经测试,这3*4个表的前八个列名都是相同的
deleteCol1=[0,2,3,4,6,8] #banlance表无用的列,保留股票代码,END_DATE,会计区间的信息
deleteCol2=[0,2,3,4,6,8] #cashflow表无用的列,和banlance表一样
saveCols3=['TICKER_SYMBOL','END_DATE','FISCAL_PERIOD','REVENUE'] #income表的保留项,分别为股票代码,截止日期,会计区间,营业收入
#对三张表进行清洗(调用函数1)
df_balance=clearEndDate(df_balance,columnNames)
df_cashflow=clearEndDate(df_cashflow,columnNames)
df_income=df_income[saveCols3]
df_income=clearEndDate(df_income,columnNames)
#对balance表和cashflow表中包含无用信息的列进行删除
df_balance.drop(df_balance.columns[deleteCol1],axis=1,inplace=True)
df_cashflow.drop(df_cashflow.columns[deleteCol1],axis=1,inplace=True)
#将缺失值填0
df_balance.fillna(0,inplace=True)