CODEWITHSUNDEEP
pythonpandasdatetimegroup
Dante van der Heijden
Dante van der Heijden

Reputation: 97

Match positive to negative values based on datetime distance

See the image below:

enter image description here

I want to match each positive stem above the red dashed line to the closest negative stem below the red line. This match is done on how much time the stems lie away from each other. So stem A will be matched to stem B. A negative stem can only be matched once, so B can not be matched to C as well. As stem D is too far away from A en C (let's say, delta time >= X) it is not considered.

            BOX_187_084_11
2005-12-01     -190.379230  D
2008-03-01     -261.853410  B
2008-09-01      268.353538  A
2011-09-01      258.084186  C

This is the corresponding Dataframe. How can this easily be solved in a pandaic way?

Upvotes: 1

Views: 43

Answers (1)

Code Different
Code Different

Reputation: 93191

This problem doesn't lend itself very well to vectorization, which is what pandas is best at. You can still solve it using a for loop.

I assume the date is your index, and of type datetime. If not, convert it to datetime with df.index = pd.to_datetime(df.index) before this code snippet:

# The two red lines
lowerbound, upperbound = -125, 125
# The time limit for a match
time_limit = pd.Timedelta(days=365)

# The signal is considered positive or negative only if it exceeds the red lines
s = df["BOX_187_084_11"]
is_positive = s > upperbound
is_negative = s < lowerbound

# What a positive signal is matched to
df["MatchedTo"] = None
# Whether the negative signal has been matched
df["Matched"] = False

# Loop through each positive signal a find a match
for index, value in s[is_positive].items():
    # A matching signal must be negative, never matched before, and within the
    # time limit
    cond = is_negative & ~df["Matched"] & (df.index > index - time_limit)
    if ~cond.any():
        continue

    # Store the matched data
    matched_index = cond.index.max()
    df.loc[index, "MatchedTo"] = matched_index
    df.loc[matched_index, "Matched"] = True

Upvotes: 0

Related Questions