Grouped subset cross row calculation and filter grouped results
Task:Find out stocks that have had three consecutive trading day limits (up 10%).
Python
| 1 | import pandas as pd |
| 2 | def con_rise(stock:pd.DataFrame): |
| 3 | rise_day_list = [] |
| 4 | rise_num = 0 |
| 5 | shift_1 = stock['CL']/stock['CL'].shift(1)-1>=0.1 |
| 6 | for bl in shift_1: |
| 7 | if bl == False: |
| 8 | rise_num = 0 |
| 9 | else: |
| 10 | rise_num+=1 |
| 11 | rise_day_list.append(rise_num) |
| 12 | return max(rise_day_list) |
| 13 | stock_file = 'E:\\txt\\StockRecords.txt' |
| 14 | stock = pd.read_csv(stock_file,sep='\t') |
| 15 | stock_g = stock.groupby(by = ['CODE']) |
| 16 | good_code = [] |
| 17 | for index,group in stock_g: |
| 18 | group = group.sort_values(by='DT') |
| 19 | group = group.reset_index(drop = True) |
| 20 | max_rise = con_rise(group) |
| 21 | if max_rise>=5: |
| 22 | good_code.append(index) |
| 23 | print(good_code) |
esProc
| A | ||
| 1 | E:\\txt\\StockRecords.txt | |
| 2 | =file(A1).import@t() | |
| 3 | =A2.group(CODE).(~.sort(DT)) | |
| 4 | =A3.select(func(A5,~)>=5).(~.CODE) | |
| 5 | func | |
| 6 | =(rise=0,A5.(rise=if(CL/CL[-1]-1>=0.1,rise=if(!CL[-1],0,rise+1),0))) | |
| 7 | =max(B6) |
The step-by-step calculation of esProc is very clear: 1. Grouping and sorting; 2. Calculating the maximum days with an increase of more than 0.1; 3. Filtering. The custom function can also be put into the loop function for loop calculation.
