How to fill table column value with string during creation?

Hi all,

I am preparing calculated parameters into a table that will be appended to an excel file for eventual data analysis using R.

Each data set has four identifiers: sampletype, strain, scan, and samplenumber. The identifiers are single cells/characters. These identifiers are constant for the calculated parameters: peaks, width, localheight. The calculated parameters are doubles that are the same length (ranging from 1x5 to 1x9 depending on the data set).

I would like the data to look like this in the table:

sampletype strain scan samplenumber peaks width localheight

example 10 1 1 100 50 95

example 10 1 1 101 46 103

example 10 1 1 103 50 95

example 10 1 1 99 60 90

example 10 1 1 80 52 110

example 10 1 1 91 54 98

example 10 1 1 94 49 99

I want the single cells/characters to 'fill down' the column until the end of the double parameters length, which will change with every data set. So, after running two data sets the eventual excel file would look like:

sampletype strain scan samplenumber peaks width localheight

example 10 1 1 100 50 95

example 10 1 1 101 46 103

example 10 1 1 103 50 95

example 10 1 1 99 60 90

example 10 1 1 80 52 110

example 10 1 1 91 54 98

example 10 1 1 94 49 99

example 10 1 2 110 30 80

example 10 1 2 115 31 81

example 10 1 2 111 28 82

My current code is:

 sampletype = string(test_name{1});
  strain = string(test_name{2});
  scan = string(test_name{3});
  samplenumber = string(txt{1,1});
%   Change the parameter rows into parameter columns
  peaks=peaks'; 
  width=width';
  localheight=localheight'
 sample_table = table(sampletype,strain,scan,samplenumber,peaks,width,localheight);
writetable(sample_table,'sample_sizes.xlsx')

Not surprisingly, I get the following error:

Error using table (line 231)
All table variables must have the same number of rows.

I cant figure out how to make a table and filling down a column with a specfic value. I found "missingvalue" but it seems I need to create a table to use the function, and I can't even make a table. Any advice is appreciated!

Answer 1

[Deepak Gupta]

Hi Sara,

From what i understand you want to create table, and want to populate first column values for all rows.

Here is a piece of code to help you do that. I have generated table with cell instead of a perticular data type because cell can hold any data type hence we don't need to specify type of the value we are going to store in it.

peaks = 1:10; % Just for demonstration. It can be any vector.
numRows = length(peaks); % Calculate the number of rows required in the table. It's not necessory though,
%We can generate an empty table with just properties names and fill it later.
myTable = cell2table(cell(numRows,7)); %Generate table with required rows and columns
myTable.Properties.VariableNames = {'sampletype', 'strain', 'scan', 'samplenumber',...
    'peaks', 'width', 'localheight'}; %Give variable names
myTable(:, 1) = cellstr(repmat(string(10), numRows, 1)); % Now assign the values to the columns. Here 
%string(10) can be replaced by respective column value. i.e. string(test_name{1})

Hope this helps.

Cheers,

Deepak

[Sara Heedy]

Hi Deepak,

Thank you so much for your reply. Your method works very well for the populated columns. Now, I am having trouble filling the 'variable columns'.

    numRows=length(peaks);
    
    myTable = cell2table(cell(numRows,7)); %Generate table with required rows and columns
myTable.Properties.VariableNames = {'sampletype', 'strain', 'scan', 'samplenumber',...
    'peaks', 'width', 'localheight'}; %Give variable names
myTable(:, 1) = cellstr(repmat(sampletype, numRows, 1)); % Now assign the values to the columns. Here 
myTable(:, 2) = cellstr(repmat(strain, numRows, 1));
myTable(:, 3) = cellstr(repmat(scan, numRows, 1));
myTable(:, 4) = cellstr(repmat(samplenumber, numRows, 1));
myTable(:, 5) = cellstr(repmat(peaks, numRows, 1));

The first four columns populate perfectly. But the 5th variable column yields the error:

Error using cellstr (line 49)
Conversion to cellstr from double is not possible.

Error in AFM_Profile_extraction (line 35)
myTable(:, 5) = cellstr(repmat(peaks, numRows, 1));

So, do I need to change the values into a string? Or, should I use the 'table' function to start a new table with the variables at a different location (start new table at 1,5?)

[Deepak Gupta]

Hi Sara,

As you don't need to replicate elements for your variable columns hence you don't need to use repmat. Assuming your variable columns are row vector, you can simply use.

myTable.peaks = peaks';

Same you can do for other columns.

Note: you can use dot(.) method for your populated columns also i.e.

myTable.sampletype = repmat(sampletype, numRows, 1);

Cheers,

Deepak

[Sara Heedy]

Hi Deepak,

Perfect! This is exactly what i need. This will streamline data analysis greatly.

Cheers!!

Sara