Home / Questions / 126348
Accepted
Jorge Ponti
Asked: 2020-12-22 04:47:07 +0800 CST 2020-12-22 04:47:07 +0800 CST

Group dates based on a difference of more than 30 days between two consecutive

  • 772

Dear, I am developing some queries and I want to have a group which between dates for each 'pfr_Fault_Code'there are no more than 30 days.

If they run the following code, they can see a grouping 'pfr_Fault_Code'by date and a count of events:

from collections import defaultdict
import pandas as pd
from datetime import datetime, timedelta
from time import time

df = pd.read_csv('FDE_before_delay_aviso_previo.csv', header=0, sep=',', usecols=[1,16,20,27,28],parse_dates = ['fault_date'])
df2=df.sort(columns=['CASS_ID','pfrs_pfr_date','fault_date'])
df2 = df2.reset_index(drop=True)

df2=df2.groupby(['pfr_Fault_Code','fault_date']).count()

print(df2)

I want to count the same, but as long as there are no more than 30 days in each date for each 'pfr_Fault_Code'from the most current date backwards . On the other hand, the dates are ordered. In pandas will it be possible to do this directly?

Example:

For one 'pfr_Fault_Code'=XXXXXXI have the following'fault_date'=[2017/12/01],[2017/11/29],[2017/11/10],[2017/09/30],[2017/09/15]

From the most current date back I see if there is a cut between dates greater than 30 days, which is generated between '[2017/11/10]and [2017/09/30]therefore it works for me 'fault_date'=[2017/12/01],[2017/11/29],[2017/11/10].

I was thinking of adding something like this, a new column with the difference in days, but it doesn't work for me:

df2['diferencia'] =df2.groupby('pfr_Fault_Code')['fault_date'].transform(pd.Series.diff).fillna(df2['fault_date'])

TypeError: unsupported operand type(s) for -: 'str' and 'str'

The CSV file to be able to execute the code is the following:

https://drive.google.com/file/d/1f8IFNvMA0Zbm_t0whweZjt4QSHR-ahVy/view?usp=sharing

python

1 Answers

  1. Best Answer

    You can use pandas.DataFrame.groupby.diff()together with count()to create a new column that differentiates each group of dates within a same value pfr_Fault_Codethat they are not separated by more than 30 days with respect to the one that precedes it. Once this is done, it would be enough to just keep the first group for each value of pfr_Fault_Code.

    Let's look at a reproducible and simplified example:

    import pandas as pd

data = {'pfr_Fault_Code': ["111111", "111111", "111111", "111111", "111111", 
                           "222222", "222222", "222222", "222222", "222222",
                           "333333", "333333", "333333", "333333"], 
            'fault_date': ["2017/12/01", "2017/11/29","2017/11/10", "2017/09/30", "2017/09/15", 
                           "2017/10/01", "2017/05/29","2017/05/10", "2017/03/30", "2017/03/30", 
                           "2017/05/01", "2017/04/29","2017/04/10", "2017/01/30"]
        }

df = pd.DataFrame(data)
df['fault_date'] = pd.to_datetime(df['fault_date'])

# Ordenamos de forma descendente las fechas, esto es un paso imprescindible
df.sort_values(by='fault_date', ascending = False, inplace = True)


df['groups'] = df.groupby('pfr_Fault_Code')['fault_date'].diff() < pd.Timedelta(days=-30)
df['groups'] = df.groupby('pfr_Fault_Code')["groups"].cumsum()

    With this we obtain the following DataFrame:

       fault_date pfr_Fault_Code  groups
0  2017-12-01         111111     0.0
1  2017-11-29         111111     0.0
2  2017-11-10         111111     0.0
5  2017-10-01         222222     0.0
3  2017-09-30         111111     1.0
4  2017-09-15         111111     1.0
6  2017-05-29         222222     1.0
7  2017-05-10         222222     1.0
10 2017-05-01         333333     0.0
11 2017-04-29         333333     0.0
12 2017-04-10         333333     0.0
8  2017-03-30         222222     2.0
9  2017-03-30         222222     2.0
13 2017-01-30         333333     1.0

    Now it is very simple to obtain for each value of pfr_Fault_Codethe rows until one of the dates of fault_datehas a difference greater than 30 days with respect to the one that precedes it:

    >>> df[df["groups"] == 0]

   fault_date pfr_Fault_Code  groups
0  2017-12-01         111111     0.0
1  2017-11-29         111111     0.0
2  2017-11-10         111111     0.0
5  2017-10-01         222222     0.0
10 2017-05-01         333333     0.0
11 2017-04-29         333333     0.0
12 2017-04-10         333333     0.0
    • 1