358 lines
14 KiB
Python
358 lines
14 KiB
Python
|
import camelot
|
|||
|
|
import re
|
|||
|
|
import os
|
|||
|
|
import json
|
|||
|
|
import numpy as np
|
|||
|
|
from datetime import datetime
|
|||
|
|
import logging
|
|||
|
|
logger = logging.getLogger(__name__)
|
|||
|
|
# 读取PDF
|
|||
|
|
import PyPDF2
|
|||
|
|
# 分析PDF的layout，提取文本
|
|||
|
|
from pdfminer.high_level import extract_pages
|
|||
|
|
from pdfminer.layout import LTTextContainer, LTRect
|
|||
|
|
import pdfplumber
|
|||
|
|
import mysql.connector
|
|||
|
|
|
|||
|
|
# 数据处理流程
|
|||
|
|
# 1. 解析pdf标题，获取多级标题名称及页码范围
|
|||
|
|
# 2. 基于规则选出需要解析的标题下内容
|
|||
|
|
# 3. 根据需要解析的标题及页码，获取所有表格内容，并转化成带语义的指标
|
|||
|
|
# 4. 根据需要解析的标题及页码，获取所有非表格类的正文
|
|||
|
|
# 5. 文本和表格指标调用大模型抽取原始指标
|
|||
|
|
# 6. 根据规则讲原始指标转化为最终显示指标
|
|||
|
|
|
|||
|
|
STR_PATTERN = '营业收入|净利润|变动比例|损益|现金流量净额|现金流|每股收益|总资产|资产总额|收益率'
|
|||
|
|
|
|||
|
|
def get_md5(str):
|
|||
|
|
import hashlib
|
|||
|
|
m = hashlib.md5()
|
|||
|
|
m.update(str.encode('utf-8'))
|
|||
|
|
return m.hexdigest()
|
|||
|
|
|
|||
|
|
#获取指标的表头信息
|
|||
|
|
def get_num_info(array,row_num,col_num,x,y):
|
|||
|
|
num_info=""
|
|||
|
|
for j in range(col_num):
|
|||
|
|
if len(str(array[x][j])) > 50:
|
|||
|
|
continue
|
|||
|
|
num_info += str(array[x][j])
|
|||
|
|
|
|||
|
|
for i in range(row_num):
|
|||
|
|
if len(str(array[i][y])) > 50:
|
|||
|
|
continue
|
|||
|
|
num_info += str(array[i][y])
|
|||
|
|
|
|||
|
|
|
|||
|
|
return num_info.replace('%','')
|
|||
|
|
|
|||
|
|
def get_parse_pages(page_dict):
|
|||
|
|
"""
|
|||
|
|
:return: 返回一个存储需要解析的页码文本
|
|||
|
|
"""
|
|||
|
|
return "all"
|
|||
|
|
|
|||
|
|
# 读取pdf文件中文本内容，不包括表格
|
|||
|
|
def get_text_content(pdf_path):
|
|||
|
|
"""
|
|||
|
|
:return: 返回pdf文件中文本内容，不包括表格
|
|||
|
|
"""
|
|||
|
|
page_obj = []
|
|||
|
|
|
|||
|
|
# 我们从PDF中提取页面
|
|||
|
|
for pagenum, page in enumerate(extract_pages(pdf_path)):
|
|||
|
|
page_text = ''
|
|||
|
|
text_obj = {}
|
|||
|
|
# 初始化检查表的数量
|
|||
|
|
table_num = 0
|
|||
|
|
first_element= True
|
|||
|
|
table_extraction_flag= False
|
|||
|
|
# # 打开pdf文件
|
|||
|
|
pdf = pdfplumber.open(pdf_path)
|
|||
|
|
# 查找已检查的页面
|
|||
|
|
page_tables = pdf.pages[pagenum]
|
|||
|
|
# 找出本页上的表格数目
|
|||
|
|
tables = page_tables.find_tables()
|
|||
|
|
|
|||
|
|
# 找到所有的元素
|
|||
|
|
page_elements = [(element.y1, element) for element in page._objs]
|
|||
|
|
# 对页面中出现的所有元素进行排序
|
|||
|
|
page_elements.sort(key=lambda a: a[0], reverse=True)
|
|||
|
|
|
|||
|
|
# 查找组成页面的元素
|
|||
|
|
for i,component in enumerate(page_elements):
|
|||
|
|
# 提取PDF中元素顶部的位置
|
|||
|
|
pos= component[0]
|
|||
|
|
# 提取页面布局的元素
|
|||
|
|
element = component[1]
|
|||
|
|
|
|||
|
|
# 检查该元素是否为文本元素
|
|||
|
|
if isinstance(element, LTTextContainer):
|
|||
|
|
# 检查文本是否出现在表中
|
|||
|
|
|
|||
|
|
if table_extraction_flag == False:
|
|||
|
|
# 使用该函数提取每个文本元素的文本和格式
|
|||
|
|
line_text = element.get_text().replace('\s+', '').replace('\n', '').replace('\r', '')
|
|||
|
|
# 将每行的文本追加到页文本
|
|||
|
|
if len(line_text) > 5:
|
|||
|
|
page_text += line_text
|
|||
|
|
|
|||
|
|
# 附加每一行包含文本的格式
|
|||
|
|
else:
|
|||
|
|
# 省略表中出现的文本
|
|||
|
|
pass
|
|||
|
|
|
|||
|
|
# 检查表的元素
|
|||
|
|
if isinstance(element, LTRect):
|
|||
|
|
# 如果第一个矩形元素
|
|||
|
|
if first_element == True and (table_num+1) <= len(tables):
|
|||
|
|
# 找到表格的边界框
|
|||
|
|
lower_side = page.bbox[3] - tables[table_num].bbox[3]
|
|||
|
|
upper_side = element.y1
|
|||
|
|
# 将标志设置为True以再次避免该内容
|
|||
|
|
table_extraction_flag = True
|
|||
|
|
# 让它成为另一个元素
|
|||
|
|
first_element = False
|
|||
|
|
|
|||
|
|
# 检查我们是否已经从页面中提取了表
|
|||
|
|
if element.y0 >= lower_side and element.y1 <= upper_side:
|
|||
|
|
pass
|
|||
|
|
elif not isinstance(page_elements[i+1][1], LTRect):
|
|||
|
|
table_extraction_flag = False
|
|||
|
|
first_element = True
|
|||
|
|
table_num+=1
|
|||
|
|
|
|||
|
|
text_obj['page_num'] = pagenum
|
|||
|
|
text_obj['text'] = page_text
|
|||
|
|
page_obj.append(text_obj)
|
|||
|
|
|
|||
|
|
# 打印提取的文本
|
|||
|
|
# print(page_obj)
|
|||
|
|
return page_obj
|
|||
|
|
|
|||
|
|
# 读取pdf中的表格,并将表格中指标和表头合并，eg: 2022年1季度营业收入为xxxxx
|
|||
|
|
def get_table_measure(file_path, page_num="all"):
|
|||
|
|
"""
|
|||
|
|
:return: pdf中的表格,并将表格中指标和表头合并，eg: 2022年1季度营业收入为xxxxx
|
|||
|
|
"""
|
|||
|
|
measure_obj = []
|
|||
|
|
tables = camelot.read_pdf(file_path, pages=page_num, strip_text=' ,\n', copy_text=['h'])
|
|||
|
|
|
|||
|
|
for t in tables:
|
|||
|
|
data_dict = {}
|
|||
|
|
measure_list = []
|
|||
|
|
arr = np.array(t.data)
|
|||
|
|
rows, cols = arr.shape
|
|||
|
|
if rows == 1 and cols == 1:
|
|||
|
|
continue
|
|||
|
|
arr_str = ''.join([''.join(map(str, row)) for row in arr])
|
|||
|
|
|
|||
|
|
matches = re.findall(STR_PATTERN, arr_str)
|
|||
|
|
if len(matches) > 0:
|
|||
|
|
arr = np.array(t.data)
|
|||
|
|
rows, cols = arr.shape
|
|||
|
|
row_num , col_num = -1 , -1
|
|||
|
|
# 使用嵌套循环遍历数组，获取第一个数值位置
|
|||
|
|
for i in range(rows):
|
|||
|
|
for j in range(cols):
|
|||
|
|
if re.match(r'^[+-]?(\d+(\.\d*)?|\.\d+)(%?)$', str(arr[i, j])):
|
|||
|
|
if j == cols-1:
|
|||
|
|
row_num , col_num = i , j
|
|||
|
|
break
|
|||
|
|
elif (re.match(r'^[+-]?(\d+(\.\d*)?|\.\d+)(%?)$', str(arr[i, j+1]))
|
|||
|
|
or str(arr[i, j+1]) == '-'):
|
|||
|
|
row_num , col_num = i , j
|
|||
|
|
break
|
|||
|
|
else:
|
|||
|
|
continue
|
|||
|
|
break
|
|||
|
|
|
|||
|
|
# 遍历数值二维数组，转成带语义的指标
|
|||
|
|
if row_num != -1 and col_num != -1:
|
|||
|
|
for i in range(row_num,arr.shape[0]):
|
|||
|
|
for j in range(col_num,arr.shape[1]):
|
|||
|
|
if arr[i, j] == '-' or arr[i, j] == '' or len(arr[i, j]) > 20:
|
|||
|
|
continue
|
|||
|
|
else:
|
|||
|
|
num_info = get_num_info(arr,row_num,col_num,i,j)
|
|||
|
|
measure_list.append(f"{num_info}为{arr[i, j]}")
|
|||
|
|
# print(f"{num_info}为{arr[i, j]}")
|
|||
|
|
else:
|
|||
|
|
pass
|
|||
|
|
if len(measure_list) > 0:
|
|||
|
|
data_dict["measure_list"] = measure_list
|
|||
|
|
data_dict["page_num"] = f"{str(t.page)}_{str(t.order)}"
|
|||
|
|
measure_obj.append(data_dict)
|
|||
|
|
# print(measure_obj)
|
|||
|
|
return measure_obj
|
|||
|
|
|
|||
|
|
# 文本和表格数据给大模型，返回大模型抽取原始指标列表
|
|||
|
|
def get_measure_from_llm(user_prompt):
|
|||
|
|
"""
|
|||
|
|
:return: 文本和表格数据给大模型，返回大模型抽取原始指标列表
|
|||
|
|
"""
|
|||
|
|
import random
|
|||
|
|
from http import HTTPStatus
|
|||
|
|
from dashscope import Generation
|
|||
|
|
|
|||
|
|
llm_measure_list = []
|
|||
|
|
system_prompt = '''
|
|||
|
|
你是一个优秀的金融分析师，从给定的数据报告中自动提取以下10个关键财务指标。指标包括：
|
|||
|
|
2023年营业收入
|
|||
|
|
2023年合计营业收入
|
|||
|
|
2023年调整后营业收入
|
|||
|
|
2022年营业收入
|
|||
|
|
2022年合计营业收入
|
|||
|
|
2022年调整后营业收入
|
|||
|
|
2023年营业收入变动比例
|
|||
|
|
2023年营业收入比上年同期增减
|
|||
|
|
2023年归属母公司净利润
|
|||
|
|
2023年归属于上市公司股东的净利润
|
|||
|
|
2023年归属母公司净利润变动比例
|
|||
|
|
请确保只抽取这些指标，并且每个指标的输出格式为：指标名:指标值
|
|||
|
|
所有的指标值必须从用户提供的信息中抽取，不允许自己生成，如果找不到相关指标，指标值显示为-
|
|||
|
|
<数据报告>
|
|||
|
|
<user_prompt>
|
|||
|
|
</数据报告>
|
|||
|
|
'''
|
|||
|
|
system_prompt = system_prompt.replace('<user_prompt>', user_prompt)
|
|||
|
|
response = Generation.call(
|
|||
|
|
model='qwen-turbo',
|
|||
|
|
prompt = system_prompt,
|
|||
|
|
seed=random.randint(1, 10000),
|
|||
|
|
top_p=0.1,
|
|||
|
|
result_format='message',
|
|||
|
|
enable_search=False,
|
|||
|
|
max_tokens=1500,
|
|||
|
|
temperature=0.85,
|
|||
|
|
repetition_penalty=1.0
|
|||
|
|
)
|
|||
|
|
if response.status_code == HTTPStatus.OK:
|
|||
|
|
result = response['output']['choices'][0]['message']['content']
|
|||
|
|
llm_measure_list = result.split('\n')
|
|||
|
|
return llm_measure_list
|
|||
|
|
else:
|
|||
|
|
logger.error('Request id: %s, Status code: %s, error code: %s, error message: %s' % (
|
|||
|
|
response.request_id, response.status_code,
|
|||
|
|
response.code, response.message
|
|||
|
|
))
|
|||
|
|
|
|||
|
|
return "llm_error"
|
|||
|
|
|
|||
|
|
|
|||
|
|
# 解析大模型抽取的指标，并插入到数据库
|
|||
|
|
def parse_llm_measure_to_db(measure_info,type,conn,cursor):
|
|||
|
|
|
|||
|
|
create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|||
|
|
|
|||
|
|
# 执行SQL语句，插入数据
|
|||
|
|
insert_query = '''
|
|||
|
|
INSERT INTO ori_measure_list
|
|||
|
|
(file_id, file_name, type, page_number, table_index, ori_measure_id, ori_measure_name, ori_measure_value, create_time, update_time)
|
|||
|
|
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
|||
|
|
'''
|
|||
|
|
file_id = '1111111'
|
|||
|
|
file_name = '科润智控.pdf'
|
|||
|
|
llm_measure = measure_info['llm_measure']
|
|||
|
|
page_num = measure_info['page_num']
|
|||
|
|
table_index = '0'
|
|||
|
|
if type == 'table':
|
|||
|
|
table_index = measure_info['table_index']
|
|||
|
|
|
|||
|
|
for measure_obj in llm_measure:
|
|||
|
|
measure_obj = measure_obj.replace('\n', '').replace('\r', '').replace(' ', '')
|
|||
|
|
|
|||
|
|
if ':' in measure_obj:
|
|||
|
|
ori_measure_name = measure_obj.split(':')[0]
|
|||
|
|
ori_measure_value = measure_obj.split(':')[1].replace('+', '')
|
|||
|
|
if '-' in ori_measure_value:
|
|||
|
|
ori_measure_value = "-"
|
|||
|
|
if '.' in ori_measure_name:
|
|||
|
|
ori_measure_name = ori_measure_name.split('.')[1]
|
|||
|
|
ori_measure_id = get_md5(ori_measure_name)
|
|||
|
|
data_to_insert = (file_id, file_name, type, int(page_num), int(table_index), ori_measure_id, ori_measure_name, ori_measure_value, create_time, create_time)
|
|||
|
|
cursor.execute(insert_query, data_to_insert)
|
|||
|
|
logger.info(f"{type},{page_num},{table_index},{ori_measure_name},{ori_measure_value}")
|
|||
|
|
|
|||
|
|
# 提交事务
|
|||
|
|
conn.commit()
|
|||
|
|
return ""
|
|||
|
|
|
|||
|
|
# 根据measure_config中的规则，更新原始指标的显示指标
|
|||
|
|
def update_ori_measure(conn,cursor):
|
|||
|
|
|
|||
|
|
# 执行SQL语句，更新数据
|
|||
|
|
update_query = '''
|
|||
|
|
UPDATE ori_measure_list
|
|||
|
|
SET measure_id = %s, measure_name = %s
|
|||
|
|
WHERE ori_measure_id = %s and ori_measure_value !='-'
|
|||
|
|
'''
|
|||
|
|
|
|||
|
|
# 执行SQL语句，更新数据
|
|||
|
|
select_query = '''
|
|||
|
|
SELECT measure_id,measure_name,ori_measure_id FROM measure_config
|
|||
|
|
'''
|
|||
|
|
cursor.execute(select_query)
|
|||
|
|
records = cursor.fetchall()
|
|||
|
|
for record in records:
|
|||
|
|
data_to_update = (record[0], record[1], record[2])
|
|||
|
|
cursor.execute(update_query, data_to_update)
|
|||
|
|
|
|||
|
|
conn.commit()
|
|||
|
|
|
|||
|
|
if __name__ == "__main__":
|
|||
|
|
start_time = datetime.now()
|
|||
|
|
logger.info("开始时间：", start_time.strftime("%Y-%m-%d %H:%M:%S"))
|
|||
|
|
|
|||
|
|
path = "/Users/zhengfei/Desktop/科润智控1.pdf"
|
|||
|
|
table_info = get_table_measure(path)
|
|||
|
|
# text_info = get_text_content(path)
|
|||
|
|
|
|||
|
|
# # 数据库连接对象
|
|||
|
|
# # 连接到MySQL数据库
|
|||
|
|
# conn = mysql.connector.connect(
|
|||
|
|
# host="121.37.185.246",
|
|||
|
|
# user="financial",
|
|||
|
|
# password="financial_8000",
|
|||
|
|
# database="financial_report"
|
|||
|
|
# )
|
|||
|
|
|
|||
|
|
# 创建一个cursor对象来执行SQL语句
|
|||
|
|
# cursor = conn.cursor()
|
|||
|
|
|
|||
|
|
for table_obj in table_info:
|
|||
|
|
table_measure_obj = {}
|
|||
|
|
table_page_num = table_obj['page_num'].split("_")[0]
|
|||
|
|
table_index = table_obj['page_num'].split("_")[1]
|
|||
|
|
table_measure = ','.join(table_obj['measure_list'])
|
|||
|
|
if table_page_num == '3':
|
|||
|
|
logger.info(f"第{table_page_num}页表格指标为：{table_measure}")
|
|||
|
|
table_llm_measure = get_measure_from_llm(table_measure)
|
|||
|
|
if table_page_num == '3':
|
|||
|
|
logger.info(f"第{table_page_num}页表格llm指标为：{table_llm_measure}")
|
|||
|
|
# table_measure_obj['page_num'] = table_page_num
|
|||
|
|
# table_measure_obj['table_index'] = table_index
|
|||
|
|
# table_measure_obj['llm_measure'] = table_llm_measure
|
|||
|
|
# parse_llm_measure_to_db(table_measure_obj,'table',conn,cursor)
|
|||
|
|
|
|||
|
|
# for text_obj in text_info:
|
|||
|
|
# text_measure_obj = {}
|
|||
|
|
# text_page_num = text_obj['page_num']
|
|||
|
|
# text = text_obj['text']
|
|||
|
|
# if len(text) > 10:
|
|||
|
|
# text_llm_measure = get_measure_from_llm(text)
|
|||
|
|
# text_measure_obj['page_num'] = text_page_num
|
|||
|
|
# text_measure_obj['llm_measure'] = text_llm_measure
|
|||
|
|
# parse_llm_measure_to_db(text_measure_obj,'text',conn,cursor)
|
|||
|
|
# print(text_llm_measure)
|
|||
|
|
|
|||
|
|
# update_ori_measure(conn,cursor)
|
|||
|
|
|
|||
|
|
# cursor.close()
|
|||
|
|
# conn.close()
|
|||
|
|
# measure_info =['1. 2023年营业收入: 983698831.48', '2. 2023年营业收入变动比例: 15.10%', '3. 2023年归属母公司净利润: - (未在报告中找到)', '4. 2023年归属母公司净利润变动比例: - (未在报告中找到)', '5. 2023年毛利率: (营业收入 - 主营业务成本) / 营业收入 = (983698831.48 - 793604607.43) / 983698831.48', '6. 2022年毛利率: (主营业务收入 - 主营业务成本) / 主营业务收入 = (854682261.31 - 690932741.27) / 854682261.31', '7. 2023年主营业务收入: 983698831.48', '8. 2022年主营业务收入: 854682261.31']
|
|||
|
|
# parse_llm_measure_to_db(measure_info)
|
|||
|
|
# get_measure_from_llm()
|
|||
|
|
end_time = datetime.now()
|
|||
|
|
logger.info("结束时间：", end_time.strftime("%Y-%m-%d %H:%M:%S"))
|
|||
|
|
#print(pdf_data)
|