CODEWITHSUNDEEP
pythonpandasnumpy
poppytop
poppytop

Reputation: 341

min value till row pandas

I have some problem where data is sorted by date, for example something like this:

date,       value,      min
2015-08-17,    3,        nan
2015-08-18,    2,        nan
2015-08-19,    4,        nan
2015-08-28,    1,        nan
2015-08-29,    5,        nan

Now I want to save min values in min column till this row, so result would look something like this: 

date,       value,      min
2015-08-17,    3,        3
2015-08-18,    2,        2
2015-08-19,    4,        2
2015-08-28,    1,        1
2015-08-29,    5,        1

I've tried some options, but still don't get what I'm doing wrong, here is one example that I tried:

data['min'] = min(data['value'], data['min'].shift())

I don't want to iterate through all rows because the data I have is big. What is the best strategy you can write using pandas for this kind of problem?

Upvotes: 3

Views: 731

Answers (2)

Divakar
Divakar

Reputation: 221574

Since you mentioned that you are working with big dataset, with focus on performance, here's one using NumPy's np.minimum.accumulate -

df['min'] = np.minimum.accumulate(df.value)

Sample run -

In [70]: df
Out[70]: 
         date  value  min
0  2015-08-17      3  NaN
1  2015-08-18      2  NaN
2  2015-08-19      4  NaN
3  2015-08-28      1  NaN
4  2015-08-29      5  NaN

In [71]: df['min'] = np.minimum.accumulate(df.value)

In [72]: df
Out[72]: 
         date  value  min
0  2015-08-17      3    3
1  2015-08-18      2    2
2  2015-08-19      4    2
3  2015-08-28      1    1
4  2015-08-29      5    1

Runtime test -

In [65]: df = pd.DataFrame(np.random.randint(0,100,(1000000)), columns=list(['value']))

# @MaxU's soln using pandas cummin
In [66]: %timeit df['min'] = df.value.cummin()
100 loops, best of 3: 6.84 ms per loop

In [67]: df = pd.DataFrame(np.random.randint(0,100,(1000000)), columns=list(['value']))

# Using NumPy
In [68]: %timeit df['min'] = np.minimum.accumulate(df.value)
100 loops, best of 3: 3.97 ms per loop

Upvotes: 5

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210842

Use cummin() method:

In [53]: df['min'] = df.value.cummin()

In [54]: df
Out[54]:
         date  value  min
0  2015-08-17      3    3
1  2015-08-18      2    2
2  2015-08-19      4    2
3  2015-08-28      1    1
4  2015-08-29      5    1

Upvotes: 5

Related Questions