constructing a table from a particular data set

Dear all,

I have the attach data set and the goal is to construct a table also attached but I do not know if matlab can do such data analysis.

I would be grateful if you could give me some guidance,

Many thanks in advance

Answer 1

[Sharad]

Hi,

As per my understanding, you are interested in organizing the data present in the excel sheet and analyzing it as shown in the pdf.

In order to do that, you can follow these steps.

• Read the excel sheet with the readtable function.

data = readtable('worksheet.xlsx');

• Create data group ranges for your rows.

dwtGroupRanges = [120000, 159999; 160000, 174999];

• Create logical indices for each Dwt group.

group1Idx = data.Dwt >= dwtGroupRanges(1, 1) & data.Dwt <= dwtGroupRanges(1, 2);
group2Idx = data.Dwt >= dwtGroupRanges(2, 1) & data.Dwt <= dwtGroupRanges(2, 2);

• Filter the data for each dwt group.

group1Data = data(group1Idx, :);
group2Data = data(group2Idx, :);

• Calculate the counts for each group and time period as you want.

totalCount = height(data);
countLast5Years = sum(data.YearOrderPlaced >= (2023 - 5));
countLast6to10Years = sum(data.YearOrderPlaced >= (2023 - 10) & data.YearOrderPlaced <= (2023 - 6));

• Assign the row names and column names as required.

Here are some documentation links that you might want to follow.

readtable : https://in.mathworks.com/help/matlab/ref/readtable.html

logical indexing : https://in.mathworks.com/company/newsletters/articles/matrix-indexing-in-matlab.html

Thank you

[ektor]

Dear Sharad,

Thank you so much. It is very very helpful. Yes, it is for the excel.

I made some additional coding. For example to create the first row I did the following

totalCount =  height(group1Data);
sumtotalCount=sum(totalCount); % in this way I obtain the cell "Total" of the first row

The problem is how to obtain the cell "<5yrs" of the first row that shows that the equipment is in service less than 5 years. The goal is to select from "group1Data" those equipments for which the difference between "year bulilt" and 2023 is less that 5.Is there a way to do that?

Maybe something like that?

countlessthan5Years =  Data.YearBuilt((group1Idx)<2023-5)
sum(countlessthan5Years) % in this way I obtain the cell "<5yrs" of the first row?

Many thanks in advance

Answer 2

[Peter Perkins]

You almost certainly do not want to do all the calculation "by hand" as Sharad's answer shows.

Import into a timetable, then use groupsummary. If you need to, you can use unstack to string the summaries out horizontally.