Home > Enterprise >  Split 24 hour data in pandas
Split 24 hour data in pandas

Time:12-15

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?

CodePudding user response:

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
  • Related