销售数据预测

好的，我会分步骤详细指导如何完成这一任务，并提供Python代码供参考。以下是步骤的详细说明和代码实现：

---

### **步骤 1：从MySQL中加载数据至Pandas DataFrame**
我们需要连接到MySQL数据库，读取所需数据，并限制结果为1000行。假设数据库包含一个名为`sales_data`的表。

```python
import pandas as pd
import pymysql
from sqlalchemy import create_engine

# 定义数据库连接信息
db_config = {
    'user': 'your_username',       # 替换为数据库用户名
    'password': 'your_password',   # 替换为数据库密码
    'host': 'your_hostname',       # 替换为数据库主机地址
    'database': 'your_database'    # 替换为目标数据库名称
}

# 创建数据库连接
engine = create_engine(f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}")

# 从数据库中读取数据并限制为1000行
query = """
SELECT product_id, price, quantity_sold, discount, region, revenue
FROM sales_data
LIMIT 1000;
"""

# 使用pandas读取数据
data = pd.read_sql(query, con=engine)

# 检查前几行数据的加载情况
print(data.head())
```

---

### **步骤 2：划分训练集和测试集**
根据需求，前800行作为训练集，其余200行作为测试集，同时指定特征列和目标列。

```python
# 特征列和目标列
features = ["product_id", "price", "quantity_sold", "discount", "region"]
target = "revenue"

# 将前800行作为训练集
df_train = data.iloc[:800, :]
df_test = data.iloc[800:, :]

# 分离训练集的特征和目标列
X_train = df_train[features]
y_train = df_train[target]

# 分离测试集的特征和目标列
X_test = df_test[features]
y_test = df_test[target]

print(f"训练集大小: {X_train.shape}, 测试集大小: {X_test.shape}")
```

---

### **步骤 3：特征编码**
由于`product_id`和`region`等特征可能是分类变量，因此需要对这些特征进行编码。例如，我们可以使用`pandas.get_dummies()`进行独热编码。

```python
# 对分类特征进行独热编码
X_train_encoded = pd.get_dummies(X_train, columns=["product_id", "region"])
X_test_encoded = pd.get_dummies(X_test, columns=["product_id", "region"])

# 确保训练集和测试集的特征列对齐（必要时补上缺失列）
X_train_encoded, X_test_encoded = X_train_encoded.align(X_test_encoded, join="left", axis=1, fill_value=0)

print(f"训练集特征编码后大小: {X_train_encoded.shape}")
```

---

### **步骤 4：训练随机森林回归模型**
使用`sklearn`的`RandomForestRegressor`对训练集进行模型训练。

```python
from sklearn.ensemble import RandomForestRegressor

# 初始化随机森林回归模型
rf_model = RandomForestRegressor(random_state=42, n_estimators=100)

# 训练模型
rf_model.fit(X_train_encoded, y_train)

print("模型训练完成！")
```

---

### **步骤 5：评估模型性能**
用模型对测试集进行预测，并基于真实值计算平均绝对误差（MAE）。

```python
from sklearn.metrics import mean_absolute_error

# 预测测试集的目标值
y_pred = rf_model.predict(X_test_encoded)

# 计算并打印平均绝对误差(MAE)
mae = mean_absolute_error(y_test, y_pred)
print(f"测试集上的平均绝对误差 (MAE): {mae}")

# 将预测结果添加到测试集DataFrame中
df_test['predicted_revenue'] = y_pred
print(df_test.head())
```

---

### **步骤 6：保存模型至文件**
将训练好的模型保存为`sales_prediction_model.pkl`文件，若文件已存在，直接覆盖。

```python
import joblib

# 保存模型到文件
model_filename = "sales_prediction_model.pkl"
joblib.dump(rf_model, model_filename)

print(f"模型已保存为 {model_filename}")
```

---

### **完整代码整理**
将上述步骤整合后，完整代码如下：

```python
import pandas as pd
from sqlalchemy import create_engine
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_absolute_error
import joblib

# 连接数据库并加载数据
db_config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_hostname',
    'database': 'your_database'
}
engine = create_engine(f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}")
query = """
SELECT product_id, price, quantity_sold, discount, region, revenue
FROM sales_data
LIMIT 1000;
"""
data = pd.read_sql(query, con=engine)

# 划分训练集和测试集
features = ["product_id", "price", "quantity_sold", "discount", "region"]
target = "revenue"
df_train = data.iloc[:800, :]
df_test = data.iloc[800:, :]
X_train = df_train[features]
y_train = df_train[target]
X_test = df_test[features]
y_test = df_test[target]

# 对分类特征进行独热编码
X_train_encoded = pd.get_dummies(X_train, columns=["product_id", "region"])
X_test_encoded = pd.get_dummies(X_test, columns=["product_id", "region"])
X_train_encoded, X_test_encoded = X_train_encoded.align(X_test_encoded, join="left", axis=1, fill_value=0)

# 训练随机森林回归模型
rf_model = RandomForestRegressor(random_state=42, n_estimators=100)
rf_model.fit(X_train_encoded, y_train)

# 测试集预测与评估
y_pred = rf_model.predict(X_test_encoded)
mae = mean_absolute_error(y_test, y_pred)
print(f"测试集上的平均绝对误差 (MAE): {mae}")

# 增加预测结果到测试集
df_test['predicted_revenue'] = y_pred
print(df_test.head())

# 保存模型
model_filename = "sales_prediction_model.pkl"
joblib.dump(rf_model, model_filename)
print(f"模型已保存为 {model_filename}")
```

运行此代码前请确保已安装相关依赖库，如`pandas`、`sqlalchemy`、`sklearn`、`pymysql`等。通过以上步骤，你可以轻松完成从数据库加载数据到训练和评估回归模型的全流程！

下面是完成该任务的代码，步骤划分得当并附有清晰的解释：

```python
import pandas as pd
import pymysql  # MySQL连接库
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_absolute_error
from sklearn.model_selection import train_test_split
import pickle  # 用于保存模型文件

# 第一步：从MySQL数据库加载数据
def load_data_from_mysql(host, user, password, database, table):
    """
    从MySQL数据库读取数据至DataFrame，限制为20000行。
    """
    connection = pymysql.connect(host=host, user=user, password=password, database=database)
    query = f"SELECT * FROM {table} LIMIT 20000"
    df = pd.read_sql(query, connection)
    connection.close()
    return df

# 第二步：准备训练集和测试集
def prepare_data(df, features, target):
    """
    拆分数据为训练集和测试集，返回特征和目标列。
    """
    # 划分训练和测试集：前16000行为训练集，剩余为测试集
    df_train = df.iloc[:16000]
    df_test = df.iloc[16000:]
    
    # 提取特征和目标
    X_train = df_train[features]
    y_train = df_train[target]
    X_test = df_test[features]
    y_test = df_test[target]
    
    return X_train, y_train, X_test, y_test, df_test

# 第三步：训练随机森林回归模型
def train_random_forest(X_train, y_train):
    """
    训练随机森林回归模型。
    """
    rf_model = RandomForestRegressor(random_state=42, n_estimators=100)
    rf_model.fit(X_train, y_train)
    return rf_model

# 第四步：评估模型性能并输出结果
def evaluate_model(rf_model, X_test, y_test, df_test):
    """
    使用测试集评估模型性能，计算MAE并输出预测结果。
    """
    predictions = rf_model.predict(X_test)
    mae = mean_absolute_error(y_test, predictions)
    print(f"模型的平均绝对误差（MAE）：{mae:.2f}")
    
    # 在测试集DataFrame中添加“预测值”列
    df_test["predicted_revenue"] = predictions
    return mae, df_test

# 第五步：保存模型到文件
def save_model(rf_model, filename="analysis_model_v2.pkl"):
    """
    保存模型为文件。如果文件已存在，则覆盖。
    """
    with open(filename, 'wb') as model_file:
        pickle.dump(rf_model, model_file)
    print(f"模型已保存为文件：{filename}")

# 主程序执行流程
def main():
    # MySQL连接信息
    host = "localhost"  # 数据库主机
    user = "root"  # 用户名
    password = "your_password"  # 请替换为实际密码
    database = "your_database"  # 数据库名
    table = "your_table"  # 数据表名

    # 设定特征列和目标列
    features = ["product_id", "quantity_sold", "region"]
    target = "revenue"

    # 1. 从MySQL加载数据
    df = load_data_from_mysql(host=host, user=user, password=password, database=database, table=table)
    
    # 2. 拆分数据集并准备特征和目标
    X_train, y_train, X_test, y_test, df_test = prepare_data(df, features, target)
    
    # 3. 训练随机森林回归模型
    rf_model = train_random_forest(X_train, y_train)
    
    # 4. 评估模型性能
    mae, df_test_with_predictions = evaluate_model(rf_model, X_test, y_test, df_test)
    
    # 5. 保存模型到文件
    save_model(rf_model)

    # 输出包含预测结果的DataFrame
    print("包含测试集预测结果的DataFrame：")
    print(df_test_with_predictions.head())

if __name__ == "__main__":
    main()
```

---

### 代码解释：

1. **加载数据**：
   - 使用`pymysql`库连接MySQL数据库，并读取指定表的前20000行记录至DataFrame。

2. **拆分训练集与测试集**：
   - 按行号，将前16000行作为`df_train`训练数据，剩余数据为`df_test`测试数据。
   - 提取特征列`["product_id", "quantity_sold", "region"]`和目标列`"revenue"`。

3. **训练模型**：
   - 使用`RandomForestRegressor`训练随机森林回归模型。
   - 设置`random_state=42`以确保运行结果可复现。

4. **评估模型性能**：
   - 使用MAE（平均绝对误差）评估模型性能，这是一个简单易懂的回归误差度量标准。
   - 将预测结果添加到测试集`df_test`，以便输出最终包含预测值的DataFrame。

5. **保存和输出**：
   - 使用`pickle`将训练好的模型以二进制文件形式保存到本地。
   - 如果文件已存在，会自动覆盖保存。

---

### 运行本代码前的准备工作：

1. 安装所需Python库：
   ```bash
   pip install pandas pymysql scikit-learn
   ```

2. 连接MySQL数据库时，确保你有权限访问，并将`host`、`user`、`password`、`database`和`table`替换为实际值。

3. 确保表中包含指定的特征列和目标列：`["product_id", "quantity_sold", "region"]` 和 `revenue`。

运行代码后，您将获得以下内容：
- **MAE值**：评估模型性能的数字。
- **df_test包含预测结果**：测试集DataFrame，新增一列`predicted_revenue`。
- **保存的模型文件**：`analysis_model_v2.pkl`。

希望这对您有所帮助！如果有更多问题，请随时询问。

好的！以下是完整的 Python 步骤指南，用于从 MySQL 加载数据、训练随机森林回归模型并评估其性能：

---

### 1. 准备必要的库并连接MySQL数据库
你需要安装以下库：
- `pandas`：用来操作数据
- `pymysql`：用来连接到 MySQL 数据库
- `sklearn`：处理机器学习模型
- `joblib`：保存模型到文件
```
pip install pandas sklearn pymysql joblib
```

---

### 2. Python 代码实现

```python
import pandas as pd
import pymysql
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_absolute_error
from sklearn.model_selection import train_test_split
import joblib

# Step 1: 从MySQL加载数据
def load_data_from_mysql(sql_query, db_config):
    """
    从MySQL数据库加载数据
    Args:
        sql_query (str): SQL 查询语句。
        db_config (dict): 包含数据库连接参数，例如 {host, user, password, database}
    Returns:
        pandas.DataFrame: 包含加载的数据
    """
    connection = pymysql.connect(
        host=db_config['host'],
        user=db_config['user'],
        password=db_config['password'],
        database=db_config['database']
    )
    try:
        print("Connecting to database...")
        df = pd.read_sql_query(sql_query, connection)
    finally:
        connection.close()

    return df


# Step 2: 训练随机森林回归模型
def train_model(df_train, feature_columns, target_column):
    """
    训练随机森林回归模型
    Args:
        df_train (DataFrame): 包含训练数据
        feature_columns (list): 特征列名
        target_column (str): 目标列名
    Returns:
        RandomForestRegressor: 训练好的模型对象
    """
    X_train = df_train[feature_columns]
    y_train = df_train[target_column]
    
    print("Training the random forest model...")
    model = RandomForestRegressor(n_estimators=100, random_state=42)
    model.fit(X_train, y_train)
    
    return model


# Step 3: 使用模型预测并计算性能
def evaluate_model(model, df_test, feature_columns, target_column):
    """
    使用模型进行预测，并评估性能
    Args:
        model: 训练好的随机森林回归模型
        df_test (DataFrame): 测试数据
        feature_columns (list): 特征列名
        target_column (str): 目标列名
    Returns:
        tuple: 包含 MAE 值和带预测结果的数据框
    """
    X_test = df_test[feature_columns]
    y_test = df_test[target_column]
    
    print("Making predictions on test data...")
    y_pred = model.predict(X_test)

    print("Calculating Mean Absolute Error...")
    mae = mean_absolute_error(y_test, y_pred)
    
    # 创建带有预测结果的新DataFrame
    df_results = df_test.copy()
    df_results['predicted_effectiveness'] = y_pred
    
    return mae, df_results


# Step 4: 主程序
if __name__ == "__main__":
    db_config = {
        "host": "your_host",
        "user": "your_user",
        "password": "your_password",
        "database": "your_database"
    }
    feature_columns = ["category", "price", "discount", "region", "promo_activity"]
    target_column = "promo_effectiveness"
    limit = 15000

    # 从MySQL读取数据
    query = f"SELECT * FROM your_table_name LIMIT {limit}"
    data = load_data_from_mysql(query, db_config)

    # 将前12000行作为训练集，剩下的作为测试集
    df_train = data.iloc[:12000]
    df_test = data.iloc[12000:]
    
    # 检查缺失值和数据清洗
    if data.isnull().sum().any():
        print("Data contains missing values. Please clean your data appropriately.")
    else:
        print("No missing values in data.")

    # 训练随机森林模型
    model = train_model(df_train, feature_columns, target_column)

    # 评估模型
    mae, df_results = evaluate_model(model, df_test, feature_columns, target_column)
    print(f"Mean Absolute Error (MAE) on test set: {mae:.2f}")

    # 保存模型到文件
    model_filename = "ecommerce_prediction_model.pkl"
    print(f"Saving model to {model_filename}...")
    joblib.dump(model, model_filename)
    print("Model saved successfully.")

    # 输出包含预测的结果DataFrame
    print("Predicted DataFrame:")
    print(df_results.head())
```

---

### 3. 代码解释

1. **从MySQL数据库读取数据**：
   使用 `pandas.read_sql_query` 来从 MySQL 数据库读取数据。使用 SQL 限制 (`LIMIT 15000`) 来控制数据量。

2. **训练集与测试集划分**：
   将数据集拆分为前12000行 (训练集) 和剩余的 (测试集)，以确保模型不使用见过的数据来评估。

3. **数据预处理**：
   - 清洗数据（检查缺失值、重复值等）。
   - 确保特征列为数值，可能需要进行编码或其他类型转换。

4. **训练随机森林模型**：
   用 `RandomForestRegressor` 创建并训练模型。

5. **模型预测与评估**：
   使用测试集预测并计算均值绝对误差（MAE）。

6. **保存模型到文件**：
   用 `joblib.dump` 保存模型，方便将来使用。

7. **输出带预测结果的新DataFrame**：
   将预测结果插入到 `df_test`，以便轻松查看并分析模型的表现。

---

### 4. 注意事项
- 确保 MySQL 数据库中的字段与代码中定义的列 (`feature_columns` 和 `target_column`) 一致。
- 如果特征列包含分类数据，需要将其转换为数值（可以用 Pandas 的 `get_dummies()` 或 `LabelEncoder`）。

希望这个方法清晰简洁，如果有任何问题，欢迎提问！