在数据分析领域，Pandas 被认为是大多数基于 Python 的数据探索的起点。但如果我们想使用像 ClickHouse 这样的 OLAP 数据库来查询我们的 DataFrame，以利用其查询引擎和 SQL 支持呢？
这就是由 ClickHouse 提供支持的 Python 库 chDB 发挥作用的地方。我们已经在博客上 几次 介绍过 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 页面 并试一试！
