Split 24 hour data in pandas

Question

I have dataframe basically like that :

19:05:09    86
19:10:09    86
19:15:09    85
19:20:09    84
..
18:55:10    165
19:00:10    164
19:05:10    163

So, it's 24 hour data. I changed to column to datetime to process data however it's all same day and i needed sorted data. I mean after 00 it must be another day. Any idea to do that?

Of course i can split data like that :

data2[(data.times >= data['times'][0])] # first day
data2[(data2.times < data2['times'][0])] # and second day

and add another day to second day.

However, data includes couple of days and all time column in same format. I have to split data 24-hour version day by day (obviosly there isn't day in data only hours) according to first value of datetime column. What is the best method to do that?

Answer 1

Here's one way to do this

import io
data= '''times
19:05:09
19:10:09
23:55:09
00:00:09
19:05:09
19:10:09
23:55:09
00:00:09
19:05:09
19:10:09
23:55:09
00:00:09
'''
df = pd.read_csv(io.StringIO(data), sep=' \s+', engine='python')
df['count'] = df.loc[df['times'].str[0:5]=='00:00'].groupby(df['times'].str[0:5]=='00:00').cumcount() + 1

# Then back or forward fill as needed
df['count'] = df['count'].bfill()
df['count'].ffill().fillna(0)

           times  count
    0   19:05:09  1.000
    1   19:10:09  1.000
    2   23:55:09  1.000
    3   00:00:09  1.000
    4   19:05:09  2.000
    5   19:10:09  2.000
    6   23:55:09  2.000
    7   00:00:09  2.000
    8   19:05:09  3.000
    9   19:10:09  3.000
    10  23:55:09  3.000
    11  00:00:09  3.000