license:mit size_categories:10M<n<100M format:parquet modality:tabular modality:text library:datasets library:dask library:mlcroissant library:polars region:us
---
license: mit
---

# Bixi's OpenData Modelisation

Here is a huggingface dataset refined with [AntoineGiraud/dbt_bixi_opendata](https://github.com/AntoineGiraud/dbt_bixi_opendata) dbt-core project<br>
that loads & transform [bixi OpenData](https://bixi.com/fr/donnees-ouvertes/) thanks to [DuckDB](https://duckdb.org/) 🦆🚀

## Viz' exploration ideas

I used Power BI to explore the transformed data offloaded to .parquet (~ 4.7 times lighter than .csv)

After the pandemic, Montrealers realy went back to bixi 🥳

![image/png](https://cdn-uploads.huggingface.co/production/uploads/66e9b7eb6d83fd1e4fa3b41a/bbDJyhw34kyLOhOIcO43V.png)

## Data sources

### Bixi Rentals OpenData ([link](https://bixi.com/fr/donnees-ouvertes/))

- 🚲 **Rentals V1** : from 2014 to 2021
  > for station info, join to station
      yearly file with station_code
- ⛽ **Stations V1** : from 2014 to 2021
  > 1 station code per year
- 🚲 **Rentals V2** : from 2022 to 2024+
  > start/end station info on each rentals\
  *-> 2.7 times heavier `.csv`* 1.4Gb -> 0.5Gb\
  *-> 2.3 times heavier `.parquet` 250Mb -> 106Mb*

### GIS referential

- 🧭 **Municipal sectors** : from the OD 2013 survey (cf. [donnees quebec](https://www.donneesquebec.ca/recherche/dataset/artm-secteurs-municipaux-od13/resource/95ab084b-727e-4322-9433-0fed7baa690d))

## Schema/DB steps :

- **raw** : raw tables loaded as is from .csv
- **stg** : intermediate tables
- **dtm** : tables ready for analytics & reporting use

![image/png](https://cdn-uploads.huggingface.co/production/uploads/66e9b7eb6d83fd1e4fa3b41a/WC9X27HN15k4br9iARpE7.png)

### Loading

DuckDB realy shines by it's speed & local OLAP capabilities 😎

Here is 🚲 v1 rentals (2014 - 2021) load & offload to .parquet
- `.csv` is **4.5** times heavier than `.parquet`
- `.json` is **2.7** times heavier than `.csv`

![image/png](https://cdn-uploads.huggingface.co/production/uploads/66e9b7eb6d83fd1e4fa3b41a/CPcQ1-GiV77kJESLeQQL7.png)
