pandas - checking business days and replacing duplicates

user_10095005 0
Viewed 67

Assuming the following dataframe:

date | id
2020-12-11 | A
2020-12-15 | A
2020-04-09 | B
2020-04-09 | C
2020-04-08 | C
2021-03-11 | D
2021-03-12 | D
2021-01-24 | E
2021-01-19 | E

Desired output:

date | id
2020-12-11 | A
2020-12-12 | 0
2020-04-09 | B
2020-04-09 | C
2020-04-08 | C
2021-03-11 | D
2021-03-12 | D
2021-01-19 | E
2021-01-20 | 0

Basically, if id is duplicate, we want to check if the dates are consecutive business days. If consecutive business days, keep both; else, only keep the smaller date, remove the latter date and replace it with the next business day, with id '0'.

pandas datetime python
1 Answers

With some assumptions:

def bus_day_dup_rep(x):

    bd1 = pd.tseries.offsets.BusinessDay(n = 1)
    bdp1 = x.iloc[0,0] + bd
    bdm1 = x.iloc[0,0] - bd

    if x.shape[0] > 1:
    
        if x.iloc[1,0] != bdp1 and x.iloc[1,0] != bdm1:
            x.iloc[1,1] = '0'

            if x.iloc[0,0] < x.iloc[1,0]:
                x.iloc[1,0] = bdp1 
            else:
                x.iloc[0,0] = x.iloc[1,0] 
                x.iloc[1,0] = x.iloc[1,0] + bd1 

    return x


df.groupby('id').apply(bus_day_dup_rep)

        date id
0 2020-12-11  A
1 2020-12-14  0
2 2020-04-09  B
3 2020-04-09  C
4 2020-04-08  C
5 2021-03-11  D
6 2021-03-12  D
7 2021-01-19  E
8 2021-01-20  0

Assumptions: Max number IDs per ID is 2. (Can have only 2 A's). 'date' is not the index. 'date' column is dtype 'datetime64[ns]'. 'date and 'id' are in relative column positions 0 and 1 respectively.

user_39687610
Related