数据来源于聚源数据库,navicat下载后使用python处理。
首先下载并关联数据。
import pandas as pd
lirun= pd.read_csv("./0515利润表.csv",encoding = "gbk",low_memory=False)
zuoshoupan = pd.read_csv("./0515昨收盘.csv",encoding = "gbk",low_memory=False)
zhubiao = pd.read_csv("./0515证券主表.csv",encoding = "gbk",low_memory=False)
zichan = pd.read_csv("./0515资产负债数据.csv",encoding = "gbk",low_memory=False)
ebit = pd.read_csv("./0515ebitda.csv",encoding = "gbk",low_memory=False)
hangye = pd.read_csv("./申万.csv",encoding = "gbk",low_memory=False)
lian1 = pd.merge(zichan,lirun,on="CompanyCode")
lian2 = pd.merge(lian1,zhubiao,on="CompanyCode")
lian3 = pd.merge(lian2,zuoshoupan,on="InnerCode")
lian4 = pd.merge(lian3,ebit,on = "CompanyCode")
lian5 = pd.merge(lian4,hangye,on = "CompanyCode")
print(lian5)
lian5.to_csv("./0517shi.csv",encoding = "gbk",index = False)
对数据处理
import numpy as np
import pandas as pd
import scipy.stats as st
df= pd.read_csv("./ev0518.csv",encoding = "gbk",low_memory=False)
b = df[df["ebitda/ev"]=="#DIV/0!"].index.tolist()
df = df.drop(b)
a = df.groupby(by="ThirdIndustryName")
new_data=pd.DataFrame(columns =['CompanyCode', 'EndDate', 'CashEquivalents', 'MinorityInterests', 'ShortTermLoan', 'LongtermLoan', 'BondsPayable', 'TradingAssets', 'TradingLiability', 'LoanAndAdvance', 'HoldForSaleAssets', 'HoldToMaturityInvestments', 'PaidInCapital', 'OperatingRevenue', 'ID', 'InnerCode', 'SecuCode', 'SecuAbbr', 'PrevClosePrice', 'EBITDA', 'ThirdIndustryName', '超额现金', '非经营性资产', 'ev', 'ev/ebitda', 'ebitda/ev'] )
for x,y in a:
y["ebitda/ev"] = y["ebitda/ev"].astype(np.float)
mean = y["ebitda/ev"].mean()
std = y["ebitda/ev"].std()
new=[]
for i in range(y.shape[0]):
k = y.iloc[i,:]["ebitda/ev"]
n = (k - mean)/std
new.append(n)
if new is not None:
y["p"] = new
new1=[]
for i in range(y.shape[0]):
l = st.norm.cdf(y.iloc[i,:]["p"])
new1.append(l)
if new1 is not None:
y["pp"] = new1
new_data = pd.concat([new_data,y])
new_data.to_csv("./0518_pp.csv",encoding = "gbk",index = False)
心得:使用groupby函数对每组数据进行处理后,将各分组重新整合成dataframe的方法:
(1)设立空的dataframe: new_data=pd.DataFrame(columns =~~),需要写出列名,且和分组后的列名一致,注意比原dataframe少了分组的那一列。
(2)使用concat函数,不知道为什么使用append追加dataframe无法得出的是空值。
new_data = pd.concat([new_data,y])