"Please help"
Please upload a data file by clicking the paperclip button. We cannot import data from a screenshot.
EXCEL TABLE - INDEXING MULTIPLE TABLES AT ONCE
8 views (last 30 days)
Show older comments
For a project that i am working on, I have to deal with a set of 36 superheated steam tables in one of 3 sheets in an excel file. Since the tables are organized as a set of 5 columns (T,V,U,H,S) with a Pressure "title", but Pressure is a variable I need to be able to read, I want to go about converting the pressure into the first column (unless there is an easier way) of each table where for the length of that table (27 rows) the pressure is the same even as the other variables change (like a matrix for each table and cell array for all 36). However when I did that I faced a number of issues:
- for any table after table 1, The rows are slightly off (the number of rows of data cuts of early and there are extra NaN rows on top)
- The pressure isn't changing for each table. It is staying as 0.01 for all tables instead of just the first table.
Here is a bit of what my table should like (as well as my original code): Please help in giving any advice to fix my code
here is my output for Table 2:
% Read the superheated data
superheatedData = readtable('steam_tables.xlsx', 'Sheet', 'Superheated_Steam_Tables', 'ReadVariableNames', false);
% Initialize cell array to store tables
superheatedTables = {};
% Set up the number of rows and columns per table
rowsPerTable = 27;
columnsPerTable = 6; % T, V, U, H, S, and Pressure (P) as placeholders
% Start processing each table
tableIndex = 1;
rowIndex = 1;
while rowIndex <= height(superheatedData)
% Check if we have enough rows left to form a table
if rowIndex + rowsPerTable - 1 <= height(superheatedData)
% Extract the relevant rows for the current table
currentTableData = superheatedData(rowIndex:rowIndex + rowsPerTable - 1, :);
% Extract temperature data from the first column (T)
T = currentTableData{:, 1};
% Extract other variables (V, U, H, S) from columns 2-5
V = currentTableData{:, 2};
U = currentTableData{:, 3};
H = currentTableData{:, 4};
S = currentTableData{:, 5};
% Set Pressure (P) to a constant value for this table (e.g., 0.01 MPa)
P = repmat(0.01, rowsPerTable, 1); % P constant across all rows for the current table
% Create a matrix for the current table with all variables
currentTable = [T, V, U, H, S, P];
% Store the current table in the cell array
superheatedTables{tableIndex} = currentTable;
% Move to the next set of rows for the next table
rowIndex = rowIndex + rowsPerTable;
tableIndex = tableIndex + 1;
else
% If there are not enough rows left to form a table, stop processing
disp('Insufficient rows to process the next table. Check the data.');
break;
end
end
% Display the first table for checking
format long g
disp(superheatedTables{2});
5 Comments
Accepted Answer
Stephen23
on 6 Dec 2024
Edited: Stephen23
on 17 Dec 2024
Here is one approach using READCELL.
It assumes that all data blocks are aligned, and then within each block:
- the 3rd cell of the 1st row contains the pressure value,
- the 2nd row contains the header text
- the 3rd row until the end contain numeric data (non-numeric are replaced with NaN).
It automatically identifies how many blocks there are and also their locations:
raw = readcell('steam_tables.xlsx', 'Sheet', 'Superheated_Steam_Tables')
% Identify data block locations:
[idr,idc] = find(cellfun(@isnumeric,raw)|cellfun(@ischar,raw));
idr = unique(idr);
idc = unique(idc);
igr = cumsum([1;diff(idr)~=1]);
igc = cumsum([1;diff(idc)~=1]);
ibr = accumarray(igr,idr,[],@min);
ier = accumarray(igr,idr,[],@max);
ibc = accumarray(igc,idc,[],@min);
iec = accumarray(igc,idc,[],@max);
% Extract data from blocks:
out = cell(igr(end),igc(end));
for kr = 1:igr(end)
for kc = 1:igc(end)
tmp = raw(ibr(kr):ier(kr),ibc(kc):iec(kc)); % one block
mpa = tmp{1,3};
hdr = tmp(2,:);
rpl = ~cellfun(@isnumeric,tmp);
rpl(1:2,:) = false;
tmp(rpl) = {NaN};
tbl = cell2table(tmp(3:end,:),'VariableNames',hdr);
tbl{:,'P'} = mpa;
out{kr,kc} = tbl;
end
end
Checking:
out{:}
More Answers (1)
dpb
on 7 Dec 2024
Moved: dpb
on 8 Dec 2024
@Stephen23's finding the subtable locations is very clever indeed; I looked at it some and didn't see the trick after reading the cell array...
But, a slight modification might make use simpler; concatenate it all into one table rather than having to reference into a whole mess of separate ones. I didn't explore trying to create the ND mesh for ND interpolation, not knowing precisely what was going to be the final need.
raw = readcell('steam_tables.xlsx', 'Sheet', 'Superheated_Steam_Tables');
% Identify data block locations:
[idr,idc] = find(cellfun(@isnumeric,raw)|cellfun(@ischar,raw));
idr = unique(idr);
idc = unique(idc);
igr = cumsum([1;diff(idr)~=1]);
igc = cumsum([1;diff(idc)~=1]);
ibr = accumarray(igr,idr,[],@min);
ier = accumarray(igr,idr,[],@max);
ibc = accumarray(igc,idc,[],@min);
iec = accumarray(igc,idc,[],@max);
% Extract data from blocks:
%out = cell(igr(end),igc(end));
tSteam=[];
for kr = 1:igr(end)
for kc = 1:igc(end)
tmp = raw(ibr(kr):ier(kr),ibc(kc):iec(kc)); % one block
mpa = tmp{1,3};
hdr = tmp(2,:);
rpl = ~cellfun(@isnumeric,tmp);
rpl(1:2,:) = false;
tmp(rpl) = {NaN};
tbl = cell2table(tmp(3:end,:),'VariableNames',hdr);
isNan=all(isnan(tbl{:,:}),2);
tbl(isNan,:)=[];
tbl=addvars(tbl,mpa*ones(height(tbl),1),'NewVariableNames',{'P'},'Before','T(°C)');
tSteam=[tSteam;tbl];
end
end
[head(tSteam);tail(tSteam)]
% make more easily-used variable names
tSteam.Properties.VariableNames(2:end)=extractBefore(tSteam.Properties.VariableNames(2:end),'(');
% useful lookup/interpolation formulae
SI_V=scatteredInterpolant(tSteam.P,tSteam.T,tSteam.V); % create the various interpolants
SI_U=scatteredInterpolant(tSteam.P,tSteam.T,tSteam.U);
SI_H=scatteredInterpolant(tSteam.P,tSteam.T,tSteam.H);
V_PT=@(p,t)SI_V(p,t); % and functions to use them
H_PT=@(p,t)SI_H(p,t);
format bank, format compact % convenient for these sized numbers
F2C=@(f)(f-32)/1.8;
PSI2MPa=@(psi)psi*0.00689475728;
H_PT(PSI2MPa(2215),F2C(645)) % enthalpy at reactor exit of pressurized water reactor
Looks reasonable, I didn't check the exact saturation properties for comparison; those aren't exactly saturated conditions, but about where operating conditions are that I remember from a prior life...in those days of yore, we still used English units for thermodynamic calculations and the reactor dimensions, hence the conversions, I don't remember the numbers in other units...the fuel assembly pitch was 8.57" and the water volume fraction 0.580307 that I can remember still after 50 years! :)
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
You can also select a web site from the following list
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom(English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)