# Average product of arrays

1 view (last 30 days)
Laxmi Bhatta on 10 Mar 2023
Commented: Laxmi Bhatta on 12 Mar 2023
Hi
I have an excel sheet having following informations;
flows occupancy
{6,18,17,14} {0.0383,0.13,0.1794,0.1522}
{12,14,12,5} {0.0717,0.0978,0.1117,0.0633}
{16,11,13,12} {0.1006,0.08,0.0978,0.1839}
In one column there is flows {x1,x2,x3,x4} and another column there is occupancy {y1,y2,y3,y4}
I need to make Matlab code as follows
averageproduct = average (x1*y1+x2*y2+x3*y3+x4*y4)
the number of rows are about more than 15000
Could you help me in this matter
##### 2 CommentsShow 1 older commentHide 1 older comment
Stephen23 on 11 Mar 2023
Edited: Stephen23 on 11 Mar 2023
"I have an excel sheet ..."
What you uploaded is a CSV file. A CSV file is a text file that exists completely independently from MS Excel.
"In one column there is flows {x1,x2,x3,x4} and another column there is occupancy {y1,y2,y3,y4}"
Note that the CSV file columns FLOWS and OCCUPANCY do not have constant four terms each, e.g. from row 944 the number of terms increases from four to five, then from row 1896 it decreases back to four again. So your solution needs to take this into account (e.g. as my answer does).
For example, the term distribution for FLOWS is as follows:
V = cellfun(@(t)numel(sscanf(strrep(t,'{',''),'%f,')),T.flows);
plot(V,'*r')
ax = gca;
ax.XAxis.Exponent = 0;
xlabel('Row Number')
ylabel('Number of Terms')

Stephen23 on 11 Mar 2023
Edited: Stephen23 on 11 Mar 2023
T = 25866×15 table
fk_pems_id postmile unixtime flows occupancy Var6 Var7 Var8 Var9 Var10 Var11 Var12 Var13 Var14 Var15 __________ ________ __________ _________________ _________________________________ ____ ____ ____ ___________ _____ _____ _____ ______ __________ ______ 4.0004e+05 26 1.2025e+09 {'{6,18,17,14}' } {'{0.0383,0.13,0.1794,0.1522}' } NaN NaN NaN {'#VALUE!'} NaN NaN NaN 1.0341 0.00064256 2.3132 4.0004e+05 26 1.2025e+09 {'{12,14,12,5}' } {'{0.0717,0.0978,0.1117,0.0633}'} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {'{16,11,13,12}'} {'{0.1006,0.08,0.0978,0.1839}' } NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {'{8,11,9,8}' } {'{0.0467,0.085,0.1244,0.0572}' } NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {'{8,11,9,8}' } {'{0.0467,0.085,0.1244,0.0572}' } NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {'{10,17,17,11}'} {'{0.0639,0.1206,0.1333,0.1228}'} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {'{8,12,8,6}' } {'{0.0517,0.0828,0.0906,0.0722}'} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {'{12,13,8,12}' } {'{0.0761,0.0894,0.1294,0.1939}'} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{5,12,7,7}' } {'{0.0289,0.0778,0.0472,0.0806}'} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{4,11,8,7}' } {'{0.0256,0.0722,0.1133,0.1122}'} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{9,10,10,6}' } {'{0.0522,0.0644,0.07,0.0933}' } NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{17,16,9,16}' } {'{0.1089,0.1117,0.1133,0.1689}'} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{8,12,6,11}' } {'{0.0489,0.0861,0.1006,0.1078}'} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{13,17,8,10}' } {'{0.0772,0.1111,0.0811,0.1056}'} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{5,8,4,6}' } {'{0.0311,0.0483,0.025,0.0622}' } NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {'{12,11,11,9}' } {'{0.0761,0.0717,0.1039,0.1339}'} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0
F = @(c)cellfun(@(t) sscanf(strrep(t,'{',''),'%f,'), c,'uni',0);
T = convertvars(T,{'flows','occupancy'}, F) % convert text to numeric
T = 25866×15 table
fk_pems_id postmile unixtime flows occupancy Var6 Var7 Var8 Var9 Var10 Var11 Var12 Var13 Var14 Var15 __________ ________ __________ ____________ ____________ ____ ____ ____ ___________ _____ _____ _____ ______ __________ ______ 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {'#VALUE!'} NaN NaN NaN 1.0341 0.00064256 2.3132 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN 30 NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0 4.0004e+05 26 1.2025e+09 {4×1 double} {4×1 double} NaN NaN NaN {0×0 char } NaN NaN NaN NaN 0 0
A = @(x,y) sum(x.*y); % "average product"
V = rowfun(A, T, 'InputVariables',{'flows','occupancy'}, 'ExtractCellContents',true, 'OutputFormat','uniform')
V = 25866×1
7.7504 3.8865 5.9678 2.8858 2.8858 6.3061 2.5652 5.4374 1.9727 2.5884
Laxmi Bhatta on 12 Mar 2023
Thanks a lot
It really works cheers

Shubham on 10 Mar 2023
Hi Laxmi,
Here's a sample code in MATLAB that should achieve what you're looking for:
% Define the data as arrays
flows = {[6,18,17,14]; [12,14,12,5]; [16,11,13,12]};
occupancy = {[0.0383,0.13,0.1794,0.1522]; [0.0717,0.0978,0.1117,0.0633]; [0.1006,0.08,0.0978,0.1839]};
% Convert the cell arrays to matrices
flows_mat = cell2mat(flows);
occupancy_mat = cell2mat(occupancy);
% Calculate the average product using element-wise multiplication and sum
averageproduct = sum(flows_mat .* occupancy_mat, 2) / size(flows_mat, 2);
% Display the result
disp(averageproduct);
This code should work for any number of rows in your data. The code first converts the cell arrays to matrices using the cell2mat function, then calculates the average product using element-wise multiplication and the sum function. Finally, it divides the sum by the number of columns in the data to get the average.
Hope this helps!
Laxmi Bhatta on 11 Mar 2023
flows=data{:,4};
occupancy=data{:,5};
averageproduct = mean(flows .* occupancy, 1);
speed = averageproduct*3600/1609.34;
Time = data{:,3};
Space= data{:,2};
y = Space;
x = Time;
[X,Y] = meshgrid(x,y);
Z = speed
figure;
heatmap(X,Y,Z,'Colormap',jet);
xlabel('Time (unix)');
ylabel('Postmile)');
title('Vehicle Speed Heatmap');
colorbar;
Undefined operator '.*' for input arguments of type 'cell'. I got this error while coding
I have also uploaded the csv data
Afetr slightly modifying the data
again have got the error
flows=data{:,4};
occupancy=data{:,5};
flows_mat = cell2mat(flows);
occupancy_mat = cell2mat(occupancy);
averageproduct = mean(flows_mat .* occupancy_mat, 2);
speed = averageproduct*3600/1609.34;
Time = data{:,3};
Space= data{:,2};
y = Space;
x = Time;
[X,Y] = meshgrid(x,y);
Z = speed
figure;
heatmap(X,Y,Z,'Colormap',jet);
xlabel('Time (unix)');
ylabel('Postmile)');
title('Vehicle Speed Heatmap');
colorbar;
Error using cat
Dimensions of arrays being concatenated are not consistent.
Error in cell2mat (line 83)
m{n} = cat(1,c{:,n});