本地正常代码在AWS Lambda报positional indexers are out-of-bounds错误

问题背景 #

• 代码用于从Google Sheets提取数据并加载至Postgres数据库
• 本地开发环境、AWS Docker容器中运行正常，但部署到AWS Lambda后持续抛出IndexError: positional indexers are out-of-bounds
• 已确认valid_columns和staffing_df均不为空

核心代码片段 #

from pathlib import Path
import pandas as pd
import gspread as gs
from datetime import datetime
from gspread_dataframe import get_as_dataframe
from upload_functions import push_df
import numpy as np

gsheets_name = "xxx"
gsheets_token = Path("xxx")

# 初始化Google Sheets客户端
gc = gs.service_account(filename=gsheets_token)

# 打开指定工作表
sheet = gc.open(gsheets_name)

# 访问目标工作表
staffing_worksheet = sheet.worksheet("xxx")
staffing_df = get_as_dataframe(staffing_worksheet, evaluate_formulas=True, header=None, skiprows=95)
staffing_df = staffing_df[staffing_df[1].notna()]

# 提取列名和日期信息
columns_A_to_E = staffing_worksheet.row_values(95)[:5]
date_row = staffing_worksheet.row_values(2)
columns_dates = pd.to_datetime(date_row[6:], format='%d.%m.%Y', errors='coerce')
current_week, current_year = pd.Timestamp.now().isocalendar()[1], pd.Timestamp.now().isocalendar()[0]

# 筛选当前及未来日期的列
valid_columns = [(date.isocalendar()[1] >= current_week and date.isocalendar()[0] == current_year) or date.isocalendar()[0] > current_year for date in columns_dates]
columns = columns_A_to_E + [date_row[i+6] for i, valid in enumerate(valid_columns) if valid]
staffing_df = staffing_df.iloc[:, np.r_[0:5, 6 + np.flatnonzero(valid_columns)]]
staffing_df.columns = columns

# 添加提取时间戳
staffing_df['pulled_timestamp'] = datetime.now()

# 数据转换与清洗
pivot_list_staffing = []
for index, row in staffing_df.iterrows():
    for i, valid in enumerate(valid_columns):
        if valid:
            column_date = columns_dates[i].strftime('%d.%m.%Y')
            pivot_list_staffing.append(pd.Series(
                [row[dim] for dim in columns_A_to_E] + [column_date, row[column_date], row['pulled_timestamp']],
                index=list(columns_A_to_E) + ['day', 'days', 'pulled_timestamp']
            ))

pivot_df_staffing = pd.DataFrame(pivot_list_staffing)
pivot_df_staffing = pivot_df_staffing.dropna(subset=['days'])

pivot_df_staffing.columns = ['ID', 'user', 'project_name', 'project_type', 'sum', 'day', 'days', 'pulled_timestamp']
pivot_df_staffing = pivot_df_staffing.drop(columns=['sum'])

pivot_df_staffing['day'] = pd.to_datetime(pivot_df_staffing['day'], errors='coerce', dayfirst=True)

# 推送数据至数据库
push_df(pivot_df_staffing, 'staffing_mc')

CloudWatch错误日志 #

LAMBDA_WARNING: Unhandled exception. The most likely cause is an issue
in the function code. However, in rare cases, a Lambda runtime update 
can cause unexpected function behavior. For functions using managed 
runtimes, runtime updates can be triggered by a function change, or 
can be applied automatically. To determine if the runtime has been 
updated, check the runtime version in the INIT_START log entry. If 
this error correlates with a change in the runtime version, you may be 
able to mitigate this error by temporarily rolling back to the previous 
runtime version. For more information, see 
https://docs.aws.amazon.com/lambda/latest/dg/runtimes-update.html
[ERROR] IndexError: positional indexers are out-of-bounds
Traceback (most recent call last):
  File "/var/task/main.py", line 37, in lambda_handler
    staffing_df = staffing_df.iloc[:, np.r_[0:5, 6 + np.flatnonzero(valid_columns)]]
  File "/var/lang/lib/python3.11/site-packages/pandas/core/indexing.py", line 1184, in __getitem__
    return self._getitem_tuple(key)
  File "/var/lang/lib/python3.11/site-packages/pandas/core/indexing.py", line 1690, in _getitem_tuple
    tup = self._validate_tuple_indexer(tup)
  File "/var/lang/lib/python3.11/site-packages/pandas/core/indexing.py", line 966, in _validate_tuple_indexer
    self._validate_key(k, i)
  File "/var/lang/lib/python3.11/site-packages/pandas/core/indexing.py", line 1612, in _validate_key
    raise IndexError("positional indexers are out-of-bounds")

错误原因分析 #

报错行staffing_df = staffing_df.iloc[:, np.r_[0:5, 6 + np.flatnonzero(valid_columns)]]的核心问题是生成的列索引超出了staffing_df的实际列数，Lambda环境与本地/Docker环境的差异点主要有：

1. 时区偏差：Lambda默认使用UTC时区，若本地使用其他时区（如欧洲时区），会导致current_week/current_year计算错误，valid_columns筛选出的日期列索引超出DataFrame范围
2. 数据加载差异：Lambda环境下get_as_dataframe加载的DataFrame可能截断了末尾的空列，导致实际列数少于预期，而本地环境保留了这些空列
3. 索引计算不匹配：date_row[6:]的长度与staffing_df从第6列开始的列数不一致，导致6 + np.flatnonzero(valid_columns)生成的索引值越界

修复与排查方案 #

1. 统一时区计算当前周/年 #

替换时区相关代码，避免UTC与本地时区的差异：

# 改为业务对应的时区（示例：欧洲柏林时区）
now = pd.Timestamp.now(tz='Europe/Berlin')
current_week, current_year = now.isocalendar()[1], now.isocalendar()[0]

2. 添加索引越界校验 #

在列选择前过滤掉超出范围的索引：

# 计算待选列索引
selected_cols = np.r_[0:5, 6 + np.flatnonzero(valid_columns)]
# 过滤超出DataFrame列数的索引
max_col_idx = staffing_df.shape[1] - 1
selected_cols = selected_cols[selected_cols <= max_col_idx]
# 执行列选择
staffing_df = staffing_df.iloc[:, selected_cols]

3. 校验数据结构一致性 #

添加日志和校验逻辑，确保date_row与DataFrame列数匹配：

import logging
logger = logging.getLogger()
logger.setLevel(logging.INFO)

# 校验日期行长度与DataFrame列数
date_cols_count = len(date_row[6:])
df_cols_from_6 = staffing_df.shape[1] - 6
if date_cols_count != df_cols_from_6:
    logger.error(f"日期列数量不匹配：date_row[6:]有{date_cols_count}列，DataFrame从第6列开始有{df_cols_from_6}列")
    raise ValueError("Google Sheets数据结构与预期不一致")

# 输出关键参数便于排查
logger.info(f"staffing_df形状：{staffing_df.shape}")
logger.info(f"选中的列索引：{selected_cols}")
logger.info(f"有效列数量：{sum(valid_columns)}")

4. 处理无效日期 #

避免columns_dates中的无效日期导致valid_columns生成错误索引：

# 过滤掉无效日期（coerce后为NaT的项）
valid_dates_mask = columns_dates.notna()
columns_dates = columns_dates[valid_dates_mask]
valid_columns = [(date.isocalendar()[1] >= current_week and date.isocalendar()[0] == current_year) or date.isocalendar()[0] > current_year for date in columns_dates]
# 同步过滤date_row对应的项
date_row_valid = [date_row[i+6] for i, valid in enumerate(valid_dates_mask) if valid]

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