Adding timestamps to pandas series

user_16597981 0
Viewed 36

I'm trying to write a code that will count a number of records in a .csv file in a single hour. So, for example:

 data = pd.read_csv('2021-07-30.csv',  parse_dates=['time'], infer_datetime_format=True)
    datafiltr = data[data.lane == "Lane 4 Op2"]
    datafiltr['time'] = pd.to_datetime(datafiltr['time'])
    df = datafiltr['time'].groupby(datafiltr.time.dt.to_period("H")).agg('count')

Prints:

2021-08-13 13:00    18
2021-08-13 14:00    10
2021-08-13 15:00     2
2021-08-13 16:00     1
2021-08-13 17:00     2
2021-08-13 18:00     4

It works superbly but I need to store data in a 12-hour timespan. Something like this will be ideal:

2021-08-13 13:00    18
2021-08-13 14:00    10
2021-08-13 15:00     2
2021-08-13 16:00     1
2021-08-13 17:00     2
2021-08-13 18:00     4
2021-08-13 19:00     0
2021-08-13 20:00     0
...

But I have no idea how to approach this, please send help.

pandas matplotlib csv python
1 Answers

have you tried with resampling, but at least one of your time sample should be of max time to consider till there

df.set_index('time').resample('H').agg('count')

out:

    1   2
0       
2021-08-13 13:00:00 1   1
2021-08-13 14:00:00 1   1
2021-08-13 15:00:00 1   1
2021-08-13 16:00:00 1   1
2021-08-13 17:00:00 1   1
2021-08-13 18:00:00 1   1
2021-08-13 19:00:00 0   0
2021-08-13 20:00:00 0   0
2021-08-13 21:00:00 0   0
2021-08-13 22:00:00 0   0
user_82080060
Related