函数未捕获值变化：Pandas生成DataFrame日期列失败求助

问题描述 #

现有如下DataFrame：

state_territory_code    week_nbr
 CA                     WEEK 49
 CA                     WEEK 49
 FL                     WEEK 49
 CA                     WEEK 50
 TN                     WEEK 50
 HI                     WEEK 50
 GA                     WEEK 51
 FL                     WEEK 51

需求是：按行迭代时，若当前行周数与下一行周数不同，则将起始日期加7天，最终生成new_cal_date列，期望输出如下：

state_territory_code    week_nbr          new_cal_date
     CA                     WEEK 49         01/01/2024
     CA                     WEEK 49         01/01/2024
     FL                     WEEK 49         01/01/2024
     CA                     WEEK 50         01/08/2024
     TN                     WEEK 50         01/08/2024
     HI                     WEEK 50         01/08/2024
     GA                     WEEK 51         01/15/2024
     FL                     WEEK 51         01/15/2024

用户尝试了以下代码，但用apply后结果全是01/08/2024，函数外测试逻辑有效，需要修复：

def cal_week_start(some_df):
    start_dt = datetime.datetime(2024, 1, 1)
    
    for i, wk in enumerate(some_df):
        if i < (len(some_df)-1): ## Keep running as long as we haven't reached end of DF
            next_value = some_df[i+1] ## index pos of next value
            if wk != next_value: ## if value changes from one to the next do something
                new_dt = start_dt + timedelta(days = 7) ## Add 7 days.
                start_dt = new_dt ## reinitiate variable to add 7 days, 14, 21, etc.
                return start_dt 
        else:
            return start_dt

df_new['new_cal_date'] = df_new['week_nbr'].apply(cal_week_start)

问题根源 #

你用apply调用函数的方式完全错误：

• apply是对Series里的每一个单独元素执行函数，而不是把整个Series传给函数。比如每次调用cal_week_start时，传入的是单个字符串（比如"WEEK 49"），不是整个week_nbr列。
• 函数里的enumerate(some_df)其实是在枚举单个字符串的字符，逻辑彻底混乱，最后只会返回第一次满足wk != next_value的start_dt+7，所以所有行都得到01/08/2024。

高效解决方案（适配大数据集） #

大数据集绝对不能用循环，要用pandas的向量化操作，速度快几个数量级。这里有两种简洁的实现方式：

方法1：用唯一周映射日期 #

import pandas as pd
from datetime import datetime, timedelta

# 初始化你的DataFrame
df = pd.DataFrame({
    'state_territory_code': ['CA', 'CA', 'FL', 'CA', 'TN', 'HI', 'GA', 'FL'],
    'week_nbr': ['WEEK 49', 'WEEK 49', 'WEEK 49', 'WEEK 50', 'WEEK 50', 'WEEK 50', 'WEEK 51', 'WEEK 51']
})

start_dt = datetime(2024, 1, 1)
# 获取按顺序排列的唯一周数
unique_weeks = df['week_nbr'].unique()
# 为每个周数分配对应的起始日期：第1周+0天，第2周+7天，以此类推
week_date_map = {week: start_dt + timedelta(days=7*i) for i, week in enumerate(unique_weeks)}

# 映射到原DataFrame，再格式化日期
df['new_cal_date'] = df['week_nbr'].map(week_date_map).dt.strftime('%m/%d/%Y')

print(df)

方法2：用factorize生成周组索引 #

import pandas as pd
from datetime import datetime, timedelta

df = pd.DataFrame({
    'state_territory_code': ['CA', 'CA', 'FL', 'CA', 'TN', 'HI', 'GA', 'FL'],
    'week_nbr': ['WEEK 49', 'WEEK 49', 'WEEK 49', 'WEEK 50', 'WEEK 50', 'WEEK 50', 'WEEK 51', 'WEEK 51']
})

start_dt = datetime(2024, 1, 1)
# factorize()会给每个唯一的周数分配一个递增的整数索引（从0开始）
df['week_group'] = df['week_nbr'].factorize()[0]
# 计算每个组对应的日期：起始日期 + 组索引*7天
df['new_cal_date'] = (start_dt + df['week_group'].apply(lambda x: timedelta(days=7*x))).dt.strftime('%m/%d/%Y')

# 可以删掉中间的week_group列（如果不需要）
df.drop('week_group', axis=1, inplace=True)

print(df)

两种方法都能得到你想要的结果，而且完全适配大数据集，不需要循环迭代每一行。

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