CODEWITHSUNDEEP
pythonpandas
Jean-Paul Azzopardi
Jean-Paul Azzopardi

Reputation: 381

How to solve this using pandas

So I'm working with a stocks dataframe in pandas and I want to label each week 'green' or 'red' according to whether the opening price for the week was greater or lesser than the closing price for the week. What's the best way to go about doing that so that each week has a label.

Also, how would I go about telling python to buy or sell (assuming I start with $100) according to whether the opening price in the upcoming week is 'red' or 'green' (assuming they are already labeled?)

What I'm working with

    Open    High    Low Close   Adj Close   Volume  Return  Date    Week_Number Year    Day Weekday mean_return volatility  labels
0   26.629999   27.000000   26.290001   26.600000   26.599386   1034900 0.000   2017-01-03  1   2017    3   Tuesday 0.6420  1.544865    red
1   26.670000   27.450001   26.540001   27.350000   27.349369   1120700 2.820   2017-01-04  1   2017    4   Wednesday   0.6420  1.544865    green
2   27.340000   27.440001   26.889999   27.139999   27.139374   874500  -0.768  2017-01-05  1   2017    5   Thursday    0.6420  1.544865    red
3   27.070000   27.420000   26.969999   27.280001   27.279371   563900  0.516   2017-01-06  1   2017    6   Friday  0.6420  1.544865    green
4   26.700001   27.190001   26.459999   27.129999   27.129374   878700  -0.550  2017-01-09  2   2017    9   Monday  0.2282  0.704495    green

Upvotes: 1

Views: 42

Answers (1)

keramat
keramat

Reputation: 4543

Your question is not consistent with your data. In your question you are asking about the weeks, but in your sample you are showing daily results. However for the daily change labeling use:

import numpy as np
np.where(df['Open']<df['Close'], 'green', 'red')

output:

array(['red', 'green', 'red', 'green', 'green'], dtype='<U5')

which is what you have provided in your data sample. For the weekly change use:

temp = df.groupby('Week_Number')[['Open', 'Close']].agg({'Open': lambda x: x.iloc[0], 'Close': lambda x: x.iloc[-1]})
temp['label'] = np.where(temp['Open']<temp['Close'], 'green', 'red')
temp.merge(df, on='Week_Number')

output:

Week_Number Open_x  Close_x Open_y  High    Low Close_y Adj Close   Volume  Return  Date    Year    Day Weekday mean_return volatility  labels
0   1   26.629999   27.280001   26.629999   27.000000   26.290001   26.600000   26.599386   1034900 0.000   2017-01-03  2017    3   Tuesday 0.6420  1.544865    red
1   1   26.629999   27.280001   26.670000   27.450001   26.540001   27.350000   27.349369   1120700 2.820   2017-01-04  2017    4   Wednesday   0.6420  1.544865    green
2   1   26.629999   27.280001   27.340000   27.440001   26.889999   27.139999   27.139374   874500  -0.768  2017-01-05  2017    5   Thursday    0.6420  1.544865    red
3   1   26.629999   27.280001   27.070000   27.420000   26.969999   27.280001   27.279371   563900  0.516   2017-01-06  2017    6   Friday  0.6420  1.544865    green
4   2   26.700001   27.129999   26.700001   27.190001   26.459999   27.129999   27.129374   878700  -0.550  2017-01-09  2017    9   Monday  0.2282  0.704495    green

Upvotes: 1

Related Questions