Jupyter Notebook中Pandas处理CSV触发KeyError('salary_currency')求助

问题情况 #

我从Kaggle下载了一份CSV文件用于学习，导入Pandas模块读取文件后，仅保留salary_currency和salary列。尝试使用loc筛选salary_currency为USD的数据以获取最高、最低薪资时，出现了KeyError('salary_currency')报错。

报错信息 #

KeyError                                  Traceback (most recent call last)
Cell In[29], line 2
      1 df.head(10)
----> 2 df=df.loc[df['salary_currency']=="USD"]
      3 max_salary=df['salary'].max()
      4 min_salary=df['salary'].min()

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\series.py:1112, in Series.__getitem__(self, key)
   1109     return self._values[key]
   1111 elif key_is_scalar:
-> 1112     return self._get_value(key)
   1114 # Convert generator to list before going through hashable part
   1115 # (We will iterate through the generator there to check for slices)
   1116 if is_iterator(key):

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\series.py:1228, in Series._get_value(self, label, takeable)
   1225     return self._values[label]
   1227 # Similar to Index.get_value, but we do not fall back to positional
-> 1228 loc = self.index.get_loc(label)
   1230 if is_integer(loc):
   1231     return self._values[loc]

File ~\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\pandas\core\indexes\range.py:417, in RangeIndex.get_loc(self, key)
    415         raise KeyError(key) from err
...
-> 417     raise KeyError(key)
    418 self._check_indexing_error(key)
    419 raise KeyError(key)

KeyError: 'salary_currency'

我的代码 #

import pandas as pd
Data = pd.read_csv('ds_salaries.csv')
df=Data[['salary_currency','salary']]
df=df.loc[df['salary_currency']=="USD"]
max_salary=df['salary'].max()
min_salary=df['salary'].min()
print(max_salary,"\n",min_salary,"\n",df)

使用loc前的DataFrame内容 #

salary_currency salary
0   EUR 80000
1   USD 30000
2   USD 25500
3   USD 175000
4   USD 120000
... ... ...
3750    USD 412000
3751    USD 151000
3752    USD 105000
3753    USD 100000
3754    INR 7000000
3755 rows × 2 columns

问题原因与解决方法 #

从提供的DataFrame输出能明显看出：CSV文件的列名和数据没有正确拆分——第一行的salary_currency salary是合并的文本，后续每行的货币和薪资也都挤在同一个单元格里，导致Pandas并没有识别出salary_currency和salary这两个独立列，自然会报KeyError。

解决步骤 #

1. 指定正确的分隔符读取CSV
   你的数据是用空格分隔的，读取时需要指定sep='\s+'（匹配一个或多个空格），让Pandas正确拆分列：

import pandas as pd
# 用任意数量空格作为分隔符读取文件
Data = pd.read_csv('ds_salaries.csv', sep='\s+')
# 现在可以正确选取两列
df = Data[['salary_currency', 'salary']]
# 筛选USD数据
df = df.loc[df['salary_currency'] == "USD"]
max_salary = df['salary'].max()
min_salary = df['salary'].min()
print(max_salary, "\n", min_salary, "\n", df)

2. 手动指定列名（备用方案）
   如果上面的方法仍有问题，可以手动指定列名并跳过错误的首行：

import pandas as pd
# 跳过首行，手动设置列名，用空格分隔数据
Data = pd.read_csv('ds_salaries.csv', skiprows=1, names=['salary_currency', 'salary'], sep='\s+')
# 筛选USD数据并计算极值
df = Data.loc[Data['salary_currency'] == "USD"]
max_salary = df['salary'].max()
min_salary = df['salary'].min()
print(max_salary, "\n", min_salary, "\n", df)

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