6

I have a dataframe like the following:

    A
1   1000
2   1000
3   1001
4   1001
5   10
6   1000
7   1010
8   9
9   10
10  6
11  999
12  10110
13  10111
14  1000

I am trying to clean my dataframe in the following way: For every row having more value than 1.5 times the previous row value or less than 0.5 times the previous row value, drop it. But If the previous row is a to-drop row, comparison must be made with the immediate previous NON-to-drop row. (For example Index 9, 10 or 13 in my dataframe) So the final dataframe should be like:

    A
1   1000
2   1000
3   1001
4   1001
6   1000
7   1010
11  999
14  1000

My dataframe is really huge so performance is appreciated.

Improve this question
5
  • What have you tried yourself so far to solve the problem? Commented Nov 20, 2019 at 16:45
  • pct_change.between(-0.5,0.5)? Commented Nov 20, 2019 at 17:00
  • @ansev this sort of thing needs a for loop as in piRSquared's answer. Commented Nov 20, 2019 at 17:02
  • yes, it is true that you need for loop but I think we could use pct-change in a more elegant solution Commented Nov 20, 2019 at 17:04
  • I'm wondering whether one could iteratively generate a mask using vectorized operations... performance would depend on specific distribution of dropped rows, supposedly. Commented Nov 20, 2019 at 17:13

2 Answers 2

Reset to default
7

You can't get away from looping through each row

Tips
  • Avoid creating new (expensive to create) objects for each row
  • Use a memory efficient iteration

I'd use a generator

I'll pass a series to a function and yield the index values for which rows satisfy the conditions.

def f(s):
    it = s.iteritems()
    i, v = next(it)
    yield i                          # Yield the first one
    for j, x in it:
        if .5 * v <= x <= 1.5 * v:
            yield j                  # Yield the ones that satisfy
            v = x                    # Update the comparative value

df.loc[list(f(df.A))]                # Use `loc` with index values
                                     # yielded by my generator

       A
1   1000
2   1000
3   1001
4   1001
6   1000
7   1010
11   999
14  1000
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

should by .5 * v <= x <= 1.5 * v
1

One alternative could be to use itertools.accumulate to push forward the last valid value and then filter out the values that are different from the original, e.g:

from itertools import accumulate


def change(x, y, pct=0.5):
    if pct * x <= y <= (1 + pct) * x:
        return y
    return x

# create a mask filtering out the values that are different from the original A
mask = (df.A == list(accumulate(df.A, change)))

print(df[mask])

Output

       A
1   1000
2   1000
3   1001
4   1001
6   1000
7   1010
11   999
14  1000

Just to get an idea, see how the accumulated column (change) compares to the original side-by-side:

        A  change
1    1000    1000
2    1000    1000
3    1001    1001
4    1001    1001
5      10    1001
6    1000    1000
7    1010    1010
8       9    1010
9      10    1010
10      6    1010
11    999     999
12  10110     999
13  10111     999
14   1000    1000

Update

To make it in the function call do:

mask = (df.A == list(accumulate(df.A, lambda x, y : change(x, y, pct=0.5))))
Improve this answer

5 Comments

Great!. How could I pass the 0.5 as parameter?
@Alfonso_MA You mean a parameter to the function?
Yeah, a parameter to change function
But i meant in the function call
@Alfonso_MA see now

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.