如何基于起止日期将DataFrame行按周扩展为多行

问题描述 #

我有一个包含工作记录的DataFrame，结构如下：

目标是将每条记录按起止日期之间的周数拆分为多行，最终得到包含Week、Week Count、Year、Week #、Period列的DataFrame。尝试过手动计算周数后用df = df.loc[df.index.repeat(df['# of weeks'])].reset_index(drop=True)扩展，但想找更优的自动拆分方法。编写的代码出现TypeError错误：

出错代码 #

df['Id'] = df['Id'].astype(str)
df['weeks'] = np.ceil((df['End_Date__c'] - df['Start_Date__c']) / np.timedelta64(1, "W"))

exp_df = pd.concat([pd.DataFrame({
    'Id': np.repeat(idx+1, weeks),
    'Start_Date__c': start,
    'End_Date__c ': end,
    'Week': pd.date_range(start, periods=weeks, freq='W-MON'),
    'Week Count': np.arange(1, weeks+1),
    'Year': pd.date_range(start, periods=weeks, freq='W-MON').year,
    'Week #': pd.date_range(start, periods=weeks, freq='W-MON').strftime('%U'),
    'Period': pd.date_range(start, periods=weeks, freq='W-MON').strftime('%Y-%U')
}) for idx, (start, end, weeks) in df.loc[:, ['Start_Date__c', 'End_Date__c', 'End_Date__c']].iterrows()])

exp_df

错误信息 #

---------------------------------------------------------------------------
TypeError                       Traceback (most recent call last)
Cell In[11], line 4
  1 df['Id'] = df['Id'].astype(str)
  2 df['weeks'] = np.ceil((df['End_Date__c'] - df['Start_Date__c']) / np.timedelta64(1, "W"))
----> 4 exp_df = pd.concat([pd.DataFrame({
      5     'Id': np.repeat(idx+1, weeks),
      6     'Start_Date__c': start,
      7     'End_Date__c ': end,
      8     'Week': pd.date_range(start, periods=weeks, freq='W-MON'),
      9     'Week Count': np.arange(1, weeks+1),
     10     'Year': pd.date_range(start, periods=weeks, freq='W-MON').year,
     11     'Week #': pd.date_range(start, periods=weeks, freq='W-MON').strftime('%U'),
     12     'Period': pd.date_range(start, periods=weeks, freq='W-MON').strftime('%Y-%U')
     13 }) for idx, (start, end, weeks) in df.loc[:, ['Start_Date__c', 'End_Date__c', 'End_Date__c']].iterrows()])
     15 exp_df

Cell In[11], line 5, in <listcomp>(.0)
      1 df['Id'] = df['Id'].astype(str)
      2 df['weeks'] = np.ceil((df['End_Date__c'] - df['Start_Date__c']) / np.timedelta64(1, "W"))
      4 exp_df = pd.concat([pd.DataFrame({
----> 5     'Id': np.repeat(idx+1, weeks),
      6     'Start_Date__c': start,
      7     'End_Date__c ': end,
      8     'Week': pd.date_range(start, periods=weeks, freq='W-MON'),
      9     'Week Count': np.arange(1, weeks+1),
     10     'Year': pd.date_range(start, periods=weeks, freq='W-MON').year,
     11     'Week #': pd.date_range(start, periods=weeks, freq='W-MON').strftime('%U'),
     12     'Period': pd.date_range(start, periods=weeks, freq='W-MON').strftime('%Y-%U')
     13 }) for idx, (start, end, weeks) in df.loc[:, ['Start_Date__c', 'End_Date__c', 'End_Date__c']].iterrows()])
     15 exp_df

File <__array_function__ internals>:5, in repeat(*args, **kwargs)

File /opt/anaconda3/lib/python3.9/site-packages/numpy/core/fromnumeric.py:479, in repeat(a, repeats, axis)
    436 @array_function_dispatch(_repeat_dispatcher)
    437 def repeat(a, repeats, axis=None):
    438     """
    439     Repeat elements of an array.
    440 
   (...)
    477 
    478     """
--> 479     return _wrapfunc(a, 'repeat', repeats, axis=axis)

File /opt/anaconda3/lib/python3.9/site-packages/numpy/core/fromnumeric.py:54, in _wrapfunc(obj, method, *args, **kwds)
     52 bound = getattr(obj, method, None)
     53 if bound is None:
--> 54     return _wrapit(obj, method, *args, **kwds)
     56 try:
     57     return bound(*args, **kwds)

File /opt/anaconda3/lib/python3.9/site-packages/numpy/core/fromnumeric.py:43, in _wrapit(obj, method, *args, **kwds)
     41 except AttributeError:
     42     wrap = None
--> 43 result = getattr(asarray(obj), method)(*args, **kwds)
     44 if wrap:
     45     if not isinstance(result, mu.ndarray):

TypeError: int() argument must be a string, a bytes-like object or a number, not 'Timestamp'

错误原因与修复 #

错误根源 #

代码里的df.loc[:, ['Start_Date__c', 'End_Date__c', 'End_Date__c']]重复选取了两次End_Date__c列，导致循环中的weeks变量实际是**End_Date__c的Timestamp值**，而非之前计算的weeks列数值，这直接引发了np.repeat的类型错误。

修正后的基础代码 #

import pandas as pd
import numpy as np

# 确保日期列是datetime类型
df['Start_Date__c'] = pd.to_datetime(df['Start_Date__c'])
df['End_Date__c'] = pd.to_datetime(df['End_Date__c'])
df['Id'] = df['Id'].astype(str)

# 计算周数并转为整数
df['weeks'] = np.ceil((df['End_Date__c'] - df['Start_Date__c']) / np.timedelta64(1, "W")).astype(int)

# 循环时选择正确的列：Start_Date__c, End_Date__c, weeks
exp_df = pd.concat([pd.DataFrame({
    'Id': np.repeat(row['Id'], row['weeks']),
    'Start_Date__c': row['Start_Date__c'],
    'End_Date__c': row['End_Date__c'],
    'Week': pd.date_range(row['Start_Date__c'], periods=row['weeks'], freq='W-MON'),
    'Week Count': np.arange(1, row['weeks']+1),
    'Year': pd.date_range(row['Start_Date__c'], periods=row['weeks'], freq='W-MON').year,
    'Week #': pd.date_range(row['Start_Date__c'], periods=row['weeks'], freq='W-MON').strftime('%U'),
    'Period': pd.date_range(row['Start_Date__c'], periods=row['weeks'], freq='W-MON').strftime('%Y-%U')
}) for _, row in df.iterrows()], ignore_index=True)

print(exp_df)

更优的扩展方法（避免循环） #

利用pandas原生的explode和分组函数，效率更高，适合大数据量场景：

import pandas as pd
import numpy as np

# 预处理日期列
df['Start_Date__c'] = pd.to_datetime(df['Start_Date__c'])
df['End_Date__c'] = pd.to_datetime(df['End_Date__c'])
df['Id'] = df['Id'].astype(str)

# 计算周数并生成每周日期列表
df['weeks'] = np.ceil((df['End_Date__c'] - df['Start_Date__c']) / np.timedelta64(1, "W")).astype(int)
df['Week'] = df.apply(lambda x: pd.date_range(x['Start_Date__c'], periods=x['weeks'], freq='W-MON').tolist(), axis=1)

# 扩展行并生成其他列
exp_df = df.explode('Week', ignore_index=True)
exp_df['Week Count'] = exp_df.groupby('Id').cumcount() + 1
exp_df['Year'] = exp_df['Week'].dt.year
exp_df['Week #'] = exp_df['Week'].dt.strftime('%U')
exp_df['Period'] = exp_df['Week'].dt.strftime('%Y-%U')

# 保留需要的列
exp_df = exp_df[['Id', 'Start_Date__c', 'End_Date__c', 'Week', 'Week Count', 'Year', 'Week #', 'Period']]

print(exp_df)

方法优势 #

• 避免iterrows()循环，利用pandas原生函数，运行效率更高
• 减少重复的pd.date_range调用，代码更简洁
• 逻辑清晰，易于维护和修改

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