Binning time data

Hello all,

I have a dataset which contains information for several parameters like "fluorescence intensity", "particle size", etc. in time. However, the time values are not uniformly distributed.

17.10.2012 17:23:53
17.10.2012 17:23:54
17.10.2012 17:23:55
17.10.2012 17:23:55
17.10.2012 17:23:56
17.10.2012 17:23:57
17.10.2012 17:23:57
17.10.2012 17:33:01
17.10.2012 17:33:02

My goal is defining a function which lets the user enter the time size (in minutes) and averages the data. Since there are gaps in the data, I need to find those gaps and add missing time values (of course there will be no data point for those lines). Would you suggest converting the data in 2D form because there are parameters connected to the time values. If I avarage the time values, I need to average also the corresponding data values. If we assume that we average for every 5 minutes, I expect to end up with something like that:

17.10.2012 17:23:00
17.10.2012 17:28:00
17.10.2010 17:33:00

What I managed to write is here: Do you have any suggestions?


Function BinWIBSData(timeinterval)
	Variable timeinterval										// timeinterval will be assigned by the user from the front panel "Set Time Resolution"
	Variable timeincrease = timeinterval * 60						// Convert the value given by the user to seconds (user gives always values in minutes)
	Variable startTime, endTime, numofLines, sizeoftimewave		
	Duplicate/O DateTimeWave, TimeWave                                                // Protect the original wave and work on the copy
	startTime = ceil(Timewave[0])								// startTime is the time where we start binning
	endTime = ceil(Wavemax(TimeWave))							// endTime is the time where we stop binning
	numofLines = ceil((endTime - startTime) / timeincrease) + 1	                // numofLines is required to generate an empty cell to save binned time values
	Make/D/O/N=(numoflines-1), BinTimeWave
	SetScale d, 0, 0, "dat", BintimeWave					// We make the destination wave. Destination wave will contain too many data points (missing data or gap between data points)
	//print BinTimeWave
	Variable k = 1
	Variable l = 0
	Variable n
	BinTimeWave[0] = startTime
	sizeoftimewave = numpnts(TimeWave)
	
	for(k=1; k<= sizeoftimewave-1;)
		if(TimeWave[k] - startTime == timeincrease)                      // If the difference between two lines equals to the timeincrease, it means that we found the latest member of this bin. Increase l and save this in Bintimewave
			l += 1
			BinTimeWave[l] = TimeWave[k]
			startTime = BinTimeWave[l] 
			//print BinTimeWave
		elseif(TimeWave[k] - TimeWave[k-1] > timeincrease)        // If the difference between two lines is larger than the given timeincrease, it means that there is a gap. We must find out how big this gap and include missing
                                                                                                     // data points.
			Variable nummislines = ceil((TimeWave[k] - TimeWave[k-1]) / timeincrease)     // Calculate the number of missing lines between two data points.
			Make/D/O/N = (nummislines+1), temp_wave1           // Make a new temporary wave to generate and store the missing date values
			SetScale d, 0, 0, "dat", temp_wave1                     
			temp_wave1[0] = ceil(TimeWave[k-1])                     // Calculate missing points.
			for(n=1; n<= nummislines; n+= 1)
				temp_wave1[n] = temp_wave1[n-1] + timeincrease
			endfor   
			BinTimeWave[l,] = temp_wave1[p-l]                        // Move missing data points to the BinTimeWave
			k += 1
		else
			k += 1                 // If difference between two lines is smaller than the given timeincrease go on searching
		endif
	endfor
	return 0
End
Hi Tooprock,

I work with time-stamped data with many (small) gaps. I first use a routine I wrote called FillGaps,
which sets all missing datapoints to NaN, then makes it a timescaled wave.

Next I use AverageScaled, which allows me to average with any interval.

Available on request.

FillGaps (NominalDeltaTime, TimeWave, WaveList)

AverageScaled (WaveToAverage, Number0fPoints, Point_Number_to_Start_With, OutputWave, MinimumNumberOfPointsInAn_Average)


Roger Pyle
Hi Roger,

It sounds great. Can I have this routine? I am not sure if it will work for my dataset. It would be great to test it.

Cheers,
Emre
Routines to:
convert a wave and its associated time wave to a time-scaled wave, with gaps indicated as NaNs.
average a time-scaled wave (ignoring NaNs).


        Function FillGaps(delnom,twave,wlist) 
	// Inserts NaN-points for all missing times
	// in a list of waves, using time-wave twave
	// The waves in the list are then x-scaled.
	// Delnom is the nominal delta-t in seconds.
	// Example: FillGaps(3600.0,tsecs,"ww1;ww2;ww3")
	// REMOVES ALL TIME STEPBACKS AND REPEATS
	// use WaveList("*",";","") to get list.
	// fillgaps(3600.,w1,"w2")
                    
	Variable/D delnom                   // Nominal delta-t (seconds)
	Wave/D twave                        // Time wave, must be double-precision real
	String wlist                        // List of waves, semicolon-separated

	Variable i=0, ii=0, iii=1           // Various internal counters
	Variable numinsert=0, pt    
	Variable/D delta, newtlen           // and variables.
	String wv                           // Will hold wavenames
	// Let user know how much
	// the waves will expand
	pt =  numpnts(twave)
	newtlen = round((twave[pt-1]-twave[0])/delnom)+1
	Print "All waves will grow from ",pt,"to",newtlen,"points."
	Print "Time range:",secs2date(twave[0],0),secs2time(twave[0],3),"to",secs2date(twave[pt-1],0),secs2time(twave[pt-1],3)
	do
		if (i+1==numpnts(twave))			// See if done
			break                                		// and leave
		endif
		delta = twave[i+1]-twave[i]              // Size of delta-t in seconds
    
		do
			if(delta<0.5*delnom)                   // Stepback or repeat?
				print/D twave[i], secs2date(twave[i],0), secs2time(twave[i],0),delta, "StepBack!"
				DeletePoints i+1,1,twave
				ii=0
				do                                   // For each wave in list:
					wv=GetStrFromList(wlist,ii,";")
					if (strlen(wv) == 0)
						break                            // Way to leave when list exhausted
					endif
					wave w=$wv                         // Since this is in a function, we need
					// a wave, not a string
					DeletePoints i+1, 1, w             // Put in the point
					w[i+1]=NaN                         // and set it to NaN
					ii+=1
				while(1)
				delta = twave[i+1] - twave[i]
			else
				break
			endif
		while(delta<0.5*delnom)

		if(delta>1.5*delnom)                     // is there a gap?
			if (delta>5*delnom)
				print/D twave[i], secs2date(twave[i],0), secs2time(twave[i],0),delta, "Step Forward!"
			endif
			ii = 0
			numinsert=round(delta/delnom)-1        // Number of missing points in gap
			InsertPoints i+1,numinsert,twave       // Stick them into twave
			iii=1
			do                                     // and calculate the values 
				twave[i+iii]=twave[i]+delnom*iii
				iii+=1
			while(iii<=numinsert)
			do                                     // For each wave in list:
				wv=GetStrFromList(wlist,ii,";")
				if (strlen(wv) == 0)
					break                              // Way to leave when list exhausted
				endif
				wave w=$wv                           		// Since this is in a function, we need
				// a wave, not a string
				InsertPoints i+1, numinsert, w		// Put in the points
				w[i+1,i+numinsert]=NaN 				// and set them to NaN
				ii+=1
			while(1)
		else
			numinsert=0                            		// Cleaning
		endif                                     			// up and
		i=i+numinsert+1                           	// getting out
	while(1)
	ii=0                                       				// Now set up the wave X-scaling
	do                                          				// by going through the wavelist
		wv=GetStrFromList(wlist,ii,";")		// again
		if (strlen(wv) == 0)
			break
		endif
		wave w=$wv
		SetScale/P x,twave[0],delnom,"dat",w
		ii+=1
	while(1)
end
<\igor>

Proc AverageScaled (win,n,ph,wout, nmin)		// Averages n points in wave win,
	// time from x-scaling, Starting at point ph.
	// Output is to wave wout, with times
	// in x-scaling.   KRP 15 October 1993
	// Wavestats version 9 June 1999
	variable n, ph, nmin
	string win, wout
	prompt win,  "Wave to Average:", popup, Wavelist("*",";","")
	prompt wout, "Output Average Wave:"
	prompt n,    "Averaging Interval (# of points):"
	prompt ph,   "Point Number with which to start"
	prompt nmin,   "Minimum # of points accepted"
	
	variable in,be,en,i,np, ii
	silent 1; pauseupdate
	Make /O /N=(numpnts($win)/n) $wout
	$wout = NaN
	DoAvgScaled($win,n,ph,$wout,nmin)
endMacro

Function DoAvgScaled(win,n,ph,wout, nmin)
	wave win, wout
	variable n, ph, nmin
	
	variable  i=0, ii=0, np=numpnts(win), in, be, en
	do 
		in=i/n;be = in*n+ph;en=min(be+n-1,np-1)
		wout[in] = PSW(win,be,en, nmin)
		i += n
		ii += 1
	while(en<(np-1))
	SetScale/P x pnt2x(win,ph),n*deltax(win),"dat", wout
End

// The following function returns the sum of the y values
// of a wave.  The first works from point number pStart up to
//  and including point number pStop. Uses Wavestats.
//	nmin version, 9 June 1999. RP

Function PSW(w,p1,p2,nmin)
	wave w
	variable p1, p2, nmin

	variable V_npnts, V_avg, V_numNaNs
	Wavestats/q/r=[p1,p2] w
	if (V_numNaNs <= (p2 - p1 + 1 - nmin))
		return (V_Avg)
	endif
	return (NaN)
end

Thank you so much for your quick response. I will try it and let you know if it works for me.

Cheers,
Emre