Pandas filtering values in dataframe

Question

I have this dataframe. The columns represent the highs and the lows in daily EURUSD price:

df.low                                            df.high
2013-01-17 16:00:00    1.33394                    2013-01-17 20:00:00    1.33874
2013-01-18 18:00:00    1.32805                    2013-01-18 09:00:00    1.33983
2013-01-21 00:00:00    1.32962                    2013-01-21 09:00:00    1.33321
2013-01-22 11:00:00    1.32667                    2013-01-22 09:00:00    1.33715
2013-01-23 17:00:00    1.32645                    2013-01-23 14:00:00    1.33545
2013-01-24 10:00:00    1.32860                    2013-01-24 18:00:00    1.33926
2013-01-25 04:00:00    1.33497                    2013-01-25 17:00:00    1.34783
2013-01-28 10:00:00    1.34246                    2013-01-28 16:00:00    1.34771
2013-01-29 13:00:00    1.34143                    2013-01-29 21:00:00    1.34972
2013-01-30 08:00:00    1.34820                    2013-01-30 21:00:00    1.35873
2013-01-31 13:00:00    1.35411                    2013-01-31 17:00:00    1.35944

I summed them up into a third column (df.extremes).

df.extremes  
2013-01-17 16:00:00    1.33394 
2013-01-17 20:00:00    1.33874
2013-01-18 18:00:00    1.32805
2013-01-18 09:00:00    1.33983
2013-01-21 00:00:00    1.32962
2013-01-21 09:00:00    1.33321
2013-01-22 09:00:00    1.33715
2013-01-22 11:00:00    1.32667
2013-01-23 14:00:00    1.33545
2013-01-23 17:00:00    1.32645
2013-01-24 10:00:00    1.32860
2013-01-24 18:00:00    1.33926 
2013-01-25 04:00:00    1.33497
2013-01-25 17:00:00    1.34783
2013-01-28 10:00:00    1.34246
2013-01-28 16:00:00    1.34771 
2013-01-29 13:00:00    1.34143
2013-01-29 21:00:00    1.34972
2013-01-30 08:00:00    1.34820
2013-01-30 21:00:00    1.35873
2013-01-31 13:00:00    1.35411
2013-01-31 17:00:00    1.35944

But now i want to filter some values from df.extremes. To explain what to filter i try with this "pseudocode":

IF following the index we move from: previous df.low --> df.low --> df.high:
    IF df.low > previous df.low: delete df.low
    IF df.low < previous df.low: delete previous df.low

If i try to work this out with a for loop, it gives me a KeyError: 1.3339399999999999.

day = df.groupby(pd.TimeGrouper('D'))

is_day_min = day.extremes.apply(lambda x: x == x.min())  

for i in df.extremes:
    if is_day_min[i] == True and is_day_min[i+1] == True:
        if df.extremes[i] > df.extremes[i+1]:
            del df.extremes[i]   


for i in df.extremes:
    if is_day_min[i] == True and is_day_min[i+1] == True:
        if df.extremes[i] < df.extremes[i+1]:
            del df.extremes[i+1]

How to filter/delete the values as i explained in pseudocode?
I am struggling with indexing and bools but i can't solve this. I strongly suspect that i need to use a lambda function, but i don't know how to apply it. So please have mercy it's too long that i'm trying on this. Hope i've been clear enough.

The routine you've outlined sounds like it would just give you the very lowest daily low in the whole dataframe. That can't be what you're after. Could you show us what output you'd expect to get with the data you've shown? — LondonRob
– LondonRob, Commented Feb 2, 2014 at 0:17

U2EF1 · Accepted Answer · 2014-02-02 11:51:02Z

2

All you're really missing is a way of saying "previous low" in a vectorized fashion. That's spelled df['low'].shift(-1). Once you have that it's just:

prev = df.low.shift(-1)
filtered_df = df[~((df.low > prev) | (df.low < prev))]

answered Feb 2, 2014 at 11:51

U2EF1

13.4k3 gold badges38 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pietrovismara Over a year ago

You got it! It was so easy i couldn't imagine it! I tried to use swings but i never thought about putting a negative integer as argument. Last question how do you type tilde and what is the meaning of tilde?

U2EF1 Over a year ago

@Cuz Shift-`, it's the key to the left of 1 on qwerty keyboards. It means complement. Since df[(df.low > prev) | (df.low < prev)] is True everywhere there is an element we would like to exclude, we invert all the booleans with ~ to select all the elements we would like to keep.

Collectives™ on Stack Overflow

Pandas filtering values in dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related