pythonpandasdataframegroup-byrowwise

Group by consecutive values in one column and select the earliest and latest date for each group


I want to group by consecutive values in col1 and for each group, select the earliest date and hour from col2 and col3 and the latest date and hour from col4 and col5.

Here is my data:

col1 col2 col3 col4 col5
0 a 2021-07-03 17:08 2021-07-04 10:41
1 b 2021-07-10 04:14 2021-07-11 04:32
2 c 2021-07-13 02:03 2021-07-14 00:45
3 d 2021-07-14 21:23 2021-07-15 02:59
4 d 2021-07-15 04:05 2021-07-15 09:41
5 e 2021-07-17 13:50 2021-07-18 08:49
6 a 2021-07-18 10:51 2021-07-18 12:27
7 a 2021-07-18 13:55 2021-07-19 06:26
8 f 2021-09-20 22:36 2021-09-20 23:19
9 f 2021-09-21 23:45 2021-09-23 10:12

Expected output:

col1 col2 col3 col4 col5
0 a 2021-07-03 17:08 2021-07-04 10:41
1 b 2021-07-10 04:14 2021-07-11 04:32
2 c 2021-07-13 02:03 2021-07-14 00:45
3 d 2021-07-14 21:23 2021-07-15 09:41
4 e 2021-07-17 13:50 2021-07-18 08:49
5 a 2021-07-18 10:51 2021-07-19 06:26
6 f 2021-09-20 22:36 2021-09-23 10:12

Solution

  • This solution expects the dates to be sorted from the earliest to the latest, as in the provided example data.

    from itertools import groupby
    from io import StringIO
    
    import pandas as pd
    
    
    df = pd.read_csv(
        StringIO(
            """col1 col2    col3    col4    col5
    a   2021-07-03  17:08   2021-07-04  10:41
    b   2021-07-10  04:14   2021-07-11  04:32
    c   2021-07-13  02:03   2021-07-14  00:45
    d   2021-07-14  21:23   2021-07-15  02:59
    d   2021-07-15  04:05   2021-07-15  09:41
    e   2021-07-17  13:50   2021-07-18  08:49
    a   2021-07-18  10:51   2021-07-18  12:27
    a   2021-07-18  13:55   2021-07-19  06:26
    f   2021-09-20  22:36   2021-09-20  23:19
    f   2021-09-21  23:45   2021-09-23  10:12
    """
        ),
        delim_whitespace=True,
        header=0,
    )
    
    # Group by consecutive values in col1
    groups = [list(group) for key, group in groupby(df["col1"].values.tolist())]
    group_end_indices = pd.Series(len(g) for g in groups).cumsum()
    group_start_indices = (group_end_indices - group_end_indices.diff(1)).fillna(0).astype(int)
    
    filtered_df = []
    for start_ix, end_ix in zip(group_start_indices, group_end_indices):
        group = df.iloc[start_ix:end_ix]
        if group.shape[0] > 1:
            group.iloc[0][["col4", "col5"]] = group.iloc[-1][["col4", "col5"]]
        filtered_df.append(group.iloc[0])
    filtered_df = pd.DataFrame(filtered_df).reset_index(drop=True)
    print(filtered_df)
    

    Output:

      col1        col2   col3        col4   col5
    0    a  2021-07-03  17:08  2021-07-04  10:41
    1    b  2021-07-10  04:14  2021-07-11  04:32
    2    c  2021-07-13  02:03  2021-07-14  00:45
    3    d  2021-07-14  21:23  2021-07-15  09:41
    4    e  2021-07-17  13:50  2021-07-18  08:49
    5    a  2021-07-18  10:51  2021-07-19  06:26
    6    f  2021-09-20  22:36  2021-09-23  10:12