Hi, i have a Timetable TT1 with a bunch of NaN values on his Data and a second TT2 with the correct values (replacements of NaN's).
How can i replace the Nan value with the correct one at each timestamp?
MATLAB: How can i Replace missing values from Timetable
replacetimetable
Related Solutions
Hi Angelavtc,
You can use unstack to first unstack the "Id" variable into multiple variables (In your case, into 3 variables). The value of each variable is the value of "Price". If there is no "Price" value for that row time and Id combination, a NaN will be filled in. Then call rmmissing to remove rows containing missing value (such row is the row time that doesn't have "Price" value for all Id values). At last, call stack to stack the multiple Id variables into one variable of "Price".
>> tt1 = unstack(T,'Price','Id',"VariableNamingRule","preserve")tt1 = 3×3 timetable Time 1 2 3 ___________ __ ___ ___ 13-Apr-2018 30 21 8 25-Apr-2018 29 24 NaN 28-Apr-2018 45 NaN NaN>> tt2 = rmmissing(tt1)tt2 = 1×3 timetable Time 1 2 3 ___________ __ __ _ 13-Apr-2018 30 21 8>> T_1 = stack(tt2,[1 2 3],"IndexVariableName",'Id',"NewDataVariableName",'Price')T_1 = 3×2 timetable Time Id Price ___________ __ _____ 13-Apr-2018 1 30 13-Apr-2018 2 21 13-Apr-2018 3 8 If you have R2014b or later, use tables and datetimes. For the specific question you've asked, simplest to use ismember and indexing:
>> Date = datetime({'01/01/2015';'01/14/2015';'05/30/2015'});>> Value = [10; 5; 10];>> TSomeDays = table(Date,Value)TSomeDays = Date Value ___________ _____ 01-Jan-2015 10 14-Jan-2015 5 30-May-2015 10 >> Date = datetime(2015,1,(1:180)');>> Value = zeros(size(Date));>> TAllDays = table(Date,Value);>> [~,i] = ismember(TSomeDays.Date,TAllDays.Date)i = 1 14 150>> TAllDays.Value(i) = TSomeDays.ValueTAllDays = Date Value ___________ _____ 01-Jan-2015 10 02-Jan-2015 0 [snip] 13-Jan-2015 0 14-Jan-2015 5 15-Jan-2015 0 [snip] 29-May-2015 0 30-May-2015 10 31-May-2015 0 [snip]>> Date = datetime(2015,1,(1:365)');>> TAllDays = table(Date);Another possibility is to use an outer join, which may be too heavy of a hammer for this specific question, but which provides capabilities that you may yet need. outerjoin fills missing values with NaN, but you can put zeros in their place:
>> TAllDays = outerjoin(TAllDays,TSomeDays,'Key','Date','MergeKeys',true)TAllDays = 365×2 table Date Value ___________ _____ 01-Jan-2015 10 02-Jan-2015 NaN 03-Jan-2015 NaN [snip] 13-Jan-2015 NaN 14-Jan-2015 5 15-Jan-2015 NaN [snip] 29-May-2015 NaN 30-May-2015 10 31-May-2015 NaN [snip]>> TAllDays.Value(isnan(TAllDays.Value)) = 0TAllDays = Date Value ___________ _____ 01-Jan-2015 10 02-Jan-2015 0 [snip] 13-Jan-2015 0 14-Jan-2015 5 15-Jan-2015 0 [snip] 29-May-2015 0 30-May-2015 10 31-May-2015 0 [snip]If you have R2016b, this is trivial using a timetable:
>> Date = datetime(2015,1,(1:365)');>> TTAllDays = retime(TTSomeDays,Date,'FillWithConstant','Constant',0)TTAllDays = Date Value ___________ _____ 01-Jan-2015 10 02-Jan-2015 0 [snip] 13-Jan-2015 0 14-Jan-2015 5 15-Jan-2015 0 [snip] 29-May-2015 0 30-May-2015 10 31-May-2015 0 [snip]
Best Answer