Re-pivoting data using OpenRefine’s Columns to Rows feature

Problem A frequent challenge for transforming time-series data (e.g. weather, meter data) is changing columns representing multiple times of the day to a single column or in OLAP terms what might generically be described as an “Hour of the Day” or “Interval” dimension. Example input schema: Date, 00:00, 00:30, 01:00, 01:30, …, 23:00, 23:30 01-01-2015,0,0,0,1,…,1,0…

Visualising energy consumption profile (by hour of day) using D3.js

With the benefit of smart electricity meters it’s possible to obtain hourly data showing household consumption in KWh. I downloaded this dataset for my own house in CSV format from United Energy’s EnergyEasy portal. With some massaging, the data can be formatted to a structure which which makes aggregation easier.  The excellent tool OpenRefine made…