基于Pandas三列数据构建用户使用数据评分函数的技术问询

Great question! Calculating a usage score/weight for your customers based on count_details, total_duration, and Total_volume is a common (and super useful) task. Let’s break this down into a customizable, actionable workflow that includes math formulas and Pandas code.

First, we need to fix a critical issue: your three metrics live on completely different scales (count is a number of actions, duration is time, volume is data size). Adding them directly would let the largest-scale metric dominate the score, which isn’t fair.

Step 1.1: Normalize Metrics to a 0-1 Range #

We’ll use min-max normalization to convert each metric to a consistent 0-1 scale. For any metric X (like count_details):
$$X_{norm} = \frac{X - X_{min}}{X_{max} - X_{min}}$$
This ensures every metric contributes equally to the score before we apply business-specific weights.

Step 1.2: Weighted Sum for Final Score #

Next, assign weights to each normalized metric based on their business importance. Let’s say:

• $w_1$ = weight for count_details_norm (e.g., 0.2 if action count is least important)
• $w_2$ = weight for total_duration_norm (e.g., 0.3 if duration is moderately important)
• $w_3$ = weight for Total_volume_norm (e.g., 0.5 if data volume is most critical)

Weights must add up to 1 ($w_1 + w_2 + w_3 = 1$). The final user score is:
$$UserScore = (w_1 \times count_details_{norm}) + (w_2 \times total_duration_{norm}) + (w_3 \times Total_volume_{norm})$$

Optional: Multiply the result by 100 to get a 0-100 score for easier interpretation.

Here’s how to turn this framework into code using your dataset:

import pandas as pd

# Load your actual dataset (replace this sample with your data)
df = pd.read_csv("your_dataset.csv")

# 1. Define the min-max normalization function
def min_max_normalize(column):
    return (column - column.min()) / (column.max() - column.min())

# 2. Apply normalization to the three target columns
df["count_norm"] = min_max_normalize(df["count_details"])
df["duration_norm"] = min_max_normalize(df["total_duration"])
df["volume_norm"] = min_max_normalize(df["Total_volume"])

# 3. Assign weights (adjust these based on your business priorities!)
WEIGHTS = {
    "count_norm": 0.2,
    "duration_norm": 0.3,
    "volume_norm": 0.5
}

# 4. Calculate the final user score
df["user_score"] = (
    df["count_norm"] * WEIGHTS["count_norm"] +
    df["duration_norm"] * WEIGHTS["duration_norm"] +
    df["volume_norm"] * WEIGHTS["volume_norm"]
)

# Optional: Scale to 0-100 for readability
df["user_score_100"] = df["user_score"] * 100

# View results
print(df[["customerID", "user_score", "user_score_100"]].head())

Tweak this to fit your needs:

• Alternative normalization: If your data has extreme outliers, use z-score normalization instead:
  $$X_{zscore} = \frac{X - X_{mean}}{X_{std}}$$
  Replace the min_max_normalize function with a z-score version.
• Non-linear transformations: If a metric is skewed (e.g., most users have low volume but a few have very high), apply a log transformation before normalizing: df["log_volume"] = np.log(df["Total_volume"])
• Automatic weights: If you don’t want to guess weights, use entropy weighting to let the data determine which metrics have the most variability (and thus more impact). Here’s a simplified snippet:

  def calculate_entropy_weights(df, cols):
      norm_df = df[cols] / df[cols].sum()
      entropy = -1 * (norm_df * np.log(norm_df + 1e-9)).sum(axis=0) / np.log(len(df))
      weights = (1 - entropy) / (len(cols) - entropy.sum())
      return weights.to_dict()
  
  # Use it like this:
  entropy_weights = calculate_entropy_weights(df, ["count_norm", "duration_norm", "volume_norm"])
  df["user_score_entropy"] = (
      df["count_norm"] * entropy_weights["count_norm"] +
      df["duration_norm"] * entropy_weights["duration_norm"] +
      df["volume_norm"] * entropy_weights["volume_norm"]
  )
  

内容的提问来源于stack exchange，提问作者Ismahane