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}}