Merging Turbine and Forecast Data Diagonally

Question

I am trying to combine my main dataset, which is hourly data collected from a turbine, with forecast data. The forecast data is collected by calling an API every hour to get a new .csv file which is the forecast for the next 7 days every hour, from the time of collection.

My goal is to diagonally merge every .csv into my main dataset. I already have a way to get the associated filename and a I have boolean if said file exists since there are some missing forecasts. Below are some examples of the data, but there are columns I am leaving out so these data frames are huge.

example of the main dataset

timestamp	InvPwr_kW	forecast_file	forecast_file_exists
2023-11-14 15:00:00-05:00	0.8033946659	file1.csv	TRUE
2023-11-14 16:00:00-05:00	0.4556512312	file2.csv	TRUE
2023-11-14 17:00:00-05:00	0.1023946659	file3.csv	TRUE
2023-11-14 18:00:00-05:00	0.6556534534	file4.csv	TRUE

example of a forecast data file, (this one would be file1.csv)

timestamp	windSpeed_mph
2023-11-14 15:00:00-05:00	10
2023-11-14 16:00:00-05:00	11
2023-11-14 17:00:00-05:00	12
2023-11-14 18:00:00-05:00	13

end goal

The first row of the forecast file would be merged with the same row as its filename in the main dataset with _0h since it was the forecast for that hour. The next row of the forecast data would be in the next row of the main dataset with _1h since it was the forecast for that time one hour ago and so on.

timestamp	InvPwr_kW	forecast_file	forecast_file_exists	windSpeed_0h	windSpeed_1h	windSpeed_mph_2h	windSpeed_mph_3h
2023-11-14 15:00:00-05:00	0.8033946659	file1.csv		10
2023-11-14 16:00:00-05:00	0.4556512312	file2.csv			11
2023-11-14 17:00:00-05:00	0.1023946659	file3.csv				12
2023-11-14 18:00:00-05:00	0.6556534534	file4.csv					13

my attempt

Here's a snippet of my attempt so far. Currently, I get no output from this. It takes too long and crashes VS Code. I know that using .itterows in a data frame is very slow, but I'm not sure how else to get around it.

#for each row in frames
for index, row in df.iterrows():
    #if the forecast file exists
    if row['forecast_file_exists'] == True:

            #error catching
            try:
                #read the file
                filename = row['forecast_file']
                forecast_df = pd.read_csv('./forecast-data-processed/' + filename, dtype=forecast_column_types, parse_dates=['timestamp'])

                #merge the forecast in
                suffix = f"_{index}h"
                df = pd.merge(df, forecast_df, how='left', on='timestamp', suffixes=('', suffix))

            except Exception as e:
                print(f"Error processing {filename}:")
                print(f"Error details: {e}")
                exit

Thank you for reading and I appreciate any advice on this. Please let me know if any clarification is needed.

Answer 1

Hadley and I offer this critique of your approach:

Your data is messy. It's time to tidy it up!

---

Consider these grades on a monthly spelling test:

student	Jan.	Feb.	Mar.
Alice	89	91	92
Bob	79	84	85

This is messy data, very inconvenient for analysis. What you really want is this:

time	student	grade
2023-01	Alice	89
2023-01	Bob	79
2023-02	Alice	91
2023-02	Bob	84
2023-03	Alice	92
2023-03	Bob	85

Now we have a compound PK of (time, student) mapping to one or more attributes, and we can conveniently pick out time ranges, compute aggregate statistics, and so on.

---

Your "end goal" diagonal that involves windSpeed_mph_3h is the same thing. You want the wind speed forecast data organized with a PK of (time, delta) mapping to a forecast windspeed.

For the row that has delta hours of 0, it's showing an actual measured windspeed, a historic fact of what was observed to happen at that time. Other rows listing the same time will have some non-zero delta, representing a forecast made that many hours ahead of time.

Recommend you store the inverter power in KW in a separate RDBMS table, and then JOIN against that when you need to combine things. You might find it convenient to CREATE VIEW, in order to record a JOIN query that you often use.

external storage

... takes too long and crashes VS Code.

It sounds like you're exhausting internal storage, you're exceeding the amount of installed RAM.

A relational database is a terrific way to deal with such challenges. A table index, such a primary key, is an on-disk datastructure that lets us navigate large datasets very rapidly, and supports fast JOIN operations. Postgres and MariaDB are excellent options, but even the very simple sqlite, bundled with python's standard libraries, would be a big step up from your current approach. Remember to put time indexes on your tables. Pandas offers great support for reading and writing table data to an RDBMS.

Answer 2

You want to reshape your forecast file. You could use Miller and run

mlr --csv cat -n then \
put '$n=$n-1;$n="windSpeed_".string($n)."h"' then \
reshape -s n,windSpeed_mph then \
unsparsify input.csv > output.csv

to get

+---------------------------+--------------+--------------+--------------+--------------+
| timestamp                 | windSpeed_0h | windSpeed_1h | windSpeed_2h | windSpeed_3h |
+---------------------------+--------------+--------------+--------------+--------------+
| 2023-11-14 15:00:00-05:00 | 10           |              |              |              |
| 2023-11-14 16:00:00-05:00 |              | 11           |              |              |
| 2023-11-14 17:00:00-05:00 |              |              | 12           |              |
| 2023-11-14 18:00:00-05:00 |              |              |              | 13           |
+---------------------------+--------------+--------------+--------------+--------------+

Some notes:

• cat -n to add a field with a progressive id counter (1 based)
• put '$n=$n-1;$n="windSpeed_".string($n)."h"' to transform to zero-based, and create your desired field name
• reshape -s n,windSpeed_mph to apply the reshape you want

But please pay attention to @j-h answer.