合并DataFrames触发KeyError问题求助（OSRS机器学习项目）

问题背景 #

我正在开发Old School RuneScape物品价格预测的机器学习项目，用来判断物品是否值得保留。运行代码时触发KeyError: 'id'，尝试修改合并字段后仍无法解决。

原代码 #

import requests
import pandas as pd

headers = {"User-Agent": "Hohman#4134 - ML project"}
prices_url = "https://prices.runescape.wiki/api/v1/osrs/latest"

try:
    prices_response = requests.get(prices_url, headers=headers)
    prices_response.raise_for_status()
    prices_data = prices_response.json()
    prices_data_df = pd.DataFrame(prices_data)

except requests.exceptions.HTTPError:
    print("Error: Could not retrieve prices data. Please check your user agent.")
    prices_data_df = pd.DataFrame()

ids_url = "https://prices.runescape.wiki/api/v1/osrs/mapping"

try:
    ids_response = requests.get(ids_url, headers=headers)
    ids_response.raise_for_status()
    ids_data = ids_response.json()
    ids_dict = {}
    for item in ids_data:
        ids_dict[item['id']] = item['name']

    ids_data_df = pd.DataFrame.from_dict(ids_dict, orient='index', columns=['name'])

except requests.exceptions.HTTPError:
    print("Error: Could not retrieve IDs data. Please check your user agent.")
    ids_data_df = pd.DataFrame()

merged_df = pd.merge(prices_data_df, ids_data_df, left_on='id', right_index=True)

item_name = input("Enter the name of the item: ")
item_data = merged_df[merged_df['name'].str.contains(item_name, case=False)]
latest_price = item_data['high'].iloc[0]

print(f"The predicted price of {item_name} is {latest_price} GP.")

错误信息 #

line 33, in <module>
    merged_df = pd.merge(prices_data_df, ids_data_df, left_on='id', right_index=True)
  line 110, in merge
    op = _MergeOperation(
   line 703, in 
    ) = self._get_merge_keys()
   line 1195, in _get_merge_keys
    left_keys.append(left._get_label_or_level_values(k))
 line 1850, in _get_label_or_level_values
    raise KeyError(key)
KeyError: 'id'

错误原因 #

问题出在价格数据的解析方式：

• OSRS价格API返回的结构是{"data": {物品ID: {"high": 价格, "low": 价格, ...}}}，直接用pd.DataFrame(prices_data)会把整个data字段作为一列，而不是将物品ID和对应的价格数据展开成行。
• 这导致prices_data_df中根本没有id列，合并时自然触发KeyError。

修复步骤 #

1. 正确解析价格数据：从prices_data['data']中提取物品价格，将物品ID转换为DataFrame的一列（命名为id）
2. 保持ID映射数据的索引一致性：ids_data_df以物品ID为索引，合并时用left_on='id'匹配右侧索引即可

修复后完整代码 #

import requests
import pandas as pd

headers = {"User-Agent": "Hohman#4134 - ML project"}
prices_url = "https://prices.runescape.wiki/api/v1/osrs/latest"

try:
    prices_response = requests.get(prices_url, headers=headers)
    prices_response.raise_for_status()
    prices_data = prices_response.json()
    # 从data字段提取数据，将物品ID转为id列
    prices_data_df = pd.DataFrame.from_dict(prices_data['data'], orient='index').reset_index(names='id')

except requests.exceptions.HTTPError:
    print("Error: Could not retrieve prices data. Please check your user agent.")
    prices_data_df = pd.DataFrame()

ids_url = "https://prices.runescape.wiki/api/v1/osrs/mapping"

try:
    ids_response = requests.get(ids_url, headers=headers)
    ids_response.raise_for_status()
    ids_data = ids_response.json()
    ids_dict = {}
    for item in ids_data:
        ids_dict[item['id']] = item['name']

    ids_data_df = pd.DataFrame.from_dict(ids_dict, orient='index', columns=['name'])

except requests.exceptions.HTTPError:
    print("Error: Could not retrieve IDs data. Please check your user agent.")
    ids_data_df = pd.DataFrame()

# 现在prices_data_df有id列，ids_data_df的索引是id，可以正常合并
merged_df = pd.merge(prices_data_df, ids_data_df, left_on='id', right_index=True)

item_name = input("Enter the name of the item: ")
item_data = merged_df[merged_df['name'].str.contains(item_name, case=False)]
if not item_data.empty:
    latest_price = item_data['high'].iloc[0]
    print(f"The latest price of {item_name} is {latest_price} GP.")
else:
    print(f"No data found for item: {item_name}")

额外优化 #

• 添加了空数据检查，避免因输入不存在的物品名称触发索引错误
• 将输出文本从"predicted price"改为"latest price"，因为当前代码仅获取实时价格，尚未实现预测逻辑（后续可添加时间序列预测模型完成ML部分）

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