R Studio中k折交叉验证逻辑回归系数与Excel结果符号相反的原因排查及代码正确性咨询

Hi there! Let's walk through why you might be seeing opposite coefficient signs between your R k-fold cross-validated logistic regression and Excel's results, plus check if your code has any red flags.

First, your code looks mostly solid #

Let's start with the good news: your core workflow for k-fold CV with logistic regression in caret is correct. A few minor notes that aren't causing the sign issue, but are worth mentioning:

• Converting the tibble to a data frame is unnecessary—caret works fine with tibbles directly.
• Your stratified train/test split via createDataPartition is the right approach for imbalanced data (since you mentioned 96k observations, mostly 0s/1s), so that's a good call.

The #1 reason for opposite coefficient signs: reference category mismatch #

Logistic regression coefficients' signs depend entirely on which category of your dependent variable is treated as the "success" event (the one the model predicts the probability of). This is almost certainly what's happening between R and Excel:

1. How R is treating your dependent variable:
   When you convert Depvar to a factor with levels "Insolvent" and "Solvent", R's default behavior for glm(family = binomial) is to model the log-odds of the second factor level (alphabetically, that's "Solvent"). So your model is calculating:

   log(P(Solvent) / P(Insolvent)) = β₀ + β₁*indvar1 + β₂*indvar2
   

2. How Excel is likely treating it:
   Excel's logistic regression tool typically defaults to modeling the probability of the higher numeric value in your original dependent variable. Since you coded 1 = Insolvent and 0 = Solvent, Excel is almost certainly calculating:

   log(P(Insolvent) / P(Solvent)) = β₀ + β₁*indvar1 + β₂*indvar2
   

These two equations are mathematical inverses of each other—so every coefficient in R will be the negative of what Excel outputs. That's the root cause of your sign discrepancy!

How to verify and fix this #

1. Check R's reference category:
   Run levels(train_df$Depvar)—you'll see the order is probably ["Insolvent", "Solvent"] (since I comes before S alphabetically). To confirm which event the model is predicting, run:

   head(predict(model1, newdata = train_df, type = "prob"))
   

   The column with higher values for rows where Depvar = "Solvent" is the one the model is targeting.

2. Align R's model with Excel's:
   Force R to use "Solvent" as the reference category, so the model predicts the probability of Insolvent (matching Excel's behavior). Add this line right after converting Depvar to a factor:

   train_df$Depvar <- relevel(train_df$Depvar, ref = "Solvent")
   test_df$Depvar <- relevel(test_df$Depvar, ref = "Solvent")
   

   Refit your model, and your coefficients should now have the same signs as Excel's.

Why k-fold CV coefficients might differ slightly in magnitude #

Even after fixing the sign issue, you might see small differences in coefficient sizes between R's CV model and Excel:

• Sampling variation: Your k-fold CV uses stratified splits of the data, while Excel's logistic regression is likely using the entire dataset (unless your boss is also doing CV in Excel, which is less common).
• Regularization: Excel's logistic regression doesn't include regularization by default, but caret's glm method also doesn't—so this shouldn't be a factor here. The main difference will be due to the train/test partitioning in your CV workflow.

Final check #

Since your boss insisted on k-fold CV, remember that the model1 object from caret::train() actually fits a final model on the entire training dataset (not an average of the k-fold models). If you're looking at coefficients from individual folds, those will vary slightly per fold, but their signs should all align once you fix the reference category.

备注：内容来源于stack exchange，提问作者StMatthias