[Python] Why am I getting an empty prediction plot?

Trying to visualise predicted balance of next year based on values of current year and save the graph as a PDF file, I am getting an empty prediction plot:

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime

def get_columns(db_path):
"""Print out the columns of the Transactions table to help identify the correct column for amounts."""
with sqlite3.connect(db_path) as conn:
cursor = conn.cursor()
cursor.execute("PRAGMA table_info(Transactions);")
columns = [row[1] for row in cursor.fetchall()]
print("Columns in Transactions table:", columns) # Debugging line to print all columns
return columns

def fetch_monthly_data(db_path):
"""Fetch monthly income and expenses data from the SQLite database."""
with sqlite3.connect(db_path) as conn:
# Check column names dynamically
columns = get_columns(db_path)

# Set 'amount_in_eur' as the correct column name for amounts (if available)
amount_column = "amount_in_eur"

# Check if the 'amount_in_eur' column exists
if amount_column not in columns:
print(f"'{amount_column}' column not found. Available columns: {columns}")
raise ValueError(f"The column '{amount_column}' was not found in the Transactions table.")

query = f"""
SELECT date, SUM({amount_column}) as amount
FROM Transactions
GROUP BY date
ORDER BY date DESC
LIMIT 90 -- Last 3 months approx
"""
df = pd.read_sql_query(query, conn)

# Convert date to datetime
df['date'] = pd.to_datetime(df['date'], errors='coerce')
df.dropna(subset=['date'], inplace=True)
df['date'] = df['date'].dt.tz_localize(None)

# Aggregate to monthly data
df['month'] = df['date'].dt.to_period('M')
monthly_data = df.groupby(['month'])['amount'].sum().to_frame()

# Debugging: Check if the monthly data is empty
print("Monthly Data:\n", monthly_data)

# Convert period index to timestamp for plotting
monthly_data.index = monthly_data.index.to_timestamp()

return monthly_data

def plot_prediction(monthly_data, predicted_balance):
"""Plot historical data and predicted balance."""
if monthly_data.empty:
print("No monthly data available. Exiting.")
return

plt.figure(figsize=(10, 6))
plt.plot(monthly_data.index, monthly_data['amount'], label='Historical Data', linestyle='-', linewidth=2)
plt.plot(predicted_balance.index, predicted_balance, label='Predicted Balance', linestyle='-', linewidth=2)
plt.xlabel('Date')
plt.ylabel('Balance')
plt.title('Predicted Balance for the Next Year')
plt.legend()
plt.grid(True)

# Save the plot
date_time = datetime.now().strftime('%Y%m%d_%H%M%S')
plt.savefig(f'{date_time}_prediction.pdf')
print(f"Prediction plot saved as {date_time}_prediction.pdf")

def main():
db_path = r"D:\Python\pythonProject\Project\Project\98661737.sqlite"

# Fetch data
try:
monthly_data = fetch_monthly_data(db_path)
except ValueError as e:
print(e)
return

# Debugging: Check if the monthly data is empty
if monthly_data.empty:
print("No monthly data available. Exiting.")
return

# Predict balance (example current balance set)
current_balance = 16000

predicted_balance = monthly_data['amount'].cumsum().fillna(0) + current_balance

# Debugging print
print("Predicted Balance:\n", predicted_balance)

# Generate plots
plot_prediction(monthly_data, predicted_balance)

if __name__ == "__main__":
main()

Continue reading...