Power BI × Pythonで学ぶロジスティック回帰分析の実践

# 'dataset' はこのスクリプトの入力データを保持しています
# 必要なライブラリをインポート
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import accuracy_score, classification_report, roc_curve, roc_auc_score

# Power BI からデータフレームを取得
df = dataset  

# 'User Principal Name' を別に保持
user_ids = df[['User Principal Name']]

# 説明変数の設定（独立変数）
X = df[['WorkingHours', 'BeingWorthDoing', 'WorkEnvironment', 
        'GrowthCareer', 'HumanRelations', 'EvaluationApproval']]

# 目的変数の設定（従属変数）
y = df['JobRotationReserve']

# 訓練データとテストデータに分割（80%:20%）
X_train, X_test, y_train, y_test, user_train, user_test = train_test_split(
    X, y, user_ids, test_size=0.2, random_state=42
)

# 標準化（スケール調整）
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)

# ロジスティック回帰モデルの作成と学習
model = LogisticRegression(class_weight='balanced')
model.fit(X_train_scaled, y_train)

# クラス1（JobRotationReserve=1）の確率を予測
y_prob = model.predict_proba(X_test_scaled)[:, 1]

# ROC曲線から最適なしきい値を見つける
fpr, tpr, thresholds = roc_curve(y_test, y_prob)
optimal_idx = (tpr - fpr).argmax()
optimal_threshold = thresholds[optimal_idx]

# AUCスコアを計算
auc_score = roc_auc_score(y_test, y_prob)

# 最適なしきい値を適用して予測
y_pred_adjusted = (y_prob > optimal_threshold).astype(int)

# 予測と精度評価
accuracy = accuracy_score(y_test, y_pred_adjusted)
report = classification_report(y_test, y_pred_adjusted, output_dict=True)

# 変数の寄与度を算出
coefs = model.coef_[0]
stds = np.std(X_train_scaled, axis=0)
feature_importance = (coefs * stds) ** 2
feature_importance /= feature_importance.sum()  # 割合に変換

# 結果をデータフレーム化
importance_df = pd.DataFrame({
    'Feature': X.columns,
    'Coefficient': coefs,
    'Importance': feature_importance
}).sort_values(by='Importance', ascending=False)

# ====== ここから CSV 予測用の処理 ======
# CSVファイルを読み込む
csv_path = r'C:\temp\TheGrahp\forecast.csv'  # ファイルパス
predict_df = pd.read_csv(csv_path)

# CSVファイルに 'User Principal Name' が含まれている場合、それを保持
user_ids_predict = predict_df[['User Principal Name']]

# 学習データと同じ特徴量を抽出
X_predict = predict_df[['WorkingHours', 'BeingWorthDoing', 'WorkEnvironment', 
                        'GrowthCareer', 'HumanRelations', 'EvaluationApproval']]

# 標準化を適用
X_predict_scaled = scaler.transform(X_predict)

# モデルを使って確率を予測
y_prob_predict = model.predict_proba(X_predict_scaled)[:, 1]

# 最適なしきい値を適用して予測クラスを決定
y_pred_adjusted_predict = (y_prob_predict > optimal_threshold).astype(int)

# 結果をデータフレームにまとめる
predict_result_df = X_predict.copy()
predict_result_df['User Principal Name'] = user_ids_predict['User Principal Name'].values
predict_result_df['予測値'] = y_pred_adjusted_predict
predict_result_df['クラス1確率'] = y_prob_predict
predict_result_df['最適なしきい値'] = optimal_threshold
predict_result_df['AUCスコア'] = auc_score  # AUCスコアを追加
predict_result_df['モデル精度'] = accuracy
predict_result_df['適合率_1'] = report['1']['precision']
predict_result_df['再現率_1'] = report['1']['recall']
predict_result_df['F1スコア_1'] = report['1']['f1-score']

# 予測結果の出力
predict_result_df

import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LogisticRegression
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import accuracy_score, classification_report, roc_curve, roc_auc_score

df = dataset 
user_ids = df[['User Principal Name']]
Power BI からデータを取得し、df に格納します。
User Principal Name（ユーザー識別子）を別に保持しておきます。

X = df[['WorkingHours', 'BeingWorthDoing', 'WorkEnvironment',
        'GrowthCareer', 'HumanRelations', 'EvaluationApproval']]
y = df['JobRotationReserve']

X_train, X_test, y_train, y_test, user_train, user_test = train_test_split(
    X, y, user_ids, test_size=0.2, random_state=42
)

scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)

model = LogisticRegression(class_weight='balanced')
model.fit(X_train_scaled, y_train)

y_prob = model.predict_proba(X_test_scaled)[:, 1]

fpr, tpr, thresholds = roc_curve(y_test, y_prob)
optimal_idx = (tpr - fpr).argmax()
optimal_threshold = thresholds[optimal_idx]

auc_score = roc_auc_score(y_test, y_prob)

y_pred_adjusted = (y_prob > optimal_threshold).astype(int)

accuracy = accuracy_score(y_test, y_pred_adjusted)
report = classification_report(y_test, y_pred_adjusted, output_dict=True)

coefs = model.coef_[0]
stds = np.std(X_train_scaled, axis=0)
feature_importance = (coefs * stds) ** 2
feature_importance /= feature_importance.sum()
importance_df = pd.DataFrame({
    'Feature': X.columns,
    'Coefficient': coefs,
    'Importance': feature_importance
}).sort_values(by='Importance', ascending=False)

csv_path = r'C:\temp\TheGrahp\forecast.csv'
predict_df = pd.read_csv(csv_path)

user_ids_predict = predict_df[['User Principal Name']]
X_predict = predict_df[['WorkingHours', 'BeingWorthDoing', 'WorkEnvironment',
                        'GrowthCareer', 'HumanRelations', 'EvaluationApproval']]

X_predict_scaled = scaler.transform(X_predict)
y_prob_predict = model.predict_proba(X_predict)[:, 1]
y_pred_adjusted_predict = (y_prob_predict > optimal_threshold).astype(int)

predict_result_df = X_predict.copy()
predict_result_df['User Principal Name'] = user_ids_predict['User Principal Name'].values
predict_result_df['予測値'] = y_pred_adjusted_predict
predict_result_df['クラス1確率'] = y_prob_predict
predict_result_df['最適なしきい値'] = optimal_threshold
predict_result_df['AUCスコア'] = auc_score
predict_result_df['モデル精度'] = accuracy
predict_result_df['適合率_1'] = report['1']['precision']
predict_result_df['再現率_1'] = report['1']['recall']
predict_result_df['F1スコア_1'] = report['1']['f1-score']
predict_result_df