Forum Discussion
strachi
8 years agoRegular Visitor
Moving average over non-numeric values (correct errors, fill missing values)
Hi, how can I smooth string values in a column? I have time series data (timestamp; string) with some errors or gaps in it: timestamp;string 1521585642;a 1521585643;a 1521585644; 1521585...
strachi
8 years agoRegular Visitor
Hi,
how can I smooth string values in a column?
I have time series data (timestamp; string) with some errors or gaps in it:
timestamp;string
1521585642;a
1521585643;a
1521585644;
1521585645;a
1521585646;a
1521585647;x
1521585648;a
1521585649;a
1521585650;a
I would like to fill the gap and replace the error ("x") with the values in proximity (lets say we want the most frequent value looking at the last 2 and next 2 values). You could call this moving average with strings. The result in this simple example would be all "a" in the string-column.
I feel like this comes close, but MAXA does not work with strings of course:
Smooth =
CALCULATE (
CALCULATE (
MAXA( 'timeseries'[string] );
'timeseries'[Datetime]
>= VALUES ( 'timeseries'[Datetime] ) - 4 ;
'timeseries'[Datetime] <= VALUES ( 'timeseries'[Datetime] )
);
ALLEXCEPT ( 'timeseries'; 'timeseries'[Tag];'timeseries'[Logfile];'timeseries'[Datetime] )
)Any Ideas would be greatly appreciated. I was not able to find a solution.