# Streamlit application for New York Housing Market Explorer # Required imports import streamlit as st import pandas as pd from llama_index import SimpleDirectoryReader, ServiceContext, StorageContext, VectorStoreIndex from llama_index.llms import OpenAI from llama_index.embeddings import FastEmbedEmbedding from qdrant_client import QdrantClient import json import os from sqlalchemy import create_engine from llama_index import SQLDatabase, ServiceContext from llama_index.indices.struct_store import NLSQLTableQueryEngine from pathlib import Path from llama_index.vector_stores.qdrant import QdrantVectorStore from llama_index.query_engine import ( SQLAutoVectorQueryEngine, RetrieverQueryEngine, ) from llama_index.tools.query_engine import QueryEngineTool from llama_index.indices.vector_store import VectorIndexAutoRetriever from llama_index.indices.vector_store.retrievers import ( VectorIndexAutoRetriever, ) from llama_index.vector_stores.types import MetadataInfo, VectorStoreInfo from llama_index.query_engine.retriever_query_engine import ( RetrieverQueryEngine, ) st.set_page_config(layout="wide") write_dir = Path("textdata") # Initialize Qdrant client client = QdrantClient( url=os.environ['QDRANT_URL'], api_key=os.environ['QDRANT_API_KEY'], ) # Initialize LLM and embedding model llm = OpenAI(temperature=0.1, model="gpt-3.5-turbo") embed_model = FastEmbedEmbedding(model_name="BAAI/bge-small-en-v1.5") service_context = ServiceContext.from_defaults(chunk_size_limit=1024, llm=llm, embed_model=embed_model) vector_store = QdrantVectorStore(client=client, collection_name="housing2") storage_context = StorageContext.from_defaults(vector_store=vector_store) #Create vector indexes and store in Qdrant. To be run only once in the beginning #from llama_index import VectorStoreIndex #index = VectorStoreIndex.from_documents(documents, vector_store=vector_store, service_context=service_context, storage_context=storage_context) # Load the vector index from Qdrant collection index = VectorStoreIndex.from_vector_store( vector_store, storage_context=storage_context ) # Function to extract and format text data from a dataframe row def get_text_data(data): return f""" BROKERTITLE: {data['BROKERTITLE']} TYPE: {data['TYPE']} PRICE: {data['PRICE']} BEDS: {data['BEDS']} BATH: {data['BATH']} PROPERTYSQFT: {data['PROPERTYSQFT']} ADDRESS: {data['ADDRESS']} STATE: {data['STATE']} MAIN_ADDRESS: {data['MAIN_ADDRESS']} ADMINISTRATIVE_AREA_LEVEL_2: {data['ADMINISTRATIVE_AREA_LEVEL_2']} LOCALITY: {data['LOCALITY']} SUBLOCALITY: {data['SUBLOCALITY']} STREET_NAME: {data['STREET_NAME']} LONG_NAME: {data['LONG_NAME']} FORMATTED_ADDRESS: {data['FORMATTED_ADDRESS']} LATITUDE: {data['LATITUDE']} LONGITUDE: {data['LONGITUDE']} """ def create_text_and_embeddings(): # Write text data to 'textdata' folder and creating individual files if write_dir.exists(): print(f"Directory exists: {write_dir}") [f.unlink() for f in write_dir.iterdir()] else: print(f"Creating directory: {write_dir}") write_dir.mkdir(exist_ok=True, parents=True) for index, row in df.iterrows(): if "text" in row: file_path = write_dir / f"Property_{index}.txt" with file_path.open("w") as f: f.write(str(row["text"])) else: print(f"No 'text' column found at index {index}") print(f"Files created in {write_dir}") #create_text_and_embeddings() #execute only once in the beginning @st.cache_data def load_data(): if write_dir.exists(): reader = SimpleDirectoryReader(input_dir="textdata") documents = reader.load_data() return documents documents = load_data() # Streamlit UI setup st.title('New York Housing Market Explorer') # Load the dataset df_file_path = 'NY-House-Dataset.csv' # Path to the csv file if os.path.exists(df_file_path): df = pd.read_csv(df_file_path) df["text"] = df.apply(get_text_data, axis=1) st.dataframe(df) # Display df in the UI else: st.error("Data file not found. Please check the path and ensure it's correct.") # Input from user user_query = st.text_input("Enter your query:", "Suggest 3 houses in Manhattan brokered by compass.") # Define the options for the radio button options = ['Simple: Qdrant Similarity Search + LLM Call (works well for filtering type of queries)', 'Advanced: Qdrant Similarity Search + Llamaindex Text-to-SQL'] # Create a radio button for the options selection = st.radio("Choose an option:", options) # Processing the query if st.button("Submit Query"): # Execute different blocks of code based on the selection if selection == 'Simple: Qdrant Similarity Search + LLM Call (works well for filtering type of queries)': # Part 1, semantic search + LLM call # Generate query vector query_vector = embed_model.get_query_embedding(user_query) # Perform search with Qdrant response = client.search(collection_name="housing2", query_vector=query_vector, limit=10) # Processing and displaying the results text = '' properties_list = [] # List to store multiple property dictionaries for scored_point in response: # Access the payload, then parse the '_node_content' JSON string to get the 'text' node_content = json.loads(scored_point.payload['_node_content']) text += f"\n{node_content['text']}\n" # Initialize a new dictionary for the current property property_dict = {} for line in node_content['text'].split('\n'): if line.strip(): # Ensure line is not empty key, value = line.split(': ', 1) property_dict[key.strip()] = value.strip() # Add the current property dictionary to the list properties_list.append(property_dict) # properties_list contains all the retrieved property dictionaries with st.status("Retrieving points/nodes based on user query", expanded = True) as status: for property_dict in properties_list: st.json(json.dumps(property_dict, indent=4)) print(property_dict) status.update(label="Retrieved points/nodes based on user query", state="complete", expanded=False) with st.status("Simple Method: Generating response based on Similarity Search + LLM Call", expanded = True) as status: prompt_template = f""" Using the below context information respond to the user query. context: '{properties_list}' query: '{user_query}' Response structure should look like this: *Detailed Response* *Relevant Details in Table Format* Also, generate the latitude and longitude for all the properties included in the response in JSON object format. For example, if there are properties at 40.761255, -73.974483 and 40.7844489, -73.9807532, the JSON object should look like this limited with 3 backticks. JUST OUTPUT THE JSON, NO NEED TO INCLUDE ANY TITLE OR TEXT BEFORE IT: ```[ {{ "latitude": 40.761255, "longitude": -73.974483 }}, {{ "latitude": 40.7844489, "longitude": -73.9807532 }} ]``` """ llm_response = llm.complete(prompt_template) response_parts = llm_response.text.split('```') st.markdown(response_parts[0]) elif selection == 'Advanced: Qdrant Similarity Search + Llamaindex Text-to-SQL': #Part 2, Semantic Search + Text-to-SQL with st.status("Advanced Method: Generating response based on Qdrant Similarity Search + Llamaindex Text-to-SQL", expanded = True): df2 = df.drop('text', axis=1) #Create a SQLite database and engine engine = create_engine("sqlite:///NY_House_Dataset.db?mode=ro", connect_args={"uri": True}) sql_database = SQLDatabase(engine) #Convert the DataFrame to a SQL table within the SQLite database df2.to_sql('housing_data_sql', con=engine, if_exists='replace', index=False) #Build sql query engine sql_query_engine = NLSQLTableQueryEngine( sql_database=sql_database ) vector_store_info = VectorStoreInfo( content_info="Housing data details for NY", metadata_info = [ MetadataInfo(name="BROKERTITLE", type="str", description="Title of the broker"), MetadataInfo(name="TYPE", type="str", description="Type of the house"), MetadataInfo(name="PRICE", type="float", description="Price of the house"), MetadataInfo(name="BEDS", type="int", description="Number of bedrooms"), MetadataInfo(name="BATH", type="float", description="Number of bathrooms"), MetadataInfo(name="PROPERTYSQFT", type="float", description="Square footage of the property"), MetadataInfo(name="ADDRESS", type="str", description="Full address of the house"), MetadataInfo(name="STATE", type="str", description="State of the house"), MetadataInfo(name="MAIN_ADDRESS", type="str", description="Main address information"), MetadataInfo(name="ADMINISTRATIVE_AREA_LEVEL_2", type="str", description="Administrative area level 2 information"), MetadataInfo(name="LOCALITY", type="str", description="Locality information"), MetadataInfo(name="SUBLOCALITY", type="str", description="Sublocality information"), MetadataInfo(name="STREET_NAME", type="str", description="Street name"), MetadataInfo(name="LONG_NAME", type="str", description="Long name of the house"), MetadataInfo(name="FORMATTED_ADDRESS", type="str", description="Formatted address"), MetadataInfo(name="LATITUDE", type="float", description="Latitude coordinate of the house"), MetadataInfo(name="LONGITUDE", type="float", description="Longitude coordinate of the house"), ], ) vector_auto_retriever = VectorIndexAutoRetriever( index, vector_store_info=vector_store_info ) retriever_query_engine = RetrieverQueryEngine.from_args( vector_auto_retriever, service_context=service_context ) sql_tool = QueryEngineTool.from_defaults( query_engine=sql_query_engine, description=( "Useful for translating a natural language query into a SQL query over" " a table 'houses', containing prices of New York houses, providing valuable insights into the real estate market in the region. It includes information such as broker titles, house types, prices, number of bedrooms and bathrooms, property square footage, addresses, state, administrative and local areas, street names, and geographical coordinates." ), ) vector_tool = QueryEngineTool.from_defaults( query_engine=retriever_query_engine, description=( f"Useful for answering questions about different housing listings in New York. Use this to refine your answers" ), ) query_engine = SQLAutoVectorQueryEngine( sql_tool, vector_tool, service_context=service_context ) response = query_engine.query(f"{user_query}+. Provide a detailed response and include lONG_NAME, PRICE, name of broker, number of beds, number of baths, propertysqft and FORMATTED_ADDRESS. ALWAYS USE LIKE in WHERE CLAUSE. ALWAYS RESPOND IN WELL FORMATTED MARKDOWN") st.markdown(response.response)