Borrar filtros
Borrar filtros

constructing a table from a particular data set

1 visualización (últimos 30 días)
ektor
ektor el 13 de Jul. de 2023
Respondida: Peter Perkins el 17 de Jul. de 2023
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

Respuesta aceptada

Sharad
Sharad el 13 de Jul. de 2023
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.
Thank you
  1 comentario
ektor
ektor el 13 de Jul. de 2023
Editada: ektor el 13 de Jul. de 2023
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

Iniciar sesión para comentar.

Más respuestas (1)

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

Community Treasure Hunt

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

Start Hunting!

Translated by