大数据集特征工程实践：将54万样本预测误差降低68%的技术路径与代码实现详解

 import os  
 import pandas as pd  
   
 df = pd.read_csv(csv_file_path)  
 df.info()

 import numpy as np  

obj_cols = ['invoiceno', 'stockcode', 'country', 'invoicedate']  

# 定义潜在的缺失值表示  
null_vals = ['', 'nan', 'N/A', None, 'na', 'None', 'none']  
replaced_null = { item: np.nan for item in null_vals }  

for col in obj_cols:  
    df[col].replace(replaced_null, inplace=True)  

 df.info()

 for i, col in enumerate(df.columns):  
     unique_num = df[col].nunique()  
     nan_num = df[col].isna().sum()  
     print(f'{i}. {col} - {unique_num:,} data points (missing data: {nan_num:,})')

 # 在特征工程前复制基础数据集  
 df_rev = df.copy()  
   
 df_rev = df_rev.drop(columns='description')  
 df_rev['is_registered'] = np.where(df_rev['customerid'].isna(), 0, 1)

 import pandas as pd  
   
 df_rev['invoicedate'] = pd.to_datetime(df_rev['invoicedate'])  
 df_rev['customerid'] = df_rev['customerid'].astype('Int64')  
 df_rev.info()

 df_rev['invoicedate'] = pd.to_datetime(df_rev['invoicedate'])  
 df_rev['year'] = df_rev['invoicedate'].dt.year  
 df_rev['year_month'] = df_rev['invoicedate'].dt.to_period('M')  
 df_rev['month_name'] = df_rev['invoicedate'].dt.strftime('%b')  
 df_rev['day_of_week'] = df_rev['invoicedate'].dt.strftime('%a')  
 df_rev = df_rev.sort_values('invoicedate')

 df_rev['sales'] = df_rev['quantity'] * df_rev['unitprice']

 import pandas as pd  

# df_fin将是模型训练的主要数据集  
df_fin = df_rev.copy()  

# 创建临时数据集  
_df_prod_month_agg = df_fin.copy().groupby(['stockcode', 'year_month']).agg(  
    prod_total_monthly_quantity=('quantity', 'sum'),  
    prod_ave_monthly_price=('unitprice', 'mean')  
).reset_index().sort_values(by=['stockcode', 'year_month'])  

_df_prod_month_agg['unit_price_bin'] = pd.qcut(  
    _df_prod_month_agg['prod_ave_monthly_price'],  
    q=3,  
    labels=['low', 'mid', 'high'],  
    duplicates='drop'  
)  

_df_prod_bin_per_stockcode = _df_prod_month_agg.groupby('stockcode')['unit_price_bin'].agg(  
    lambda x: x.mode()[0] if not x.mode().empty else None  
).reset_index()  

# 合并到主数据集(df_fin)  
df_fin = pd.merge(  
    df_fin,  
    _df_prod_bin_per_stockcode[['stockcode', 'unit_price_bin']],  
    on='stockcode',  
    how='left'  
)  

 df_fin.info()

 import pandas as pd  

_df_prod_month_agg['product_avg_quantity_last_month'] = _df_prod_month_agg.groupby('stockcode')['prod_total_monthly_quantity'].shift(1)  
_df_prod_last_month_agg = _df_prod_month_agg.groupby('stockcode')['product_avg_quantity_last_month'].mean().reset_index()  
df_fin = pd.merge(  
    df_fin,  
    _df_prod_last_month_agg [['stockcode', 'product_avg_quantity_last_month']],  
    on='stockcode',  
    how='left'  
)  

# 缺失数据表示期间内没有产品销售，用零填充  
df_fin['product_avg_quantity_last_month'] = df_fin['product_avg_quantity_last_month'].fillna(value=0)  
 df_fin.info()

 import pandas as pd  

# 创建临时数据集   
_df_all_customers_year_month = pd.MultiIndex.from_product(  
    [df_fin['customerid'].unique(), df_fin['year_month'].unique()], # type: ignore  
    names=['customerid', 'year_month']  
).to_frame(index=False).sort_values(by=['customerid', 'year_month']).reset_index(drop=True)  

_df_customer_monthly_agg = df_fin.copy().groupby(['customerid', 'year_month']).agg(  
    monthly_sales=('sales', 'sum'),  
    monthly_unique_invoices=('invoiceno', 'nunique'),  
    monthly_last_purchase_date=('invoicedate', 'max')  
).reset_index()  

_df_cus = _df_all_customers_year_month.merge(_df_customer_monthly_agg, on=['customerid', 'year_month'], how='left').sort_values(by=['customerid', 'year_month'])  

# 添加时间戳  
_df_cus['pfin_last_purchase_date'] = _df_cus.groupby('customerid')['monthly_last_purchase_date'].shift(1)  
_df_cus['invoice_timestamp_end'] = _df_cus['year_month'].dt.end_time  

# 计算近期度天数  
_df_cus['customer_recency_days'] = (_df_cus['invoice_timestamp_end'] - _df_cus['pfin_last_purchase_date']).dt.days  

# 合并和填充  
df_fin['customer_recency_days'] = _df_cus['customer_recency_days']  

max_recency = _df_cus['customer_recency_days'].max()  
df_fin['customer_recency_days'] = df_fin['customer_recency_days'].fillna(value=max_recency + 30)  

 df_fin.info()

 _df_cus['customer_total_spend_ltm'] = _df_cus.groupby('customerid')['monthly_sales'].rolling(window=3, closed='left').sum().reset_index(level=0, drop=True)  
   
 df_fin['customer_total_spend_ltm'] = _df_cus['customer_total_spend_ltm']  
 df_fin['customer_total_spend_ltm'] = df_fin['customer_total_spend_ltm'].fillna(value=0)  
   
 df_fin.info()

 _df_cus['customer_freq_ltm'] = _df_cus.groupby('customerid')['monthly_unique_invoices'].rolling(window=3, closed='left').sum().reset_index(level=0, drop=True)  
   
 df_fin['customer_freq_ltm'] = _df_cus['customer_freq_ltm']  
 df_fin['customer_freq_ltm'] = df_fin['customer_freq_ltm'].fillna(value=0)  
   
 df_fin.info()

 df_fin.isna().sum()

 df_null=df_fin[df_fin['stockcode'].isnull()]  
 df_null.head().transpose()

 df_fin['stockcode'] = df_fin['stockcode'].fillna(value='unknown')  
 df_fin['unit_price_bin'] = df_fin['unit_price_bin'].fillna(value='low')

 import numpy as np  
   
 df_fin['country'] = df_fin['country'].fillna(value=df_fin['country'].mode().iloc[0])   
 df_fin['quantity'] = df_fin['quantity'].fillna(value=np.floor(df_fin['sales'] / df_fin['unitprice']))

 df_fin['year_month'] = df_fin['year_month'].dt.month  
 df_fin['invoicedate'] = df_fin['invoicedate'].astype(int) / 10 ** 9  
 df_fin = df_fin.drop(columns=['month_name'], axis='columns')  
   
 df_fin.info()

 cat_cols = [  
    'invoiceno',  
    'stockcode',  
    'quantity',  
    'customerid',  
    'country',  
    'year',  
    'year_month',  
    'day_of_week',  
    'is_registered',  
    'unit_price_bin',  
    'customer_recency_days',  
]  
num_cols = [  
    'unitprice',  
    'product_avg_quantity_last_month',  
    'customer_total_spend_ltm',  
    'customer_freq_ltm',  
    'invoicedate'  
]  

 target_col = 'sales'

 fromsklearn.model_selectionimporttrain_test_split  

target_col='sales'  
X=df_fin.copy().drop(columns=target_col)  
y=df_fin.copy()[target_col]  

test_size=50000  
X_tv, X_test, y_tv, y_test=train_test_split(X, y, test_size=test_size, random_state=42)  
 X_train, X_val, y_train, y_val=train_test_split(X_tv, y_tv, test_size=test_size, random_state=42)

 from sklearn.preprocessing import RobustScaler  
from sklearn.compose import ColumnTransformer  
from sklearn.pipeline import Pipeline  
from category_encoders import BinaryEncoder  


# 数值特征处理管道  
num_transformer = Pipeline(steps=[  
    ('scaler', RobustScaler(with_centering=True, with_scaling=True))  
])  

# 分类特征处理管道  
cat_transformer = Pipeline(steps=[  
    ('encoder', BinaryEncoder(cols=cat_cols, handle_missing='0'))  
])  

# 定义预处理器  
preprocessor_en = ColumnTransformer(  
    transformers=[  
        ('num', num_transformer, num_cols),  
        ('cat', cat_transformer, cat_cols)  
    ],  
    remainder='passthrough',  
)  

# 数据转换  
X_train_processed = preprocessor_en.fit_transform(X_train)  
X_val_processed = preprocessor_en.transform(X_val)  
X_test_processed = preprocessor_en.transform(X_test)  

# 模型初始化和训练  
from sklearn.linear_model import ElasticNet  
elastic_net = ElasticNet(  
    alpha=1,            # 正则化总强度  
    l1_ratio=0.5,       # l1与l2比例 = 1:1  
    fit_intercept=True, # 通过计算拟合y截距  
    precompute=False,   # 不使用预计算的Gram矩阵  
    max_iter=5000,      # 5000轮迭代  
    copy_X=True,        # 在拟合前复制X  
    tol=1e-5,           # 停止迭代的容差  
    random_state=42,    # 随机数生成器种子  
    warm_start=False,   # 忽略前一次拟合调用的解  
    positive=False,     # 可以有负和正系数  
    selection="cyclic"  # 循环地逐个更新系数（与随机相对）  
 ).fit(X_train_processed, y_train)

 from sklearn.compose import ColumnTransformer  
from sklearn.pipeline import Pipeline  
from sklearn.ensemble import RandomForestRegressor  
from category_encoders import BinaryEncoder  

# 定义预处理器  
cat_transformer = Pipeline(steps=[  
    ('encoder', BinaryEncoder(cols=cat_cols, handle_missing='0'))  
])  

preprocessor_rf = ColumnTransformer(  
    transformers=[  
        ('cat', cat_transformer, cat_cols)  
    ],  
    remainder='passthrough',  
)  


# 数据转换  
X_train_processed = preprocessor_rf.fit_transform(X_train)  
X_val_processed = preprocessor_rf.transform(X_val)  
X_test_processed = preprocessor_rf.transform(X_test)  


# 模型初始化和训练  
random_forest = RandomForestRegressor(  
    n_estimators=1000,  
    criterion="squared_error",  
    max_depth=None,  
    min_samples_split=2,  
    min_samples_leaf=1,  
    min_weight_fraction_leaf=0,  
    max_features='sqrt',  
    max_leaf_nodes=None,  
    min_impurity_decrease=1e-10,  
    bootstrap=True,  
    oob_score=True,  
    n_jobs=-1,  
    random_state=42,  
    verbose=0,  
    warm_start=False,  
    ccp_alpha=0,  
    max_samples=None,  
 ).fit(X_train_processed, y_train)

 import torch  
from torch.utils.data import DataLoader, TensorDataset  
from sklearn.preprocessing import StandardScaler  
from sklearn.compose import ColumnTransformer  
from sklearn.pipeline import Pipeline  
from category_encoders import BinaryEncoder  

num_transformer = Pipeline(steps=[('scaler', StandardScaler())])  
cat_transformer = Pipeline(steps=[('encoder', BinaryEncoder(cols=cat_cols, handle_missing='0'))])  

# 定义预处理器  
preprocessor_dfn = ColumnTransformer(  
    transformers=[  
        ('num', num_transformer, num_cols),  
        ('cat', cat_transformer, cat_cols)  
    ],  
    remainder='passthrough'  
)  

# 数据转换  
X_train_processed_dfn = preprocessor_dfn.fit_transform(X_train)  
X_val_processed_dfn = preprocessor_dfn.transform(X_val)  
X_test_processed_dfn = preprocessor_dfn.transform(X_test)  


# 将NumPy数组转换为PyTorch张量  
X_train_tensor = torch.tensor(X_train_processed_dfn, dtype=torch.float32)  
X_val_tensor = torch.tensor(X_val_processed_dfn, dtype=torch.float32)  
X_test_tensor = torch.tensor(X_test_processed_dfn, dtype=torch.float32)  

# 转换1D张量  
y_train_tensor = torch.tensor(y_train.values, dtype=torch.float32).view(-1, 1)  
y_val_tensor = torch.tensor(y_val.values, dtype=torch.float32).view(-1, 1)  
y_test_tensor = torch.tensor(y_test.values, dtype=torch.float32).view(-1, 1)  

# 转换为TensorDataset  
train_dataset = TensorDataset(X_train_tensor, y_train_tensor)  
val_dataset = TensorDataset(X_val_tensor, y_val_tensor)  
test_dataset = TensorDataset(X_test_tensor, y_test_tensor)  

# 批处理  
batch_size = 32  
train_loader = DataLoader(dataset=train_dataset, batch_size=batch_size, shuffle=False)  
val_loader = DataLoader(dataset=val_dataset, batch_size=batch_size, shuffle=False)  
 test_loader = DataLoader(dataset=test_dataset, batch_size=batch_size, shuffle=False)

 importnumpyasnp  
importtorch  
importtorch.nnasnn  
importtorch.optimasoptim  

classDFN(nn.Module):  
    def__init__(self, input_dim):  
        super(DFN, self).__init__()  
        self.fc1=nn.Linear(input_dim, 32)  
        self.relu1=nn.ReLU()  
        self.dropout1=nn.Dropout(0.1)  
        self.fc2=nn.Linear(32, 16)  
        self.relu2=nn.ReLU()  
        self.dropout2=nn.Dropout(0.1)  
        self.fc3=nn.Linear(16, 1)  
    defforward(self, x):  
        x=self.fc1(x)  
        x=self.relu1(x)  
        x=self.dropout1(x)  
        x=self.fc2(x)  
        x=self.relu2(x)  
        x=self.dropout2(x)  
        x=self.fc3(x)  
        returnx  

input_dim=X_train_processed_dfn.shape[1]  

device=torch.device('cuda'iftorch.cuda.is_available() else'cpu')  

model=DFN(input_dim).to(device)  
criterion=nn.L1Loss()  
 optimizer=optim.Adam(model.parameters(), lr=0.001)

 from sklearn.metrics import mean_squared_error, mean_absolute_error  

num_epochs = 100  
best_val_loss = float('inf')  
patience = 10  
patience_counter = 0  
min_delta = 1e-4  
history = {  
  'train_loss': [],  
  'val_loss': [],   
  'train_mse': [],   
  'val_mse': [],   
  'train_mae': [],   
  'val_mae': []  
}  

for epoch in range(num_epochs):  
    model.train()  

    running_train_loss = 0.0  
    all_train_preds = []  
    all_train_targets = []  

    for batch_idx, (data, target) in enumerate(train_loader):  
        data, target = data.to(device), target.to(device)  
        optimizer.zero_grad()  
        outputs = model(data)  
        loss = criterion(outputs, target)  
        loss.backward()  
        optimizer.step()  
        running_train_loss += loss.item() * data.size(0)  
        all_train_preds.extend(outputs.detach().cpu().numpy())  
        all_train_targets.extend(target.detach().cpu().numpy())  

    epoch_train_loss = running_train_loss / len(train_dataset)  
    train_mse = mean_squared_error(np.array(all_train_targets), np.array(all_train_preds))  
    train_mae = mean_absolute_error(np.array(all_train_targets), np.array(all_train_preds))  
      
    model.eval()  
    running_val_loss = 0.0  
    all_val_preds = []  
    all_val_targets = []  

    with torch.no_grad():  
        for data, target in val_loader:  
            data, target = data.to(device), target.to(device)  
            outputs = model(data)  
            loss = criterion(outputs, target)  
            running_val_loss += loss.item() * data.size(0)  
            all_val_preds.extend(outputs.cpu().numpy())  
            all_val_targets.extend(target.cpu().numpy())  

    epoch_val_loss = running_val_loss / len(val_dataset)  
    val_mse = mean_squared_error(np.array(all_val_targets), np.array(all_val_preds))  
    val_mae = mean_absolute_error(np.array(all_val_targets), np.array(all_val_preds))  

    history['train_loss'].append(epoch_train_loss)  
    history['val_loss'].append(epoch_val_loss)  
    history['train_mse'].append(train_mse)  
    history['val_mse'].append(val_mse)  
    history['train_mae'].append(train_mae)  
     history['val_mae'].append(val_mae)

 import numpy as np  

# 使用新数据集  
df_fin_rev = df_fin.copy()  

# 添加退货标志  
df_fin_rev['is_return'] = (df_fin_rev['sales'] < 0).astype(int)  

# 销售列中的零或正值  
df_fin_rev['sales'] = df_fin_rev['sales'].apply(lambda x: max(x, 0))  

# 在取对数前应用拉普拉斯平滑  
alpha = 1  
df_fin_rev['sales'] = np.log(df_fin_rev['sales'] + alpha)  

 df_fin_rev.info()

 df_fin_rev.isna().sum()

 import numpy as np  
from sklearn.model_selection import train_test_split  
from sklearn.preprocessing import StandardScaler  
from sklearn.compose import ColumnTransformer  
from sklearn.pipeline import Pipeline  
from sklearn.metrics import mean_squared_error, mean_absolute_error  
from category_encoders import BinaryEncoder  

# 创建数据集  
X = df_fin_rev.copy().drop(columns=target_col)  
y = df_fin_rev.copy()[target_col]  

test_size = 50000  
X_tv, X_test, y_tv, y_test = train_test_split(X, y, test_size=test_size, random_state=42)  
X_train, X_val, y_train, y_val = train_test_split(X_tv, y_tv, test_size=test_size, random_state=42)  

# 预处理  
num_transformer = Pipeline(steps=[('scaler', StandardScaler())])  
cat_transformer = Pipeline(steps=[('encoder', BinaryEncoder(cols=cat_cols, handle_missing='0'))])  
preprocessor_en = ColumnTransformer(  
    transformers=[  
        ('num', num_transformer, num_cols),  
        ('cat', cat_transformer, cat_cols)  
    ],  
    remainder='passthrough'   
)  
X_train_processed_en = preprocessor_en.fit_transform(X_train)  
X_val_processed_en = preprocessor_en.transform(X_val)  
X_test_processed_en = preprocessor_en.transform(X_test)  

# 模型训练  
elastic_net.fit(X_train_processed_en, y_train)  

# 预测（对数销售）  
y_pred_train = elastic_net.predict(X_train_processed_en)   
y_pred_val = elastic_net.predict(X_val_processed_en)  
y_pred_test = elastic_net.predict(X_test_processed_en)  

# 评估 - 对数销售 - 使用MSE  
mse_train = mean_squared_error(y_train, y_pred_train)  
mse_val = mean_squared_error(y_val, y_pred_val)  
mse_test = mean_squared_error(y_test, y_pred_test)  

# 评估 - 实际销售 - 使用MAE  
mae_train_exp = mean_absolute_error(np.exp(y_train), np.exp(y_pred_train))  
mae_val_exp = mean_absolute_error(np.exp(y_val), np.exp(y_pred_val))  
 mae_test_exp = mean_absolute_error(np.exp(y_test), np.exp(y_pred_test))