How to find the longest run of consecutive decreases(or increases) row by row in a dataframe

Viewed 245

in my dataframe rows has multiple sequence of consecutively decreasing (or increasing) values. I am trying to find the longest run of consecutive decreases(or increases) in each row.

Condition(s):

  • If startMonth > endMonth, we should see Neg99 = -999

  • If endMonth=2(startMonth=1), we should look M_1, M_2 and M_3. Because we actually compare i-th and i+1-th month.

Below is my sample data;

import pandas as pd
import numpy as np

idx= [1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015]
data = {'M_1': [3,1,3,1,1,0,0,1,2,2,0,2,0,5,1],
                               'M_2': [2,1,2,1,1,1,0,1,2,1,1,1,1,4,2],
                               'M_3': [1,1,1,5,0,0,0,1,2,0,2,0,2,3,3],
                               'M_4': [1,3,0,4,1,1,0,1,2,0,0,0,1,2,4],
                               'M_5': [1,2,0,3,2,2,0,1,2,0,0,0,1,1,5],
                               'M_6': [3,1,3,2,3,3,0,1,2,0,0,0,1,0,5],
                               'M_7': [2,0,2,1,4,4,0,1,2,0,0,0,1,0,5],
                               'M_8': [1,0,1,0,5,4,0,1,2,0,0,0,1,0,5],
                               'M_9': [0,0,1,0,4,4,0,1,2,0,0,0,1,0,5],
                               'M_10': [0,0,0,0,3,4,0,1,2,0,0,0,1,0,5],
                               'M_11': [0,0,0,0,2,4,0,1,2,0,0,0,1,0,5],
                               'M_12': [0,0,0,0,2,4,0,1,2,0,0,0,1,0,5]}

startMonth = pd.DataFrame([1,1,1,4,4,4,1,1,1,1,1,1,1,3,2],
                          columns=['start'],index=idx)
endMonth = pd.DataFrame([12,12,12,12,12,12,12,12,12,2,2,1,1,1,1],
                        columns=['end'], index=idx)

df1 = pd.DataFrame(data, index=idx)
Neg99 = -999

And I managed the find the count of first decreasing pattern but couldn't do the longest part.

arr_bool = (np.less_equal.outer(startMonth.start, range(1,13)) 
            & np.greater_equal.outer(endMonth.end, range(1,13))
            )

masked=df1.filter(regex='M_[0-9]').mask(~arr_bool)

incr = (df1.rename(columns={col:int(col.split('_')[1]) for col in masked.columns})
           .diff(-1, axis=1) < 0).mask(~arr_bool).idxmin(axis=1) - startMonth.start
result_incr = pd.DataFrame(incr,
                      index=idx, columns=['incr'])
result_incr.incr= np.select( condlist = [startMonth.start > endMonth.end],
                           choicelist = [Neg99],
                           default = final_incr.incr)

decr = (df1.rename(columns={col:int(col.split('_')[1]) for col in masked.columns})
           .diff(-1, axis=1) > 0).mask(~arr_bool).idxmin(axis=1) - startMonth.start
result_decr = pd.DataFrame(incr,
                      index=idx, columns=['decr'])
result_decr.decr= np.select( condlist = [startMonth.start > endMonth.end],
                           choicelist = [Neg99],
                           default = result_decr.decr)  

Can you help with your tips?

Decreasing Table:
idx,expected,my_result
1001,3,0
1002,3,0
1003,3,0
1004,4,0
1005,3,4
1006,0,3
1007,0,0
1008,0,0
1009,0,0
1010,2,0
1011,0,0
1012,1,0
1013,0,0
1014,-999,-999
1015,-999,-999

Increasing Table:
idx,expected,my_result
1001,1,0
1002,1,0
1003,1,0
1004,0,0
1005,4,4
1006,3,3
1007,0,0
1008,0,0
1009,0,0
1010,0,0
1011,2,0
1012,0,0
1013,1,0
1014,-999,-999
1015,-999,-999
0 Answers
Related