Find zeros in a large table and replace with average of two previous column values

Hi everyone,

I have a fairly big table that I've imported from excel (11201x136). The first column is essentially a time series in seconds and the remaining 135 columns are different sets of data. For some columns, there is missing data which is pulling as NaN and this is fine as I don't need to use these columns. However, there are other columns where some of the data has not recorded properly and therefore some values are zero. What I am looking to do is find and replace those zero values across each column with an average of the two previous readings but I'm pretty new to matlab and struggling to write the correct code. I can't attach the data I'm afraid as it is confidential.

I've loaded the data as a table as the first column is hh mm ss which doesn't seem to work with xlsread (example below):

Data = readtable('Data.xls', 'sheet', 'Rawdata')

Correcting the zeros is the first thing I need to do but unfortunately it seems more difficult than I anticipated...

Any help is much appreciated,

Answer 1

[KSSV]

You can find the zeros using logical indexing or the functon find. Replace those 0 values with NaN and then use the function fillmissing. Read about fillmissing.

[Charlotte Bell]

Thanks - the replace part is generally the code that I can't seem to get to work. I can find the zeros across the data set using Data.Variables == 0 or find(Data.Variables == 0) however, when I try using Data(Data.Variables==0)=NaN the error "Using '.Variables' with more than one level of subscripting is not supported." is generated. I was also concerned that by replacing the zeros with NaN it might interfere with the other data that I don't need (already filled as NaN)

[Torsten]

idx = Data.Variables ==0

Data.Variables(idx) = NaN

[Charlotte Bell]

Thanks for that - unfortunately I'm still getting the Using '.Variables' with more than one level of subscripting is not supported. Use brace subscripting directly on the table instead. but perhaps I'm still doing something wrong!

[Torsten]

Use

Data.Variables = table2array(Data.Variables)

before indexing.

[Charlotte Bell]

Thanks all, i'm getting somewhere now