Average product of arrays

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

[Laxmi Bhatta]

% 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]};

I have 25000 data and how would I type such a huge data, could you please make this defination that can be valid for all.

[Stephen23]

"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:

T = readtable('pems_rawdata_NB.csv');
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')

Answer 1

[Stephen23]

T = readtable('pems_rawdata_NB.csv')

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]

Thanks a lot

It really works cheers

Answer 2

[Shubham]

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]

Hi Thanks for the details

If we have to import from csvfile and in csv file, flows is in column D and occupancy is in column E then could you please make code for me. the data in row is up to 25867 number.

Thanks

[Shubham]

Yes, you can modify the previous code to read data from a CSV file using the readtable function. Here is an updated version of the code:

% read data from CSV file
data = readtable('filename.csv');

% extract flows and occupancy from the table
flows = data{:, 'D'};
occupancy = data{:, 'E'};

% calculate the average product
averageproduct = mean(flows .* occupancy);

In this code, you should replace 'filename.csv' with the actual name of your CSV file. The readtable function creates a table from the CSV file, and then the {:,'D'} and {:,'E'} indexing expressions extract the data from the 'D' and 'E' columns, respectively. The rest of the code is the same as before - it calculates the average product using element-wise multiplication and the mean function.

[Laxmi Bhatta]

>> data=readtable('C:\Users\z5266795\pems_rawdata_NB.csv');

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

data=readtable('C:\Users\z5266795\pems_rawdata_NB.csv');

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});