读取CSV至Pandas DataFrame并解决对数回报计算的类型错误问题

原代码 #

import pandas as pd
import numpy as np

def portfolio_log_returns(portfolio):
    dataset = pd.read_csv(portfolio)
    log_returns = pd.DataFrame(columns=dataset.columns)

    for col in dataset.columns:
        log_returns[col] = np.log(dataset[col]/dataset[col].shift(1))
    
    log_returns = log_returns.dropna()
    return log_returns

log_returns_df = portfolio_log_returns('some_csv_file.csv')

报错信息 #

log_returns_df = portfolio_log_returns('some_csv_file.csv')
Traceback (most recent call last):

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\ops\array_ops.py:171 in _na_arithmetic_op
result = func(left, right)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\computation\expressions.py:239 in evaluate
return _evaluate(op, op_str, a, b)  # type: ignore[misc]

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\computation\expressions.py:128 in _evaluate_numexpr
result = _evaluate_standard(op, op_str, a, b)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\computation\expressions.py:70 in _evaluate_standard
return op(a, b)

TypeError: unsupported operand type(s) for /: 'str' and 'NoneType'


During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  Cell In[4], line 1
log_returns_df = portfolio_log_returns('some_csv_file.csv')

  Cell In[1], line 9 in portfolio_log_returns
log_returns[col] = np.log(dataset[col]/dataset[col].shift(1))

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\ops\common.py:81 in new_method
return method(self, other)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\arraylike.py:210 in __truediv__
return self._arith_method(other, operator.truediv)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\series.py:6112 in _arith_method
return base.IndexOpsMixin._arith_method(self, other, op)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\base.py:1348 in _arith_method
result = ops.arithmetic_op(lvalues, rvalues, op)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\ops\array_ops.py:232 in arithmetic_op
res_values = _na_arithmetic_op(left, right, op)  # type: ignore[arg-type]

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\ops\array_ops.py:178 in _na_arithmetic_op
result = _masked_arith_op(left, right, op)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\ops\array_ops.py:116 in _masked_arith_op
result[mask] = op(xrav[mask], yrav[mask])

TypeError: unsupported operand type(s) for /: 'str' and 'str'

问题原因 #

核心是CSV中的数值列被解析成了字符串类型，导致除法运算（/）无法在字符串之间执行，触发类型错误。常见触发场景：

• CSV数据包含非数值字符（如千分位逗号1,000、货币符号$100）
• CSV存在空值或非数值内容，pandas自动将列转为object（字符串）类型
• 读取CSV时未指定正确参数，导致数值列识别失败

修复方案 #

1. 修正CSV数据格式 #

确保需要计算的列是纯数值，移除所有非数字字符（逗号、符号等）。

2. 优化CSV读取逻辑 #

通过pd.read_csv参数强制识别数值类型：

# 方式1：指定特定列的数值类型
dataset = pd.read_csv(portfolio, dtype={'目标列名': float})

# 方式2：自动转换所有列到合适的数值类型
dataset = pd.read_csv(portfolio).convert_dtypes()

# 方式3：处理带千分位逗号的数值
dataset = pd.read_csv(portfolio, thousands=',')

3. 重构函数，提前处理类型转换 #

在计算前批量转换列类型，同时处理异常值：

import pandas as pd
import numpy as np

def portfolio_log_returns(portfolio):
    dataset = pd.read_csv(portfolio)
    # 批量转换所有列为数值类型，无法转换的设为NaN
    dataset = dataset.apply(pd.to_numeric, errors='coerce')
    # 直接对整个DataFrame计算对数回报，避免循环
    log_returns = np.log(dataset / dataset.shift(1)).dropna()
    return log_returns

log_returns_df = portfolio_log_returns('some_csv_file.csv')

该版本优势：

• 用pd.to_numeric批量处理类型转换，异常值转为NaN不中断程序
• 直接对DataFrame做向量运算，比循环效率更高
• 链式调用简化代码逻辑

内容的提问来源于stack exchange，提问作者Mahmoud Abdel-Rahman