如何在Python DataFrame中实现类MySQL单行查询并输出对应值

问题需求 #

想要在Python的DataFrame中实现类似MySQL的查询操作，例如执行如下SQL语句：

select columnx, 
from table 
where columny = 'thisvalue' and columnx = 'thisvalue2'

需要获取满足columny和columnx匹配指定值的单行对应值（已知符合条件的记录唯一），同时想了解如何打印该值。

当前代码 #

from pandas.io.html import import_optional_dependency
import requests
import pandas as pd
from bs4 import BeautifulSoup
import lxml

smurl = 'GOOGLE DOCS URL WITH TABLE OF DATA'
smdata = requests.get(smurl).content
smdata = smdata.decode('UTF-8')
smlist = pd.read_html(smdata)
smdf = smlist[-1]
smdf.columns = smdf.iloc[0]
smdf = smdf[1:]
smdf = smdf.sort_values(['x-coordinate', 'y-coordinate'], ascending=[True, True])

ccountmax = max(smdf['y-coordinate'])
rcountmax = max(smdf['x-coordinate'])
rcount= 0
ccount = 0

print(smdf)
print(rcountmax)
print(ccountmax)

for x in range(0, int(rcountmax)):
  for y in range(0, int(ccountmax)):
    char = smdf[int(smdf['x-coordinate']) == x & int(smdf['y-coordinate']) == y]
    if char.empty:
      print(' ', end='')
    else:
      print(char['character'], end='')  
print('\n')

实现方案 #

1. 修正筛选逻辑（核心问题修复） #

原代码中x & int(smdf['y-coordinate']) == y的写法错误，&优先级高于==，必须给每个条件单独加括号，同时注意列值的类型匹配（比如如果x-coordinate是字符串类型，要把整数x转成字符串）：

# 正确的筛选条件写法
filtered_row = smdf[(smdf['x-coordinate'] == str(x)) & (smdf['y-coordinate'] == str(y))]
# 若列是数值类型，直接用数值匹配：
# filtered_row = smdf[(smdf['x-coordinate'] == x) & (smdf['y-coordinate'] == y)]

2. 提取并打印唯一值 #

因为已知符合条件的记录唯一，用iloc[0]直接提取单个值，避免返回Series类型：

if not filtered_row.empty:
    char_value = filtered_row['character'].iloc[0]
    print(char_value, end='')
else:
    print(' ', end='')

3. 修复后的完整循环代码 #

替换原循环部分即可：

for x in range(0, int(rcountmax)):
    for y in range(0, int(ccountmax)):
        filtered_row = smdf[(smdf['x-coordinate'] == str(x)) & (smdf['y-coordinate'] == str(y))]
        if filtered_row.empty:
            print(' ', end='')
        else:
            print(filtered_row['character'].iloc[0], end='')
    print()  # 每行结束换行，让输出排版更整齐

4. 直接查询指定值的单行 #

如果不需要循环，仅需查询某一组x、y对应的character值，可直接写：

# 示例：查询x=2，y=3对应的字符
target_x = 2
target_y = 3
result = smdf.loc[(smdf['x-coordinate'] == str(target_x)) & (smdf['y-coordinate'] == str(target_y)), 'character']
if not result.empty:
    print(result.iloc[0])

内容的提问来源于stack exchange，提问作者Jonathan Jon E