From Data to Motion: Video Generation in Python
<p>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.</p>
<h1>Data transformation: DuckDB Pivot</h1>
<p>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.</p>
<p> </p>
<p>AVG kwh production</p>
<p>In order to retrieve the data I am making use of the <code>fact_hourly_metrics_v</code> macro (detailed here), which has as the parameters:</p>
<ol>
<li><code>source_query_timestamp_utc</code>, the timestamp the event was recorded by the source</li>
<li><code>analytics_query_timestamp_utc</code>, the timestamp the event was processed in the analytics area</li>
<li><code>from_date</code>, the start observation date of the event</li>
<li><code>to_date</code>, the end observation date of the event.</li>
</ol>
<p>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:</p>
<p><a href="https://betterprogramming.pub/from-data-to-motion-video-generation-in-python-ad0092f0d48">Read More</a></p>