如何将数据添加到Pandas DataFrame索引列？解决长度不匹配报错

目标DataFrame结构 #

期望生成如下结构的DataFrame：

Ratio  A  B  C
ROA
ROE
ROI
PE Ratio

尝试的代码 #

以下是尝试运行的代码：

import pandas as pd

col_list = ['A', 'B', 'C']

data = pd.DataFrame([['ROA', 'ROE', 'ROI']], columns=col_list)
data.index.names = ['Ratios']

ratio_rows = ['ROA', 'ROE', 'ROI', 'PE Ratio']

data.set_axis(ratio_rows)

报错信息 #

运行最后一行代码时出现如下错误：

Traceback (most recent call last):

  Cell In[7], line 1
data.set_axis(ratio_rows)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\frame.py:5034 in set_axis
    return super().set_axis(labels, axis=axis, copy=copy)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\generic.py:708 in set_axis
    return self._set_axis_nocheck(labels, axis=axis, inplace=False, copy=copy)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\generic.py:720 in _set_axis_nocheck
    setattr(obj, obj._get_axis_name(axis), labels)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\generic.py:6002 in __setattr__
    return object.__setattr__(self, name, value)

  File pandas\_libs\properties.pyx:69 in pandas._libs.properties.AxisProperty.__set__

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\generic.py:730 in _set_axis
    self._mgr.set_axis(axis, labels)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\internals\managers.py:225 in set_axis
    self._validate_set_axis(axis, new_labels)

  File D:\Users\Mahmoud\anaconda3\Lib\site-packages\pandas\core\internals\base.py:70 in _validate_set_axis
    raise ValueError(

ValueError: Length mismatch: Expected axis has 0 elements, new values have 4 elements

解决方案 #

错误原因分析 #

1. 初始创建的DataFrame仅包含1行数据，但要设置的索引列表ratio_rows有4个元素，二者长度不匹配；
2. set_axis()方法默认修改的是列标签（axis=1），而非行索引，这也直接触发了长度不匹配的报错。

正确实现方式 #

要生成目标结构的DataFrame，最直接的方式是在创建时就指定行索引和列名：

import pandas as pd

col_list = ['A', 'B', 'C']
ratio_rows = ['ROA', 'ROE', 'ROI', 'PE Ratio']

# 创建空的DataFrame，指定索引和列
data = pd.DataFrame(index=ratio_rows, columns=col_list)
# 设置索引名称
data.index.name = 'Ratio'

如果后续需要填充数据，直接通过索引和列名定位赋值即可：

# 示例：给ROA行的A列赋值0.15
data.loc['ROA', 'A'] = 0.15

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