Exploring data with Marimo notebooks and chDB

```
query_reactive = f"""
SELECT
    toYear(date) AS year,
    round(avg(price)) AS price
FROM remoteSecure(
    '{os.environ.get("CLICKHOUSE_CLOUD_HOSTNAME")}',
    'default.pp_complete',
    '{os.environ.get("CLICKHOUSE_CLOUD_USER")}',
    '{os.environ.get("CLICKHOUSE_CLOUD_PASSWORD")}'
)
WHERE town = '{town_selector.value}'
GROUP BY year
ORDER BY year
"""

df_reactive = chdb.query(query_reactive, "DataFrame")
df_reactive
```

Now create a chart that updates automatically when you change the town.
You can move the chart above the dynamic dataframe so that it appears
below the cell with the dropdown.

```
fig_reactive = px.line(
    df_reactive,
    x='year',
    y='price',
    title=f'Average Property Prices in {town_selector.value} Over Time',
    labels={'price': 'Average Price (£)', 'year': 'Year'}
)

fig_reactive.update_traces(mode='lines+markers')
fig_reactive.update_layout(hovermode='x unified')
fig_reactive
```

Now when you select a town from the drop-down the chart will update dynamically:

![Marimo dynamic chart](/docs/assets/images/7-4f0e8121f7f1c75a100f6e52623b44b7.gif)

### Exploring price distributions with interactive box plots[​](#exploring-price-distributions "Direct link to Exploring price distributions with interactive box plots")

Let's dive deeper into the data by examining the distribution of property prices in London for different years.
A box and whisker plot will show us the median, quartiles, and outliers, giving us a much better understanding than just the average price.
First, let's create a year slider that will let us interactively explore different years:

In a new cell, add the following:

```
year_slider = mo.ui.slider(
    start=1995,
    stop=2024,
    value=2020,
    step=1,
    label='Select Year:',
    show_value=True
)
year_slider
```

Now, let's query the individual property prices for the selected year.
Note that we're not aggregating here - we want all the individual transactions to build our distribution:

```
query_distribution = f"""
SELECT
    price,
    toYear(date) AS year
FROM remoteSecure(
    '{os.environ.get("CLICKHOUSE_CLOUD_HOSTNAME")}',
    'default.pp_complete',
    '{os.environ.get("CLICKHOUSE_CLOUD_USER")}',
    '{os.environ.get("CLICKHOUSE_CLOUD_PASSWORD")}'
)
WHERE town = 'LONDON'
  AND toYear(date) = {year_slider.value}
  AND price > 0
  AND price < 5000000
"""

df_distribution = chdb.query(query_distribution, "DataFrame")

# create an interactive box plot.
fig_box = go.Figure()

fig_box.add_trace(
    go.Box(
        y=df_distribution['price'],
        name=f'London {year_slider.value}',
        boxmean='sd',  # Show mean and standard deviation
        marker_color='lightblue',
        boxpoints='outliers'  # Show outlier points
    )
)

fig_box.update_layout(
    title=f'Distribution of Property Prices in London ({year_slider.value})',
    yaxis=dict(
        title='Price (£)',
        tickformat=',.0f'
    ),
    showlegend=False,
    height=600
)

fig_box
```

If you select the options button in the top right hand of the cell, you can hide
the code.
As you move the slider, the plot will automatically update thanks to Marimo's reactive execution:

![Marimo dynamic chart](/docs/assets/images/8-3ca5c59410dfc404a2b485f0087562a1.gif)

## Summary[​](#summary "Direct link to Summary")

This guide demonstrated how you can use chDB to explore your data in ClickHouse Cloud using Marimo notebooks.
Using the UK Property Price dataset, we showed how to query remote ClickHouse Cloud data with the `remoteSecure()` function, and convert results directly to Pandas DataFrames for analysis and visualization.
Through chDB and Marimo's reactive execution model, data scientists can leverage ClickHouse's powerful SQL capabilities alongside familiar Python tools like Pandas and Plotly, with the added benefit of interactive widgets and automatic dependency tracking that make exploratory analysis more efficient and reproducible.

- [Setup](#setup)
  - [Loading the dataset](#loading-the-dataset)- [Setting up credentials](#setting-up-credentials)- [Installing Marimo](#installing-marimo)- [Installing dependencies](#installing-dependencies)- [Exploring the data](#exploring-the-data)
      - [Querying ClickHouse Cloud data](#querying-clickhouse-cloud-data)- [Visualizing the data](#visualizing-the-data)- [Interactive town selection](#interactive-town-selection)- [Exploring price distributions with interactive box plots](#exploring-price-distributions)- [Summary](#summary)

Was this page helpful?