How can I calculate the daily average values of data matrix

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. 

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!     

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

 

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!