How add column by condition met in other column - python

Silvio Aranda 0
Viewed 41

I would like to add a condition by last n rows values of another column.

For example, I have column A and if last 3 A rows are <= 25, I would like to add a 1 in column B but in next row AFTER the condition met:

enter image description here

In this case, rows [3,4,5] meet the condition (all are <= 25) so I'll add a "1" in B in next row (row number 6 of B).

Also rows [12,13,14] meet the condition.

I tried something like this for split values in 3 last rows

for i in range(0,len(l),n):
        yield l[i:i + n]

But I get stuck in add column B

Thanks!

python
1 Answers

You can do something like this. If you want to use libraries you can use the Counter in collections, or probably a number of other methods.

def column_sum(column, desired_val=25, window=3):
    true_indices = []
    counter = window
    for i in range(len(column)):
        if (column[i] <= desired_val):
            counter -= 1
        else:
            counter = window
        if counter == 0:
            true_indices.append(i)
            counter += 1  # could be next one is less than too
    return true_indices

A = [40, 12, 34, 25, 23, 20, 51, 70, 21, 80, 23, 22, 21, 13, 18]
inds = column_sum(A)
print(inds)

Note, you probably don't want the index after the three consecutive values but rather the index at the last consecutive value. What if you have three consecutive values at the end? What does the index mean? If you really do you want that, not sure of your context, it's easy to change the true_indices.append(i) to true_indices.append(i+1). If you really need a full-on non-sparse zeros and ones it is easy enough to convert the returned indices to something that does it through a list comprehension e.g. full_logical = [1 if i in inds else 0 for i in range(len(A))].

Andrew Holmgren0
Related