Max index of the remaining current week whereby open is max

Question

I have a price dataframe: Date, Open, Close, Week, Trading

I would like to express in python, for each row in the dataframe, how to calculate the max price meeting the following conditions:

Trading is true
Date is future (more than current row date)
Week is the same
MaxH is maximum open or if last trading day of the week, MaxH=Close

I know a for-loop will probably get the answer, but I am looking for a more pythonic expression.

For example,

Row, Date,       Open,    Close,   Week, Trading,MaxHDate      MaxH
0    2017.6.12   120        1      W1    True    2017.6.14     500
1    2017.6.13   100        2      W1    True    2017.6.14     500
2    2017.6.14   500        3      W1    True    2017.6.14     500
3    2017.6.15   300      350      W1    True    2017.6.15     350
4    2017.6.16   1000       4      W1    False
5    2017.7.1    2000       5      W2    True    2017.7.1      5

In row 1, it will take max(Open) in rows 2..3 since only these rows have similar Week and Trading is True and with Date later than 2017.6.13. The answer is 500 on 2017.6.14. In row 3, since it is the last row in week W1 and Trading==True, it will return max(open,close) on the same day.

The original code has a lot more conditions and quite lengthy to post. I simplified the question to focus on what I wanted to ask. Thanks. — william wong, Jun 13 '17 at 10:04

score 0 · Answer 1 · answered Jun 13 '17 at 08:37

First we need to filter the dataframe to have the rows that we want to compute the max price

Let df be dataframe assume date is a type of Datetime

df[df.Trading == True && df.date > datetime.datetime.now().date()]

ok this filter 1 and 2

what do you mean by week is the same you can use something like groupby

df.groupby('Week').count()

You can filter 4 by doing the following (I am confuse what you are trying to do)

df[df.Open == df.Open.max() & df.Close == df.Close.max()]

and refer to this link to get last day of the week you can combine everything with &

Python: give start and end of week data from a given date

The question is too open it need to be more specific

Thanks for the answer. In (2) I was referring to the current row date not the current time. In (4) I was trying to find the max open in the remaining trading days of same week (indicated by Trading==True). — william wong, Jun 13 '17 at 09:51

score 0 · Answer 2 · answered Jun 20 '17 at 07:49

Here is my trial:

a = pd.DataFrame(data={
    'open': [120, 100, 500, 300, 1000, 2000], 'close': [1, 2, 3, 350, 4, 5],
    'Week': ['W1', 'W1', 'W1', 'W1', 'W1', 'W2'], 'Trading': [True, True, True, True, False, True]},
index=[pd.to_datetime('2017-6-12'), pd.to_datetime('2017-6-13'), pd.to_datetime('2017-6-14'),pd.to_datetime('2017-6-15'), pd.to_datetime('2017-6-16'), pd.to_datetime('2017-7-1')])

for d in a.index:
    f = a[(a.index >= d) & (a.Week == a.loc[d].Week) & a.Trading]
    if len(f)>0:
        m = f.open.argmax()
        a.loc[d,'MaxHDate'] = m
        a.loc[d,'MaxH'] = f.loc[m].open if len(f)>1 else f.loc[d].close

Max index of the remaining current week whereby open is max

2 Answers2