4
$\begingroup$

I have a list of list as below (I show part of it)

{{1902, 0.4662}, {1903, 0.22443}, {1905, 0.02936}, {1906, 
  0.02702}, {1908, -0.08354}, {1909, -0.05241}, {1911, 
  0.02388}, {1912, 0.03738}, {1914, 0.25015}, {1915, 0.2831}, {1917, 
  0.4415}, {1919, 0.18315}, {1921, 0.2256}, {1923, 0.24132}, {1926, 
  0.21473}, {1928, 0.29596}, {1930, 0.47693}, {1933, 0.41607}, {1935, 
  0.22161}, {1937, 0.3322}, {1940, 0.2099}, {1942, 0.23376}, {1944, 
  0.44114}, {1947, 0.15876}, {1949, 0.43953}, {1951, 0.71407}, {1954, 
  0.9595}, {1956, 0.59436}, {2000, 0.6832}, {2004, 0.86861}, {2007, 
  0.48201}, {2011, 0.70796}, {2015, 0.57029}, {2020, 0.61997}, {2026, 
  0.79266}, {2032, 0.78726}, {2038, 0.83884}}

For example, in {1902,0.4662} "1902" represent time 19:02 and 0.4662 represent the data at time 19:02.

What I want to do is to calculate average of the data in every 5 minutes. That is from 19:02 to 19:06, from 19:07 to 19:11. Notice the time step is not evenly distributed.

Histogram can naturally count how many of the data are in each interval. I want to partition the list like Histogram and then I can calculate average in each interval. And notice that the data may not be in a single day.

I can't figure out an elegant way to do this with built-in function. Can somebody help me?

Besides I think this is a very simple statistical calculation of data. I want to know what software could do this easily and directly.

Update: the full data sample is here http://en.textsave.org/VdL with date information

Improve this question
$\endgroup$
7
  • $\begingroup$ You say that you want something from 19:07 to 19:11 but your set of data do not include 1907. Am I missing something? $\endgroup$
    – Öskå
    Commented Sep 28, 2013 at 11:25
  • $\begingroup$ Are your data always referring to the same day ? $\endgroup$
    – b.gates.you.know.what
    Commented Sep 28, 2013 at 12:00
  • $\begingroup$ @Öskå No, the data is not evenly distributed. Sometimes there is even no data within 5 minutes. $\endgroup$
    – matheorem
    Commented Sep 28, 2013 at 12:02
  • $\begingroup$ @b.gatessucks No, the instrument gathering the data all the time. $\endgroup$
    – matheorem
    Commented Sep 28, 2013 at 12:03
  • $\begingroup$ I think it's difficult to make something robust if you don't include the full date in your list. $\endgroup$
    – b.gates.you.know.what
    Commented Sep 28, 2013 at 12:20

4 Answers 4

Reset to default
8
$\begingroup$

One approach:

  1. Create intervals from dataset;

    int = Table[Interval[{j, j + 4}], {j, 1902, 2038, 5}];

  2. Calling your dataset data the means for the desired intervals can be obtained:

    Mean /@ GatherBy[data, IntervalMemberQ[int, #[[1]]] &][[All, All, 2]]

yielding:

{0.186753, -0.0373567, 0.19021, 0.283417, 0.228025, 0.386445, 
0.31884, 0.27105, 0.33745, 0.437453, 0.77693, 0.6832, 0.86861, 
0.594985, 0.57029, 0.61997, 0.79266, 0.78726, 0.83884}

EDIT

Mr. Wizard correctly pointed out the error in my code. My first edit was wrong. The easiest approach (it seems to me) is to convert times to temporal data.

f[x_] := {2013, 9, 28, IntegerPart[x/100], 
  100 (x/100 - IntegerPart[x/100])}

Then using TemporalData

td = TemporalData[{f[#[[1]]], #[[2]]} & /@ data];
answ = TemporalData`Aggregate[td, {5, "Minute"}]

The default function applied to the partitioned temporal data is mean.

This can be visualised:

DateListPlot[{td["Path"], answ["Path"]}, Joined -> {False, True}]

A good post is here.

enter image description here

Improve this answer
$\endgroup$
7
  • $\begingroup$ FYI: You need an extra four space indent under numbered lists for proper formatting. $\endgroup$
    – Mr.Wizard
    Commented Sep 28, 2013 at 12:54
  • $\begingroup$ You undid my edit. I am guessing that was not intentional. May I revert it? Also, I see that your code can be simplified. Do you mind if I edit it? $\endgroup$
    – Mr.Wizard
    Commented Sep 28, 2013 at 12:56
  • $\begingroup$ @Mr.Wizard I was typing at same time and I am always happy to learn so feel free. $\endgroup$
    – ubpdqn
    Commented Sep 28, 2013 at 12:58
  • $\begingroup$ I have given my vote because this is an good idea. However, it may not be complete as it does not account for a sixty minute hour. I believe you will need to convert the data at some point to account for that. $\endgroup$
    – Mr.Wizard
    Commented Sep 28, 2013 at 13:03
  • $\begingroup$ @Mr.Wizard thank you. I agree that ideally the "time" could be managed better as a {y,m,d,h,m,s} and TemporalData has functionality. $\endgroup$
    – ubpdqn
    Commented Sep 28, 2013 at 13:09
6
$\begingroup$

This is probably not going to be the fastest method as it doesn't make good use of vector optimizations, but I believe it is quite general and convenient.

split[data_, width_, normfn_] :=
  With[{offset = Mod[Min[normfn /@ First /@ data], width]},
    GatherBy[data, Floor[normfn[#[[1]]] - offset, width] &]
  ]

Now: with your data assigned to data:

dat2 = split[data, 5, QuotientRemainder[#, 100].{60, 1} &]

{{{1902, 0.4662}, {1903, 0.22443}, {1905, 0.02936}, {1906, 0.02702}},
 {{1908, -0.08354}, {1909, -0.05241}, {1911, 0.02388}}, ... }

QuotientRemainder[#, 100].{60, 1} & is an off-hand function to convert your hour-minute integers to minutes.

From there you can extract your second elements and find means:

Mean /@ data2[[All, All, 2]]

{0.186753, -0.0373567, 0.19021, 0.283417, 0.228025, 0.386445, 0.31884, 0.27105,
 0.33745, 0.437453, 0.77693, 0.6832, 0.86861, 0.594985, 0.57029, 0.61997, 0.79266, 
 0.78726, 0.83884}

This function will not give values for empty bins. It was not clear to me from the question if you want that.

Here is a different approach. I will use BinLists this time which is I believe the intended function for such things, though at least in version 7 is often quite slow compared to alternatives (GatherBy) so I tend to avoid it. It will however return empty bins which the method above does not.

This time I will convert the date/time data to single numeric values in advance and let BinLists do most of the rest. For hour/minute data we may use:

normfn = QuotientRemainder[#, 100].{60, 1} &;

If your data includes dates you will need to split it differently and then use AbsoluteTime. If you use AbsoluteTime the data will be in seconds rather than minutes so you will need to account for that.

data2 = data;
data2[[All, 1]] = normfn @ data2[[All, 1]];
{min, max} = {Min@#, Max@#} &[First /@ data2];

BinLists[data2, {min, max, 5}, {-1*^1000, 1*^1000, 2*^1000}]

You can process the data as needed from there.

Improve this answer
$\endgroup$
9
  • $\begingroup$ My understanding is that same hour/minute but different date would be two different bins; does your function account for that (if you share my assumption) ? $\endgroup$
    – b.gates.you.know.what
    Commented Sep 28, 2013 at 13:21
  • $\begingroup$ @b.gatessucks The data only contains hour/minute per the OP's description. If it is to include dates it will need a different format. But that's a good point; really this should all be converted with AbsoluteTime for direct comparison. $\endgroup$
    – Mr.Wizard
    Commented Sep 28, 2013 at 13:24
  • $\begingroup$ By the way this code is far from my best; I'm applying normfn twice to every value for one thing. However I'm just not in the mood to improve it right now. $\endgroup$
    – Mr.Wizard
    Commented Sep 28, 2013 at 17:24
  • $\begingroup$ @Mr.Wizard Thank you, Mr.Wizard! Your method using Binlists is quite general, I like it! It is hard to determine which answer should be accepted as the best answer. ubpdqn's answer using TemporalData is really suitable for this particular problem involving date. $\endgroup$
    – matheorem
    Commented Sep 29, 2013 at 4:06
  • $\begingroup$ @matheorem As stated I expect GatherBy to be more efficient (than BinLists) once the function is better written. I'll try to do that soon but frankly I'm a bit fatigued with Stack Exchange at the moment and I'm working on other things. As for Accepting, if all your data is date/time tagged I think TemporalData is the way to go (though I don't have it in v7 to compare; I'm assuming performance is good). If/when I update split I'll be curios to know how it compares. $\endgroup$
    – Mr.Wizard
    Commented Sep 29, 2013 at 5:49
3
$\begingroup$

TimeSeriesAggregate

dl = MapAt[AbsoluteTime @ DateList[{StringInsert[ToString[#], ":", 3],
  {"Hour", "Minute"}}]&, data, {All, 1}];

dlmean = TimeSeriesAggregate[dl, {{5, "Minute"}, Left}, Mean];

DateListPlot[{dl, dlmean}, Joined -> {False, True}, PlotStyle -> Thick,
  GridLines -> {dlmean[[All, 1]], Automatic}]

enter image description here

Improve this answer
$\endgroup$
0
$\begingroup$

Let the list be L.

If the difference is 1 min all the time:

Mean /@ Partition[L[[All, 2]], 5]

If not, an approach could be:

Integrate[InterpolatingPolynomial[#, t], {t, -1/2, 9/2}]/5 & /@ 
  Map[{Mod[#[[1]], 5], #[[2]]} &, GatherBy[L, Quotient[#[[1]], 5] &], {2}]
Improve this answer
$\endgroup$
2
  • $\begingroup$ I see, i added something now $\endgroup$
    – Coolwater
    Commented Sep 28, 2013 at 11:10
  • $\begingroup$ Furthermore you can add back ticks `` when something is a variable such as L. See here for more details. $\endgroup$
    – Öskå
    Commented Sep 28, 2013 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.