CODEWITHSUNDEEP
pythonpandasloopspandas-groupby
JD_Trader
JD_Trader

Reputation: 19

finding first instance of condition in all the groups using groupby in pandas

I am trying to find the first instance of a condition getting satisfied in each group and then merge different groups together. Here in the below data, I want the first instance in a separate column as True when 'putbuy' column turns 1 from 0 for every month in the data, which is from 1994-2018.

Data: 

month_x year_x  day_x   putbuy Desired putbuy
5977    2   2018    14  1           1
5978    2   2018    15  1           0
5979    2   2018    16  1           0
5980    2   2018    19  1           0
5981    2   2018    20  1           0
5982    2   2018    21  1           0
5983    2   2018    22  0           0
5984    2   2018    23  1           0  
5985    2   2018    26  0           0
5986    2   2018    27  1           0
5987    2   2018    28  0           0
5988    3   2018    1   0           0
5989    3   2018    5   0           0
5990    3   2018    6   0           0
5991    3   2018    7   0           0
5992    3   2018    8   0           0
5993    3   2018    9   0           0
5994    3   2018    12  0           0
5995    3   2018    13  0           0
5996    3   2018    14  0           0
5997    3   2018    15  0           0
5998    3   2018    16  0           0
5999    3   2018    19  1           1
6000    3   2018    20  1           0
6001    3   2018    21  0           0
6002    3   2018    22  1           0 
6003    3   2018    23  1           0
6004    3   2018    26  1           0
6005    3   2018    27  0           0
6006    3   2018    28  0           0

Solution attempt:

grouped=options.groupby(['month_x','year_x'])

for group in grouped:
   while 'Close_x'>'pstrike':
       putb=0
    else:
        putb=1
    break       

    print(group)

Snapshot of my dataset:

snapshot of my dataset

Upvotes: 0

Views: 1862

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

IIUC, you can use idxmax to find the index of first occurance of the maximum value of 'putbuy':

df.loc[df.groupby(['year_x','month_x'])['putbuy'].idxmax(),'DO'] = 1
df['DO'] = df.DO.fillna(0).astype(int)

print(df)

Output:

      month_x  year_x  day_x  putbuy  Desired putbuy  DO
5977        2    2018     14       1               1   1
5978        2    2018     15       1               0   0
5979        2    2018     16       1               0   0
5980        2    2018     19       1               0   0
5981        2    2018     20       1               0   0
5982        2    2018     21       1               0   0
5983        2    2018     22       0               0   0
5984        2    2018     23       1               0   0
5985        2    2018     26       0               0   0
5986        2    2018     27       1               0   0
5987        2    2018     28       0               0   0
5988        3    2018      1       0               0   0
5989        3    2018      5       0               0   0
5990        3    2018      6       0               0   0
5991        3    2018      7       0               0   0
5992        3    2018      8       0               0   0
5993        3    2018      9       0               0   0
5994        3    2018     12       0               0   0
5995        3    2018     13       0               0   0
5996        3    2018     14       0               0   0
5997        3    2018     15       0               0   0
5998        3    2018     16       0               0   0
5999        3    2018     19       1               1   1
6000        3    2018     20       1               0   0
6001        3    2018     21       0               0   0
6002        3    2018     22       1               0   0
6003        3    2018     23       1               0   0
6004        3    2018     26       1               0   0
6005        3    2018     27       0               0   0
6006        3    2018     28       0               0   0

Upvotes: 3

user3582076
user3582076

Reputation: 1343

# create a copy of data
tmp_df = options.copy()

# take diff from previous day
tmp_df.loc[:, 'putbuy_change'] = tmp_df.groupby(['month_x', 'year_x']).putbuy.diff(1)

# keep rows where change is 1
keep = tmp_df[tmp_df.putbuy_change == 1]

# keep first instance of each month
first_ins = keep.groupby(['month_x', 'year_x']).head(1)

# add desired boolean indicator
first_ins.loc[:, 'result_col'] = True

# merge back onto data
result_df = options.merge(first_ins[['month_x', 'year_x', 'day_x', 'result_col']], on=['month_x', 'year_x', 'day_x'], how='left')

Upvotes: 0

Related Questions