Finding lowest value by shifting n numbers of datapoint

user_12275675 0
Viewed 183

I have the following dataframe:

num
Out[89]: 
Date
2015-07-06    33
2015-07-07    20
2015-07-08     4
2015-07-09     3
2015-07-10     8
              ..
2020-06-29    14
2020-06-30    13
2020-07-01    18
2020-07-02    20
2020-07-03    28
Length: 1228, dtype: int64

I want to apply something like num.lt(5) on shifting basis every n days. Such as if it were able to find 5 but the day after there was a lower value of 4 and then 3, so on an interval of lets say 3 days it logs the value 3. So essentially what should happen is less than 5 but the minimum value lowest in the next 3 days. How can i do this?

For example :

 Date
    2015-07-06    33
    2015-07-07    20
    2015-07-08     4
    2015-07-09     3
    2015-07-10     8

On date 2015-07-08 it has a value of 4 which is true if I use lt(5) but the day after it has 3. I want it so that it logs 3 instead of both 4 and 3 because 3 is the lowest value. It should check on 3 days interval. Thus if it started to check for condition on 2015-07-08 it should check for the lowest value for the next 3 days which is till 2015-07-11. Could you please advise this could be done?

Edit:

enter image description here So wherever lt(5) is true will be the starting point and the next 3 days is where the lowest value needs to be found. The search for should only start at the end of the interval for the next starting point of lt(5)

pandas python-3.x python
4 Answers

Is this what you were looking for?

import pandas as pd

df = pd.DataFrame(
    columns=['Date', 'X'],
    data=[['2015-07-06', 33],
          ['2015-07-07', 20],
          ['2015-07-08', 4],
          ['2015-07-09', 3],
          ['2015-07-10', 8]],
)
df = df.sort_values('Date', ascending=False)
df['rolling_min'] = df.X.rolling(window=3, min_periods=0).min().astype(int)
df = df.sort_values('Date', ascending=True)

returns

       Date   X  rolling_min
 2015-07-06  33            4
 2015-07-07  20            3
 2015-07-08   4            3
 2015-07-09   3            3
 2015-07-10   8            8

If you only want to modify dates with value < 5, you could use something like this:

df['X'] = df.apply(lambda x: x['X'] if x['X'] > 5 else x['rolling_min'], axis=1)
user_35339600

My guess is you need the indices. Once you get those you can manipulate the data as you want.

df  = pd.DataFrame(data = {
    "data":[3, 2, 77, 655, 4, 5, 444, 5, 444, 5, 456, 456, 45, 34, 34, 88]
})

a = 0
increment = 3
b = increment
l = []
while b<df.shape[0]:
    a = df.iloc[a:b, 0].idxmin() + 1
    
    l.append(a)
    b = a + increment 


print(l)

[2, 5, 6, 8, 10, 13]  #for increment =3
user_42133620 
data = {
    'date': pd.date_range('2015-07-06', periods=20, freq='D'),
    'value': [2, 3, 10, 20, 33, 20, 4, 3, 8, 9, 2, 3, 4, 1, 4, 7, 4, 5, 7, 20]
}

df = pd.DataFrame(data)
df['log'] = df['value']

increment = 4
i = 0
while i < df.shape[0]:
    # Start find the lowest value in the range
    if df.loc[i, 'log'] < 5:
        # Find lowest the range
        lowest = df.loc[i, 'log']
        for j in range(1, increment):
            if df.loc[i+j, 'log'] > 5:
                break
            if lowest > df.loc[i+j, 'log']:
                lowest = df.loc[i+j, 'log']
        # Update value to the lowest value in the range
        df.loc[i, 'log'] = lowest
        for j in range(1, increment):
               if df.loc[i+j, 'log'] < 5:
                   df.loc[i+j, 'log'] = lowest
        i += increment
    else:
        i += 1

Output

         date  value  log
0  2015-07-06      2    2
1  2015-07-07      3    2
2  2015-07-08     10   10
3  2015-07-09     20   20
4  2015-07-10     33   33
5  2015-07-11     20   20
6  2015-07-12      4    3
7  2015-07-13      3    3
8  2015-07-14      8    8
9  2015-07-15      9    9
10 2015-07-16      2    1
11 2015-07-17      3    1
12 2015-07-18      4    1
13 2015-07-19      1    1
14 2015-07-20      4    4
15 2015-07-21      7    7
16 2015-07-22      4    4
17 2015-07-23      5    5
18 2015-07-24      7    7
19 2015-07-25     20   20
user_138133650 
import pandas as pd
import numpy as np
    
df = pd.DataFrame(columns=['date', 'number'],
    data=[['2020-01-01', 3],
          ['2020-01-02', 2],
          ['2020-01-03', 77],
          ['2020-01-04', 655],
          ['2020-01-05', 4],
          ['2020-01-06', 5],
          ['2020-01-07', 444],
          ['2020-01-08', 5],
          ['2020-01-09', 444],
          ['2020-01-10', 5],
          ['2020-01-11', 456],
          ['2020-01-12', 456],
          ['2020-01-13', 45],
          ['2020-01-14', 34],
          ['2020-01-15', 34],
          ['2020-01-16', 88],
          ['2020-01-17', 2]])

new_df = []
interval = 3
interval_temp = 0
for index, row in df.iterrows():
    if row[1] <= 5 and index >= interval_temp:
        lowest = row[1]
        for i, row in df[index:index+interval+1].iterrows():
            if row[1] <= lowest:
                lowest_temp = [row[0], row[1]]
                lowest = row[1]
        new_df.append(lowest_temp)
        interval_temp = index + interval

new_df = pd.DataFrame(columns=['date', 'number'], data= new_df)

output:

         date  number
0  2020-01-02       2
1  2020-01-05       4
2  2020-01-10       5
3  2020-01-17       2
user_93071410
Related