constructing a table from a particular data set

1 次查看（过去 30 天）
ektor
ektor 2023-7-13
回答： Peter Perkins 2023-7-17
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

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

采纳的回答

Sharad
Sharad 2023-7-13
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
  1 个评论
ektor
ektor 2023-7-13
编辑：ektor 2023-7-13
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

请先登录，再进行评论。

更多回答（1 个）

Peter Perkins
Peter Perkins 2023-7-17
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.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by