Dash读取CSV绘制散点图报KeyError等错误排查方案咨询

原方案计划直连Oracle数据库读取数据实现可视化，因个人能力限制暂无法完成直连开发，调整方案为先将数据导出为CSV文件，读取CSV完成可视化实现。初始编写代码如下：

import cx_Oracle
import os
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

import dash
import dash_html_components as html
import dash_core_components as dcc
from dash import html, dcc
import plotly.graph_objects as go
import pandas as pd
from dash.dependencies import Input, Output
import dash_bootstrap_components as dbc

import cx_Oracle
import os

# 注释掉的Oracle直连代码段省略

########################################################################

# Load data
df = pd.read_csv('data/20220607.csv', encoding='cp949')
print(df)

# Initialize the app
app = dash.Dash()

app.layout = html.Div([
                        dcc.Graph(
                            id='scatterplot',
                                 figure={
                                     'data':[go.Scatter(
                                     x=df[df['category']],
                                     y=df[df['fee']],
                                     mode='markers',
                                     marker={
                                         'size':12,
                                         'color':'rgb(51,204,153)',
                                         'symbol':'pentagon',
                                         'line':{'width':2}
                                     }
                                 )],
                                         'layout':go.Layout(title='My Scatterplot',
                                                            xaxis={'title':'Some X title'})}
                                 ),
                        dcc.Graph(
                            id='scatterplot2',
                                 figure={
                                     'data':[go.Scatter(
                                     x=df[df['category']],
                                     y=df[df['fee']],
                                     mode='markers',
                                     marker={
                                         'size':12,
                                         'color':'rgb(200,204,53)',
                                         'symbol':'pentagon',
                                         'line':{'width':2}
                                     }
                                 )],
                                         'layout':go.Layout(title='Second Plot',
                                                            xaxis={'title':'Some X title'})}
                                 )
                       ])

if __name__ == '__main__':
    app.run_server(debug=True)

初始运行报错 #

运行初始代码触发KeyError: 'category'报错，报错栈如下：

Traceback (most recent call last):
  File "C:\Python38\lib\site-packages\pandas\core\indexes\base.py", line 3621, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas\_libs\index.pyx", line 136, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index.pyx", line 163, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\hashtable_class_helper.pxi", line 5198, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas\_libs\hashtable_class_helper.pxi", line 5206, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'category'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "testtest.py", line 77, in <module>
    x=df[df['category']],
  File "C:\Python38\lib\site-packages\pandas\core\frame.py", line 3505, in __getitem__
    indexer = self.columns.get_loc(key)
  File "C:\Python38\lib\site-packages\pandas\core\indexes\base.py", line 3623, in get_loc
    raise KeyError(key) from err
KeyError: 'category'

尝试修复后新增报错 #

为排查列名问题，添加去空白代码df = df.apply(lambda x: x.str.strip())后，首先出现Dash组件包弃用警告，同时触发新的AttributeError报错，完整信息如下：

testtest.py:8: UserWarning: 
The dash_html_components package is deprecated. Please replace
`import dash_html_components as html` with `from dash import html`
  import dash_html_components as html
testtest.py:9: UserWarning: 
The dash_core_components package is deprecated. Please replace
`import dash_core_components as dcc` with `from dash import dcc`
  import dash_core_components as dcc

Traceback (most recent call last):
  File "testtest.py", line 65, in <module>
    df = df.apply(lambda x: x.str.strip())
  File "C:\Python38\lib\site-packages\pandas\core\frame.py", line 8839, in apply
    return op.apply().__finalize__(self, method="apply")
  File "C:\Python38\lib\site-packages\pandas\core\apply.py", line 727, in apply
    return self.apply_standard()
  File "C:\Python38\lib\site-packages\pandas\core\apply.py", line 851, in apply_standard
    results, res_index = self.apply_series_generator()
  File "C:\Python38\lib\site-packages\pandas\core\apply.py", line 867, in apply_series_generator
    results[i] = self.f(v)
  File "testtest.py", line 65, in <lambda>
    df = df.apply(lambda x: x.str.strip())
  File "C:\Python38\lib\site-packages\pandas\core\generic.py", line 5575, in __getattr__
    return object.__getattribute__(self, name)
  File "C:\Python38\lib\site-packages\pandas\core\accessor.py", line 182, in __get__
    accessor_obj = self._accessor(obj)
  File "C:\Python38\lib\site-packages\pandas\core\strings\accessor.py", line 177, in __init__
    self._inferred_dtype = self._validate(data)
  File "C:\Python38\lib\site-packages\pandas\core\strings\accessor.py", line 231, in _validate
    raise AttributeError("Can only use .str accessor with string values!")
AttributeError: Can only use .str accessor with string values!

• 清理冗余导入，解决弃用警告：删除重复导入的cx_Oracle、os、pandas，删除未使用的numpy、matplotlib.pyplot、dash、dash_bootstrap_components、Input/Output导入，移除已弃用的dash_html_components、dash_core_components导入语句，统一使用from dash import html, dcc写法即可消除警告。
• 定位KeyError: 'category'根因：
  1. 读取CSV后先执行print(df.columns.tolist())打印实际列名，因为CSV使用cp949编码，列名可能存在转义偏差、首尾空格，和代码中写死的category、fee不一致，需根据打印出的实际列名调整代码。
  2. 修正绘图取值语法错误：原代码中x=df[df['category']]是布尔索引的写法（用列值作为筛选条件取行），直接取列作为坐标轴数据应写为x=df['category']，y轴取值同理。
• 修复AttributeError问题：.str是Pandas字符串类型专属访问器，全局对整个DataFrame做apply(lambda x: x.str.strip())会在数值列（比如费用列）上调用字符串方法，必然报错。正确的去空白逻辑分两步：
  1. 先对列名去空白：df.columns = df.columns.str.strip()
  2. 仅对字符串类型的内容列去空白，跳过数值列：

     str_cols = df.select_dtypes(include='object').columns
     df[str_cols] = df[str_cols].apply(lambda x: x.str.strip())
     

import pandas as pd
import plotly.graph_objects as go
from dash import Dash, html, dcc

# 读取CSV数据
df = pd.read_csv('data/20220607.csv', encoding='cp949')
# 列名去首尾空白
df.columns = df.columns.str.strip()
# 打印列名确认字段匹配
print("数据集列名清单：", df.columns.tolist())
# 仅对字符串类型列做内容去空白
str_cols = df.select_dtypes(include='object').columns
df[str_cols] = df[str_cols].apply(lambda x: x.str.strip())

# 初始化Dash应用
app = Dash(__name__)

app.layout = html.Div([
    dcc.Graph(
        id='scatterplot',
        figure={
            'data': [go.Scatter(
                x=df['category'],
                y=df['fee'],
                mode='markers',
                marker={
                    'size': 12,
                    'color': 'rgb(51,204,153)',
                    'symbol': 'pentagon',
                    'line': {'width': 2}
                }
            )],
            'layout': go.Layout(
                title='My Scatterplot',
                xaxis={'title': '分类'},
                yaxis={'title': '费用'}
            )
        }
    ),
    dcc.Graph(
        id='scatterplot2',
        figure={
            'data': [go.Scatter(
                x=df['category'],
                y=df['fee'],
                mode='markers',
                marker={
                    'size': 12,
                    'color': 'rgb(200,204,53)',
                    'symbol': 'pentagon',
                    'line': {'width': 2}
                }
            )],
            'layout': go.Layout(
                title='Second Plot',
                xaxis={'title': '分类'},
                yaxis={'title': '费用'}
            )
        }
    )
])

if __name__ == '__main__':
    app.run_server(debug=True)

注：如果打印列名后发现实际列名不是category、fee，替换代码中对应列名字符串即可。

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