Pandas：如何将一个 DataFrame 中的所有值除以另一个 DataFrame 中的值？

Question

[编辑]我实际上已经弄清楚了这一点，你可以在下面看到我的答案。 ]

在引用其他问题< /a> 具有几乎相同的标题，我已经看过它并尝试了他们的解决方案，但没有运气。我不确定我到底做错了什么，但我得到的错误与其他问题中的错误不同。

我想做的是基于两个基础交易对创建一个人工交易对。这是针对配对交易的。

例如。 “ETHUSD/BTCUSD”

在 TradingView 上可以看到这样的图表，我正在尝试在本地进行相同的计算。

因此，我将 ETHUSD 和 BTCUSD 的 OHLCV 数据保存在两个 DataFrame 中。我正在尝试合并它们并将每个 OHLC 值与基本对（例如“ETHUSD”除以第二对，例如“BTCUSD”）

这是我到目前为止所写的内容，这给了我一个类型错误：

def create_synthetic_pair(base, quote, timeframe, limit):
    """
      This is not working yet. 
    """
    base_bars = exchange.fetch_ohlcv(base, timeframe, limit)
    quote_bars = exchange.fetch_ohlcv(quote, timeframe, limit)

    df_base = pd.DataFrame(base_bars[:-1], columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])
    df_base['timestamp'] = pd.to_datetime(df_base['timestamp'], unit='ms')
    
    df_quote = pd.DataFrame(quote_bars[:-1], columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])
    df_quote['timestamp'] = pd.to_datetime(df_quote['timestamp'], unit='ms')
    
    df_synth = (df_base.merge(df_quote, on='timestamp', how='left', suffixes=['_base', '_quote']) #how='left', sort=False)
                  .eval("""
                        open=open_base/open_quote
                        high=high_base/high_quote
                        low=low_base/low_quote
                        close=close_base/close_quote
                        """)
                )
    
    return df_synth

当我运行该代码时我收到以下错误：

File "/home/jonathon/Developer/*****/*****/*****/main.py", line 105, in run_bot
    create_synthetic_pair('ETH/USDT', 'BTC/USDT', "1m", 100)
  File "/home/jonathon/Developer/*****/*****/*****/main.py", line 131, in create_synthetic_pair
    .eval("""
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/frame.py", line 4234, in eval
    return _eval(expr, inplace=inplace, **kwargs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/eval.py", line 350, in eval
    parsed_expr = Expr(expr, engine=engine, parser=parser, env=env)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 811, in __init__
    self.terms = self.parse()
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 830, in parse
    return self._visitor.visit(self.expr)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 415, in visit
    return visitor(node, **kwargs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 421, in visit_Module
    return self.visit(expr, **kwargs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 415, in visit
    return visitor(node, **kwargs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 636, in visit_Assign
    return self.visit(node.value, **kwargs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 415, in visit
    return visitor(node, **kwargs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 538, in visit_BinOp
    return self._maybe_evaluate_binop(op, op_class, left, right)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 505, in _maybe_evaluate_binop
    res = op(lhs, rhs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/expr.py", line 541, in <lambda>
    return lambda lhs, rhs: Div(lhs, rhs)
  File "/home/jonathon/.local/share/virtualenvs/*****-r-7MCNdX/lib/python3.10/site-packages/pandas/core/computation/ops.py", line 537, in __init__
    raise TypeError(
TypeError: unsupported operand type(s) for /: 'object' and 'object'

我不明白为什么当这些应该是浮点变量而不是对象时我会收到类型错误

我缺少什么？

Answer 1

所以，我实际上自己解决了这个问题。如果其他人也尝试做同样的事情，我将分享我的答案。

实际上，TypeError 是由于 DataFrame 为空而导致的，这与 pandas 无关，而是因为我错误地调用了交换 API 而发生的。

对于任何感兴趣的人，我正在使用 ccxt，并且我将开头从以下位置更改

def create_synthetic_pair(base, quote, timeframe, limit):

    base_bars = exchange.fetch_ohlcv(base, timeframe, limit)
    quote_bars = exchange.fetch_ohlcv(quote, timeframe, limit)

为：

def create_synthetic_pair(base, quote, _timeframe, _limit):

    base_bars = exchange.fetch_ohlcv(base, timeframe=_timeframe, limit=_limit)
    quote_bars = exchange.fetch_ohlcv(quote, timeframe=_timeframe, limit=_limit)

修复了 TypeError。然而，它仍然无法正常工作，而且奇怪的是，除了“关闭”列之外，它对每个列都有效。我最终将函数的这一部分更改

df_synth = (df_base.merge(df_quote, on='timestamp', how='left', suffixes=['_base', '_quote']) #how='left', sort=False)
                  .eval("""
                        open=open_base/open_quote
                        high=high_base/high_quote
                        low=low_base/low_quote
                        close=close_base/close_quote
                        """)
                )

为：

df_synth = (df_base.merge(df_quote, on='timestamp', how='left', suffixes=['_base', '_quote'], sort=False)
                  .pipe(lambda x: x.assign(open=x.open_base/x.open_quote, 
                                           high=x.high_base/x.high_quote,
                                           low=x.low_base/x.low_quote,
                                           close=x.close_base/x.close_quote,
                                           volume=x.volume_base/x.volume_quote))
                )

然后我最终得到了正确的 OHLCV 数据，但从基础和引用 DataFrames 中得到了一堆额外不需要的列。

为了删除这些列，我在下面添加了以下内容：

for name in df_synth.iteritems():
      if name[0].endswith('_base') or name[0].endswith('_quote'):
        df_synth = df_synth.drop(name[0] , 1)

现在它可以工作了！

然而，我实际上仍然没有让它与 .eval() 一起工作，而是使用了 .pipe()，所以如果有人想评论如何用 .eval() 做同样的事情，那么将受到欢迎，因为这可能是一个更优雅的解决方案。

我想我会将其标题更改为“Pandas：如何从两个基础交易对创建合成交易对 OHLCV”

（如果您能想到一个仍能体现该含义的不太冗长的标题，请告诉我

）知道是否有人需要这个，但我想出的最终工作功能是：

def create_synthetic_pair(base_bars, quote_bars): 
    """
      This takes raw kline data from calling
      ccxt.exchange.fetch_ohlcv() as inputs,
      turns them into DataFrames, then divides 
      each base OHLCV data point value by its 
      corresponding quote value, and returns
      a new DataFrame with the new OHLCV values.
    """
    global exchange

    df_base = pd.DataFrame(base_bars[:-1], columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])
    df_base['timestamp'] = pd.to_datetime(df_base['timestamp'], unit='ms')
    
    df_quote = pd.DataFrame(quote_bars[:-1], columns=['timestamp', 'open', 'high', 'low', 'close', 'volume'])
    df_quote['timestamp'] = pd.to_datetime(df_quote['timestamp'], unit='ms')

    df_synth = (df_base.merge(df_quote, on='timestamp', how='left', suffixes=['_base', '_quote'], sort=False)
                  .pipe(lambda x: x.assign(open=x.open_base/x.open_quote, 
                                           high=x.high_base/x.high_quote,
                                           low=x.low_base/x.low_quote,
                                           close=x.close_base/x.close_quote,
                                           volume=x.volume_base/x.volume_quote))
                )
    for name in df_synth.iteritems():
      if name[0].endswith('_base') or name[0].endswith('_quote'):
        df_synth = df_synth.drop(name[0] , 1)

    print("\n------------------------------------------------\n"
          +f"[df_base]\n{df_base}"
          +"\n------------------------------------------------\n"
          +f"[df_quote]\n{df_quote}"
          +"\n------------------------------------------------\n"
          +f"[df_synth]\n{df_synth}"
          +"\n------------------------------------------------\n")
    return df_synth