无法调试生成churn_flag与post_churn_billing_flag的Python函数求助

规则说明 #

• churn_flag规则：每个经销商的首行设为0；在该经销商首个非零Volume出现后开始标记，所有Volume为0的行设为1。
• post_churn_billing_flag规则：当某行churn_flag为1时，若下一行存在非零Volume，则将下一行的Post_churn_billing字段设为1。

参考数据集 #

import pandas as pd

# 参考数据集
data = {
    'Dealers_code': [1, 1, 2, 2, 2, 2, 2, 3, 3, 4, 4, 4,4, 4, 4, 4],
    'Date': ['01-06-2020', '01-07-2020', '01-08-2020', '01-09-2020', '01-09-2021', '01-10-2021', '01-12-2021', '01-06-2022', '01-07-2022', '01-12-2022', '01-01-2023', '01-02-2023', '01-03-2023', '01-05-2023', '01-06-2023', '01-07-2023'],
    'Volume': [0, 52, 0, 28, 0, 98, 55.55, 0, 56, 70, 77.5, 0, 3, 0, 31, 8],
    'Zone': ['ODC', 'ODC', 'ODC', 'ODC', 'ODC', 'ODC', 'ODC', 'WBN', 'WBN', 'KL1', 'KL1', 'KL1', 'KL1', 'KL1', 'KL1', 'KL1'],
    'State': [18, 18, 18, 18, 18, 18, 18, 3, 3, 11, 11, 11, 11, 11, 11, 11],
    'Dealer_cat': ['Gold', 'Platenum', 'Silver', 'Others', 'Gold', 'Platenum', 'Silver', 'Gold', 'Gold', 'Platenum', 'Silver', 'Gold', 'Platenum', 'Silver', 'Others', 'Others']
}

# 创建DataFrame
df = pd.DataFrame(data)

现有错误实现代码 #

# Convert the 'Date' column to datetime type
df['Date'] = pd.to_datetime(df['Date'], format='%d-%m-%Y')

# Sort the dataframe by 'Dealers_code' and 'Date' to ensure the data is in chronological order
df.sort_values(['Dealers_code', 'Date'], inplace=True)

# Create the 'churn_flag' column and initialize it with 0
df['churn_flag'] = 0

# Find the index of the first non-null 'Volume' for each dealer and update the 'churn_flag'
def update_churn_flag(group):
    non_null_volume_idx = group['Volume'].ne(0).idxmax()
    if pd.notnull(non_null_volume_idx):
        group.loc[non_null_volume_idx:, 'churn_flag'] = 1
    return group

df = df.groupby('Dealers_code').apply(update_churn_flag)

# Create the 'Post_churn_billing' column
df['Post_churn_billing'] = 0

# Find the index of churn_flag being 1, and if the next row has a non-null 'Volume', set 'Post_churn_billing' to 1
def update_post_churn_billing(group):
    churn_flag_idx = group[group['churn_flag'] == 1].index
    for idx in churn_flag_idx:
        next_idx = idx + 1
        if next_idx < len(group) and pd.notnull(group.loc[next_idx, 'Volume']):
            group.loc[next_idx, 'Post_churn_billing'] = 1
    return group

df = df.groupby('Dealers_code').apply(update_post_churn_billing)

# Display the updated dataframe with the new 'Post_churn_billing' feature
print(df)

期望输出 #

Dealers_code       Date  Volume Zone  State Dealer_cat  churn_flag  Post_churn_billing
7        1 2022-06-01    0.00  WBN      3       Gold           0                   0
8        1 2022-07-01   56.00  WBN      3       Gold           0                   0
9       2 2022-12-01   70.00  KL1     11   Platenum           0                   0
10      2 2023-01-01   77.50  KL1     11     Silver           0                   0
11      2 2023-02-01    0.00  KL1     11       Gold           1                   0
12      2 2023-03-01    3.00  KL1     11   Platenum           0                   0
13      2 2023-05-01    0.00  KL1     11     Silver           1                   0
14      2 2023-06-01   31.00  KL1     11     Others           0                   1
15      2 2023-07-01    8.00  KL1     11     Others           0                   0
0       3 2020-06-01    0.00  ODC     18       Gold            0                   0
1       3 2020-07-01   52.00  ODC     18   Platenum            0                   0
2       3 2020-08-01    0.00  ODC     18     Silver            1                   0
3       3 2020-09-01   28.00  ODC     18     Others            0                   1
4       3 2021-09-01    0.00  ODC     18       Gold            1                   0
5       3 2021-10-01   98.00  ODC     18   Platenum            0                   0
6       3 2021-12-01   55.55  ODC     18     Silver            0                   0

修正后的代码及说明 #

原代码问题分析 #

1. churn_flag逻辑错误：原代码将首个非零Volume之后的所有行都设为1，不符合规则中“仅Volume为0的行设为1”的要求，同时未单独处理首行固定为0的规则。
2. post_churn_billing逻辑错误：使用全局索引而非组内相对位置判断下一行，且判断条件错误（应判断Volume非零而非非null）。

修正代码 #

import pandas as pd

# 加载数据
data = {
    'Dealers_code': [1, 1, 2, 2, 2, 2, 2, 3, 3, 4, 4, 4,4, 4, 4, 4],
    'Date': ['01-06-2020', '01-07-2020', '01-08-2020', '01-09-2020', '01-09-2021', '01-10-2021', '01-12-2021', '01-06-2022', '01-07-2022', '01-12-2022', '01-01-2023', '01-02-2023', '01-03-2023', '01-05-2023', '01-06-2023', '01-07-2023'],
    'Volume': [0, 52, 0, 28, 0, 98, 55.55, 0, 56, 70, 77.5, 0, 3, 0, 31, 8],
    'Zone': ['ODC', 'ODC', 'ODC', 'ODC', 'ODC', 'ODC', 'ODC', 'WBN', 'WBN', 'KL1', 'KL1', 'KL1', 'KL1', 'KL1', 'KL1', 'KL1'],
    'State': [18, 18, 18, 18, 18, 18, 18, 3, 3, 11, 11, 11, 11, 11, 11, 11],
    'Dealer_cat': ['Gold', 'Platenum', 'Silver', 'Others', 'Gold', 'Platenum', 'Silver', 'Gold', 'Gold', 'Platenum', 'Silver', 'Gold', 'Platenum', 'Silver', 'Others', 'Others']
}

df = pd.DataFrame(data)

# 日期转换与排序
df['Date'] = pd.to_datetime(df['Date'], format='%d-%m-%Y')
df.sort_values(['Dealers_code', 'Date'], inplace=True)

# 初始化字段
df['churn_flag'] = 0
df['Post_churn_billing'] = 0

# 处理churn_flag
def calculate_churn_flag(group):
    # 首行固定为0
    group['churn_flag'].iloc[0] = 0
    # 找到首个非零Volume的位置
    first_non_zero_idx = group['Volume'].ne(0).idxmax()
    # 获取组内的行索引
    group_indices = group.index
    # 找到首个非零Volume在组内的位置
    first_non_zero_pos = group_indices.get_loc(first_non_zero_idx)
    
    # 从首个非零Volume之后的行开始，Volume为0的设为1
    for pos in range(first_non_zero_pos + 1, len(group)):
        if group['Volume'].iloc[pos] == 0:
            group['churn_flag'].iloc[pos] = 1
    return group

df = df.groupby('Dealers_code').apply(calculate_churn_flag)

# 处理Post_churn_billing
def calculate_post_churn(group):
    # 遍历组内每一行（除最后一行）
    for i in range(len(group)-1):
        if group['churn_flag'].iloc[i] == 1 and group['Volume'].iloc[i+1] != 0:
            group['Post_churn_billing'].iloc[i+1] = 1
    return group

df = df.groupby('Dealers_code').apply(calculate_post_churn)

# 打印结果
print(df)

代码说明 #

1. churn_flag处理：
   • 确保每个经销商首行churn_flag为0。
   • 找到首个非零Volume的位置，之后的行中仅Volume为0的行设为1。
2. Post_churn_billing处理：
   • 按经销商分组后，遍历组内每一行（排除最后一行），若当前行churn_flag为1且下一行Volume非零，则将下一行的Post_churn_billing设为1。

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