Python中plotly scatter_geo美国州级气泡不显示问题及解决

问题场景 #

使用以下代码调用Plotly的px.scatter_geo绘制美国各州气泡图，数据涵盖44个美国州，但气泡无法正常显示：

query = '''SELECT country, state, count(puppies) as rich_pop FROM data group by state HAVING country=="United States"''' 

df = sqldf(query)

fig = px.scatter_geo(df, locations="state", locationmode='USA-states', scope="usa", size="rich_pop", color='count')

fig.show()

问题根源 #

Plotly的locationmode='USA-states'模式要求州缩写必须为大写字母，如果数据中的州缩写是小写或混合大小写，就无法匹配内置的美国州定位数据，导致气泡无法显示。

另外原代码中color='count'存在字段错误：查询结果里没有count字段，应使用聚合后的rich_pop字段。

修正方案 #

可以通过两种方式修正州缩写的大小写问题：

方案1：在SQL查询中直接转换为大写

query = '''SELECT country, UPPER(state) as state, count(puppies) as rich_pop FROM data group by state HAVING country=="United States"''' 

df = sqldf(query)

# 修正color参数为正确的字段名
fig = px.scatter_geo(df, locations="state", locationmode='USA-states', scope="usa", size="rich_pop", color='rich_pop')

fig.show()

方案2：在DataFrame中统一转换为大写

query = '''SELECT country, state, count(puppies) as rich_pop FROM data group by state HAVING country=="United States"''' 

df = sqldf(query)
# 将州缩写批量转换为大写
df['state'] = df['state'].str.upper()

# 修正color参数为正确的字段名
fig = px.scatter_geo(df, locations="state", locationmode='USA-states', scope="usa", size="rich_pop", color='rich_pop')

fig.show()

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