1
$\begingroup$

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.

$\endgroup$

2 Answers 2

2
$\begingroup$

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.

$\endgroup$
1
  • $\begingroup$ Hey Hadley, thanks for the response. I don't have much experience with databases, and ultimately the data will be used for machine learning so I would like it to be accessible. Do you think using a database could be limited for the future of my project? Also, what sort of SQL command would give me the result I'm looking for? Any tips for making this work with just dataframes so that I could test it on a smaller scale? $\endgroup$
    – Eli Orians
    Jan 12 at 3:05
1
$\begingroup$

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.

$\endgroup$
5
  • $\begingroup$ Thanks for answering! So you're suggesting that I reshape forecast files and then simply concat the two? And I am working to set up a database now... $\endgroup$
    – Eli Orians
    Jan 12 at 17:31
  • 1
    $\begingroup$ @EliOrians I am not suggesting anything to you. But I have shown you how to accomplish the most important part of your request. I hope my answer is useful to you $\endgroup$
    – aborruso
    Jan 12 at 18:54
  • $\begingroup$ @EliOrians What solution did you use? Did it work? $\endgroup$
    – aborruso
    Jan 14 at 17:31
  • $\begingroup$ Not at a final solution yet. I used your suggestion of reshaping the forecast data so it is ready to be merged directly into my main dataset, but I did it using pandas since I'm more familiar with that. However, when trying to merge the forecasts into my main dataset (using pandas pd.merge) I get this error; "'method' object is not subscriptable". I haven't had time to dig into this yet, but I'd appreciate further suggestions if you have them. I'll update once I figure it out as well. $\endgroup$
    – Eli Orians
    Jan 15 at 21:08
  • $\begingroup$ @EliOrians I will use once again Miller to make the final simple join $\endgroup$
    – aborruso
    Jan 15 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.