How to identify edge date cases using pandas

user_10829044 0
Viewed 72

I have a dataframe like as shown below

df1 = pd.DataFrame({'person_id': [11,11,11,11,11,12,12,12,12,12,13,13,13,13,14,14,14,14,14],
                        'date_birth': ['12/31/1961','01/01/1961','10/21/1961','12/11/1961','02/11/1961',
                                       '05/29/1967','01/29/1967','04/29/1967','03/19/1967','01/01/1957', 
                                       '12/31/1959','01/01/1959','01/01/1959','07/27/1959',
                                       '01/01/1957','01/01/1957','12/31/1957','12/31/1958','01/01/1957']})
df1 = df1.melt('person_id', value_name='dates')
df1['dates'] = pd.to_datetime(df1['dates'])

My objective is to identify the edge cases in this data frame.

An edge case is defined as a scenario when a subject has both Jan 1st and Dec 31st in their dates column. For instance, from the sample data frame we can see that person_id=11 is a edge case because he has both Jan 1st and Dec 31st in their dates column values whereas person_id = 12 is not a edge case because he doesn't have both Dec 31st and Jan 1st

This is what I tried

op_df = df1.groupby(['person_id'], sort=False).apply(lambda x: x.sort_values(['dates'], ascending=True)).reset_index(drop=True)
op_df['day'] = op_df.dates.dt.day
op_df['month'] = op_df.dates.dt.month
op_df['points'] = np.where(((op_df['day'] == 1) & (op_df['month'] == 1)) & ((op_df['day'] == 31) & (op_df['month'] == 12)),'edge','No')

But the code above doesn't filter correctly. It returns as No for all my person_ids.

I expect my output to be like as below

enter image description here

pandas-groupby pandas dataframe datetime python
1 Answers

Here is problem is not possible day=1& month=1 with end of month, need chain by | for OR:

op_df = df1.sort_values(['person_id','dates'])
op_df['day'] = op_df.dates.dt.day
op_df['month'] = op_df.dates.dt.month
op_df['points'] = np.where(((op_df['day'] == 1) & (op_df['month'] == 1)) | ((op_df['day'] == 31) & (op_df['month'] == 12)),'edge','No')

If need for both edges separate columns is possible create 2 columns first by masks, aggregate sum for count Trues values and add Edge column in DataFrame.insert for second column by condition - Yes if at least one 0 in one or second column:

#instead groupby + sort_values use sort_values by 2 columns
op_df = df1.sort_values(['person_id','dates'], ascending=True)

day = op_df.dates.dt.day
month = op_df.dates.dt.month
   
op_df['1.1'] = (day == 1) & (month == 1)
op_df['31.12'] = (day == 31) & (month == 12)

op_df = op_df.groupby('person_id', as_index=False)[['1.1','31.12']].sum()
op_df.insert(1, 'Edge', np.where(op_df[['1.1','31.12']].eq(0).any(axis=1),'No','Yes'))
print (op_df)
   person_id Edge  1.1  31.12
0         11  Yes    1      1
1         12   No    1      0
2         13  Yes    2      1
3         14  Yes    3      2
user_29010020
Related