股票申万二级行业分类,申万一级行业历年涨跌排名

数据来源于聚源数据库，navicat下载后使用python处理。
首先下载并关联数据。

import pandas as pdlirun= 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 npimport pandas as pdimport scipy.stats as stdf= 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])