We all heard that (almost) everything can be achieved with Python, but sure I was surprised to discover moviepy. In this article we go through pivoting data with DuckDB, using the data to generate 3D charts in Plotly, and creating a video with Python from chart images.
Data transformation: DuckDB Pivot
Released in version 0.8.0, the pivot function aims to help data people pivot their data with SQL, directly with DuckDB. In my energy data warehouse, I am making use of pivots in order to plot the data in heatmap charts, either a combination of day and hour in a month or of month and day (name) in a year.
AVG kwh production
In order to retrieve the data I am making use of the fact_hourly_metrics_v macro (detailed here), which has as the parameters:
source_query_timestamp_utc, the timestamp the event was recorded by the sourceanalytics_query_timestamp_utc, the timestamp the event was processed in the analytics areafrom_date, the start observation date of the eventto_date, the end observation date of the event.
From it, I extract the most recent data for a certain observation date, and with pandas pivot I create a data frame that has a column for each hour and index the date from an observation month: