Find column with error and delete it

Hey guys,

I got another problem which I couldn't solve myself or found anything helpful so far (not saying there isn't anything, just that I couldn't find it).

I got an Excel file with some financial data. In this data there is one column with #ERROR in the first row (where usually all the names of the variables are) and in the 2nd row $$ER:E110,INVALID DATA ENTERED. Because of this column I always get the Error "Unable to concatenate the table variables Var1 and x-Error, because their types are datetime and cell."

I do know the reason, it is this column, so far so good, but I can't get the column out of my spreedsheet without doing it by hand. I was thinking about letting Matlab do the work, but neither my first idea worked

dataFromTable=readtable('Budapest.xlsx','TreatAsEmpty','NA');
timeDataFromTable=table2timetable(dataFromTable); 
%Preprocessing Data
for k=1:size(dataFromTable,2)
    dataFromTable{1,k}==ERROR
        if k=1:3:10000
            dataFromTable(:,k)=[];
            dataFromTable(:,k+1)=[];
            dataFromTable(:,k+2)=[];
        elseif k=2:3:10000
            dataFromTable(:,k-1)=[];
            dataFromTable(:,k)=[];
            dataFromTable(:,k+1)=[];
        elseif k=3:3:10000
            dataFromTable(:,k-2)=[];
            dataFromTable(:,k-1)=[];
            dataFromTable(:,k)=[];
        end
end

nor my second

ismissing(dataFromTable,[#ERROR])

What I want Matlab to do is to find all the columns with #Error and delete that column andtwo more.

Depending on the number of the column the error is spotted it's either the following two collumns, one following and one previous column or it's the two previous columns.

For some reason I can't pick the Matlab Release out of the drop down menue. I use Matlab R2019a.

Any help is much appreciated.

[Guillaume]

Wouldn't it be best to target your efforts at fixing the problem with whatever generates these excel files so that they're not full of problems?

Before you can select a release in the dropdown, you first need to select a Product (Matlab). I've done this for you.

[Tobias]

If I had to work with this stuff more than just this one time I would try to fix the problems with Thomson Reuters Datastream, but it seemed to be easier to use Matlab to fix it. If it isn't and I can't fix the reason for that problem as well I'm gonna do it by hand and get the paper done.

Thank you for helping with the release and product!

[Tobias]

I deleted all rows by hand, since it is not possible to avoid getting some data with those errors and I don't want to take to much time of someone for something I can do by hand within minutes, but thx anyway for trying!

Answer 1

[dpb]

isStr=find(cellfun(@isstr,table2cell(dataFromTable(1,:))));
if(cell2mat(strfind(dataFromTable{1,isStr},'$$ER')))
  dataFromTable(:,isStr)=[];
end

[Tobias]

This works great for one column. I need to delete two more columns if I find one with an error depending on the position.

isStr=find(cellfun(@isstr,table2cell(dataFromTable(1,:))));
if(cell2mat(strfind(dataFromTable{1,isStr},'$$ER')))
  dataFromTable(:,[isStr isStr+1 isStr+2])=[];
end

helps to delete the two other columns. But that only works if the first out of the three columns is the one with the error. I hoped it would work with a loop, but it doesn't.

isStr=find(cellfun(@isstr,table2cell(dataFromTable(1,:))));
if(cell2mat(strfind(dataFromTable{1,isStr},'$$ER')))
  for isStr=2:3:size(dataFromTable,2)-2
      dataFromTable(:,[isStr isStr+1 isStr+2])=[];
  end
end

That way every third column and the following two gets deleted - that's not what I was looking for. Do you've got any idea on how to get it done?

Thanks for your help so far, I really appreciate it!

[dpb]

Presuming the ERR is always the first of the three offending columns, isStr will be an array of N elements (and also, if only the one column has the error flag as in the example). That was the purpose of cell2mat to return the base column array indices as an array...

You then iterate over that array and delete that column and its two companions--

for i=1:numel(isStr)
  t(:,isStr:isStr+2)=[];
end

If the file isn't regular in this manner, it'll be much more of a pain to parse...

[Tobias]

And the problem that it is not alwaysthe first of the three. I edited the question and hope that everyone can understand that it could be any of those three columns.

[dpb]

Then you'll have to find the initial column(s) as above and then do a search and destroy operation on the other columns that have a string match for the security name portion of the title field to locate the other two columns.

ADDENDUM

Actually, even that doesn't cut it because there isn't an identifiable name match with that column.

All I can see you can do if you can't prevent the column order from being random as well is to separate off however many pieces of the names are needed to be unique (only the first in the example suffices; don't know in general) and then count how many of those have precisely three occurrences. Then, either keep those or trash those that don't.

A quick example of getting started with the sample file looks something like--

>> [u,iu,ia]=unique(extractBefore(t.Properties.VariableNames(2:end).','_'));
>> n=histc(ia,unique(ia))
n =
     3
     3
     3
     2
     3
     1
>> 

where the first returns the unique names of each column excluding the the date column based on the string prior to the first underscore. The count of how many times each occurs is in the latter--one can also return the bin number and then work backwards to find out which ones are the offending ones without the count of three.

All in all, looks like working on solving the download problem might be worthwhile exercise.

Answer 2

[Campion Loong]

You can fine-tune how MATLAB imports your data using various Import Options (Create import options based on file content). First step is to create one using auto detection (which you can also tweak, but here's the most basic form):

>> opt = detectImportOptions('Budapest.xlsx');

Then you can preview where it gets you:

>> preview('Budapest.xlsx',opt)
ans =
  8×16 table
            Var1            MUENCHENERRUCK__BUD__TURNOVERBYVOLUME    MUENCHENERRUCK__BUD__UNADJUSTEDPRICE    MUENCHENERRUCK__BUD_                   x_ERROR                   SAP_BUD__UNADJUSTEDPRICE    SAP_BUD_    ISHARESCOREEURO_BUD_STOXX50UCITSETF_DE__TURNOVERBYVOLUME    ISHARESCOREEURO_BUD_STOXX50UCITSETF_DE__UNADJUSTEDPRICE    ISHARESCOREEURO_BUD_STOXX50UCITSETF_DE_    ISHARESNASDAQ_100_BUD__DE__TURNOVERBYVOLUME    ISHARESNASDAQ_100_BUD__DE__UNADJUSTEDPRICE    ISHARESNASDAQ_100_BUD__DE_    VOLKSWAGENPREF__BUD__TURNOVERBYVOLUME    VOLKSWAGENPREF__BUD__UNADJUSTEDPRICE    VOLKSWAGENPREF__BUD_
    ____________________    _____________________________________    ____________________________________    ____________________    _____________________________________    ________________________    ________    ________________________________________________________    _______________________________________________________    _______________________________________    ___________________________________________    __________________________________________    __________________________    _____________________________________    ____________________________________    ____________________

    14-Jun-2012 00:00:00                    'NA'                                      NaN                             NaN            '$$ER: E110,INVALID END DATE ENTERED'               NaN                 NaN                                'NA'                                                         NaN                                                NaN                                        'NA'                                            NaN                                   NaN                               'NA'                                   'NA'                            'NA'          
    15-Jun-2012 00:00:00                    'NA'                                      NaN                             NaN            ''                                                  NaN                 NaN                                'NA'                                                         NaN                                                NaN                                        'NA'                                            NaN                                   NaN                               'NA'                                   'NA'                            'NA'          
    01-Oct-2012 00:00:00                    'NA'                                      NaN                             NaN            ''                                                  NaN                 NaN                                'NA'                                                         NaN                                                NaN                                        'NA'                                            NaN                                   NaN                               'NA'                                   'NA'                            'NA'          
    02-Oct-2012 00:00:00                    'NA'                                      NaN                             NaN            ''                                                  NaN                 NaN                                'NA'                                                         NaN                                                NaN                                        'NA'                                            NaN                                   NaN                               'NA'                                   'NA'                            'NA'          
    03-Oct-2012 00:00:00                    'NA'                                      NaN                             NaN            ''                                                  NaN                 NaN                                'NA'                                                         NaN                                                NaN                                        'NA'                                            NaN                                   NaN                               'NA'                                   'NA'                            'NA'          
    13-May-2019 00:00:00                    'NA'                                    68280                           68280            ''                                                36695               36695                                'NA'                                                       11018                                              11018                                        'NA'                                          21665                                 21665                               'NA'                                   '52700'                         '52700'       
    14-May-2019 00:00:00                    'NA'                                    68280                           68280            ''                                                35500               35500                                'NA'                                                       11018                                              11018                                        'NA'                                          21665                                 21665                               'NA'                                   '52700'                         '52700'       
    15-May-2019 00:00:00                    'NA'                                    68280                           68280            ''                                                35500               35500                                'NA'                                                       11018                                              11018                                        '0'                                           21000                                 21000                               'NA'                                   '52700'                         '52700' 

You notice the followings

1. for your 19a installation, the '#ERROR' column in Excel is imported as 'x_ERROR' in MATLAB
2. some variables with 'NA' are treated with text rather than numbers
3. the first variable is detected (approriately) as datetime

For #1, as you mentioned, just skip from import any variables with 'ERROR' in the name:

% 'SelectedVariableNames' lets to specify which variables to import
% In this case, only select variables WITHOUT the word 'ERROR' in the names
>> opt.SelectedVariableNames = opt.SelectedVariableNames(~contains(opt.VariableNames,'ERROR'));

For #2, you'd force all variables (except the timestamps) to be 'double' type and treat 'NA' as missing

% Force 2nd to last variables to be 'double'
>> opt = setvartype(opt,2:length(opt.VariableNames),'double');

% Treat 'NA' as missing values in all variables
>> opt = setvaropts(opt,'TreatAsMissing','NA');

For #3, since you're dealing with time-stamped data, you would rather import as a timetable instead of a table

% Read in as a timetable - the first datetime variable are picked up as timestamps automatically
>> tt = readtimetable('Budapest.xlsx',opt)
tt =

  39×14 timetable

            Var1            MUENCHENERRUCK__BUD__TURNOVERBYVOLUME    MUENCHENERRUCK__BUD__UNADJUSTEDPRICE    MUENCHENERRUCK__BUD_    SAP_BUD__UNADJUSTEDPRICE    SAP_BUD_    ISHARESCOREEURO_BUD_STOXX50UCITSETF_DE__TURNOVERBYVOLUME    ISHARESCOREEURO_BUD_STOXX50UCITSETF_DE__UNADJUSTEDPRICE    ISHARESCOREEURO_BUD_STOXX50UCITSETF_DE_    ISHARESNASDAQ_100_BUD__DE__TURNOVERBYVOLUME    ISHARESNASDAQ_100_BUD__DE__UNADJUSTEDPRICE    ISHARESNASDAQ_100_BUD__DE_    VOLKSWAGENPREF__BUD__TURNOVERBYVOLUME    VOLKSWAGENPREF__BUD__UNADJUSTEDPRICE    VOLKSWAGENPREF__BUD_
    ____________________    _____________________________________    ____________________________________    ____________________    ________________________    ________    ________________________________________________________    _______________________________________________________    _______________________________________    ___________________________________________    __________________________________________    __________________________    _____________________________________    ____________________________________    ____________________

    14-Jun-2012 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    15-Jun-2012 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    01-Oct-2012 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    02-Oct-2012 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    03-Oct-2012 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    13-May-2019 00:00:00                     NaN                                    68280                           68280                     36695               36695                                NaN                                                        11018                                              11018                                         NaN                                          21665                                 21665                                NaN                                    52700                           52700        
    14-May-2019 00:00:00                     NaN                                    68280                           68280                     35500               35500                                NaN                                                        11018                                              11018                                         NaN                                          21665                                 21665                                NaN                                    52700                           52700        
    15-May-2019 00:00:00                     NaN                                    68280                           68280                     35500               35500                                NaN                                                        11018                                              11018                                           0                                          21000                                 21000                                NaN                                    52700                           52700        
    16-May-2019 00:00:00                     NaN                                    68280                           68280                     35500               35500                                NaN                                                        11018                                              11018                                         NaN                                          21000                                 21000                                NaN                                    52700                           52700        
    17-May-2019 00:00:00                     NaN                                    68280                           68280                     35500               35500                                NaN                                                        11018                                              11018                                         NaN                                          21000                                 21000                                NaN                                    52700                           52700        
    20-May-2019 00:00:00                     NaN                                    68280                           68280                     35500               35500                                NaN                                                        11018                                              11018                                           0                                          21085                                 21085                                NaN                                    52700                           52700        
    21-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    22-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    23-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    24-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    27-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    28-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    29-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    30-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    31-May-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          21085                                 21085                                NaN                                    52700                           52700        
    03-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                           0                                          19936                                 19936                                NaN                                    52700                           52700        
    04-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          19936                                 19936                                  0                                    46290                           46290        
    05-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          19936                                 19936                                NaN                                      NaN                             NaN        
    06-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          19936                                 19936                                NaN                                      NaN                             NaN        
    07-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          19936                                 19936                                NaN                                      NaN                             NaN        
    10-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          19936                                 19936                                NaN                                      NaN                             NaN        
    11-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                         NaN                                          19936                                 19936                                NaN                                      NaN                             NaN        
    12-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                        11018                                              11018                                           0                                          20775                                 20775                                NaN                                      NaN                             NaN        
    13-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                  0                                                        11218                                              11218                                         NaN                                          20775                                 20775                                NaN                                      NaN                             NaN        
    14-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                          NaN                                                NaN                                         NaN                                          20775                                 20775                                NaN                                      NaN                             NaN        
    17-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                          NaN                                                NaN                                         NaN                                          20775                                 20775                                NaN                                      NaN                             NaN        
    18-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                          NaN                                                NaN                                           0                                          21425                                 21425                                NaN                                      NaN                             NaN        
    19-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    20-Jun-2019 00:00:00                     NaN                                    68280                           68280                     37270               37270                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    21-Jun-2019 00:00:00                       0                                    73000                           73000                     37270               37270                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    24-Jun-2019 00:00:00                     NaN                                      NaN                             NaN                     38500               38500                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    25-Jun-2019 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    26-Jun-2019 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN        
    27-Jun-2019 00:00:00                     NaN                                      NaN                             NaN                       NaN                 NaN                                NaN                                                          NaN                                                NaN                                         NaN                                            NaN                                   NaN                                NaN                                      NaN                             NaN  

[Tobias]

I might wasn't precise enough. Although your help got rid of the column I also need to delete two more columns, which depend on the first one. As you can see in the first row their are always three columns which belong together. So if I can't use the data from the error column I have to delete the two following as well. In some cases the error column isn't the first, so it isn't always the following two. That is why I was thinking of the for loop

for k=1:size(dataFromTable,2)
    dataFromTable{1,k}==ERROR
        if k=1:3:10000
            dataFromTable(:,k)=[];
            dataFromTable(:,k+1)=[];
            dataFromTable(:,k+2)=[];
        elseif k=2:3:10000
            dataFromTable(:,k-1)=[];
            dataFromTable(:,k)=[];
            dataFromTable(:,k+1)=[];
        elseif k=3:3:10000
            dataFromTable(:,k-2)=[];
            dataFromTable(:,k-1)=[];
            dataFromTable(:,k)=[];
        end
end

As for #2 and #3 I thought

dataFromTable=readtable('Budapest.xlsx','TreatAsEmpty','NA');
timeDataFromTable=table2timetable(dataFromTable); 

would do the trick. It might be faster if I do it as you proposed?

Thank you for your help so far, I really appreciate it!