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