Averaging values on the same date
Hi,
I have a 1D timeseries wave (let's call it data1) that spans over 20 days and its corresponding timestamps wave (t_series). The issue is that the number of rows are not equally distributed over 20 days, and I need to calculate daily averages. So I cannot simply tell the program to average over a fixed number of rows since each day has data spread over a different number of rows. My plan is to somehow tell the program to average data in all rows that correspond to a fixed date.
However, I couldn't figure out how to write the code for this. Kindly help. The timestamps wave is in MM/DD/YYYY HH:MM:SS format.
What I'm thinking is to make a temp wave that contains all points corresponding to the same date, run Wavestats/Q temp, get V_avg and load the value into a new daily averaged wave. The problem is I can't figure out how to tell the program to isolate values from data1 wave into the temp wave on the basis of date.
Many Thanks in advance!
Peeyush
It would be first interesting if your dates (t_series) is a text wave with time stamps as strings or in a date/time format. In the former case you just could loop through each entry together with extracting averages from data1 and look if the first 10 characters (the MM/DD/YYYY part) is still the same, i.e. the same day, and if not begin a new average wave. In the latter case you just need to do the extra step and convert the date/time into a string (e.g., by using Secs2Date()) and then do the comparison. I am sure other members will come up with more effective methods, but this is what came to my mind first.
June 24, 2021 at 07:38 am - Permalink
Hi chozo!
This is great, thanks! I was thinking somewhat on the same lines vis-a-vis identifying the first few characters of the string. However, t_series is in dat format. Could you please help me with the syntax for character identification in a string?
For converting date/time to text, is this how I'd use the function you mentioned "Secs2Date(t_series)"?
Thanks,
Peeyush
June 24, 2021 at 10:09 am - Permalink
I'd first convert the timestamp wave into a wave containing the Julian values (see the dateToJulian() function). That will give you constant values for all entries of the same day. Then use the following function to obtain the averages:
Variable firstDay=WaveMin(julianWave)
Variable lastDay=WaveMax(julianWave)
Variable dayNum=firstDay
Variable numDays=lastDay-firstDay+1
Variable count=0
Make/N=(numDays)/O averageWave=nan
do
MatrixOP/FREE/O aa=sum(dataWave*equal(julianWave,dayNum))/sum(equal(julianWave,dayNum))
averageWave[count]=aa[0]
count+=1
dayNum+=1
while(dayNum<lastDay)
End
You can probably write a more efficient function that eliminates the while loop but I think this is a decent starting point.
A.G.
June 24, 2021 at 10:59 am - Permalink
Hi,
If you just want the unique day as a value, then I would create a new text wave, thedate, and fill it it with t_series[0,9] which picks the first 10 characters of the time stamp wave.
From there I would create a text wave with the unique days
Findduplicate /RT=uniquedays, thedate
This will give you a text wave with all the unique dates (MM/DD/YYYY).
From there I would use extract to partition your data , and in this example the first date in the unqiuedays wave. You can easily extend this to loop over your whole data set to get a statistics for each day.
EXTRACT data1, data1subset, stringmatch(thedate,uniquedays[0])
mean(data1subset)
Andy
June 24, 2021 at 11:04 am - Permalink
... and here is a function to convert the text wave to a julian wave:
variable rows=numpnts(twave)
Make/O/N=(rows) julWave
variable i
String mm,dd,yyyy,dates
for(i=0;i<rows;i+=1)
dates=twave[i]
mm=dates[0,1]
dd=dates[3,4]
yyyy=dates[6,9]
julWave[i]=dateToJulian(str2num(yyyy),str2num(mm),str2num(dd))
endfor
End
A.G.
June 24, 2021 at 11:36 am - Permalink