在数据分析领域，Pandas 被认为是大多数基于 Python 的数据探索的起点。但是，如果我们想使用像 ClickHouse 这样的 OLAP 数据库来查询我们的 DataFrame，以利用其查询引擎和 SQL 支持，该怎么办呢？
这就是 chDB（一个由 ClickHouse 驱动的 Python 库）发挥作用的地方。我们已经在博客上 几次 介绍过 chDB，但在本文中，我们将重点介绍其查询 Pandas DataFrame、将它们连接在一起、聚合数据，然后将结果导出回 Pandas 的能力。
chDB 可通过 PyPi 获取，这意味着我们可以使用 pip 安装它
pip install chdb
我们还需要安装 Pandas 和 PyArrow，因为 chDB 的 DataFrame 功能依赖于这些库
pip install pandas pyarrow
好的，现在我们可以开始了。我们将探索 Kaggle 的加拿大主要城市房价数据集，其中包含 2021 年人口普查的房地产数据。
下载 CSV 文件后，我们将把它读入 Pandas DataFrame。
import pandas as pd
house_prices = pd.read_csv(
filepath_or_buffer="data/HouseListings-Top45Cities-10292023-kaggle.csv",
encoding = "ISO-8859-1"
)
然后我们可以看看其中的几条记录
house_prices.head(n=2).T
0 1
City Toronto Toronto
Price 779900.0 799999.0
Address #318 -20 SOUTHPORT ST #818 -60 SOUTHPORT ST
Number_Beds 3 3
Number_Baths 2 1
Province Ontario Ontario
Population 5647656 5647656
Latitude 43.7417 43.7417
Longitude -79.3733 -79.3733
Median_Family_Income 97000.0 97000.0
使用 ClickHouse 查询 DataFrame
要在 chDB 中查询 DataFrame，我们需要导入 chdb.dataframe 模块
import chdb.dataframe as cdf
此模块有一个名为 query 的函数可供我们使用。我们可以传入 1 个或多个 DataFrame 作为命名参数，然后在查询中寻址这些参数。我们使用的参数名称可以寻址为 __<parameter-name>__。以下查询查找了拥有最多房产的前 10 个城市
cdf.query(
house_prices=house_prices,
sql="""
FROM __house_prices__
SELECT City, Province, count(*)
GROUP BY ALL
LIMIT 10
""")
City Province count()
b'White Rock' b'British Columbia' 1175
b'Toronto' b'Ontario' 1276
b'Kelowna' b'British Columbia' 1280
b'Winnipeg' b'Manitoba' 530
b'Winnipeg' b'Ontario' 1
b'Red Deer' b'Alberta' 326
b'Thunder Bay' b'Ontario' 154
b'Lethbridge' b'Alberta' 379
b'St. Catharines' b'Ontario' 1268
b'Trois-Rivieres' b'Quebec' 165
使用 ClickHouse 连接 DataFrame
除了查询单个 DataFrame 之外，我们还可以将它们连接在一起。因此，我们将引入另一个数据集，其中包含 有关加拿大城市的元数据。让我们看看这个数据集
cities = pd.read_csv(
filepath_or_buffer="data/canadacities.csv"
)
cities.head(n=1).T
0
city Toronto
city_ascii Toronto
province_id ON
province_name Ontario
lat 43.7417
lng -79.3733
population 5647656.0
density 4427.8
timezone America/Toronto
ranking 1
postal M5T M5V M5P M5S M5R M5E M5G M5A M5C M5B M5M M5...
id 1124279679
我们可以通过 city_ascii 和 province_name 字段将此 DataFrame 与第一个 DataFrame 连接起来。
top_cities = cdf.query(
house_prices=house_prices,
cities=cities,
sql="""
FROM __house_prices__ AS hp
JOIN __cities__ AS c
ON c.city_ascii = hp.City AND c.province_name = hp.Province
SELECT City, Province, count(*),
round(avg(Price)) AS avgPrice,
round(max(Price)) AS maxPrice,
ranking, density
GROUP BY ALL
LIMIT 10
""")
如果我们查看 top_cities 变量，我们将看到类似于以下内容
City Province count() avgPrice maxPrice ranking density
b'Brantford' b'Ontario' 628 955923.0 6495000.0 2 1061.2
b'Hamilton' b'Ontario' 1289 975543.0 10995000.0 2 509.1
b'Thunder Bay' b'Ontario' 154 459703.0 5599000.0 2 332.1
b'Caledon' b'Ontario' 1336 1383366.0 9995000.0 3 111.2
b'Calgary' b'Alberta' 1322 660046.0 5250000.0 1 1592.4
b'Windsor' b'Ontario' 720 643019.0 2750000.0 2 1572.8
b'Medicine Hat' b'Alberta' 277 448137.0 1475000.0 3 565.1
b'Montreal' b'Quebec' 212 931392.0 4400000.0 1 4833.5
b'Edmonton' b'Alberta' 1351 425582.0 4463445.0 1 1320.4
b'Sudbury' b'Ontario' 203 596087.0 7699900.0 2 52.1
top_cities 的类型为 <class 'chdb.dataframe.query.Table'>，实际上我们也可以使用 SQL 查询 chDB 表。我们可以使用 query 函数来执行此操作，其中底层表可以作为 __table__ 访问。
因此，如果我们想获取 top_cities 的前 5 行，我们可以编写以下内容
top_cities.query("""
FROM __table__
SELECT City, maxPrice, ranking, density
LIMIT 5
""")
City maxPrice ranking density
b'Brantford' 6495000.0 2 1061.2
b'Hamilton' 10995000.0 2 509.1
b'Thunder Bay' 5599000.0 2 332.1
b'Caledon' 9995000.0 3 111.2
b'Calgary' 5250000.0 1 1592.4
将 chDB 表导出到 Pandas DataFrame
如果我们已经使用 ClickHouse 进行了足够的查询，我们始终可以使用 to_pandas 函数将该表转换回 Pandas DataFrame
top_cities_df = top_cities.to_pandas()
让我们在 Pandas 中做一些查询来完成
(top_cities_df[top_cities_df["Province"] == b"Ontario"]
.sort_values(["ranking", "density"])
.drop(["Province"], axis=1)
)
City count() avgPrice maxPrice ranking density
b'Sudbury' 203 596087.0 7699900.0 2 52.1
b'Thunder Bay' 154 459703.0 5599000.0 2 332.1
b'Hamilton' 1289 975543.0 10995000.0 2 509.1
b'Brantford' 628 955923.0 6495000.0 2 1061.2
b'Windsor' 720 643019.0 2750000.0 2 1572.8
b'Caledon' 1336 1383366.0 9995000.0 3 111.2
结论
chDB 在不断发展，但它已经可以做的事情非常酷。所以前往 GitHub 页面 试用一下吧！
