Counting value changes in irregular time series data

Question

I have time series data that look like this:

Timestamp Value 26/09/2013 17:00:00 1 26/09/2013 17:05:00 1 26/09/2013 17:08:41 1 26/09/2013 17:38:43 1 26/09/2013 17:49:55 0 26/09/2013 17:49:57 1 

I want to convert it to a regular time series (15 m) with counting the number of times the value has changed over a period of 15 m. So something like this

 End Timestamp Value at End Times Changed 26/09/2013 17:15:00 1 0 26/09/2013 17:30:00 1 0 26/09/2013 17:45:00 1 0 26/09/2013 18:00:00 1 2 

I looked at Pandas and I cannot figure out how to do this.

A little context may help. This is SCADA data (sensor), and 1 and 0 correspond to the state of the equipment - for example, the switch is open or closed. The SCADA system reports the current value when it changes, but it also regularly polls and reports the current value at this point in time (which may not have changed).

What I want to do is get the data in a form that can be loaded into the database, and we can start a query about which switches change frequently.

Answer 1

This is a bit of a hack, but it works:

 import datetime import pandas as pd time_vec = [datetime.datetime(2013,9,26,17,0,0) ,datetime.datetime(2013,9,26,17,5,0) ,datetime.datetime(2013,9,26,17,8,41) ,datetime.datetime(2013,9,26,17,38,43) ,datetime.datetime(2013,9,26,17,49,55) ,datetime.datetime(2013,9,26,17,49,57)] df = pd.DataFrame([1,1,1,1,0,1],index = time_vec,columns=['value']) df['count_change']=0 df.ix[df.value!=df.value.shift(1),'count_change']=1 df.ix[0,'count_change']=0 df.resample('15min',how={'value': 'last', 'count_change': 'sum'},fill_method='ffill',label='right') 

Edit:

I just realized that you can only forward the filling of the value column with intervals without data, but not the count_change column (although this does not change anything in this particular example). Workaround may be:

 df.resample('15min',how={'value': 'last', 'count_change': 'sum'},label='right').fillna(value={'count_change':0}).fillna(method='ffill')