[Python] How to calculate attrition using data from another file not loaded in the app?

I have two Excel files:

leave_membership.xlsx – contains columns: name, year, gym_location

avg_membership.xlsx – contains columns: gym_location, avg_members_2024, avg_members_2023, avg_members_2022

In my app, only leave_membership.xlsx is loaded by default. However, when a user queries something related to attrition, I want the app to use the avg_membership.xlsx data for the calculation.

Specifically, attrition should be calculated as: attrition_rate = number_of_leavers / avg_members_(based on what year user queries)

Since avg_members is only available in avg_membership.xlsx, I want to know how to incorporate that file into the calculation dynamically or behind the scenes when needed.

Here is my code:

file_path = "C:/Users/0012/Desktop/Project/stream/leave_membership.txt"
file_path3 = "C:/Users/0012/Desktop/avg_membership.xlsx"
df = pd.read_excel(file_path)
df3 = pd.read_excel(file_path3)
llm = OpenAI(api_token=OPENAI_API_KEY)
pandas_ai = SmartDataframe(df, config={"llm": llm, "conversational": True})


with tab1:

# AI Query section
st.subheader("Ask Questions")

if Suggestions1:
query = st.selectbox(
"Enter your question:",
options=Suggestions1,
index=0,
placeholder="Example: What is the average value in column X?",
accept_new_options=True)
else:
st.warning("No suggestions available to show in dropdown.")

if st.button("Run"):
if query:
with st.spinner("AI is analyzing..."):
try:
# Run the query
st.subheader("Analysis Result")
response = pandas_ai.chat(query+"Show in table.")

# Check if the response is a DataFrame
if isinstance(response, pd.DataFrame):
st.dataframe(response, use_container_width=True)
response_to_save = "DataFrame result displayed"
elif isinstance(response, pd.Series):
st.dataframe(response.to_frame(), use_container_width=True)
response_to_save = "Series result displayed"
else:
st.write(response)
response_to_save = str(response)

# Check for generated charts
chart_generated = False
if plt.get_fignums():
st.subheader("Generated Chart")
st.pyplot(plt.gcf())
plt.clf()
chart_generated = True

# Add to chat history
timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
entry_id = str(uuid.uuid4())
st.session_state.chat_history.append({
"id": entry_id,
"timestamp": timestamp,
"query": query,
"response": response_to_save,
"had_chart": chart_generated
})

except Exception as e:
error_msg = f"An error occurred: {str(e)}"
st.error(error_msg)

# Add error to chat history
timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
entry_id = str(uuid.uuid4())
st.session_state.chat_history.append({
"id": entry_id,
"timestamp": timestamp,
"query": query,
"response": f"ERROR: {error_msg}",
"had_chart": False
})

Continue reading...