Relating two data sets at the row level where table one may correlate to zero or more rows in another

Question

I'm just looking for some high level recommendations on libraries, design patterns, or algorithms here. I obviously don't expect you to build a model for me.

I would like to predict the monetary impact that an outage will have on the electric grid.

I have data of historical outages that I could train a model on. This basically consists of the equipment-ID that was on outage, with a start and end timestamp.

An outage may cause zero or more constraints. A constraint is where a line becomes loaded to the extent that power must be flowed an alternate way. I already have data on which constraint-IDs occurred at what start and end timestamp, as well as the economic impact of that particular occurrence of that constraint. Note that a constraint may exist without any outages, so there is a bit of a loose relationship. Also, outages may occur without any constraints occurring, or it may take a combination of outages to cause a particular constraint. Generally speaking though, there is a moderate to strong relationship between outages and constraints.

I presume that there should be an algorithm of sorts, that can deduce which outages generally correlate to what constraints after they have been seen to overlap throughout history.

To recap, an outage may cause a constraint. I would like to feed the model outage data and constraint data, so that if I have planned outage data, it can predict if zero or more constraints are X% likely to occur.

I prefer to use Python, but am open to R.

How would you go about modeling a problem like this? In simplified terms, how do you correlate a row in one table to zero or more rows in another where there is no direct mapping? Most solutions I have seen correlate at the column level as opposed to the row.

Edit:

After some thinking about this, I've come up with what I think is a solution. Make each row with a timestamp for that particular hour of the day. One hot encode all outages for that hour. Also, create a column for each potential constraint, with the monetary impact as the value (Scaled to a 0-1 scale).

The thing that seems crazy to me, is that this data set is going to end up being thousands of columns wide if I do it this way. Are super-wide data sets like this common in ML? - or, is there a better approach?

Answer 1

I would like to predict the monetary impact that an outage will have on the electric grid.

It may help to think of your problem as two problems:

1. Given an outage (or set of outages), predict the number of constraints that will result

2. Given a constraint, predict the monetary impact

You would need to build two regressive models - one for each of the problems. It's difficult to give advice about which specific techniques or algorithms to use without a better understanding of the domain and of your dataset.

It sounds like you have a set of timestamped historical outages and a set of timestamped historical constraints. To me, your description implied that there was no authoritative mapping between outages and constraints, so the first wrinkle may be assigning blame for each outage to one or more constraints. This likely requires domain knowledge that most SO contributors won't have.

Another wrinkle is that you only have date and time information about outages and constraints. If the temporal information explains a lot of the variance in number of outages and cost of those outages, then no problem. Otherwise, it's going to be difficult to build accurate models.

If you go with the approach I suggested above, then you have a timeseries forecasting problem on your hands. Traditional algorithms for this type of problem include auto-regressive models like ARMA, ARIMA, SARIMA, etc. You can read up on those to see if they seem suitable for your problem.

Alternatively, Facebook recently released an open-source forecasting library Prophet designed to be easy-to-use and to work well out-of-the-box. It's super easy to try out, so you could give it a shot.