将Excel正态分布公式转换为Pandas实现的技术咨询

Got it, let's tackle this problem step by step. You're trying to replicate that Excel-based normal distribution calculation in pandas, and I'll walk you through how to do it efficiently using scipy.stats.norm (since Excel's NORM.DIST(x, mean, std, TRUE) maps directly to the cumulative distribution function, CDF, which is norm.cdf() in scipy).

Step 1: Import Required Libraries #

First, make sure you have pandas and scipy installed, then import them:

import pandas as pd
from scipy.stats import norm

Step 2: Define Your Input DataFrame #

Let's start with your sample data:

data = {
    'Id': ['ijk', 'xyz'],
    'a': [4, 12],
    'b': [3.5, 3],
    'c': [12.53, 10.74]
}
df = pd.DataFrame(data)

Step 3: Calculate the Fixed Denominator #

The denominator (1 - NORM.DIST(a, b, c, TRUE)) is the same for columns 1-3 in each row. Calculating it once avoids redundant work:

# Denominator: 1 minus CDF of 'a' with mean 'b' and std 'c'
denominator = 1 - norm.cdf(df['a'], loc=df['b'], scale=df['c'])

Step 4: Compute Columns 1, 2, 3 #

For each column index k (1, 2, 3), calculate the numerator as 1 - CDF(a + k, b, c), then divide by the precomputed denominator:

# Loop through columns 1 to 3 and compute values
for col in range(1, 4):
    numerator = 1 - norm.cdf(df['a'] + col, loc=df['b'], scale=df['c'])
    df[str(col)] = numerator / denominator

Step 5: Add Column 0 & Reorder #

Add the fixed column 0 with value 1, then reorder columns to match your expected output:

# Add column 0 (all values = 1)
df['0'] = 1

# Reorder columns to match your desired structure
df = df[['Id', 'a', 'b', 'c', '0', '1', '2', '3']]

# Round to 2 decimal places to match your sample results
df = df.round(2)

Final Output #

When you print df, you'll get exactly the result you wanted:

Id   a    b       c  0     1     2     3
0  ijk   4  3.5  12.53  1  0.93  0.87  0.81
1  xyz  12  3.0  10.74  1  0.87  0.76  0.66

Key Notes #

• Excel-Python Mapping: NORM.DIST(x, mean, std, TRUE) in Excel is identical to norm.cdf(x, loc=mean, scale=std) in scipy—both return the cumulative probability up to x.
• Efficiency: Calculating the denominator once instead of recalculating it for each column saves computation time, especially with large datasets.
• Edge Case Handling: If the denominator equals 0 (when NORM.DIST(a, b, c, TRUE) is 1, meaning a is extremely far in the right tail), you'll get a division-by-zero error. You can handle this with np.where if needed:

  import numpy as np
  df[str(col)] = np.where(denominator == 0, 0, numerator / denominator)
  

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