如何找到连续下降的次数(增加)

本文介绍了如何找到连续下降的次数(增加)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何找到连续减少(增加)的数量

我有一个数据框，其中包含500K行和12列(用于月份)，并包含开始和结束月份.每列代表一个月.我需要比较范围(startMonth，endmonth)中的第i个月和第(i + 1)个月的每一行. (注:范围不是恒定的.Every行具有不同的范围大小.)

条件:如果开始月>结束月，我应该看到"Neg99 = -999"

这是我的示例数据:

import pandas as pd import numpy as np idx = [1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018] data = {'M_1': [3, 1, 0, 0, 1, 0, 1, 1, 1, 0, 6, 6, 6,0,0,2,0,2], 'M_2': [2, 2, 3, 1, 1, 0, 1, 2, 0, 1, 5, 5, 5,1,1,1,1,2], 'M_3': [1, 3, 2, 2, 1, 0, 1, 2, 1, 0, 4, 4, 4,1,1,0,2,2], 'M_4': [0, 4, 1, 3, 1, 0, 1, 2, 0, 1, 3, 0, 3,1,1,0,0,0], 'M_5': [1, 0, 0, 4, 2, 0, 1, 3, 1, 0, 2, 1, 2,1,1,0,0,0], 'M_6': [2, 0, 0, 0, 3, 0, 1, 3, 0, 1, 1, 2, 1,1,1,0,0,0], 'M_7': [3, 0, 0, 0, 2, 0, 1, 2, 1, 0, 0, 3, 0,0,1,0,0,0], 'M_8': [0, 1, 0, 0, 2, 0, 1, 2, 0, 1, 1, 1, 1,0,0,0,0,0], 'M_9': [0, 2, 0, 0, 1, 0, 1, 2, 1, 0, 0, 0, 0,0,0,0,0,0], 'M_10': [0, 3, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0,0,0,0,0,0], 'M_11': [0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0,0,0,0,0,0], 'M_12': [0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0,0,0,0,0,0]} startMonth = pd.DataFrame([1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 4, 5,1,1,1,1,1], columns=['start'],index=idx) endMonth = pd.DataFrame([12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 2,12,12,2,2,2], columns=['end'], index=idx) df1 = pd.DataFrame(data, index=idx) Neg99 = -999

我为日期范围写了bool数组；

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)

我需要找到每行的连续减少和增加.

• 这是减少代码；

# Consecutive Decreases decr = (np.diff(np.hstack((masked.values, np.zeros((masked.values.shape[0], 1)))), axis=1) > 0).argmin(axis=1) final_decr = pd.DataFrame(decr, index=idx, columns=['decr']) final_decr.decr= np.select( condlist = [startMonth.start > endMonth.end], choicelist = [Neg99], default = final_decr.decr)

• 这里是增加代码；

incr = (np.diff(np.hstack((masked.values, np.zeros((masked.values.shape[0], 1)))), axis=1) < 0).argmin(axis=1) final_incr = pd.DataFrame(incr, index=idx, columns=['incr']) final_incr.incr= np.select( condlist = [startMonth.start > endMonth.end], choicelist = [Neg99], default = final_incr.incr)

最后，我的预期输出是；

Final increase table (.csv); idx,my_results,expected_result 1001,0,0 1002,3,3 1003,1,1 1004,4,4 1005,0,0 1006,0,0 1007,0,0 1008,1,1 1009,0,0 1010,1,1 1011,0,3 1012,0,0 1013,-999,-999 1014,5,1 1015,6,1 1016,0,0 1017,0,2 1018,0,0 Final decrease table (.csv); idx,my_result,expected_result 1001,3,3 1002,0,0 1003,0,0 1004,0,0 1005,0,0 1006,0,0 1007,0,0 1008,0,0 1009,1,1 1010,0,0 1011,0,0 1012,0,3 1013,-999,-999 1014,0,0 1015,0,0 1016,0,2 1017,0,0 1018,0,0 Final NoChange table (.csv); idx,my_result,expected_result 1001,0,0 1002,0,0 1003,0,0 1004,0,0 1005,3,3 1006,11,11 1007,11,11 1008,0,0 1009,0,0 1010,0,0 1011,0,0 1012,0,0 1013,-999,-999 1014,0,0 1015,0,0 1016,0,0 1017,0,0 1018,2,0 Thanks for your advice!

解决方案

所以我认为您可以使用 idxmin .然后删除startMonth的值，例如:

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 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

然后您可以像执行np.select一样执行操作，或者也许只需.illna(-999)就足够了，因为我认为，使用此解决方案的任何地方，只要满足条件startMonth.start > endMonth.end，就可以实现

how to find number of consecutive decreases(increases)

I have a dataframe which has 500K rows and 12 columns for months and include start and end month. Every columns represent a month. I need to compare every rows, i-th month and (i+1)-th month in range (startMonth, endmonth). (Ps: range is not constant. Everty row has different range size.)

Condition: If start month > end month, I should see "Neg99 = -999"

Here is my example 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,1016,1017,1018] data = {'M_1': [3, 1, 0, 0, 1, 0, 1, 1, 1, 0, 6, 6, 6,0,0,2,0,2], 'M_2': [2, 2, 3, 1, 1, 0, 1, 2, 0, 1, 5, 5, 5,1,1,1,1,2], 'M_3': [1, 3, 2, 2, 1, 0, 1, 2, 1, 0, 4, 4, 4,1,1,0,2,2], 'M_4': [0, 4, 1, 3, 1, 0, 1, 2, 0, 1, 3, 0, 3,1,1,0,0,0], 'M_5': [1, 0, 0, 4, 2, 0, 1, 3, 1, 0, 2, 1, 2,1,1,0,0,0], 'M_6': [2, 0, 0, 0, 3, 0, 1, 3, 0, 1, 1, 2, 1,1,1,0,0,0], 'M_7': [3, 0, 0, 0, 2, 0, 1, 2, 1, 0, 0, 3, 0,0,1,0,0,0], 'M_8': [0, 1, 0, 0, 2, 0, 1, 2, 0, 1, 1, 1, 1,0,0,0,0,0], 'M_9': [0, 2, 0, 0, 1, 0, 1, 2, 1, 0, 0, 0, 0,0,0,0,0,0], 'M_10': [0, 3, 0, 0, 1, 0, 1, 0, 0, 1, 0, 0, 0,0,0,0,0,0], 'M_11': [0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0,0,0,0,0,0], 'M_12': [0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0,0,0,0,0,0]} startMonth = pd.DataFrame([1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 4, 5,1,1,1,1,1], columns=['start'],index=idx) endMonth = pd.DataFrame([12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 2,12,12,2,2,2], columns=['end'], index=idx) df1 = pd.DataFrame(data, index=idx) Neg99 = -999

I wrote bool array for date range;

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)

I need to find consecutive decreases and increases for every rows.

• Here is decreases code;

# Consecutive Decreases decr = (np.diff(np.hstack((masked.values, np.zeros((masked.values.shape[0], 1)))), axis=1) > 0).argmin(axis=1) final_decr = pd.DataFrame(decr, index=idx, columns=['decr']) final_decr.decr= np.select( condlist = [startMonth.start > endMonth.end], choicelist = [Neg99], default = final_decr.decr)

• Here is increasees code;

incr = (np.diff(np.hstack((masked.values, np.zeros((masked.values.shape[0], 1)))), axis=1) < 0).argmin(axis=1) final_incr = pd.DataFrame(incr, index=idx, columns=['incr']) final_incr.incr= np.select( condlist = [startMonth.start > endMonth.end], choicelist = [Neg99], default = final_incr.incr)

And finally, My expected outputs are;

Final increase table (.csv); idx,my_results,expected_result 1001,0,0 1002,3,3 1003,1,1 1004,4,4 1005,0,0 1006,0,0 1007,0,0 1008,1,1 1009,0,0 1010,1,1 1011,0,3 1012,0,0 1013,-999,-999 1014,5,1 1015,6,1 1016,0,0 1017,0,2 1018,0,0 Final decrease table (.csv); idx,my_result,expected_result 1001,3,3 1002,0,0 1003,0,0 1004,0,0 1005,0,0 1006,0,0 1007,0,0 1008,0,0 1009,1,1 1010,0,0 1011,0,0 1012,0,3 1013,-999,-999 1014,0,0 1015,0,0 1016,0,2 1017,0,0 1018,0,0 Final NoChange table (.csv); idx,my_result,expected_result 1001,0,0 1002,0,0 1003,0,0 1004,0,0 1005,3,3 1006,11,11 1007,11,11 1008,0,0 1009,0,0 1010,0,0 1011,0,0 1012,0,0 1013,-999,-999 1014,0,0 1015,0,0 1016,0,0 1017,0,0 1018,2,0 Thanks for your advice!

解决方案

So I think instead of using argmin, you can use idxmin after renaming the column to get an integer value of the position. Then remove the value of the startMonth such as:

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 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

Then you can do the np.select as you do, or probably just a .illna(-999) should be enough as I think with this solution everywhere you have nan in the result will be where your condition startMonth.start > endMonth.end is met