实验报告：通讯行业分析¶
第一步：读取通讯行业的Excel，并转为CSV格式¶
In [1]:
import pandas as pd
import openpyxl
import re
xlsx = 'E:/anaconda/通讯行业.xlsx'
df = pd.read_excel(xlsx)
exf = openpyxl.load_workbook(xlsx)
sheet = exf.active
C2 = sheet['C2']
C = sheet['C']
links = [c.value for c in C]
links_1 = links[1:-1]
links_2 = ''.join(links_1)
sample = '=HYPERLINK("http://news.windin.com/ns/bulletin.php?code=239751740736&id=126293652&type=1","星网锐捷:2019年年度报告(更新后)")'
p = re.compile('"(.*?)","(.*?)"')
list_of_tuple = p.findall(links_2)
df2 = pd.DataFrame({'link':[t[0] for t in list_of_tuple], 'f_name':[t[1] for t in list_of_tuple]})
df2.to_csv('通讯行业.csv')
第二步：读取CSV文件，筛选符合条件的文件并下载¶
In [ ]:
import requests
import re
import pandas as pd
import os
import fitz
import time
df = pd.read_csv('E:/anaconda/通讯行业.csv', encoding='gb2312')
p = re.compile('(?<=\d{4}(年度))')
f_names = [p.sub('年年度报告', f) for f in df.f_name]
df['f_name'] = f_names; del p,f_names
def filter_links(words,df,include=True):
ls = []
for word in words:
if include:
ls.append([word in f for f in df.f_name])
else:
ls.append([word not in f for f in df.f_name])
index = []
for r in range(len(df)):
flag = not include
for c in range(len(words)):
if include:
ls.append([word not in f for f in df.f_name])
index=[]
for r in range(len(df)):
flag=not include
for c in range(len(words)):
if include:
flag = flag or ls[c][r]
else:
flag = flag and ls[c][r]
index.append(flag)
df2=df[index]
return(df2)
df_all = filter_links(['摘要','问询函','社会责任','审计','财务','风险','债券'],df,include=[False])
df_orig = filter_links(['(','('],df_all,include=[False])
df_updt = filter_links(['(','('],df_all,include=[True])
df_updt = filter_links(['取消'],df_updt,include=[False])
def sub_with_update(df_updt,df_orig):
df_newest = df_orig.copy()
index_orig=[]
index_updt=[]
for i,f in enumerate(df_orig.f_name):
for j,fn in enumerate(df_updt.f_name):
if f in fn:
index_orig.append(i)
index_updt.append(j)
for n in range(len(index_orig)):
i = index_orig[n]
j = index_updt[n]
df_orig.iloc[i,-2] = df_updt.iloc[j,-2]
return(df_newest)
df_newest = sub_with_update(df_updt,df_orig)
df_all.sort_values(by=['f_name'],inplace=True,ignore_index=True)
df_newest['公司简称'] = [f[:4] for f in df_newest.f_name]
counts = df_newest['公司简称'].value_counts()
ten_company = []
for cn in counts.index[:10]:
ten_company.append(filter_links([cn],df_newest))
if not os.path.exists('10companies'):
os.makedirs('10companies')
for df_com in ten_company:
cn=df_com['公司简称'].iloc[0]
df_com.to_csv('10companies/%s.csv' % cn)
ten_csv=os.listdir('10companies')
os.chdir('E:/anaconda/10companies')
f_1=os.listdir()
links = []
f_names = []
def get_PDF_url(url):
r = requests.get(url);r.encoding = 'utf-8'; html = r.text
r.close()
p = re.compile('<a href=(.*?)\s.*?>(.*?)</a>', re.DOTALL)
a = p.search(html)
if a is None:
Warning('没有找到下载链接。请手动检查链接：%s' % url)
return()
else:
href = a.group(1); fname = a.group(2).strip()
href = r.url[:26] + href
return((href,fname))
hrefs=[];fnames=[]
for link in links:
href,fname = get_PDF_url(link)
hrefs.append(href)
fnames.append(fname)
df_final_links=pd.DataFrame({'href':hrefs,'fname':fnames})
df_final_links.to_csv('E:/anaconda/10companies/通讯links.csv')
df_final_links=pd.read_csv('E:/anaconda/10companies/通讯links.csv')
f_names=df_final_links['fname']
hrefs=df_final_links['href']
for i in range(len(hrefs)):
href=hrefs[i];f_name=f_names[i]
r = requests.get(href, allow_redirects=True)
open('%s' %f_name, 'wb').write(r.content)
time.sleep(10)
r.close()
第三步：读取相关的数据内容，并画出图表分析，首先是十个企业近十年的营业总收入对比图¶
In [3]:
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib import rcParams
%matplotlib inline
rcParams['font.family'] = 'simhei'
df = pd.read_excel("E:/anaconda/10companies/工作簿1.xlsx")
print(df)
plt.plot(df["年份"],df["st新海"],label='st新海',linewidth=1,color='r',markersize=12)
plt.plot(df["年份"],df["春兴精工"],label='春兴精工',linewidth=1,color='y',markersize=12)
plt.plot(df["年份"],df["光弘科技"],label='光弘科技',linewidth=1,color='blue',markersize=12)
plt.plot(df["年份"],df["汇源通信"],label='汇源通信',linewidth=1,color='purple',markersize=12)
plt.plot(df["年份"],df["科创新源"],label='科创新源',linewidth=1,color='green',markersize=12)
plt.plot(df["年份"],df["立昂技术"],label='立昂技术',linewidth=1,color='brown',markersize=12)
plt.plot(df["年份"],df["日海智能"],label='日海智能',linewidth=1,color='black',markersize=12)
plt.plot(df["年份"],df["润建股份"],label='润建股份',linewidth=1,color='pink',markersize=12)
plt.plot(df["年份"],df["星网锐捷"],label='星网锐捷',linewidth=1,color='grey',markersize=12)
plt.plot(df["年份"],df["亿联网络"],label='亿联网络',linewidth=1,color='violet',markersize=12)
plt.xlabel("年份")
plt.ylabel('营业总收入')
plt.title("营业收入走势图")
plt.legend()
plt.grid()
plt.show()
第四步：分析这十个公司近五年，每年的营业成本，并作图对比¶
In [4]:
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
from matplotlib import rcParams
rcParams['font.family'] = 'simhei'
data = pd.read_excel('E:/anaconda/10companies/工作簿2.xlsx').set_index(['date'])
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['st新海','春兴精工','光弘科技','汇源通信','立昂技术','科创新源', '日海智能', '润建股份', '星网锐捷', '亿联网络']
data0=data.iloc[0]
plt.barh(range(len(data0)), data0, tick_label=companies_name, color='#6699CC')
plt.title('2017年营业成本对比（单位：万元)')
plt.show()
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['st新海','春兴精工','光弘科技','汇源通信','立昂技术','科创新源', '日海智能', '润建股份', '星网锐捷', '亿联网络']
data0=data.iloc[1]
plt.barh(range(len(data0)), data0, tick_label=companies_name, color='#6699CC')
plt.title('2018年营业成本对比（单位：万元)')
plt.show()
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['st新海','春兴精工','光弘科技','汇源通信','立昂技术','科创新源', '日海智能', '润建股份', '星网锐捷', '亿联网络']
data0=data.iloc[2]
plt.barh(range(len(data0)), data0, tick_label=companies_name, color='#6699CC')
plt.title('2019年营业成本对比（单位：万元)')
plt.show()
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['st新海','春兴精工','光弘科技','汇源通信','立昂技术','科创新源', '日海智能', '润建股份', '星网锐捷', '亿联网络']
data0=data.iloc[3]
plt.barh(range(len(data0)), data0, tick_label=companies_name, color='#6699CC')
plt.title('2020年营业成本对比（单位：万元)')
plt.show()
plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
companies_name = ['st新海','春兴精工','光弘科技','汇源通信','立昂技术','科创新源', '日海智能', '润建股份', '星网锐捷', '亿联网络']
data0=data.iloc[4]
plt.barh(range(len(data0)), data0, tick_label=companies_name, color='#6699CC')
plt.title('2021年营业成本对比（单位：万元)')
plt.show()
In [ ]: