How can I calculate the daily average values of data matrix

Share icon

Hello, there

How do I calculate the daily average values of the data matrix based on the corresponding time wave, which may lack some hours in one day or some days? If there are any easier method without using the loop after adding missing time points?

 example data (17.65 KB)

AFAIK there is no way to do what you want without writing a loop. I've approached this problem previously by generating a numerical classifier wave, which in your case would have each day as a distinct integer. This wave could then be used to extract the data from the matrix (in a loop, day by day) and generate the average and storing it in a separate wave. 

April 1, 2019 at 09:16 am - Permalink

Thanks for the timely answer, sjr51!  It's very kind of you to share your experience in the process of calculating data.  I also have an idea to calculated the data, but I think the method you provided is better. So I wonder if it is convenient to share the Igor code to me？ I would appreciate it very much!     

April 1, 2019 at 10:16 pm - Permalink

Sure, here's some old code to do this for a 1D wave. It works in Igor 7 or 8. You'd need to change the code a bit for it to work with 2D waves (for example, Variable nPnts = dimsize(inW,0)).

I don't know how you want to average your data per day, e.g. one value per day (average of all columns and all rows) or average of each column per day. In the former case, you can generate a 1D wave from your matrix which contains the averages of each row and then use that with this code as it is.

Finally, to make your keywave (numerical classifier wave), you could calculate the number of days from the first day/time in your date wave, rounding down using floor(). Hopefully this makes sense!

// In the case where we have 1D input wave and a corresponding keywave which shows the indexing for the first wave
// i.e. {3,2,4.5,8} and {1,1,2,2}
// Average these per index point.
/// @param  keyW
/// @param  inW input wave 1D wave with same number of points as keyW
/// @param  avgOpt  0 for mean, 1 for median
/// @param  errOpt  if avgOpt = 0: 0 for sd, 1 for sem; if avgOpt = 1: 0 for MAD, 1 for IQR
Function AverageMultiplePoints(keyW,inW,avgOpt,errOpt)
    Wave keyW,inW
    Variable avgOpt, errOpt
   
    // need to check that keyW and inW are the same length
    Variable nPnts = numpnts(inW)
    if(nPnts != numpnts(keyW))
        Abort "Wave lengths don't match"
    endif
   
    FindDuplicates/RN=uniqueW keyW
    WAVE/Z uniqueW
    Variable numUniqueVals = numpnts(uniqueW)
   
    String avgName, errName
    String medName, q25Name, q75Name
   
    if(avgOpt == 0)
        avgName = "avg_" + nameofWave(inW)
        Make/O/N=(numUniqueVals) $avgName
        Wave avgW = $avgName
        if(errOpt == 1)
            errName = "sem_" + nameofWave(inW)
            Make/O/N=(numUniqueVals) $errName
            Wave errW = $errName
        elseif(errOpt == 0)
            errName = "sd_" + nameofWave(inW)
            Make/O/N=(numUniqueVals) $errName
            Wave errW = $errName
        else
            Abort "Incorrect errOpt"
        endif
    elseif(avgOpt == 1)
        medName = "med_" + nameofWave(inW)
        Make/O/N=(numUniqueVals) $medName
        Wave medW = $medName
        if(errOpt == 1)
            q25Name = "q25_" + nameofWave(inW)
            q75Name = "q75_" + nameofWave(inW)
            Make/O/N=(numUniqueVals) $q25Name,$q75Name
            Wave q25W = $q25Name
            Wave q75W = $q75Name
        elseif(errOpt == 0)
            errName = "sd_" + nameofWave(inW)
            Make/O/N=(numUniqueVals) $errName
            Wave errW = $errName
        else
            Abort "Incorrect errVar"
        endif
    else
        Abort "Incorrect avgVar"
    endif
   
    Variable uniqueVal
   
    Variable i
   
    for(i = 0; i < numUniqueVals; i += 1)
        uniqueVal = uniqueW[i]
        Make/FREE/N=(nPnts) tempW
        tempW = (keyW[p] == uniqueVal) ? inW[p] : NaN
        WaveTransform zapnans tempW
        if(avgOpt == 0)
            WaveStats/Q tempW
            avgW[i] = V_Avg
            if(errOpt == 0)
                errW[i] = V_sdev
            elseif(errOpt == 1)
                errW[i] = V_sem
            endif
        elseif(avgOpt == 1)
            StatsQuantiles/Q tempW
            medW[i] = V_Median
            if(errOpt == 0)
                errW[i] = V_MAD
            elseif(errOpt == 1)
                q25W[i] = V_Median - V_Q25
                q75W[i] = V_Q75 - V_Median
            endif
        endif
    endfor
   
    String plotname = "plot_" + NameofWave(inW)
    KillWindow/Z $plotname
    // check unique wave is contiguous then plot accordingly
    if(numUniqueVals - 1 == uniqueW[numUniqueVals - 1] - uniqueW[0])
        if(avgOpt == 0)
            Display/N=$plotname avgW
        else
            Display/N=$plotname medW
        endif
    else
        if(avgOpt == 0)
            Display/N=$plotName avgW vs uniqueW
        else
            Display/N=$plotName medW vs uniqueW
        endif
    endif
    // add error bars
    if(avgOpt == 0)
        ErrorBars/W=$plotName $avgName SHADE= {0,0,(0,0,0,0),(0,0,0,0)},wave=($errName,$errName)
    elseif(avgOpt == 1)
        if(errOpt == 0)
            ErrorBars/W=$plotName $medName SHADE= {0,0,(0,0,0,0),(0,0,0,0)},wave=($errName,$errName)
        elseif(errOpt == 1)
            ErrorBars/W=$plotName $medName SHADE= {0,0,(0,0,0,0),(0,0,0,0)},wave=($q75Name,$q25Name)
        endif
    endif
End

 

April 2, 2019 at 07:01 am - Permalink

Thanks for sharing your code to me and sorry for the delayed reply! Actually,  I have found the method to adjust the code based on the suggestion you posed to me! It's very helpful and thanks again! 

April 5, 2019 at 01:57 am - Permalink