How to find consecutive rows of a dataframe that best match given criteria?

user_19344793 0
Viewed 36

My dataframe looks like this:

             Strength    Power
2021-04-27   0.91       -0.04
2021-04-27   0.72        0.70
2021-04-27   0.85        0.70
2021-04-27   0.87        0.70
2021-04-27   0.64        0.70
2021-04-28   0.65        1.94
2021-04-28   0.54        2.44
2021-04-28   0.89        2.44
2021-04-28   0.97        2.44
2021-04-28   0.74        2.44
2021-04-29   0.94       -0.18
2021-04-29   0.82       -0.18
2021-04-29   0.45       -0.15
2021-04-29   0.30       -0.15
...          ...         ...

I need to get a new dataframe with consecutive rows that match "the most" given criteria. This means, they don't have to match it exactly, but they must be the closest match.

The criteria are date range and Strength interval.

For example, I need to get a new dataframe that:

  • Occurs within 1 day (so date range = 1 day) ---> ALL rows that match this criteria should be considered.
  • Its Strength value lies between 0.9 and 0.75 (0.75 <= Strength <= 0.9).

In this case, none of the data matches the criteria exactly (since it should consider all inputs that match the date range criteria), but the code should return the closest "group" based on the percentage of available data that lies within the desired Strength interval.

In this example:

  • In 2021-04-27, 40% (2/5) of the data lies within the given interval.
  • In 2021-04-28, just 20% (1/5) and in 2021-04-09, 25% (1/4).

Expected output:

             Strength    Power
2021-04-27   0.91       -0.04
2021-04-27   0.72        0.70
2021-04-27   0.85        0.70
2021-04-27   0.87        0.70
2021-04-27   0.64        0.70

My current attempt is to loop through the rows according to the criteria while setting a counter to identify the "group" that matches the criteria the most (basically dividing the counter over the total length of the group and taking the maximum percentage).

However, I don't know is there is a better way of doing this in Pandas, since I have huge dataframes and this approach doesn't seem so effective.

pandas dataframe python
1 Answers

With the dataframe you provided:

import pandas as pd

df = pd.DataFrame(
    {
        "Strength": [0.91, 0.72, 0.85, 0.87, 0.64, 0.65, 0.54, 0.89, 0.97, 0.74, 0.94, 0.82, 0.45, 0.3,],
        "Power": [0.04, 0.7, 0.7, 0.7, 0.7, 1.94, 2.44, 2.44, 2.44, 2.44,  0.18, 0.18, 0.15, 0.15,],
    },
    index=["2021-04-27", "2021-04-27", "2021-04-27", "2021-04-27", "2021-04-27", "2021-04-28", "2021-04-28", "2021-04-28", "2021-04-28", "2021-04-28", "2021-04-29", "2021-04-29", "2021-04-29", "2021-04-29",],
)

Here is one way to do it:

best_match = df.loc[
    df.reset_index()
    .groupby("index")
    .agg(list)["Strength"]
    .apply(
        lambda x: [
            True if value >= 0.75 and value <= 0.9 else False for value in x
        ].count(True)
        / len(x)
    )
    .idxmax(),
    :,
]

print(best_match)
# Output
            Strength  Power
2021-04-27      0.91   0.04
2021-04-27      0.72   0.70
2021-04-27      0.85   0.70
2021-04-27      0.87   0.70
2021-04-27      0.64   0.70
user_112460560
Related