How to create a dataset array from table?

Hi evereyone,

I have a large table (3824x97) and everyday becomes larger. My table (VESSEL_DATA) has 97 variables name (first row) and i want to convert it in dataseet array. So i use :

xl_path=strcat(cd,'\','vessels_data','.xlsx');

xlswrite(xl_path,VESSEL_DATA);

Vessel_Data=dataset('XLSFile',xl_path);

It works but it takes too long. So I want to ask you if there is a different way to create my dataset directly, without excel help.

Tks & Brgds

Answer 1

[Matt Tearle]

It seems like the problem is that your data is in the form of a 3824-by-97 cell array -- ie every value is in its own cell. If, instead, you had a 1-by-97 cell array where each cell contained a 3824-element array (well, 3823-element array, not including the header), things might be easier. So... here's a possible solution, assuming that everything is just either string or floating point number:

c = {'VESSEL_NAME' 'DATE' 'TEL_TYPE' 'LATITUDE_DEG' 'LONGITUDE_SEC';
'NECTAR' 734789.500 'N' 17 35;
'MARIPOSA' 734431.708 'D' 36 47;
'SAMOS' 734451.500 'D' 1 49}

% extract the data and transpose
d = c(2:end,:)';
% determine the formatting string
fmt = repmat('%s ',size(c,2),1)';
fmt(2,cellfun(@isnumeric,c(2,:))) = 'f';
fmt = fmt(1:end)
% print the data out and read it back in!
foo = textscan(sprintf(fmt,d{:}),fmt)
% now the data is in columns
x = dataset(foo{:},'varnames',c(1,:))

[alexis]

Thank you so much!!!!!!!!!!

[owr]

Thats a nice trick with the format string Matt - I'll have to remember that for my own future use.

Glad you got some help Alexis - sorry I didnt check back here sooner.

Answer 2

[Paul Peeling]

Hi Alexis

You should be able to create a dataset directly from your table, without writing and reading from Excel. Once you have your variable names in a cell array of strings called VarNames, you create the dataset with this syntax:

Vessel_Data = dataset({VESSEL_DATA(2:end,:),VarNames{:}});

Regards

[alexis]

Hi Paul Thanks for your answear, but i think that i have another problem.It works but all the data are cells and i don't want this.. At variable editor all values are between []. Can you help me?

Tks & Brgds

[Paul Peeling]

Hi Alexis

I'm glad to help. Tell me about the VESSEL_DATA table. You mentioned that the first row contains the column names. What is in the rest of the table? Is VESSEL_DATA a 2D cell array of strings

Thanks

Paul

[alexis]

VESSEL_DATA is a 2D table. If i open variable editor and double click in a cell:

First row: Names of variable.....char.

The other column are different. For example some are double and other char.

Thanks

Alex

Answer 3

[owr]

I think Paul is on the right track but the syntax is slightly off.

If "Vessel_Data" looks like this:

>> Vessel_Data = {'Col1','Col2';'A','D';'B','E';'C','F'}

Vessel_Data = 

      'Col1'    'Col2'
      'A'       'D'   
      'B'       'E'   
      'C'       'F'

(note the column headers in row 1)

Then this call to dataset will get you what I think you are looking for:

>> dataset([{Vessel_Data(2:end,:)},Vessel_Data(1,:)])

ans = 

      Col1       Col2   
      'A'        'D'    
      'B'        'E'    
      'C'        'F'

Its a bit confusing, but pick apart each step and you'll understand. You are basically passing the dataset array constructor one cell array with 2 elements. The first is the data itself, in this case a 3x2 cell array (could easily have been numeric data), the second is a cell array with column headers - one for each column in the data (in this case 2 columns).

Hope this helps. It took me awhile to get this right when I needed it as well.

[alexis]

Hi owr

I have same problem....

It works but after that all data except char is [ ].

Thanks

[owr]

If the data is already loaded in the MATLAB in a cell array and some of the columns contain chars and others contain doubles you will need to separate them before creating the dataset array. An alternative is to load the table straight from a file like you originally tried to do with Excel. It may be faster to use a CSV file.

Where does your data originally come from?

Post a small example of what your table looks like. Just copy and paste it from the desktop.

Without knowing these 2 things I can't help you further.

[alexis]

Pls help pe because i've tried everything. Why matlab convert all values in cells?????