Prev: second order non linear boundary value problem
Next: java heap space error while creating movie
From: Ashish Uthama on 27 Apr 2010 15:52
On Tue, 27 Apr 2010 12:56:04 -0300, Jeremy <jsgth5(a)mail.missouri.edu>
wrote:

> "Ashish Uthama" <first.last(a)mathworks.com> wrote in message
> <op.vbtpfme5a5ziv5(a)uthamaa.dhcp.mathworks.com>...
>> On Mon, 26 Apr 2010 22:12:05 -0300, Jeremy <jsgth5(a)mail.missouri.edu>
>> wrote:
>> > I would like to import a complicated data set from excel into
>> MatLab. > What I'd like to achieve is a structure array where each
>> field > represents a column variable for example.
>> >
>> > aa.TrialNumber
>> > aa.MaleID
>> > aa.FemaleID
>> > aa.TimeInTrial
>> > aa.FileNames
>> >
>> > What I'd like to be able to do is access 1 trial worth of data by
>> using > a similar command as: aa(1,1) to get the following output
>> >
>> > TrialNumber: 1
>> > MaleID: 23.4
>> > FemaleID: 22.1
>> > TimeInTrial: [0 12 16 36]
>> > FileNames: ['P0' 'P12' 'P16' 'P36']
>> >
>> > I know one problem I could encounter is that not all of my fields
>> have > the same number of rows due to trial level variation. I'm
>> relatively > new to MatLab, so if you have suggestions please take
>> that into > consideration - Thanks you. Please let me know if you
>> need other > information to assist with this question.
>> Code in this example might be useful:
>> http://www.mathworks.com/matlabcentral/fileexchange/19707-xls2var
>
> Thanks everyone for giving advise - much appreciated. Tideman, I came
> up with a similar solution as the one you proposed, I just couldn't
> figure out how to combine the created arrays into a structure array.
>
> Ashish - thanks for suggesting the above post! It is very close to what
> I'd like to accomplish more automatically. Currently we create the same
> type of structure by copying each excel column into Matlab (which is a
> serious pain for large datasets). So the issue now, is how can I turn
> the 1x1 structure into a 1xN structure where each N is (from my above
> example) all of the fields for trial 1? - Thanks

Depending on the size of your existing code, it might be easier to handle
a 1x1 structure with 1xN sized fields in your existing code. Modifying the
FEX submission design to return a 1xN structure would be very inefficient,
since you would have to do translation per row rather than once per
variable (column).

A kludge might be to write a loop to turn the struct inside out. Dont
think this is the way you want to go...

Aside: I dont get why you would have two indices? (as in aa(1,1) ?)
From: Jeremy on 27 Apr 2010 17:48
> Depending on the size of your existing code, it might be easier to handle
> a 1x1 structure with 1xN sized fields in your existing code. Modifying the
> FEX submission design to return a 1xN structure would be very inefficient,
> since you would have to do translation per row rather than once per
> variable (column).
>
> A kludge might be to write a loop to turn the struct inside out. Dont
> think this is the way you want to go...
>
> Aside: I dont get why you would have two indices? (as in aa(1,1) ?)

I only included the aa(1,1) as an example because that is the nomenclature I've seen used when referring to structures with multiple elements. For some time now we have been manually cutting and pasting data into matlab to construct structure arrays that are 1X1. The link you provided above has now automated that process which is a huge step forward - thank you! However, the code also builds a 1x1 structure array. While this is how we've been processing the data, were each fieldname contains all relevant data related to that specific column on the data sheet. What we'd like to move towards is more in line with examples I've seen of database like structure arrays, where each row is a new element in the structure. In this case every structure element would contain all the fieldnames and data for trial #1. Hence if you typed in the command window aa.(1) for example, it would return
all the values for the fieldnames for just 1 trial. While what we've accomplished, with your suggestions have been a huge step forward, I'd like to go one step farther and build the array more like a database structure.
From: Ashish Uthama on 28 Apr 2010 08:01
On Tue, 27 Apr 2010 18:48:03 -0300, Jeremy <jsgth5(a)mail.missouri.edu>
wrote:

>> Depending on the size of your existing code, it might be easier to
>> handle a 1x1 structure with 1xN sized fields in your existing code.
>> Modifying the FEX submission design to return a 1xN structure would be
>> very inefficient, since you would have to do translation per row
>> rather than once per variable (column).
>> A kludge might be to write a loop to turn the struct inside out. Dont
>> think this is the way you want to go...
>> Aside: I dont get why you would have two indices? (as in aa(1,1) ?)
>
> I only included the aa(1,1) as an example because that is the
> nomenclature I've seen used when referring to structures with multiple
> elements. For some time now we have been manually cutting and pasting
> data into matlab to construct structure arrays that are 1X1. The link
> you provided above has now automated that process which is a huge step
> forward - thank you! However, the code also builds a 1x1 structure
> array. While this is how we've been processing the data, were each
> fieldname contains all relevant data related to that specific column on
> the data sheet. What we'd like to move towards is more in line with
> examples I've seen of database like structure arrays, where each row is
> a new element in the structure. In this case every structure element
> would contain all the fieldnames and data for trial #1. Hence if you
> typed in the command window aa.(1) for example, it would return all the
> values for the fieldnames for just 1 trial. While what we've
> accomplished, with your suggestions have been a huge step forward, I'd
> like to go one step farther and build the array more like a database
> structure.

ok, that makes sense to me.

I have updated the FEX submission with an option to return a structure
array instead of a structure with field arrays. While it goes through:



function xlsStruct=xls2struct(file,flag)
%xls2struct Load Excel file contents as a structure
% xlsstruct = xls2struct(file) loads the Excel file 'file'. The first row
is
% used to generate the field names for the 1x1 structure xlsstruct. Each
% column (excluding the first row) is then converted to a numeric/cell
% array and assigned to the corresponding field name. All columns are
% expected to have equal number of entries.
%
% xlsstructArray = xls2struct(file,'structArray') will return a 1xNumCol
% structure array xlsstructArray. The kth element of the structure will
% have field names corresponding to the first row and values corresponding
% to the kth row.
%
% GENVARNAME is used to generate a valid MATLAB structure field name from
% the first row data.
%
% Blank entries are returned as 'NaN'.
%
% Platform support is dependent on XLSREAD.
%
% %Example: Given this Excel file content (9 columns, 5 rows):
%
% %one two three four ' ' six se ven
% %1 2 3 6 7
% %11 22 three
% % seven
% % 222 33
%
% xlsStruct=xls2struct('example.xls')
% % Where:
% % xlsStruct.one' = 1 11 NaN NaN
% % xlsStruct.two' = 2 22 NaN 222
% % xlsStruct.three' = [3] 'three' [NaN] [33]
% % xlsStruct.four' = NaN NaN NaN NaN
% % xlsStruct.x' = NaN NaN NaN NaN
% % xlsStruct.six' = 6 NaN NaN NaN
% % xlsStruct.seVen' = [7] [NaN] 'seven' [NaN]
%
% xlsStructArray=xls2struct('example.xls','structArray')
% % Where:
% % xlsStructArray =
% %
% % 1x4 struct array with fields:
% % one
% % two
% % three
% % four
% % x
% % six
% % seVen

% See also: xlsread genvarname

%% handle argin
if nargin < 1
error('MATLAB:xls2struct:FileName',...
'Input excel filename must be specified.');
elseif nargin ==1
flag = '';
elseif nargin ==2
if(strcmpi(flag,'structArray'))
flag=true;
else
error('MATLAB:xls2struct:flag',...
'Invalid second argument');
end
else
error('MATLAB:xls2struct:inputArgCount',...
'Invalid number of input arguments.');
end


if ~ischar(file)
error('MATLAB:xls2struct:InputClass','Filename must be a string.');
end


%% XLSREAD the file
try
%obtain numeric and text data (mutually exclusive contents)
[num,txt]=xlsread(file);
catch ME
error('MATLAB:xls2struct:xlsreaderr',...
'XLSREAD was unable to read this file: %s',ME.message);
end

%% Process the data

% The cell array txt contains all the strings in the excel file
% including the first 'header' row which we assume to be variable names
[rows,numVars]=size(txt); %#ok<ASGLU>

for varInd=1:numVars

%loop through each column in the excel sheet

%Assume first row element in the current column is the variable name
varName=txt{1,varInd};

%since this string might not be a valid MATLAB variable name (it might
%contain spaces, create one from it:
varName=genvarname(varName);

%if there is a string in this column (other than the first one of
%course) we create a cell array for the data.
stringData=txt(2:end,varInd);
strInds=~cellfun(@isempty,stringData);

if( any(strInds) )
%this column contains strings, use cells
varData={};

try %#ok<TRYNC>
%try to convert any numbers present in this column to cells
varData=num2cell( num(:,varInd) );
end
varData(strInds)=stringData(strInds); %#ok<AGROW>

else
%this column only contains numbers, use arrays
varData=num(:,varInd);

if(flag)
%we need a cell array to 'deal' to fields of structure array
varData=num2cell(varData);
end

end



%Use dynamic field names for MATLAB structures
if(flag)
%create structure array as output
[xlsStruct(1:length(varData)).(varName)]=deal(varData{:});
else
%create field arrays as output
xlsStruct.(varName)=varData;
end

end
From: Jeremy on 28 Apr 2010 10:49
Ashish, - FANTASTIC!!!

It's on the money! Thank you so much for your assistance! I really appreciate the time you took in helping us resolve these issues - thanks!
First  |  Prev  | 
Pages: 1 2
Prev: second order non linear boundary value problem
Next: java heap space error while creating movie