Find column with error and delete it

20 views (last 30 days)
Tobias
Tobias on 30 Jun 2019
Commented: Tobias on 4 Aug 2019
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.
  3 Comments
Tobias
Tobias on 4 Aug 2019
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!

Sign in to comment.

Answers (2)

dpb
dpb on 1 Jul 2019
isStr=find(cellfun(@isstr,table2cell(dataFromTable(1,:))));
if(cell2mat(strfind(dataFromTable{1,isStr},'$$ER')))
dataFromTable(:,isStr)=[];
end
  4 Comments
dpb
dpb on 3 Jul 2019
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.

Sign in to comment.


Campion Loong
Campion Loong on 1 Jul 2019
Edited: Campion Loong on 1 Jul 2019
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
  1 Comment
Tobias
Tobias on 2 Jul 2019
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!

Sign in to comment.

Products


Release

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by