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