CODEWITHSUNDEEP
excelmatlab
user2457324
user2457324

Reputation: 113

Using xlswrite in MATLAB

I am working with three datasets in MATLAB, e.g.,

  1. Dates:
    There are D dates that are chars each, but saved in a cell array.

    {'01-May-2019','02-May-2019','03-May-2019'....}

  2. Labels:
    There are 100 labels that are strings each, but saved in a cell array.

    {'A','B','C',...}

  3. Values: 

    [0, 1, 2,...]

    This is one row of the Values matrix of size D×100.

I would like the following output in Excel:

date            labels     Values

01-May-2019       A           0
01-May-2019       B           1
01-May-2019       C           2

till the same date repeats itself 100 times. Then, the next date is added (+ repeated 100 times) onto the subsequent row along with the 100 labels in the second column and new values from 2nd row of Values matrix transposed in third column. This repeats until the date length D is reached.

For the first date, I used: 

c_1 = {datestr(datenum(dates(1))*ones(100,1))}
c_2 = labels
c_3 = num2cell(Values(1,:)')
xlswrite('test.xls',[c_1, c_2, c_3])

but, unfortunately, this seemed to have put everything in one column, i.e., the date, then, labels, then, 1st row of values array. I need these to be in three columns.

Also, I think that the above needs to be in a for loop over each day that I am considering. I tried using the table function, but, didn't have much luck with it.

How to solve this efficiently?

Upvotes: 1

Views: 778

Answers (1)

sco1
sco1

Reputation: 12214

You can use repmat and reshape to build your columns and (optionally) add them to a table for exporting.

For example:

dates = {'01-May-2019','02-May-2019'};
labels = {'A','B', 'C'};
values = [0, 1, 2];

n_dates = numel(dates);
n_labels = numel(labels);

dates_repeated = reshape(repmat(dates, n_labels, 1), [], 1);
labels_repeated = reshape(repmat(labels, n_dates, 1).', [], 1);
values_repeated = reshape(repmat(values, n_dates, 1).', [], 1);

full_table = table(dates_repeated, labels_repeated, values_repeated);

Gives us the following table:

>> full_table

full_table =

  6×3 table

    dates_repeated    labels_repeated    values_repeated
    ______________    _______________    _______________

    '01-May-2019'           'A'                 0       
    '01-May-2019'           'B'                 1       
    '01-May-2019'           'C'                 2       
    '02-May-2019'           'A'                 0       
    '02-May-2019'           'B'                 1       
    '02-May-2019'           'C'                 2

Which should export to a spreadsheet with writetable as desired.

What we're doing with repmat and reshape is "stacking" the values and then converting them into a single column:

>> repmat(dates, n_labels, 1)

ans =

  3×2 cell array

    {'01-May-2019'}    {'02-May-2019'}
    {'01-May-2019'}    {'02-May-2019'}
    {'01-May-2019'}    {'02-May-2019'}

We transpose the labels and values so they get woven together (e.g [0, 1, 0, 1] vs [0, 0, 1, 1]), as repmat is column-major.

If you don't want the intermediate table, you can use num2cell to create a cell array from values so you can concatenate all 3 cell arrays together for xlswrite (or writematrix, added in R2019a, which also deprecates xlswrite):

values_repeated = num2cell(reshape(repmat(values, n_dates, 1).', [], 1));
full_array = [dates_repeated, labels_repeated, values_repeated];

Upvotes: 1

Related Questions