Datevector conversion from Excel to Matlab

Question

Eric Escoto 2020-9-21

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/597019-datevector-conversion-from-excel-to-matlab

评论： Cris LaPierre 2020-9-21

datevec_raw.xlsx.zip

I'm trying to convert an long Excel-format date array and the conversion I normally use is returning incorrect values. The date starts at 3/1/1942.

The format in excel is: 3/1/1942 1:00:00 AM

Below are my lines to import and convert to MATLAB Date Vector and Datenum. I've cleaned up the dataset to only include the date vector from excel. So, ignore the txt, and raw variables.

    %% Define file location, and load into the workspace.
    stafiles_WGA = 'datevec_raw.xlsx';
% Read the data.
    [num_WGA, txt_WGA, raw_WGA] = xlsread(stafiles_WGA); % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.    
    
% Rename the numerical matrix and remove the upper row that originally contained the data headers.
    sta_WGA = num_WGA;
    datevec_WGA = datetime(sta_WGA,'ConvertFrom','excel');
    date_num_WGA = datenum(datevec_WGA);

Any idea what I'm not catching?

Thanks

2 个评论
显示无隐藏无

Walter Roberson 2020-9-21

Is there a reason you are not using readtable() ?

Eric Escoto 2020-9-21

Only that I’ve just used this method for all my scripts to date with no issues regarding the date conversions. If there’s a better way I’m fine to try it.

请先登录，再进行评论。

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

Answer 1

Cris LaPierre 2020-9-21

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/597019-datevector-conversion-from-excel-to-matlab#answer_497668

在 MATLAB Online 中打开

I would read them in this way:

data = readtable('datevec_raw.xlsx','ReadVariableNames',false);

14 个评论
显示 12更早的评论隐藏 12更早的评论

Eric Escoto 2020-9-21

编辑：Eric Escoto 2020-9-21

在 MATLAB Online 中打开

datevec_raw_full.xlsx

That works to read the table. I will attach a new file with all the variables I'm working with which may prove better to dissect.

%% Define file location, and load into the workspace.
    stafiles_WGA = 'datevec_raw_full.xlsx';
    
% Read the data.
    [num_WGA, txt_WGA, raw_WGA] = xlsread(stafiles_WGA); % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.    
    
% Rename the numerical matrix and remove the upper row that originally contained the data headers.
    sta_WGA = num_WGA(2:end, :);
% Change the fifth column (excel datenum value) to MATLAB datenum value.
    datevec_WGA = datetime(data,'ConvertFrom','excel');
    date_num_WGA = datenum(datevec_WGA);
    sta_WGA = [sta_WGA(:, 1:4) date_num_WGA sta_WGA(:,6:end)];
% Rename the 'txt' cell to 'header' and clear unused variables.
    header_WGA = txt_WGA(1,:);
    clearvars('num_WGA', 'txt_WGA', 'raw_WGA', 'date_num_WGA');
    
%% Use timetable for organizing records (of select variables).
    TT1_staWGA = timetable(datevec_WGA, sta_WGA(:,14), sta_WGA(:,17), sta_WGA(:,18), sta_WGA(:,15), sta_WGA(:,16), sta_WGA(:,7));  % Note: this is in hourly resolution.
    

Here's my script finishing off with a TT to organize and parse.

Eric Escoto 2020-9-21

编辑：Eric Escoto 2020-9-21

在 MATLAB Online 中打开

This is the error I get when trying to use this method.

    stafiles_WGA = readtable('datevec_raw_full.xlsx','ReadVariableNames',false);
    
% Read the data.
%    [num_WGA, txt_WGA, raw_WGA] = stafiles_WGA; % Creates three variables. A number (double), raw (cell), and txt (cell).
% Note that the 'txt' and 'raw' variables contain the decriptions of the columns.   
    
% Rename the numerical matrix and remove the upper row that originally contained the data headers.
    sta_WGA = stafiles_WGA(:, 5);
% Change the fifth column (excel datenum value) to MATLAB datenum value.
    datevec_WGA = datetime(sta_WGA,'ConvertFrom','excel');
    date_num_WGA = datenum(datevec_WGA);
    sta_WGA = [sta_WGA(:, 1:4) date_num_WGA sta_WGA(:,6:end)];
% Rename the 'txt' cell to 'header' and clear unused variables.
    header_WGA = txt_WGA(1,:);
    clearvars('num_WGA', 'txt_WGA', 'raw_WGA', 'date_num_WGA');
    
    
Error using datetime (line 586)
Input data must be one numeric matrix when converting from a different date/time representation.

Walter Roberson 2020-9-21

When you readtable(), the stafiles-WGA.DATE column (5th column) is already in datetime format and does not need to be converted.

There is a new file attached above that includes more than only the first file with rows of dates.

I used that file in my testing. It has 48 data rows in it, plus a header row.

Oh also I see that you are pulling from the 4th column. the dates are in the 5th column.

No, after you do the xlsread() you are pulling the numeric values from the first output of xlsread(), num_WGA . The first column of input is not numeric, so attempting to convert it to numeric internally gives a column of all nan. Then, when xlsread() is producing the num (numeric) output, it searches for the first and last row and columns that have something that is not a NaN, and it trims out anything outside of that. The first row of the input file is all text, so str2double() of it converts to all NaN, so the first row of the file is trimmed out of the num results. The first column of the file is all non-numeric, so str2double() of it converts to all NaN, so the first column of the file is trimmed out of the num results.

As a result, what was column 5 of the input shows up as column 4 of num_WGA, with the original column 1 having been removed as all nan.

Removing a row or column as all-NaN is only done around the edges, never in the middle.

Because of the way this happens, in order to be sure you are getting the columns you expect, if there is any possibility that a leading column might be all text but it just might happen to have an entry that looks like a number, then you cannot be sure that the entire column was removed or not. You have to refer back to the "raw" output to be sure you get the correct column, if a leading column might contain user input that might look like a number. Or consider for example that most hexadecimal would have characters that cannot form decimal numbers, but there might happen to be a leading row that contains '0000' and that looks like a decimal number to xlsread() so suddenly and accidentally that column gets retained as being numeric...

These kinds of problems are why I avoid xlsread().

readtable() does not have these issues. It can have different issues, such as if users might write in notes in numeric columns, like "power failure" where a number was expected, but readtable() will not chop out leading rows or columns.

Cris LaPierre 2020-9-21

编辑：Cris LaPierre 2020-9-21

在 MATLAB Online 中打开

Where are you expecting to see them? As I said, the coumn headers become the VariableNames (e.g. stafiles_WGA.DATE).

stafiles_WGA = 
     Station_Name     ELEVATION    LATITUDE    LONGITUDE       DATE       HOURLYVISIBILITY    AIRTEMPC    HOURLYDRYBULBTEMPF    HOURLYDRYBULBTEMPC    HOURLYWETBULBTEMPF    HOURLYWETBULBTEMPC    HOURLYDewPointTempF    HOURLYDewPointTempC    HOURLYRelativeHumidity    HOURLYWindSpeed    HOURLYWindDirection    HOURLYPrecip_in    HOURLYPrecip_mm    HOURLYAltimeterSetting
    ______________    _________    ________    _________    __________    ________________    ________    __________________    __________________    __________________    __________________    ___________________    ___________________    ______________________    _______________    ___________________    _______________    _______________    ______________________
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             39                    4.1                {'NAN'}               {'NAN'}                  37                       3                      93                     1                   360                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             37                      3                {'NAN'}               {'NAN'}                  35                     1.9                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             36                    2.4                {'NAN'}               {'NAN'}                  34                     1.3                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             36                    2.4                {'NAN'}               {'NAN'}                  34                     1.3                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             35                    1.9                {'NAN'}               {'NAN'}                  33                     0.8                      93                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             34                    1.3                {'NAN'}               {'NAN'}                  32                     0.2                      92                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             34                    1.3                {'NAN'}               {'NAN'}                  32                     0.2                      92                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             42                    5.8                {'NAN'}               {'NAN'}                  35                     1.9                      76                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             51                   10.7                {'NAN'}               {'NAN'}                  39                     4.1                      64                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
    {'WBAN:23104'}      421.2        33.3       -111.67     7.0936e+05         99.42          {'NAN'}             54                   12.4                {'NAN'}               {'NAN'}                  38                     3.5                      55                     0                     0                {'NAN'}            {'NAN'}               {'NAN'}        
...

To see the property values, use the following code:

stafiles_WGA.Properties.VariableNames
ans = 1×19 cell    
'Station_Name'    'ELEVATION'    'LATITUDE'    'LONGITUDE'    'DATE'       'HOURLYVISIBILITY'    'AIRTEMPC'    'HOURLYDRYBULBTEMPF'    'HOURLYDRYBULBTEMPC'    'HOURLYWETBULBTEMPF'    'HOURLYWETBULBTEMPC'    'HOURLYDewPointTempF'    'HOURLYDewPointTempC'    'HOURLYRelativeHumidity'    'HOURLYWindSpeed'    'HOURLYWindDirection'    'HOURLYPrecip_in'    'HOURLYPrecip_mm'    'HOURLYAltimeterSetting'    

Eric Escoto 2020-9-21

编辑：Eric Escoto 2020-9-21

在 MATLAB Online 中打开

Oh, I found the issue way back when I used this line that was provided.

data = readtable('datevec_raw.xlsx','ReadVariableNames',false);

I just changed it and all is well.

stafiles_WGA = readtable('WGA_stationdata_corrected.xlsx','ReadVariableNames',true);

Thanks!

Cris LaPierre 2020-9-21

Yup, or drop the "ReadVariableNames" setting completely, like I showed in my previous response (true is the default setting, so doesn't need to be set explicitely).

请先登录，再进行评论。