[Python] Feeding tabular data to Chromadb for RAG

I am working on a RAG chatbot which takes .csv financial tables (eg. income statements/balance sheets etc.) of a company in the last 3 quarters, and answers questions based on the provided report context. Each CSV file contains a financial table for a company, with 3 rows representing 3 quarters and columns representing the metrics (eg. total assets, net income, shareholder equity etc).

These are the snapshots of the csv files (actual columns are way more than shown below), they named using the format COMPANY_ReportType_LatestQuarter.csv (eg. AMZN_cashflow_2024Q3.csv)

CompanyA_balance_2024Q3.csv

CompanyA_income_2024Q3.csv

I have previously created a working chatbot with persistence here, and I would like to enhance this prototype further by implementing a financial RAG feature.

The modules used are:

• Ollama: Embeddings and LLM Model
• ChromaDB and Langchain: For vector storage and document retrieval
• Chainlit: UI for my chatbot

My initial thoughts:

1. Initialize chromadb client and vectorstore with Ollama embeddings.
2. convert CSV files to documents using Langchain's CSVLoader with 1 Document per row.
3. Include the following metadatas in each document: i . Company (eg. MSFT/AMZN/AAPL) ii . Report_type (eg. Income Statement/Balance Sheet/Cashflow Statement) iii. Quarter (eg. 2024Q1, 2024Q2, 2024Q3)
4. Load each document with their respective metadatas.

The problem:


When I ask questions related to the documents, sometimes they are unable to retrieve data for specific quarters.

For example:

Question: "Is there an increase in Total Revenue across quarters?"
Answer:
"The Total Revenue in Q1 2024 was XX,XXX.
The Total Revenue in Q3 2024 was XX,XXX.

However, since there was no data provided for Q2 2024, we are unable to calculate the change in Total Revenue from Q1 2024 to Q2 2024, or Q2 2024 to Q3 2024."


Question: "Give me the Operating Cash Flow from Q1 2024 to Q3 2024."
Answer:
"The Operating Cash Flow for CompanyA are as follows:
- Q1 2024 : Not available as the provided cashflow statement did not include Operating Cash Flow for Q1 2024.
- Q2 2024 : XXX,XXX.XX
- Q3 2024 : XX,XXX.XX



This seems weird because when I inspected the vector database using vectorstore.get(), all the documents for Q1, Q2, and Q3 were indeed included along with their metadatas.

I have also tried clearing the vector database and re-ingesting the table, setting temperature=0.1 etc. but nothing worked, the retriever seems to just retrieve random documents it could.

This is the script I used to load the csv files into my chromadb vector database,

import chromadb
from langchain_chroma import Chroma
import os
import re
from langchain_ollama import OllamaEmbeddings, OllamaLLM
from langchain_community.document_loaders.csv_loader import CSVLoader

llm_model = "phi4:14b"
llm = OllamaLLM(model=llm_model)

embedding = OllamaEmbeddings(
model=llm_model,
base_url="http://localhost:11434"
)

chroma_client = chromadb.PersistentClient(path=os.path.join(os.getcwd(), "./chroma_db"))

vectorstore = Chroma(
client=chroma_client,
collection_name="fin_reports",
embedding_function=embedding
)

folder_path="./src"

# This regex pattern is used to extract the metadata fields from the filename.
filename_pattern = re.compile(r"([A-Z]+)_(income|balance|cashflow)_(\d{4}Q\d)\.csv")

# Map the ReportType metadata to their proper terms
map={"income":"Income Statement", "balance":"Balance Sheet", "cashflow": "Cashflow Statement"}

if __name__ == "__main__":
documents=[]
for filename in os.listdir(folder_path):
if filename.endswith(".csv"):
match = filename_pattern.match(filename)
if match:
company, report_type, quarter = match.groups()
file_path = os.path.join(folder_path, filename)
loader = CSVLoader(file_path, encoding="utf-7")
docs = loader.load()

for doc in docs:
# Get the quarter for each row (NOT to be confused with latest quarter in metadata).
match= re.search(r"Quarter:\s*([^\n]+)", doc.page_content)
if match:
# Assigning individual metadata for each row of document
doc.metadata["company"] = company
doc.metadata["report_type"] = map[report_type]
doc.metadata["quarter"] = match.group(1)
del doc.metadata["row"]
del doc.metadata["source"]

# Convert the loaded CSV table into natural language.
doc.page_content=f"This is the {map[report_type]} for {company}:\n" + doc.page_content
doc.page_content=doc.page_content.replace(":\n"," in ").replace("Quarter:","Quarter").replace("\n",", the ").replace(":"," is")

documents.extend(docs)
if documents:
vectorstore.add_documents(documents)


And this is the output of vectorstore.get(include=['embeddings', 'documents', 'metadatas']):

{'ids': ['abe17ec3-7b46-4ad9-a797-dce0b1d6ccb5', 'cd834610-b383-421f-ab27-49aedb8607f5', '667d64ad-3c8c-47a7-bdcb-7a40376fc082', 'b72b9341-c055-47d5-9b75-cbb12010c90a', '7ab68a4c-a2aa-4c9e-9396-8b4a63c1992c', '511dd725-c1c8-4adf-a3a3-0d7e072959aa', '9473f3dc-0718-466f-b340-483e5aa63879', '3c902c4c-a560-4329-b3b1-251b64f3011d', '99f1597e-022d-41cc-b21e-899ef5637923'],

'embeddings': array([[-1.41350282e-02, 8.76112841e-03, -2.13063452e-02, ...,
4.15409030e-03, -7.05788424e-03, -7.38847209e-03],
[-1.45367859e-02, 1.69306658e-02, -1.71646141e-02, ...,
-1.15523115e-04, -9.89801716e-03, -8.85582063e-03],
[-1.70514323e-02, 1.50136873e-02, -2.20932588e-02, ...,
-1.00878415e-05, -1.24230348e-02, -1.34052020e-02],
...,
[-6.36808481e-03, -2.84058508e-03, -1.51665546e-02, ...,
2.20017880e-03, 7.84451701e-03, -9.18203034e-03],
[-4.21447773e-03, -4.56405617e-03, -2.06277855e-02, ...,
3.17326194e-04, 4.39113053e-03, -6.34333445e-03],
[-7.46455975e-03, -3.84342880e-03, -9.71509703e-03, ...,
9.92560410e-04, 2.78300210e-03, -4.73287236e-03]]),

'documents': [
'This is the Balance Sheet for CompanyA in Quarter 2024Q1, the Total Assets is 519830471, the Cash, Cash Equivalents & Federal Funds Sold is 16859145, the Cash And Cash Equivalents is 16855368, the Cash is 9404164, the Cash Equivalents is --, the Cash And Due from Banks is 7451204',
'This is the Balance Sheet for CompanyA in Quarter 2024Q2, the Total Assets is 525572703, the Cash, Cash Equivalents & Federal Funds Sold is 20037159, the Cash And Cash Equivalents is 20033299, the Cash is 12371746, the Cash Equivalents is --, the Cash And Due from Banks is 7661553',
'This is the Balance Sheet for CompanyA in Quarter 2024Q3, the Total Assets is 526813662, the Cash, Cash Equivalents & Federal Funds Sold is 22156277, the Cash And Cash Equivalents is 22154535, the Cash is 14486877, the Cash Equivalents is --, the Cash And Due from Banks is 7667658',
'This is the Income Statement for CompanyA in Quarter 2024Q1, the Total Revenue is 3410798, the Net Interest Income is 2317974, the Interest Income is 4698234, the Interest Expense is 2380260, the Non Interest Income is 1092824, the Total Premiums Earned is --',
'This is the Income Statement for CompanyA in Quarter 2024Q2, the Total Revenue is 3578113, the Net Interest Income is 2406316, the Interest Income is 4718530, the Interest Expense is 2312214, the Non Interest Income is 1171797, the Total Premiums Earned is --',
'This is the Income Statement for CompanyA in Quarter 2024Q3, the Total Revenue is 3641496, the Net Interest Income is 2409285, the Interest Income is 4787045, the Interest Expense is 2377760, the Non Interest Income is 1232211, the Total Premiums Earned is 29188',
'This is the Cashflow Statement for CompanyA in Quarter 2024Q1, the Operating Cash Flow is 1655398, the Investing Cash Flow is 1719186, the Financing Cash Flow is -25345, the End Cash Position is 11208036, the Changes in Cash is 3349239, the Effect of Exchange Rate Changes is -105013, the Beginning Cash Position is 7963810, the Capital Expenditure is -45638, the Issuance of Debt is --, the Repayment of Debt is --, the Free Cash Flow is 1609760',
'This is the Cashflow Statement for CompanyA in Quarter 2024Q2, the Operating Cash Flow is 2080257, the Investing Cash Flow is 2967090, the Financing Cash Flow is -1967102, the End Cash Position is 13366303, the Changes in Cash is 3080245, the Effect of Exchange Rate Changes is -921978, the Beginning Cash Position is 11208036, the Capital Expenditure is -60991, the Issuance of Debt is --, the Repayment of Debt is -1500000, the Free Cash Flow is 2019266',
'This is the Cashflow Statement for CompanyA in Quarter 2024Q3, the Operating Cash Flow is 1845301, the Investing Cash Flow is -2437003, the Financing Cash Flow is -25211, the End Cash Position is 13244091, the Changes in Cash is -616913, the Effect of Exchange Rate Changes is 494701, the Beginning Cash Position is 13366303, the Capital Expenditure is -42864, the Issuance of Debt is 1000000, the Repayment of Debt is -1000000, the Free Cash Flow is 1802437'],

'uris': None, 'data': None,

'metadatas': [
{'company': 'CompanyA', 'quarter': '2024Q1', 'report_type': 'Balance Sheet'},
{'company': 'CompanyA', 'quarter': '2024Q2', 'report_type': 'Balance Sheet'},
{'company': 'CompanyA', 'quarter': '2024Q3', 'report_type': 'Balance Sheet'},
{'company': 'CompanyA', 'quarter': '2024Q1', 'report_type': 'Income Statement'},
{'company': 'CompanyA', 'quarter': '2024Q2', 'report_type': 'Income Statement'},
{'company': 'CompanyA', 'quarter': '2024Q3', 'report_type': 'Income Statement'},
{'company': 'CompanyA', 'quarter': '2024Q1', 'report_type': 'Cashflow Statement'},
{'company': 'CompanyA', 'quarter': '2024Q2', 'report_type': 'Cashflow Statement'},
{'company': 'CompanyA', 'quarter': '2024Q3', 'report_type': 'Cashflow Statement'}],

'included': [<IncludeEnum.embeddings: 'embeddings'>, <IncludeEnum.documents: 'documents'>, <IncludeEnum.metadatas: 'metadatas'>]}

Continue reading...