Average based on multiple conditions

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.

[Mudasser Seraj]

Someone please help me with this.

[Walter Roberson]

I looked through the data. There are no missing data points, at least not in any obvious form. There are a lot of entries that are suspiciously exactly 27.5, and a bunch that are exactly 25, and there is a section that is exactly 8. We might tend to suspect that 27.5 in particular might flag "missing data", but it is difficult to say.

The position data does not make sense given the velocity data. For example there is a section for the last car:

27.5	-355.2539571
27.5	-352.4986362
27.5	-349.7435865
27.5	-346.9887382
27.5	-344.2341473

The differences in position are

          2.75532089999996
          2.75504970000003
          2.75484829999999
          2.75459089999998

which are far less than the 27.5 velocity would indicate. It looks almost as if the velocity is 10 times too large -- but if so then you would expect exactly 2.75 difference between entries, not just approximately that. Unless, that is, the 27.5 is the approximation.

Though perhaps "cars at that position" is talking about the values such as -352.4986362 ? But if so then there are no entries in which the positions of the cars exactly match the target positions from sheet2, all of which are nice round numbers. You will need to explain further how the matching to position is to be done. All cars past that position? All cars within a certain range of that position??

[Mudasser Seraj]

27.5 / 25/ 8 m/s are velocities at certain time stamp. There are 10 time stamps at each second. so, positions are updated 10 times in each second with . The change is due to acceleration/deceleration. I hope that answers your questions.

The lowest starting postion (which in this case is -400 m for car 10) can be counted as starting point or 0 m and other starting point can be adjusted accordingly.

For velocity calculation, at a specific time stamp (such 130), and for a specific location (such as 1100 m) any vehicle within the range of 1097and 1103 will be considered for calculating average velocity. 3m is considered to be the maxium distance a car could go within a time stamp.

[Mohammad Sami]

You can use the function interp2 to get the time, position you need.

%X = ActualTimestamp
%Y = ActualPosition
%V = ActualVelocity
%Xq = specifictimestamps
%Yq = specificpositions
Vq = interp2(X,Y,V,Xq,Yq);

[Mudasser Seraj]

Didn't work. Showing error.

[Mudasser Seraj]

Someone please help me with this issue.

Answer 1

[Walter Roberson]

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.

[Mudasser Seraj]

Can you kindly write the comand to contruct bin edges with spacing 2*3 =6?

And how to determine which bing edges from this correspond to the target position?

I would really appreciate if you could kindly write the commands for the steps you mentioned? It seems very complicated for me due to lack of my knowledge.

[Walter Roberson]

Now that I think about it, spacing 6 would not work out because your target positions are not set a multiple of 6 apart.

What you could do is to construct the vector

sort([-inf, target_positions-3, target_positions+3,inf])

Then every second bin should correspond to one of the target positions and the remaining bins would correspond to everything in between.

[Mudasser Seraj]

I don't understand. So far, I have extracted the position and velocity vectors on target timestamps (e.g. 1,51,101....) for 20 cars.

What functions to use from here on? Please help.

[Mudasser Seraj]

Any response? Please!

[Walter Roberson]

• 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]

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