Exploring Chicago crimes dataset with DuckDB, Malloy Data, and soon new Panel/PyScript data and dashboard tools ...
Data from: https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-Present/ijzp-q8t2
Note: Chicago crimes data is too large for a github repository. You can download it from the the link above.
![Chicago Crimes 2001 to Present Info ...](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-info.png?raw=true "Chicago Crimes 2001 to Present Info ...")
Raw dataset view in VSCode with Tabular Data Viewer and Rainbow CSV:
![Chicago Crimes 2001 to Present Data ...](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-data.png?raw=true "Chicago Crimes 2001 to Present Data ...")
Crimes CSV
data imported in DBeaver for comparison:
![Crimes Data in DBeaver](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-dbeaver.png?raw=true "Crimes Data in DBeaver")
Crimes CSV
data imported into Tad Viewer:
![Crimes Data in Tad Viewer](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-in-tad.png?raw=true "Crimes Data in Tad Viewer")
Quick Chicago crimes CSV
data scan and Arrests query with Polars in one cell code block :
![Chicago Crimes with Polars](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-with-polars.png?raw=true "Chicago Crimes with Polars")
Loading Chicago crimes .parquet
data file with polars.read_parquet()
:
![Chicago Crimes with Polars Parquet](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-polars-parquet.png?raw=true "Chicago Crimes with Polars Parquet")
Loading Chicago crimes raw CSV
data with PyArrow CSV:
![Chicago Crimes with PyArrow](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-pyarrow.png?raw=true "Chicago Crimes with PyArrow")
Writing and reading Chicago crimes PyArrow Table data in Feather and Parquet data file formats:
![Chicago Crimes with PyArrow Feather and Parquet](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-feather-and-parquet.png?raw=true "Chicago Crimes with PyArrow Feather and Parquet")
Loading Chicago crimes CSV
data into a blank in-memory
DuckDB instance:
![Chicago Crimes with DuckDB](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-with-duckdb.png?raw=true "Chicago Crimes with DuckDB")
Loading Chicago crimes .parquet
data via DuckDB read_parquet()
:
![Chicago Crimes with DuckDB Parquet](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-duckdb-parquet.png?raw=true "Chicago Crimes with DuckDB Parquet")
Loading Chicago crimes CSV
data into a blank in-memory
DuckDB with ipython-sql
SQLMagic in VSCode Jupyter Notebook:
![Chicago Crimes with DuckDB SQLMagic](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-duckdb-sqlmagic.png?raw=true "Chicago Crimes with DuckDB SQLMagic")
Loading Chicago crimes 2022 parquet
data with Malloy Data tools via DuckDB parquet data table source, with queries, data schema, Malloy queries outline, data preview, and query results displayed in VSCode Malloy extension query editor and views:
![Chicago Crimes with Malloy](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-duckdb-malloy-2022.png?raw=true "Chicago Crimes with Malloy")
Loading Chicago crimes CSV
data with Pandas:
![Chicago Crimes Pandas Notebook](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-with-pandas.png?raw=true "Chicago Crimes Pandas Notebook")
Loading Chicago crimes CSV
data with DBI R library and DuckDB R API in R Studio:
![Chicago Crimes with DuckDB in R](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-duckdb-in-r-studio.png?raw=true "Chicago Crimes with DuckDB in R")
Reading Chicago crimes CSV
data with DuckDB Julia Package in VSCode Julia lang, and running it in Julia REPL:
![Chicago Crimes CSV to DuckDB in Julia REPL](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-duckdb-julia-repl.png?raw=true "Chicago Crimes CSV to DuckDB in Julia REPL")
Loading Chicago crimes CSV
data via Julia CSVFiles into native Julia DataFrames:
![Chicago Crimes CSV in Julia DataFrame](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-csv-julia-dataframe.png?raw=true "Chicago Crimes CSV in Julia DataFrame")
Reading Chicago crimes CSV
data with SBCL + cl-duckdb in Emacs + SLY:
![Chicago Crimes CSV to DuckDB in Emacs](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-in-emacs.png?raw=true "Chicago Crimes CSV to DuckDB in Emacs")
Collection of Jupyter notebooks and data apps visualizing Chicago crimes data from above.
Visualizing Chicago crimes data loaded with Pandas using Matplotlib:
![Chicago Crimes Matplotlib Chart](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-with-matplotlib.png?raw=true "Chicago Crimes Matplotlib Chart")
2001-2022 Chicago crimes data loaded from a parquet
file and summarized with Pandas and Altair charts:
![Chicago Crimes Altair Charts Notebook](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-altair-charts-ipynb.gif?raw=true "Chicago Crimes Altair Charts Notebook")
2022 Chicago crimes data loaded from a CSV
file with data summary Altair charts in a browser, using Pyodide runtime and Pandas:
https://randomfractals.github.io/chicago-crimes/apps/pyscript/
![Chicago Crimes PyScript Page](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-pyscript-page.gif?raw=true "Chicago Crimes PyScript Page")
Displaying Chicago crimes 2022 parquet
data with Malloy Charts using Malloy Import with table source, measures, and data queries defined in Malloy Data section above:
![Chicago Crimes Malloy Charts Summary](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-by-type-malloy-summary.png?raw=true "Chicago Crimes Malloy Charts Summary")
View and query 2022 Chicago crime reports data loaded from parquet
file with Malloy Composer app in your browser:
https://randomfractals.github.io/chicago-crimes/apps/malloy-composer/
![Chicago Crimes Malloy Composer App](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crimes-malloy-composer.gif?raw=true "Chicago Crimes Malloy Composer App")
View 2022 Chicago crime reports data, schema, Malloy model, and queries with Malloy Fiddle app in your browser:
https://randomfractals.github.io/chicago-crimes/apps/malloy-fiddle
![Chicago Crime Reports Malloy Fiddle App](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crime-reports-malloy-fiddle-app.gif?raw=true "Chicago Crime Reports Malloy Fiddle App")
Loading and querying 7,687,725 Chicago crime reports recorded from 2001 through the end of November 2022 from a large 1.68 GB CSV data file with new VSCode DuckDB Sql Tools extension:
![Chicago Crime Reports with DuckDB Sql Tools](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/create-chicago-crime-reports-duckdb-table.gif?raw=true "Create and Query Chicago Crime Reports DuckDB Table")
Exporting in-memory DuckDB instance with DuckDB Sql Tools:
![Chicago Crime Reports DuckDB Database Export](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/export-chicago-crime-reports-duckdb.gif?raw=true "Chicago Crime Reports DuckDB Database Export")
Exporting DuckDB instance in Parquet data format and importing it into new test DuckDB memory instance:
![Chicago Crime Reports DuckDB Database Export](https://github.com/RandomFractals/chicago-crimes/blob/main/docs/images/chicago-crime-reports-parquet-data-export-import.gif?raw=true "Chicago Crime Reports DuckDB Parquet Data Export/Import")
Links to our prior works on Chicago Crimes EDA circa 2017/2018:
π Chicago Crimes EDA Summary on Linkedin
π Chicago Crimes EDA 2017 Jupyter Notebooks
π Observable JS Chicago Crimes Notebook Collection 2018
π Chicago Homocides Observable JS Notebook Collection 2018
π¦ Chicago Crimes EDA, Data Preview and Tabular Data Viewer tweets