0

Given a timestamped df with timedelta showing time covered such as:

df = pd.DataFrame(pd.to_timedelta(['00:45:00','01:00:00','00:30:00']).rename('span'), 
    index=pd.to_datetime(['2019-09-19 18:00','2019-09-19 19:00','2019-09-19 21:00']).rename('ts'))

#                         span
# ts                          
# 2019-09-19 18:00:00 00:45:00
# 2019-09-19 19:00:00 01:00:00
# 2019-09-19 21:00:00 00:30:00

How can I plot a bar graph showing drop outs every 15 minutes? What I want is a bar graph that will show 0 or 1 on the Y axis with a 1 for each 15 minute segment in the time periods covered above, and a 0 for all the 15 minute segments not covered.

Per this answer I tried:

df['span'].astype('timedelta64[m]').plot.bar()

However this plots each timespan vertically, and does not show that the whole hour of 2019-09-19 20:00 is missing.

vertical_timegap_plot.

I tried

df['span'].astype('timedelta64[m]').plot()

It plots the following which is not very useful.

enter image description here

I also tried this answer to no avail.

Update

Based on lostCode's answer I was able to further modify the DataFrame as follows:

def isvalid(period):
    for ndx, row in df.iterrows():
        if (period.start_time >= ndx) and (period.start_time < row.end):
            return 1
    return 0
df['end']= df.index + df.span
ds = pd.period_range(df.index.min(), df.end.max(), freq='15T')
df_valid = pd.DataFrame(ds.map(isvalid).rename('valid'), index=ds.rename('period'))

Is there a better, more efficient way to do it?

SpeedCoder5
  • 8,188
  • 6
  • 33
  • 34

1 Answers1

2

You can use DataFrame.resample to create a new DataFrame to to verify the existence of time spaces. To check use DataFrame.isin

import numpy as np
check=df.resample('H')['span'].sum().reset_index()
d=df.reset_index('ts').sort_values('ts')
check['valid']=np.where(check['ts'].isin(d['ts']),1,0)
check.set_index('ts')['valid'].plot(kind='bar',figsize=(10,10))

enter image description here

ansev
  • 30,322
  • 5
  • 17
  • 31
  • Thank you. Yes that works per hour. How can we add to it to resample every 15 minutes or every minute to show the dropout from 18:45:00 to 19:00:00? – SpeedCoder5 Sep 20 '19 at 15:12