Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Replacing missing data by regressed values

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Replacing missing data by regressed values

Jul 8, 2016 7:39 AM
(1969 views)

Hi

I have a time series for which the variable has missing values. I would like to replace the missing values by a linear model derived from the closest available values in time.

What would be the best way?

And if the script could do that to all the columns of my table (except the time), that would be even better

Thanks

Example

What I have

t | Output |
---|---|

0 | 10 |

1 | . |

2 | . |

4 | 30 |

10 | . |

20 | 10 |

What I want:

t | Output |
---|---|

0 | 10 |

1 | 15 |

2 | 20 |

4 | 30 |

10 | 22.5 |

20 | 10 |

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Replacing missing data by regressed values

try this for starters

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Replacing missing data by regressed values

Thanks Ron but I don’t see anything in this that does interpolation?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Replacing missing data by regressed values

here is a script that will do the task

**Names Default To Here( 1 );**

**dt = Current Data Table();**

**For( rows = 1, rows <= N Rows( dt ), rows++,**

** If( Rows == 1, counter = 0 );**

** If( Is Missing( dt:output[rows] ) == 1,**

** counter = counter + 1,**

** If( counter > 0,**

** incr = (dt:output[Rows] - dt:output[Rows - counter - 1]) / (counter + 1);**

** For( i = Rows - counter, i <= Rows - 1, i++,**

** dt:output = dt:output[i - 1] + incr**

** );**

** counter = 0;**

** )**

** );**

**);**

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Replacing missing data by regressed values

I had a similar problem some years ago and wrote a script to do imputation by "local regression".

Here's an updated version:

dt = Current Data Table**()**;

n = N Rows**(**dt**)**;

x = Column**(****1****)**;// Column("t")

For**(**i = **2**, i <= N Col**(**dt**)**, i++,

y = Column**(**i**)**;

For Each Row**(**

If**(****1** < Row**()** < n & Is Missing**(**y**[])**,

// find previous non-missing row

Eval**(**Eval Expr**(**r1 = Max**(**dt << **get rows where****(**!Is Missing**(**y**[])** & Row**()** < Expr**(**Row**())))))**;

// find next non-missing row

Eval**(**Eval Expr**(**r2 = Min**(**dt << **get rows where****(**!Is Missing**(**y**[])** & Row**()** > Expr**(**Row**())))))**;

y**[]** = y**[**r1**]** + **(**y**[**r2**]** - y**[**r1**])** / **(**x**[**r2**]** - x**[**r1**])** * **(**x**[]** - x**[**r1**])**;

**)**

**)**

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Replacing missing data by regressed values

Spot on MS,

Cheers!