Average based on multiple conditions

2 次查看（过去 30 天）
Mudasser Seraj
Mudasser Seraj 2020-3-12
评论： Mudasser Seraj 2020-3-16
Hi,
I have the following dataset in excel file.
In sheet 1,
Column 1= time stamp, Column 2 =velocity of car 1, Column 3= position of car 1, Column 4 = velocity of car 2, Column 5 = position of car 2, .........
In sheet 2,
Column 1 = specific time stamps, Row 1 = specific positions 1
I want to fill the table with average velocity from all cars at that specific time stamps and at that position.
Interpolation for velocity and position is acceptable.
If certain car does not have values for certain position, only the average of the available data points could be taken.
Please help me solving the problem. I really appreciate your help. Thank you.
  6 个评论
显示 4更早的评论
Mudasser Seraj
Mudasser Seraj 2020-3-14
Someone please help me with this issue.

请先登录，再进行评论。

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

回答（1 个）

Walter Roberson
Walter Roberson 2020-3-14
Extract the target timestamp data and target position data from sheet2.
Extract the timestamp information from sheet1.
Determine which rows of the sheet1 timestamps correspond to the target timestamps. I will call this TR.
Extract all of the position data for rows TR. Find the minimum of it all, and subtract that from the position data.
Construct bin edges with spacing 2*3=6.
Determine which bin edges from this correspond to the target positions. I will call this TP.
Now, discretize() the position data for rows TR with the above bin edges, getting out bin numbers.
Extract the velocity data for rows TR.
For each row in TR, accumarray() passing in the bin numbers for the row in the first position, and the velocities for the row in the second position, and size [] and function @mean, and default value nan, and set the sparse flag.
From that accumarray information, extract the rows corresponding to TP, and construct an appropriate output row for it, with the current timestamp in one column, and the target positions in the remaining columns, with the information extracted from accumarray as the data for those.
Loop back for the next row in TR.
  6 个评论
显示 4更早的评论
Walter Roberson
Walter Roberson 2020-3-16
  • I am assisting multiple people, some of whom need many hours of investigation
  • you have not posted any code attempts at all, so you are not invested in solving the problem, only in getting the code
  • the question is obviously an assignment, so you need to be actively working on it, not expecting others to give you the code
  • I already wrote a full outline of how to proceed
  • volunteers have to rest too
Mudasser Seraj
Mudasser Seraj 2020-3-16
This is what I've done so far
%raw data file
excelfile = '1000_0.xlsx';
% reads velocity data from raw data
Velocity = zeros(9001,20);
range1 = 'M2:M9002';
for i = 2 : 11
Velocity(:,i-1) = xlsread(excelfile,i,range1);
end
for i = 13 : 22
Velocity(:,i-2) = xlsread(excelfile,i,range1);
end
%reads position data from raw data
Position = zeros(9001,20);
range2 = 'N2:N9002';
for i = 2 : 11
Position(:,i-1) = xlsread(excelfile,i,range2);
end
for i = 13 : 22
Position(:,i-2) = xlsread(excelfile,i,range2);
end
% selects position and velocity at required timesteps
for i = 0:180
req_position (i+1,:)= Position(50*i+1,:);
end
subs =min(min(req_position));
req_position = req_position - subs;
for i = 0:180
req_velocity (i+1,:)= Velocity(50*i+1,:);
end

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Large Files and Big Data 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by