Re: create/insert information for missing data points
- To: mathgroup at smc.vnet.net
- Subject: [mg119916] Re: create/insert information for missing data points
- From: Ray Koopman <koopman at sfu.ca>
- Date: Wed, 29 Jun 2011 05:29:48 -0400 (EDT)
On Mon, 27 Jun 2011 at 07:32:55 -0400 (EDT),
lillydogy <lillydogy at yahoo.com> wrote:
> how do I create and insert information for missing data?
>
> have an experiment running in military time, and sometimes it has
> missing data points, sometimes three missing data points in a row or
> more. I want to take the average of the two adjacent (joining) data
> points and insert for the missing point/s. for example, these would
> be the missing points inserted for the example below.
> {305,38.5},{311,40.5},{312,40.5},{313,40.5}.
>
> before :(
> listmissing = {{301,44}, {302,46}, {303,40}, {304,30}, {306,47},
> {307,37}, {308,42}, {309,40}, {310,43}, {314,38}}
>
> after :)
> listfull = {{301,44}, {302,46}, {303,40}, {304,30}, {305,38.5},
> {306,47}, {307,37}, {308,42}, {309,40}, {310,43},
> {311,40.5}, {312,40.5}, {313,40.5}, {314,38}}
>
> btw, these are millitary times, so it goes 959,1000....1059,1100...
> etc because there are only 60 minutes in an hour. there would be
> no such thing as 1392, because 1:92pm does not exist.
Here are two utility functions.
The first converts military time to minutes.
The second goes the other way.
mil2min[mil_] := Quotient[mil,100]*60 + Mod[mil,100]
min2mil[min_] := Quotient[min,60]*100 + Mod[min,60]
Both functions are Listable.
mil2min@{957,958,959,1000,1001,1002,1003}
min2mil@%
{597, 598, 599, 600, 601, 602, 603}
{957, 958, 959, 1000, 1001, 1002, 1003}
The first step is to convert to minutes. Frem here on we work with xy.
xy = MapAt[mil2min,#,1]&/@listmissing
{{181,44}, {182,46}, {183,40}, {184,30}, {186,47},
{187,37}, {188,42}, {189,40}, {190,43}, {194,38}}
This fills in the missing data by linear interpolation.
I know you asked for averages, but this is easier and
(IMHO) probably more reasonable.
L = Interpolation[xy,InterpolationOrder->1];
{min2mil@#,L@#}& /@ Range[xy[[1,1]],xy[[-1,1]]] /. y_Rational:>N@y
{{301,44}, {302,46}, {303,40}, {304,30}, {305,38.5},
{306,47}, {307,37}, {308,42}, {309,40}, {310,43},
{311,41.75}, {312,40.5}, {313,39.25}, {314,38}}
Inserting averages is tricky because the function produced
by Interpolation does not follow the documentation when
InterpolationOrder->0 is specified: given the first x-value,
it returns the second y-value instead of the first.
f = Interpolation[xy,InterpolationOrder->0];
p = Prepend[ Rest[f/@Range@@xy[[{1,-1},1]]],xy[[1,2]]];
g = Interpolation[Reverse[{-1,1}#&/@xy],InterpolationOrder->0];
q = Append[Most[Reverse[g/@Range@@-xy[[{-1,1},1]]]],xy[[-1,2]]];
Transpose@{min2mil[Range@@xy[[{1,-1},1]]],(p+q)/2} /. y_Rational:>N@y
% == listfull
{{301,44}, {302,46}, {303,40}, {304,30}, {305,38.5},
{306,47}, {307,37}, {308,42}, {309,40}, {310,43},
{311,40.5}, {312,40.5}, {313,40.5}, {314,38}}
True
>
> this is another example of how the data looks
> {{957,6}, {958,4}, {959,5}, {1000,5}, {1001,4}, {1002,2}, {1003,3}}