Pandas多数据帧条件筛选报错：ValueError整数转换问题求助

问题需求 #

开发交易算法时，需要筛选df_high数据帧中满足以下条件的行：

• 今日数据（单行数据帧df_today）的高点突破df_high中的前期高点
• 今日收盘价低于该前期高点

用户原代码如下：

# Create dictonary of assets
assets = ['EURUSD']

# Connect to MarketData.db
conn = db.connect("MarketData.db")
c = conn.cursor()

# DAILY REJECTION CALC PROCESS (notes) - 
#         loop: for row in rows, if todayhigh > high AND todayclose < high = TRUE, else FALSE
#         If TRUE, save df row to list >>> print list at end 

def calculate():

    validsweep = pd.DataFrame({
        'asset': pd.Series(dtype='str'),
        'date': pd.Series(dtype='str'),
        'open': pd.Series(dtype='float'),
        'high': pd.Series(dtype='float'),
        'low': pd.Series(dtype='float'),
        'close': pd.Series(dtype='float'),
        'fractal_high': pd.Series(dtype='int'),
        'fractal_low': pd.Series(dtype='int')})

    for x in assets:
        c.execute(f'SELECT * FROM {x}')
        rows = c.fetchall()
        df = pd.DataFrame(rows)
        df = df.rename(columns={0: "date", 1: "open", 2: "high", 3: "low", 4: "close", 5: "fractal_high", 6: "fractal_low"})
        df.insert(0, 'asset', str(x))

        df_high = df[df['fractal_high'] == 1]
        df_low = df[df['fractal_low'] == 1]
        df_today = df[df['date'] == "2022-06-27"]

        for row in df_high.itertuples(index=False):
            if ((df_today['high'].iloc[0] > df_high['high']) & (df_today['close'].iloc[0] < df_high['high'])).any():
                validsweep.combine([validsweep, df_high.iloc[[row]]])
        
    return

calculate()

错误信息 #

运行后触发如下报错：

Traceback (most recent call last):
  File "/Users/baker/Desktop/Swing Rejection Strategy/testbed.py", line 54, in <module>
    calculate()
  File "/Users/baker/Desktop/Swing Rejection Strategy/testbed.py", line 44, in calculate
    validsweep.combine([validsweep, df_high.iloc[[row]]])
                                    ~~~~~~~~~~~~^^^^^^^
  File "/Users/baker/Desktop/Swing Rejection Strategy/venv/lib/python3.11/site-packages/pandas/core/indexing.py", line 1073, in __getitem__
    return self._getitem_axis(maybe_callable, axis=axis)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/baker/Desktop/Swing Rejection Strategy/venv/lib/python3.11/site-packages/pandas/core/indexing.py", line 1616, in _getitem_axis
    return self._get_list_axis(key, axis=axis)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/baker/Desktop/Swing Rejection Strategy/venv/lib/python3.11/site-packages/pandas/core/indexing.py", line 1587, in _get_list_axis
    return self.obj._take_with_is_copy(key, axis=axis)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/baker/Desktop/Swing Rejection Strategy/venv/lib/python3.11/site-packages/pandas/core/generic.py", line 3902, in _take_with_is_copy
    result = self._take(indices=indices, axis=axis)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/baker/Desktop/Swing Rejection Strategy/venv/lib/python3.11/site-packages/pandas/core/generic.py", line 3886, in _take
    new_data = self._mgr.take(
               ^^^^^^^^^^^^^^^
  File "/Users/baker/Desktop/Swing Rejection Strategy/venv/lib/python3.11/site-packages/pandas/core/internals/managers.py", line 972, in take
    else np.asanyarray(indexer, dtype=np.intp)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ValueError: invalid literal for int() with base 10: 'EURUSD'

错误原因 #

1. itertuples使用错误：df_high.itertuples(index=False)返回的是namedtuple对象，不是行索引。直接将row传入df_high.iloc[[row]]时，Python会尝试把tuple的第一个元素（即asset列的'EURUSD'）转换为整数索引，导致类型转换失败。
2. if条件逻辑错误：原代码的if判断是检查整个df_high是否存在满足条件的行，而非针对当前循环的单个row进行判断。
3. DataFrame合并方式错误：combine方法需要传入合并函数，不能直接传入DataFrame列表，正确的合并方式应使用pd.concat。

修正后的代码 #

import pandas as pd
import db  # 确保db模块已正确导入

# Create list of assets
assets = ['EURUSD']

# Connect to MarketData.db
conn = db.connect("MarketData.db")
c = conn.cursor()

def calculate():
    # 初始化结果DataFrame
    validsweep = pd.DataFrame({
        'asset': pd.Series(dtype='str'),
        'date': pd.Series(dtype='str'),
        'open': pd.Series(dtype='float'),
        'high': pd.Series(dtype='float'),
        'low': pd.Series(dtype='float'),
        'close': pd.Series(dtype='float'),
        'fractal_high': pd.Series(dtype='int'),
        'fractal_low': pd.Series(dtype='int')})

    for x in assets:
        c.execute(f'SELECT * FROM {x}')
        rows = c.fetchall()
        df = pd.DataFrame(rows)
        df = df.rename(columns={0: "date", 1: "open", 2: "high", 3: "low", 4: "close", 5: "fractal_high", 6: "fractal_low"})
        df.insert(0, 'asset', str(x))

        df_high = df[df['fractal_high'] == 1]
        df_today = df[df['date'] == "2022-06-27"]
        
        # 确保今日数据存在
        if df_today.empty:
            print(f"No data for date 2022-06-27 on asset {x}")
            continue
            
        today_high = df_today['high'].iloc[0]
        today_close = df_today['close'].iloc[0]

        # 方式1：带索引的itertuples，直接用索引取行
        for idx, row in enumerate(df_high.itertuples(index=False)):
            if today_high > row.high and today_close < row.high:
                # 取出当前满足条件的行
                valid_row = df_high.iloc[[idx]]
                # 合并到结果DataFrame
                validsweep = pd.concat([validsweep, valid_row], ignore_index=True)
        
        # 方式2：更高效的向量式操作（推荐，避免循环）
        # filter_mask = (today_high > df_high['high']) & (today_close < df_high['high'])
        # valid_rows = df_high[filter_mask]
        # validsweep = pd.concat([validsweep, valid_rows], ignore_index=True)
        
    print(validsweep)
    return validsweep

calculate()

关键修正点 #

• 用enumerate获取循环的索引，通过索引从df_high中取出对应行；
• 针对当前row的high属性单独判断条件，而非检查整个DataFrame；
• 使用pd.concat合并满足条件的行到结果DataFrame；
• 增加今日数据为空的判断，避免索引越界；
• 额外提供了向量式操作的优化方案（推荐），避免循环提升效率。

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