Binning time data
tooprock
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
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
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
March 6, 2013 at 05:03 pm - Permalink
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
March 7, 2013 at 01:37 am - Permalink
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).
// 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
March 7, 2013 at 07:05 am - Permalink
Cheers,
Emre
March 7, 2013 at 07:34 am - Permalink