使用高精度RAG实现对表格数据的检索

!apt-get -qq install poppler-utils tesseract-ocr%pip install -q --user --upgrade pillow%pip install -q --upgrade unstructured["all-docs"]%pip install kdbai_client%pip install langchain-openai%pip install langchain%pip install langchain-community%pip install pymupdf%pip install --upgrade nltk
import osfrom getpass import getpassimport openaifrom openai import OpenAIfrom unstructured.partition.pdf import partition_pdffrom unstructured.partition.auto import partitionfrom langchain_openai import OpenAIEmbeddingsimport kdbai_client as kdbaifrom langchain_community.vectorstores import KDBAIfrom langchain.chains import RetrievalQAfrom langchain_openai import ChatOpenAIimport fitznltk.download('punkt')

# 配置 OpenAI APIif "OPENAI_API_KEY" in os.environ:KDBAI_API_KEY = os.environ["OPENAI_API_KEY"]else:# 提示用户输入 API 密钥OPENAI_API_KEY = getpass("请输入 OPENAI API 密钥: ")# 将 API 密钥保存为当前会话的环境变量os.environ["OPENAI_API_KEY"] = OPENAI_API_KEY

!wget 'https://s21.q4cdn.com/399680738/files/doc_news/Meta-Reports-Second-Quarter-2024-Results-2024.pdf' -O './doc1.pdf'

elements = partition_pdf('./doc1.pdf',策略="高分辨率",分块策略="按标题",最大字符数=2500,每n字符后新建=2300,)

from collections import Counter显示(Counter(元素.__class__ for 元素 in elements))

>>> Counter({unstructured.documents.elements.CompositeElement: 17, unstructured.documents.elements.Table: 10})

print(elements[-2])>>>Foreign exchange effect on 2024 revenue using 2023 rates Revenue excluding foreign exchange effect GAAP revenue year-over-year change % Revenue excluding foreign exchange effect year-over-year change % GAAP advertising revenue Foreign exchange effect on 2024 advertising revenue using 2023 rates Advertising revenue excluding foreign exchange effect 2024 $ 39,071 371 $ 39,442 22 % 23 % $ 38,329 367 $ 38,696 22 % 2023 $ 31,999 $ 31,498 2024 $ 75,527 265 $ 75,792 25 % 25 % $ 73,965 261 $ 74,226 24 % 2023 GAAP advertising revenue year-over-year change % Advertising revenue excluding foreign exchange effect year-over-year change % 23 % 25 % Net cash provided by operating activities Purchases of property and equipment, net Principal payments on finance leases $ 19,370 (8,173) (299) $ 10,898 $ 17,309 (6,134) (220) $ 10,955 $ 38,616 (14,573) (614) $ 23,429

def extract_text_from_pdf(pdf_path):text = ""with fitz.open(pdf_path) as doc:for page in doc:text += page.get_text()return text
pdf_path = './doc1.pdf'document_content = extract_text_from_pdf(pdf_path)

# Initialize the OpenAI clientclient = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
def get_table_description(table_content, document_context):prompt = f"""Given the following table and its context from the original document,provide a detailed description of the table. Then, include the table in markdown format.
Original Document Context:{document_context}
Table Content:{table_content}
Please provide:1. A comprehensive description of the table.2. The table in markdown format."""
response = client.chat.completions.create(model="gpt-4o",messages=[{"role": "system", "content": "You are a helpful assistant that describes tables and formats them in markdown."},{"role": "user", "content": prompt}])
return response.choices[0].message.content

# Process each table in the directoryfor element in elements:if element.to_dict()['type'] == 'Table':table_content = element.to_dict()['text']
# Get description and markdown table from GPT-4oresult = get_table_description(table_content, document_content)# Replace each Table elements text with the new descriptionelement.text = result
print("Processing complete.")

This markdown table provides a concise presentation of the financial data, making it easy to read and comprehend in a digital format.### Detailed Description of the Table
The table presents segment information from Meta Platforms, Inc. for both revenue and income (loss) from operations. The data is organized into two main sections: 1. **Revenue**: This section is subdivided into two categories: "Advertising" and "Other revenue". The total revenue generated from these subcategories is then summed up for two segments: "Family of Apps" and "Reality Labs". The table provides the revenue figures for three months and six months ended June 30, for the years 2024 and 2023.2. **Income (loss) from operations**: This section shows the income or loss from operations for the "Family of Apps" and "Reality Labs" segments, again for the same time periods.
The table allows for a comparison between the two segments of Meta's business over time, illustrating the performance of each segment in terms of revenue and operational income or loss. 
### The Table in Markdown Format
```markdown### Segment Information (In millions, Unaudited)
|| Three Months Ended June 30, 2024 | Three Months Ended June 30, 2023 | Six Months Ended June 30, 2024 | Six Months Ended June 30, 2023 ||----------------------------|----------------------------------|----------------------------------|------------------------------- |-------------------------------|| **Revenue:** ||| | || Advertising| $38,329| $31,498| $73,965 | $59,599 || Other revenue| $389 | $225 | $769| $430|| **Family of Apps** | $38,718| $31,723| $74,734 | $60,029 || Reality Labs | $353 | $276 | $793| $616|| **Total revenue**| $39,071| $31,999| $75,527 | $60,645 ||||| | || **Income (loss) from operations:** ||| | || Family of Apps | $19,335| $13,131| $36,999 | $24,351 || Reality Labs | $(4,488) | $(3,739) | $(8,334)| $(7,732)|| **Total income from operations** | $14,847| $9,392 | $28,665 | $16,619 |

from unstructured.embed.openai import OpenAIEmbeddingConfig, OpenAIEmbeddingEncoder``````pythonembedding_encoder = OpenAIEmbeddingEncoder(config=OpenAIEmbeddingConfig(api_key=os.getenv("OPENAI_API_KEY"),model_name="text-embedding-3-small",))elements = embedding_encoder.embed_documents(elements=elements)

import pandas as pddata = []``````pythonfor c in elements:row = {}row['id'] = c.idrow['text'] = c.textrow['metadata'] = c.metadata.to_dict()row['embedding'] = c.embeddingsdata.append(row)
df = pd.DataFrame(data)

KDBAI_ENDPOINT = (os.environ["KDBAI_ENDPOINT"]if "KDBAI_ENDPOINT" in os.environelse input("KDB.AI endpoint: "))KDBAI_API_KEY = (os.environ["KDBAI_API_KEY"]if "KDBAI_API_KEY" in os.environelse getpass("KDB.AI API key: "))
session = kdbai.Session(api_key=KDBAI_API_KEY, endpoint=KDBAI_ENDPOINT)

schema = {'columns': [{'name': 'id', 'pytype': 'str'},{'name': 'text', 'pytype': 'str'},{'name': 'metadata', 'pytype': 'dict'},{'name': 'embedding','vectorIndex': {'dims': 1536, 'type': 'flat', 'metric': 'L2'}}]}

KDBAI_TABLE_NAME = "Table_RAG"
# 首先确保表不存在if KDBAI_TABLE_NAME in session.list():session.table(KDBAI_TABLE_NAME).drop()```# 创建表```pythontable = session.create_table(KDBAI_TABLE_NAME, schema)

# 将元素插入到 KDB.AI 表中table.insert(df)

# Define OpenAI embedding model for LangChain to embed the queryembeddings = OpenAIEmbeddings(model="text-embedding-3-small")
# use KDBAI as vector storevecdb_kdbai = KDBAI(table, embeddings)

# 定义一个问题/答案 LangChain 链qabot = RetrievalQA.from_chain_type(chain_type="stuff",llm=ChatOpenAI(model="gpt-4o"),retriever=vecdb_kdbai.as_retriever(search_kwargs=dict(k=5)),return_source_documents=True,)

# 辅助函数以执行 RAGdef RAG(query):print(query)print("-----")return qabot.invoke(dict(query=query))["result"]

# Query the RAG chain!RAG("what is the 2024 GAAP advertising Revenue in the three months  ended June 30th? What about net cash by operating activies")

# Query the RAG chain!RAG("what is the three month costs and expensis for 2023?")

# Query the RAG chain!RAG("At the end of 2023, what was the value of Meta's Goodwill assets?")

# Query the RAG chain!RAG("What is the research and development costs for six months ended in June 2024")

# Query the RAG chain!RAG("Given a sentiment score between 1 and 10 for the outlook? Explain your reasoning")

I would give the sentiment score for Meta Platforms, Inc.'s outlook an **8 out of 10**. Here's the reasoning behind this score:
### Positive Indicators:1. **Significant Increase in Earnings Per Share (EPS)**: - **Basic EPS** has risen from $3.03 in Q2 2023 to $5.31 in Q2 2024, and from $5.24 in H1 2023 to $10.17 in H1 2024. - **Diluted EPS** has also shown substantial growth, from $2.98 in Q2 2023 to $5.16 in Q2 2024, and from $5.18 in H1 2023 to $9.86 in H1 2024. 2. **Revenue Growth**: - Revenue increased by 22% from $31.999 billion in Q2 2023 to $39.071 billion in Q2 2024.
3. **Improved Income from Operations**: - Income from operations rose by 58%, from $9.392 billion in Q2 2023 to $14.847 billion in Q2 2024. - Operating margin improved from 29% in Q2 2023 to 38% in Q2 2024.
4. **Net Income Growth**: - Net income for Q2 2024 was $13.465 billion, marking a 73% increase from $7.788 billion in Q2 2023.
5. **Effective Tax Rate**: - The effective tax rate decreased from 16% in Q2 2023 to 11% in Q2 2024, benefiting overall profitability.
### Negative or Neutral Indicators:1. **Increase in Costs and Expenses**: - Total costs and expenses increased by 7%, from $22.607 billion in Q2 2023 to $24.224 billion in Q2 2024.
2. **Decrease in Retained Earnings**: - Retained earnings slightly decreased from $82,070 million at the end of 2023 to $81,188 million by June 30, 2024.
### Conclusion:The significant improvements in EPS, revenue, income from operations, and net income indicate strong financial performance and a positive outlook for Meta Platforms, Inc. The increase in costs and expenses and a slight decrease in retained earnings are areas to watch, but they don't outweigh the overall positive momentum. Hence, the sentiment score of 8 reflects a strong outlook with some room for careful monitoring of expenses.