上市公司年报数据可视化——以医药行业净利润为例

• 姓名：张竣杰
• 学号：4201467

从excel中提取超链接

import pandas as pd
import openpyxl
import re

xlsx = '中药行业.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_1 = [link for link in links_1 if '摘要' not in link]
links_2 = "".join(links_1)
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('medical.csv', encoding='utf_8_sig')

筛选链接

import requests
import re
import pandas as pd
import time
import os

df = pd.read_csv('medical.csv')


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:
                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)

df_updt.to_csv('medical_updt.csv', encoding='utf_8_sig')

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)):
            df_newest.iloc[i, -2] = df_updt.iloc[j, -2]
        return (df_newest)

df_newest = sub_with_update(df_updt, df_orig)

df_newest.sort_values(by=['f_name'], inplace=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, encoding='utf_8_sig')

ten_csv = os.listdir('10companies')

下载PDF

import requests
import re
import pandas as pd
import time

df = pd.read_csv('C:/Users/10428/Desktop/data_gathering/10companies/huasen.csv')

links = df['link']
f_names = df['f_name']

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))

for link in links:
    href, fname = get_PDF_url(link)
    r = requests.get(href, allow_redirects=True)
    open('%s' % fname, 'wb').write(r.content)
    time.sleep(10)
r.close()

提取数据绘图

import fitz
import re
import pandas as pd
import matplotlib.pyplot as plt
from pandas import DataFrame

#设置显示的最大列、宽等参数，消掉打印不完全中间的省略号
pd.set_option('display.max_columns', 1000)
pd.set_option('display.width', 1000)
pd.set_option('display.max_colwidth', 1000)

def parse_data_line(subtext):
    subp = '([0-9,.%\- ]*?)\n'
    psub = '%s%s%s%s' % (subp,subp,subp,subp)
    p = re.compile('(?<=\\n)(\D+\n)+%s'% psub)
    lines = p.findall(subtext)
    lines = [(re.sub('\s', '', l[0]), l[1], l[2], l[3], l[4]) for l in lines]
    return lines

def parse_data_title(subtext):
    p = re.compile('(?<=否)\s*\n(.*?)(?=\\n营业收入)', re.DOTALL)
    title = p.search(subtext)
    if title is None:
        Warning('主要会计数据与财务指标，标题行没有匹配成功')
    else:
        title = title.group().strip().split('\n')
        title = [re.sub('\s', '', t) for t in title]
    return title

def parse_accounting_financial_data(text):
    title = parse_data_title(text)
    lines = parse_data_line(text)
    df = pd.DataFrame({
        'item': [l[0] for l in lines],
        '%s' % title[0]: [l[1] for l in lines],
        '%s' % title[1]: [l[2] for l in lines],
        '%s' % title[2]: [l[3] for l in lines],
        '%s' % title[3]: [l[4] for l in lines],
    })
    return df

#紫鑫药业
zixin = []
doc = fitz.open('紫鑫药业：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
zixin.append(df.iloc[:,4][1])
zixin.append(df.iloc[:,2][1])
zixin.append(df.iloc[:,1][1])

doc = fitz.open('紫鑫药业：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
zixin.append(df.iloc[:,1][1])

doc = fitz.open('紫鑫药业：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]+1
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
zixin.append(df.iloc[:,1][1])

zixin = [float(x.replace(',','')) for x in zixin]

time = ['2016', '2017', '2018', '2019', '2020']
plt.figure()
plt.plot(time, zixin, 'r-')
plt.xlabel('年度')
plt.ylabel('归属于股东的净利润')
plt.title('紫鑫药业净利润')
plt.show()


#以岭药业
yiling = []
doc = fitz.open('以岭药业：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
yiling.append(df.iloc[:,4][1])
yiling.append(df.iloc[:,2][1])
yiling.append(df.iloc[:,1][1])

doc = fitz.open('以岭药业：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
yiling.append(df.iloc[:,1][1])

doc = fitz.open('以岭药业：2020年年度报告.pdf')
page_number = 6
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
yiling.append(df.iloc[:,1][1])

yiling = [float(x.replace(',','')) for x in yiling]

plt.figure()
plt.plot(time, yiling, 'r-')
plt.xlabel('年度')
plt.ylabel('归属于股东的净利润')
plt.title('以岭药业净利润')
plt.show()


#新天药业
xintian = []
doc = fitz.open('新天药业：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
xintian.append(df.iloc[:,4][1])
xintian.append(df.iloc[:,2][1])
xintian.append(df.iloc[:,1][1])

doc = fitz.open('新天药业：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
xintian.append(df.iloc[:,1][1])

doc = fitz.open('新天药业：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
xintian.append(df.iloc[:,1][1])

xintian = [float(x.replace(',','')) for x in xintian]

plt.figure()
plt.plot(time, xintian, 'r-')
plt.xlabel('年度')
plt.ylabel('归属于股东的净利润')
plt.title('新天药业净利润')
plt.show()

#信邦制药
xinbang = []
doc = fitz.open('信邦制药：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
xinbang.append(df.iloc[:,4][1])
xinbang.append(df.iloc[:,2][1])
xinbang.append(df.iloc[:,1][1])

doc = fitz.open('信邦制药：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
xinbang.append(df.iloc[:,1][1])

doc = fitz.open('信邦制药：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
xinbang.append(df.iloc[:,1][1])

xinbang = [float(x.replace(',','')) for x in xinbang]

plt.figure()
plt.plot(time, xinbang, 'r-')
plt.xlabel('年度')
plt.ylabel('归属于股东的净利润')
plt.title('信邦制药净利润')
plt.show()


#沃华医药
wohua = []
doc = fitz.open('沃华医药：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
wohua.append(df.iloc[:,4][1])
wohua.append(df.iloc[:,2][1])
wohua.append(df.iloc[:,1][1])

doc = fitz.open('沃华医药：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
wohua.append(df.iloc[:,1][1])

doc = fitz.open('沃华医药：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
wohua.append(df.iloc[:,1][1])

wohua = [float(x.replace(',','')) for x in wohua]

plt.figure()
plt.plot(time, wohua, 'r-')
plt.xlabel('年度')
plt.ylabel('归属于股东的净利润')
plt.title('沃华医药净利润')
plt.show()


#瑞康医药
ruikang = []
doc = fitz.open('瑞康医药：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
ruikang.append(df.iloc[:,4][1])
ruikang.append(df.iloc[:,2][1])
ruikang.append(df.iloc[:,1][1])

doc = fitz.open('瑞康医药：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
ruikang.append(df.iloc[:,1][1])

doc = fitz.open('瑞康医药：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
ruikang.append(df.iloc[:,1][1])

ruikang = [float(x.replace(',','')) for x in ruikang]

plt.figure()
plt.plot(time, ruikang, 'r-')
plt.xlabel('年度')
plt.ylabel('归属于股东的净利润')
plt.title('瑞康医药净利润')
plt.show()

#奇正藏药
qizheng = []
doc = fitz.open('奇正藏药：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
qizheng.append(df.iloc[:,4][1])
qizheng.append(df.iloc[:,2][1])
qizheng.append(df.iloc[:,1][1])

doc = fitz.open('奇正藏药：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
qizheng.append(df.iloc[:,1][1])

doc = fitz.open('奇正藏药：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
qizheng.append(df.iloc[:,1][1])

qizheng = [float(x.replace(',','')) for x in qizheng]

plt.figure()
plt.plot(time, qizheng, 'r-')
plt.xlabel('年度')
plt.ylabel('EPS')
plt.title('奇正藏药净利润')
plt.show()


#华森制药
huasen = []
doc = fitz.open('华森制药：2018年年度报告.pdf')
page_number = 7
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
huasen.append(df.iloc[:,4][1])
huasen.append(df.iloc[:,2][1])
huasen.append(df.iloc[:,1][1])

doc = fitz.open('华森制药：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
huasen.append(df.iloc[:,1][1])

doc = fitz.open('华森制药：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
huasen.append(df.iloc[:,1][1])

huasen = [float(x.replace(',','')) for x in huasen]

plt.figure()
plt.plot(time, huasen, 'r-')
plt.xlabel('年度')
plt.ylabel('归属于股东的净利润')
plt.title('华森制药净利润')
plt.show()


#汉森制药
hansen = []
doc = fitz.open('汉森制药：2018年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
hansen.append(df.iloc[:,4][1])
hansen.append(df.iloc[:,2][1])
hansen.append(df.iloc[:,1][1])

doc = fitz.open('汉森制药：2019年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
hansen.append(df.iloc[:,1][1])

doc = fitz.open('汉森制药：2020年年度报告.pdf')
toc = doc.get_toc()
page_number = toc[7][2]
page7 = doc.load_page(page_number-1)
text = page7.getText()
df = parse_accounting_financial_data(text)
hansen.append(df.iloc[:,1][1])