In time-indexed Pandas, how to detect groups of rows where values are the same, and output a new column with 1 or 0?

user_4197720 0
Viewed 130

I have a dataframe where rows represents dates or minutes, and their corresponding values for each.

Is it possible for pandas to first (1) detect a increase in value, (2) then goes flat for x number of rows, (3) then increases again?

For e.g, in the image below, at 0934 value is 8.135. It increases to 8.18 and stays there until 0941, seven rows later. it then increases again to 0.185 at 0942am.

This group of rows needs to be identified with a 1 at the end of such a group. Referring to the earlier example, the desired output is 1 at 0942am.

Note that the increaseing plateaus is just for illustration, not to be in the actual dataframe. The code needs to find the "values" column amongst the 30+ other columns in the dataframe, process the column to find the plateaus and output to a new column called "desired output".

And the numbers under the value column do not just increase, may decrease as well. No set pattern, just that in this case an event occurred causing the numbers to go up. At least two of the same values are needed across two rows to be considered a plateau.

enter image description here

Sample CSV here

The closest example I can find here is the following, but the solutions seem to involve sorting the columns, which I cannot in this case as the rows are representing time.

Pandas: for groups of rows where 2 or more particular columns values are exactly the same, how to assign a unique integer as a new column

I'm still learning python and can write something like below, but I'm do not know where to start for the above case. Please advise!

Will be great if the above case can be solved using numpy or some other fast method!

def check2(df):
    df.loc[:, 'check2'] = np.where((df.open > df.close) & (df.close.shift(1) > df.open.shift(1)),
        np.where((df.open > df.close.shift(1)) & (df.close < df.open.shift(1)), 1, 0), 0)
    return df
pandas numpy python
2 Answers

Here is one approach (using my own data). Is aware of decreasing as well as increasing values. Detects uptick-plateau-uptick patterns and marks the ending uptick for each pattern detected:

d = {'value': [10,10,11,11,11,11,13,13,13,15,15,15,13,13,13,12,12,13,14,14,14,15,15,15]}

df = pd.DataFrame(d)

df['diff'] = df['value'].diff()
df['plat_up_end'] = (df['diff'] > 0) & (df['diff'].shift() == 0)
df['output'] = (df['plat_up_end'] 
            & (df['diff'].replace(to_replace=0, method='ffill') > 0)
            .shift()).astype('int')
df[['value','output']]

    value  output
0      10       0
1      10       0
2      11       0
3      11       0
4      11       0
5      11       0
6      13       1
7      13       0
8      13       0
9      15       1
10     15       0
11     15       0
12     13       0
13     13       0
14     13       0
15     12       0
16     12       0
17     13       0
18     14       0
19     14       0
20     14       0
21     15       1
22     15       0
23     15       0

Does not assume a monotonically increasing series. Values may decrease as well as increase. It does assume that at least two of the same values are needed to form a plateau. Could be adjusted if not true.

Here's how to extend the minimum required number to form a plateau. You would need to modify this line:

df['plat_up_end'] = (df['diff'] > 0) & (df['diff'].shift() == 0)

This is the look-back part of that line:

(df['diff'].shift() == 0)

A zero in the 'diff' column indicates that the entry is the same as the entry before it. So you need to look back n-1 entries from the uptick to identify a plateau of n values. If you want to see at least 4 of the same values to form a plateau then you would need 3 look-backs. Notice that each shift() looks back one further than the one before it.

& (df['diff'].shift() == 0) & (df['diff'].shift(2) == 0) & (df['diff'].shift(3) == 0)

Full line:

df['plat_up_end'] = (df['diff'] > 0) & (df['diff'].shift() == 0) & (df['diff'].shift(2) == 0) & (df['diff'].shift(3) == 0)

Result:

    value  output
0      10       0
1      10       0
2      11       0
3      11       0
4      11       0
5      11       0
6      13       1
7      13       0
8      13       0
9      15       0
10     15       0
11     15       0
12     13       0
13     13       0
14     13       0
15     12       0
16     12       0
17     13       0
18     14       0
19     14       0
20     14       0
21     15       0
22     15       0
23     15       0

Though if the plateau size gets much bigger we would probably want to change the logic to do a rolling sum instead.

user_39687610
  1. We first make a series (tmp) that marks where the value has increased.
  2. Then we exclude where previous value also has increased.
tmp = df['values'].diff().gt(0.0, fill_value=True)           # 1
df['sol'] = (tmp & ~tmp.shift(fill_value=True)).astype(int)  # 2

Example Input/Output:

df = pd.DataFrame({
    't': pd.date_range('2021-01-01 12:00:00', '2021-01-01 12:10:00', freq='1min', closed='left'),
    'values': [8.0, 8.15, 8.15, 8.15, 8.2, 8.3, 8.4, 8.4, 8.5, 8.5],
    'ans': [0, 0, 0, 0, 1, 0, 0, 0, 1, 0],
})
# ...
df
                    t  values  ans  sol
0 2021-01-01 12:00:00    8.00    0    0
1 2021-01-01 12:01:00    8.15    0    0
2 2021-01-01 12:02:00    8.15    0    0
3 2021-01-01 12:03:00    8.15    0    0
4 2021-01-01 12:04:00    8.20    1    1
5 2021-01-01 12:05:00    8.30    0    0
6 2021-01-01 12:06:00    8.40    0    0
7 2021-01-01 12:07:00    8.40    0    0
8 2021-01-01 12:08:00    8.50    1    1
9 2021-01-01 12:09:00    8.50    0    0
user_85165130
Related