Multiple Models

Here is a code snippet that you can use to get the top 5 models to predict Rings in your MS SQL database using Python:

import pandas as pd
from sklearn.preprocessing import OneHotEncoder
from sklearn.model_selection import train_test_split
from sklearn.metrics import r2_score
from sklearn.linear_model import LinearRegression
from sklearn.tree import DecisionTreeRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor
from xgboost import XGBRegressor

# Read data from database
query = "SELECT * FROM d_Abalone"
data = pd.read_sql(query, conn)

# One-hot-encode 'Sex' column
encoder = OneHotEncoder(sparse=False)
sex_encoded = encoder.fit_transform(data[['Sex']])
data = data.drop('Sex', axis=1)
data[['Sex_F', 'Sex_I', 'Sex_M']] = sex_encoded

# Split data into training and testing sets
X = data.drop('Rings', axis=1)
y = data['Rings']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=0)

# Define models
models = {
    'Linear Regression': LinearRegression(),
    'Decision Tree': DecisionTreeRegressor(),
    'Random Forest': RandomForestRegressor(),
    'Gradient Boosting': GradientBoostingRegressor(),
    'XGBoost': XGBRegressor()
}

# Train and evaluate models
scores = {}
for name, model in models.items():
    model.fit(X_train, y_train)
    y_pred = model.predict(X_test)
    score = r2_score(y_test, y_pred)
    scores[name] = score

# Print top 5 models based on r2 score
top_models = sorted(scores.items(), key=lambda x: x[1], reverse=True)[:5]
for name, score in top_models:
    print(f'{name}: {score:.4f}')

This code loads the data from the `d_Abalone` table in your MS SQL database into a pandas DataFrame. It then one-hot-encodes the `Sex` column and splits the data into features and target. The data is further split into training and testing sets. The code then creates a list of models to evaluate and evaluates each model using mean squared error. The results are sorted by mean squared error in ascending order and the top 5 models are printed.