How to extract the numerial values out of readtable output?

Question

Leon 2019-6-4

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/465537-how-to-extract-the-numerial-values-out-of-readtable-output

编辑： Arjun 2023-3-13

采纳的回答： Star Strider

AA.xlsx

在 MATLAB Online 中打开

Good afternoon! My apologies for the many questions related to readtable.

Attached is my data file. I use the below command to read the Excel file into Matlab

T = readtable('AA.xlsx');

I know there will be 1-5 columns (unknow amount) that will be scalar strings. They will all be located towards the rightmost side of the table.

Here is my question. How do I get the numerical columns out of the table T. For example, in the above example, I have a total of 48 columns, but only 47 of them are numerical. What I need is

Dat = T(:,[1:47]);

How do I get Dat (numerical values of the table) in a more automatic way, because the 47 number could vary from file to file.

Many thanks!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Star Strider 2019-6-4

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/465537-how-to-extract-the-numerial-values-out-of-readtable-output#answer_377891

在 MATLAB Online 中打开

It is possible, although not without some serious conniptions.

Originally:

AA_Table = readtable('AA.xlsx');
headers = AA_Table.Properties.VariableNames;

then:

AA_C = table2cell(AA_Table);                                    % Convert To Cell Array
NumIdx = cellfun(@isnumeric,AA_C);                              % Determine Numeric Values
Vrbls = headers(NumIdx(1,:));                                   % Get Corresponding Headers (If Needed Later)
NrsC = reshape(AA_C(NumIdx), size(AA_C,1), []);                 % Reshape Vector To Matrix
Nrs = cell2mat(NrsC);                                           % Numeric Array (Finally)

The only works if specific columns are all numeric and other columns are all not numeric. It willl fail otherwise. For what it’s worth, it works here.

4 个评论
显示 2更早的评论隐藏 2更早的评论

Nancy Hammond 2021-11-3

编辑：Nancy Hammond 2021-11-3

Why is this so complicated?

We all use excel products for data?

In all these years, why don't you have a simple procedure for reading date with dates and numeric values?

Star Strider 2021-11-3

在 MATLAB Online 中打开

For the record, I’m a volunteer here. I have no significant connection with MathWorks, ancd certainly do not make any design decisions.

There have been a number of upgrades to readtable in the last 2½ years since this was posted. It generally imports dates as datetime variables if it recognises the format, otherwise it usually imports them as character arrays that can then be used with 'InputFormat' to convert them to datetime arrays.

Not all Excel files are easy to read because they may not have been created correctly, one example being character variables of numeric values instead of the numeric values themselves. Beyond that, detectImportOptions can be used to specify how fields are read in many situations, however it’s sometimes easier to do the conversion on the immported files instead.

With respect to reading the dates, this file is actually a relevant example —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/223011/AA.xlsx', 'VariableNamingRule','preserve')
T1 = 3×48 table
    STNNBR    CASTNO    BTLNBR    BTLNBR_FLAG_W       DATE        Year    Month    Day            TIME            LATITUDE    LONGITUDE    DEPTH    CTDPRS    CTDTMP    CTDSAL    CTDSAL_FLAG_W    SALNTY    SALNTY_FLAG_W    CTDOXY    CTDOXY_FLAG_W    OXYGEN    OXYGEN_FLAG_W    SILCAT    SILCAT_FLAG_W    NITRAT    NITRAT_FLAG_W    NITRIT    NITRIT_FLAG_W    PHSPHT    PHSPHT_FLAG_W    AMMONI    AMMONI_FLAG_W    TCARBN    TCARBN_FLAG_W    ALKALI    ALKALI_FLAG_W    PH_TOT    PH_TOT_FLAG_W    PH_TMP    CHLORA    PON     POC     POC/PON    PON_1    POC_1    LINE    Accession         EXPOCODE    
    ______    ______    ______    _____________    ___________    ____    _____    ___    ____________________    ________    _________    _____    ______    ______    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    _____________    ______    ______    ____    ____    _______    _____    _____    ____    __________    ________________

      1         1         1             2          12-Aug-2011    2011      8      12     31-Dec-1899 14:21:41     47.11       -126.05     2292     2309.6    1.759     34.636          2          34.633          2           75.6           2          75.61           6           185.3          2          40.02           2           0.05           2           2.89           2           0.01           2          2386.8          6          2436.3          2          7.447           3             25      -999     -999    -999     -999      -999     -999      1      1.5746e+05    {'32WC20110812'}
      1         1         2             2          12-Aug-2011    2011      8      12     31-Dec-1899 14:21:59     47.11       -126.05     2292     2309.6    1.759     34.636          2            -999          9             72           2           -999           9            -999          9           -999           9           -999           9           -999           9           -999           9            -999          9            -999          9           -999           9           -999      -999     0.37    3.75     10.2      0.38     3.86      1      1.5746e+05    {'32WC20110812'}
      1         1         3             2          12-Aug-2011    2011      8      12     31-Dec-1899 14:23:39     47.11       -126.05     2292     2286.3    1.759     34.636          2            -999          9           71.9           2           -999           9          185.22          2          39.98           2           0.04           2           2.91           2           0.01           2          2385.8          2          2429.4          6          7.442           3             25      -999     -999    -999     -999      -999     -999      1      1.5746e+05    {'32WC20110812'}
T1.DateTime = T1.DATE + timeofday(T1.TIME);                      % Combine 'DATE' & 'TIME' Into A Single Array
T1.DateTime
ans = 3×1 datetime array
   12-Aug-2011 14:21:41
   12-Aug-2011 14:21:59
   12-Aug-2011 14:23:39

Then, ‘DATE’, ‘Year’, ‘Month’, ‘Day’ and ‘TIME’ can be deleted and replaced by ‘DateTime’. They can then be put in a cell array because they are different variable types, and only cell arrays allow that, or the numerical values can be put into a matrix, and the ‘DateTime’ variable into a cell array by itself (or perhaps with ‘EXPOCODE’).

Reading uncomplicated Excel files has become fairly straightforward, actually.

.

请先登录，再进行评论。

Answer 2

Arjun 2023-3-13

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/465537-how-to-extract-the-numerial-values-out-of-readtable-output#answer_1192055

编辑：Arjun 2023-3-13

An additional comment:

var_name = T{row_start:end,[columns]}

OR

var_name = T{row_start:end,('column_name')}

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

How to extract the numerial values out of readtable output?

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论
显示 2更早的评论隐藏 2更早的评论

更多回答（1 个）

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

How to extract the numerial values out of readtable output?

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

4 个评论 显示 2更早的评论隐藏 2更早的评论

更多回答（1 个）

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

4 个评论
显示 2更早的评论隐藏 2更早的评论

0 个评论
显示 -2更早的评论隐藏 -2更早的评论