Split dataframe rows in two based on condition

user_76024 0
Viewed 231

I have dataframe with columns from and to time of some event.

import pandas as pd
import numpy as np
data = [
    [np.datetime64('2019-06-25T03:30'), np.datetime64('2019-06-25T05:30')],
    [np.datetime64('2019-06-25T22:30'),np.datetime64('2019-06-26T04:30')],
] 
df = pd.DataFrame(data, columns = ['from', 'to'])

which looks like this

                   from                  to
0   2019-06-25 03:30:00 2019-06-25 05:30:00
1   2019-06-25 22:30:00 2019-06-26 04:30:00

Note how the second row crosses a day boundary. I would like to split that row into two, with the to and from times set to midnight

Expected result:

                   from                  to
0   2019-06-25 03:30:00 2019-06-25 05:30:00
1   2019-06-25 22:30:00 2019-06-26 00:00:00
2   2019-06-26 00:00:00 2019-06-26 04:30:00

I thought about applying a function which does the split but this does not seem to work.

def split_days(row):
    # if it's the same day, do not split
    if row['from'].date == row['to'].date :
        return row

    # if different days, make two rows
    # first day until mignight and second day from midnight onwards
    else:
        row2 = row.copy(deep=True)
        row['to']=row['to'].replace(hour=0, minute=0, second=0, microsecond=0)
        row2['from']=row2['to'].replace(hour=0, minute=0, second=0, microsecond=0)

        return pd.concat([row, row2], axis=1).T # <= this is certainly incorrect

df.apply(split_days, axis=1)

How can I apply a function to each row of a DF, and replace it with several new rows?

pandas numpy
1 Answers

pd.date_range, floor, ceil

delta = pd.Timedelta(days=1)

def ranger(F, T): return pd.date_range(F.ceil('D'), T.floor('D')).union([F, T])
def dzippr(D): return zip(D, D[1:])

pd.DataFrame(
    [[f, t]
     for F, T in zip(df['from'], df['to'])
     for f, t in dzippr(ranger(F, T))],
    columns=df.columns
)

                 from                  to
0 2019-06-25 03:30:00 2019-06-25 05:30:00
1 2019-06-25 22:30:00 2019-06-26 00:00:00
2 2019-06-26 00:00:00 2019-06-26 04:30:00
user_23366540
Related