CSV Processing with VoltScript

Blogs
, ,
1

CSV files are a common way to store data, and are just as readable by a script as by a person. VoltScript provides a handy string splitting function, and StreamVSE can easily read through a file line by line. Here’s a simple example using the PJM Hourly Energy Consumption dataset from Kaggle, which comes cleaned and ready to use.

The first line of a CSV is the header, which describes the format of the rest of the data. These files have two columns: the date of the reading, and the power usage in megawatts. For this example, we’re calculating a few averages for the power usage. The first is a simple mean, the second is a weighted moving average which smooths out big peaks.

(This is an excuse to show off the syntax highlighting for VoltScript on this forum!)

Option Declare
Option Public

UseVSE "*StreamVSE"

Sub Initialize
    Dim dataStream as New Stream
    Dim ewmaPower, avgPower, hourlyPower as Double
    Dim lineCount as Long
    Dim header, entry as Variant

    ' American Electric Power - utility company, headquartered in Columbus
    ' source: https://www.kaggle.com/datasets/robikscube/hourly-energy-consumption
    Call dataStream.Open("datasets/AEP_hourly.csv", "UTF-8")
    header = split(dataStream.ReadText(True, 1), ",")

    Do Until dataStream.IsEOS
        entry = split(dataStream.ReadText(True, 1), ",")
        lineCount++
        hourlyPower = CDbl(entry(1))

        ewmaPower = 0.9 * ewmaPower + 0.1 * hourlyPower
        avgPower += (hourlyPower - avgPower) / lineCount
    Loop

    Call dataStream.Close()

    Print "Average hourly power consumption: " & avgPower & " MW"
    Print "Moving average for hourly power consumption: " & ewmaPower & " MW"
End Sub

Running this script, we get:

Average hourly power consumption: 15499.5137169857 MW
Moving average for hourly power consumption: 20050.6441073496 MW

The file ends on a general upwards trend, so the moving average is greater than the overall average.

You could also use ZuluVSE to parse the datetime codes in the file and organize data by hour, month, or year, or output data in JSON format with JsonVSE.

2 Likes