averaging and interpolating random number of rows of data

Question

Bryan Tan 2020-9-29

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/601879-averaging-and-interpolating-random-number-of-rows-of-data

编辑： Bryan Tan 2020-10-1

plot temperature.xlsx

Hello, I have a list 800 particles data in an excel file. A total of 174755 rows...

Is there a way to extract these values and then average them based on their time point, later interpolate them based on the time point. I would like to average the first row of every particle 1,2,3....800. So after NaN, it will consider that as row 1,2,3...last number before NaN, then another row 1 again.

For example:

row 1 1 5

row 2 2 6

row 3 3 7

NaN

row 1 4 8

row 2 5 9

row 3 6 10

row 4 10 11

NaN

row 1 7 1

row 2 8 2

row 3 9 3

So the desired output average should be:

average of row 1 4 4.67

average of row 2 5 5.75

average of row 3 6 6.67

average of row 4 10 11

filename = 'plottemperature.xlsx';
T = xlsread(filename); %read excel file
[rows,columns] = size(T); %List the number of rows and columns
nanLocations = isnan(T); %declare all NaN locations in the excel file
for row = 1: rows
    for col = 1:columns
        if ~nanLocations(row,col) %I wanted to write it so that after the 3rd NaN it will be another row 1
%I am not sure how to continue from here to set the rows after every NaN as row 1 and then average all row 1

Please guide me. Thank you very much!

4 个评论
显示 2更早的评论隐藏 2更早的评论

Bryan Tan 2020-9-29

编辑：Bryan Tan 2020-9-29

在 MATLAB Online 中打开

Okay I used xlsread() to read my spreadsheet to remove all the variables and only numbers. So those cells that were initially 'particle 1 or 2...' are labelled as NaN.

For now how do I average the first value after every NaN, then 2nd value after NaN and so on? Is there a way to do that?

eg:

I have 3 rows of NaN, but I have numbers after that. So after every 3rd NaN, I want to make it as row 1, so I can average each row 1.

( the first data has no NaN before it):

1

2

3

NaN

4

5

6

10

NaN

7

8

9

So the output average should be:

4

5

6

10

Thank you.

filename = 'plottemperature.xlsx';
T = xlsread(filename); %read excel file
[rows,columns] = size(T); %List the number of rows and columns
nanLocations = isnan(T); %declare all NaN locations in the excel file
for row = 1: rows
    for col = 1:columns
        if ~nanLocations(row,col) %I wanted to write it so that after the 3rd NaN it will be another row 1
%I am not sure how to continue from here to set the rows after every NaN as row 1 and then average all row 1

Adam Danz 2020-9-29

Unless you're using an old version of matlab, you sould not use xlsread. Instead, use one of the other functions recommended in the documentation. See the top of this page.

https://www.mathworks.com/help/matlab/ref/xlsread.html

I didn't follow your averaging example. I can't imaging what would produce an avg of 4, for example.

Bryan Tan 2020-9-30

Alright I had downloaded the latest matlab and I can use read table code now. Thanks.

In the simplified example i gave, the average have 4 rows because some of the data have 4 rows, some have 3. So it will be:

(1+4+7)/3 (The average of all the first rows)

(2+5+8)/3 (The average of all the second rows)

(3+6+9)/3 (The average of all the third rows)

10/1 (Since there is only 1 forth row, its just 10)

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2020-9-29

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/601879-averaging-and-interpolating-random-number-of-rows-of-data#answer_502546

在 MATLAB Online 中打开

Yes, try this (untested):

badRows = isnan(T{:, 1}); % Find nans in column 1.
T = T(~badRows, :); % Extract all rows except the bad rows.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Image Analyst 2020-9-30

Not sure I understand your new question. Please give an example input table and output table.

Bryan Tan 2020-9-30

编辑：Bryan Tan 2020-9-30

在 MATLAB Online 中打开

Sorry for the bad explanation, I will create a table:

Column 1(Time) Column 2 (Temperature)

row 1 0 20

row 2 1 25

row 3 2 28

row 1 0 23 Because column 1 is '0' again, this will be row 1 again. row 2 1.1 24

row 3 2.1 27

row 4 3.1 32 Some data have more rows than others

Desired Output

Column 1(Time) Column 2 (Temperature)

Average of all row 1s 0 21.5

Average of all row 2s 1.05 24.5

Average of all row 3s 2.05 27.5

average of all row 4s 3.1 32

I hope this is clearer now. Currently, I am up until here, I was playing around with mean codes but could not figure out.

filename = 'plottemperature.xlsx'; %declare file name
T = readtable(filename); %read the file
[r, c] = size(T); %display number of rows and columns
%ca=cell(rows,columns)%crete array of rows and columns
badRows = isnan(T{:, 1}); % Find NaNs in column 1
T = T(~badRows, :); %Extract all rows except the bad rows
Tm=T{:,:}; %convert table to matrix
a=0; 
if Tm(:,1)==0
    %I am thinking when column 1 = 0 then move the rows below it over to 2 new columns. 
    %But it is not ideal because I will need to check every new columns for 0 to repeat the moving of the rows over again.
    %Once every numbers are in their new columns then I will average every even columns and odd columns based on their rows.
end

Thank you!!

请先登录，再进行评论。

averaging and interpolating random number of rows of data

4 个评论
显示 2更早的评论隐藏 2更早的评论

回答（1 个）

3 个评论
显示 1更早的评论隐藏 1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

averaging and interpolating random number of rows of data

4 个评论 显示 2更早的评论隐藏 2更早的评论

回答（1 个）

3 个评论 显示 1更早的评论隐藏 1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

4 个评论
显示 2更早的评论隐藏 2更早的评论

3 个评论
显示 1更早的评论隐藏 1更早的评论