trying to pad smaller table to larger table

i have a csv file that table thats a (20 x 30,0000) and i calculated data thats (1 x 27) and need to add that to that csv file. im stuck on what to do and cant seem to figure this out.

code is:

im sure im doing something wrong but have no clue what it could be.

Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];
PD = padarray(Table1,[1,0],0)
T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
T_new = [T_preserve, PD];
writetable(T_new, 'Tesla_Steady_State_027.csv');

[Walter Roberson]

i have a csv file that table thats a (20 x 30,0000)

Could you confirm that you have a table that has 20 rows with 30000 variables? And that you want to add one row with 27 variables?

Answer 1

[Adam Danz]

Padding & horizontally concatenating tables

As of r2020b, padarray does not support table inputs. Use outerjoin() to horizontally concatenate and pad tables with different numbers of rows (or columns).

Here's a demo

% Table T1 is 5x3
% Table T2 is 3x4
T1 = array2table(rand(5,3),'VariableNames',{'a' 'b' 'c'})

T1 = 5x3 table
       a          b           c   
    _______    ________    _______

    0.48834    0.037879    0.90083
      0.331     0.40246    0.78311
    0.86148     0.41245    0.24863
    0.42455    0.049638    0.46388
    0.99029     0.57911    0.71199

T2 = array2table(rand(3,4),'VariableNames',{'d', 'e' 'f', 'g'})

T2 = 3x4 table
       d          e           f           g   
    _______    ________    ________    _______

    0.45315    0.083668      0.5282    0.37319
    0.81502     0.98497     0.33396      0.999
    0.70467     0.35012    0.029792    0.81679

% Add temporary key to each table 
T1.KEY = (1:height(T1))' 

T1 = 5x4 table
       a          b           c       KEY
    _______    ________    _______    ___

    0.48834    0.037879    0.90083     1 
      0.331     0.40246    0.78311     2 
    0.86148     0.41245    0.24863     3 
    0.42455    0.049638    0.46388     4 
    0.99029     0.57911    0.71199     5 

T2.KEY = (1:height(T2))'

T2 = 3x5 table
       d          e           f           g       KEY
    _______    ________    ________    _______    ___

    0.45315    0.083668      0.5282    0.37319     1 
    0.81502     0.98497     0.33396      0.999     2 
    0.70467     0.35012    0.029792    0.81679     3 

% Join tables
T = outerjoin(T1,T2, 'Keys', 'KEY')

T = 5x9 table
       a          b           c       KEY_T1       d          e           f           g       KEY_T2
    _______    ________    _______    ______    _______    ________    ________    _______    ______

    0.48834    0.037879    0.90083      1       0.45315    0.083668      0.5282    0.37319       1  
      0.331     0.40246    0.78311      2       0.81502     0.98497     0.33396      0.999       2  
    0.86148     0.41245    0.24863      3       0.70467     0.35012    0.029792    0.81679       3  
    0.42455    0.049638    0.46388      4           NaN         NaN         NaN        NaN     NaN  
    0.99029     0.57911    0.71199      5           NaN         NaN         NaN        NaN     NaN  

% Remove KEY columns
T(:, cumsum([width(T1), width(T2)])) = []

T = 5x7 table
       a          b           c          d          e           f           g   
    _______    ________    _______    _______    ________    ________    _______

    0.48834    0.037879    0.90083    0.45315    0.083668      0.5282    0.37319
      0.331     0.40246    0.78311    0.81502     0.98497     0.33396      0.999
    0.86148     0.41245    0.24863    0.70467     0.35012    0.029792    0.81679
    0.42455    0.049638    0.46388        NaN         NaN         NaN        NaN
    0.99029     0.57911    0.71199        NaN         NaN         NaN        NaN

[isamh]

thanks for the reply, the data i want to add to the file does have headers and the values underneath. i would like to add them as new columns but was told that i wasnt able to do so.

this is the error:

Error using padarray>ParseInputs
(line 131)
Function padarray expected A
(argument 1) to be numeric or
logical for constant padding.

Error in padarray (line 68)
[a, method, padSize, padVal,
direction] = ParseInputs(args{:});
 

[Adam Danz]

What's your inputs to padarray?

[isamh]

when i clicked on (line 131), this is what i got:

% Check the input array type
if strcmp(method,'constant') && ~(isnumeric(a) || islogical(a))
    error(message('images:padarray:badTypeForConstantPadding'))
end

[isamh]

not exactly sure what you mean by "whats your input to padarray?"

[Adam Danz]

You're getting an error using padarray. Specifically, the first input is not numeric (or logical).

I'm asking what you're using for the inputs to padarray. For example, if this is the line of code that generates the error,

PD = padarray(Table1,[1,0],0)

then show me what Table1 is. You can copy-paste its content from the command window (example below). If it's a large array just share enough for us to get an idea of what you're working with.

Table1 =
      0.68267      0.64837
      0.47241      0.61694
      0.13856      0.23662
      0.96388      0.63728
      0.56376      0.82002

[isamh]

so, it is numerical, has headers as well, not sure if that could cause the error.

Table1 contains the values that i want to add to the csv file.

Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];
PD = padarray(Table1,[1,0],0)

[isamh]

this is a some of the data for Table1 with headers of course,

>> Table1

Table1 =

  1×27 table

    mean_speed50_1_MPH    mean_power50_1_MPH    mean_force80_1_KPH    
    __________________    __________________    __________________  

          5343                4323                  823223        

[Adam Danz]

It can't be numeric and have headers. It must either be a cell array or a table. But padarray would throw an error in either of those cases.

The error message you shared tells you the problem,

Error using padarray>ParseInputs
(line 131)
Function padarray expected A     % <---
(argument 1) to be numeric or    % <---
logical for constant padding.    % <---

If your data do not have headers, why are you still using a table rather than a matrix?

Where do the [Speed_50_1,...] values come from? If you're reading them in, read them in as matrices instead of tables. If you're loading them and they are tables, convert them to matrices using table2array.

[isamh]

i need to keep the headers, would there be a way to add them with the headers?

[Adam Danz]

" would there be a way to add them with the headers? "

Yes, I've updated my answer to show how to use outerjoin.

[isamh]

so something like this? not to mention, i have the 2019 version of matlab, would it still work with me? also, the csv file is huge, do i need to mention the actual length?

Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];
PD = array2table(Table1);
PD1 = padarray(PD,[1,0],0);
T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
T_new = [T_preserve, PD];
writetable(T_new, 'Tesla_Steady_State_027.csv');

%%error message is:
Error using padarray>ParseInputs (line 131)
Function padarray expected A (argument 1) to be
numeric or logical for constant padding.

Error in padarray (line 68)
[a, method, padSize, padVal, direction] =
ParseInputs(args{:});
 

[Adam Danz]

No, forget padarray if you're working with tables.

See my updated answer - use outerjoin.

[isamh]

so i tried this and got an error message about the height. whats weird is that PD2 and Table 1 are equal. shouldnt PD2 be 1 column more?

%code is:
Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];
PD2 = array2table(Table1);
T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
PD1 = array2table(T_preserve);
PD1.key = (1:height(PD1))';
PD2.key = (1:height(PD1))';
J = outerjoin(PD1,PD2, 'Key', 'Key');
J(:, cumsum([width(PD1), width(PD2)])) = [];

%error:
To
assign
to or
create
a
variable
in a
table,
the
number
of
rows
must
match
the
height
of the
table.

[Adam Danz]

The first line below produces a table, right?

Why is the second line converting the table when the input is already a table?

T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
PD1 = array2table(T_preserve);

The input to the 2nd line below is incorrect.

PD1.key = (1:height(PD1))';
PD2.key = (1:height(PD1))';

[isamh]

this seems to work: i dont see the results on the csv file though

Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];
T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
PD1= T_preserve;
PD2 = array2table(Table1);
PD1.KEY = (1:height(PD1))';
PD2.KEY = (1:height(PD2))';
J = outerjoin(PD1,PD2, 'Keys', 'KEY');
J(:, cumsum([width(PD1), width(PD2)])) = [];

[Adam Danz]

Where are you writing the data in the code above?

Did you forget to call writetable?

[isamh]

would writetable include J? I dont see J in workspace

Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];

T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
PD1= T_preserve;

PD2 = array2table(Table1);

PD1.KEY = (1:height(PD1))';
PD2.KEY = (1:height(PD2))';

J = outerjoin(PD1,PD2, 'Keys', 'KEY');

J(:, cumsum([width(PD1), width(PD2)])) = [];

writetable(J, 'Tesla_Steady_State_027.csv');

[isamh]

also, i got an error saying this:

Error using writetable (line 152)
Writing nested tables/timetables is not
supported. Use SPLITVARS on the nested table
before writing.

[Adam Danz]

@isamh, are you trying to solve these problems or are you just reporting the problems to us?

"would writetable include J? I dont see J in workspace"

If you're running the code you shared 2 comments above, then J should be in your workspace. According on that comment, J is the joined table. It looks like you're using the correct syntax, writetable(TABLE,FILENAME). If you don't use a full path to the file, it will be saved to the current directory. You can open that directory using,

winopen(cd())  % for Microsoft Windows

"i got an error saying ... :Writing nested tables/timetables is not supported"

Well, that sounds like a formatting problem. Does PD1 have a nested table? PD2 shouldn't have a nested table.

[isamh]

i guess PD2 is,

adding snap shots of results

[isamh]

it worked, i got it lol. Thank you!!

[isamh]

only issue that i would like to fix is, the file says VAR1-VAR18 instead of the orignal headers. how can i include those? issue is with T_preserve

T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
PD1= T_preserve;

PD2 = Table1;

PD1.KEY = (1:height(PD1))';
PD2.KEY = (1:height(PD2))';

J = outerjoin(PD1,PD2, 'Keys', 'KEY');

J(:, cumsum([width(PD1), width(PD2)])) = [];

writetable(J,'Tesla_Steady_State_027.csv')

[Adam Danz]

If the solution worked, why not accept the answer?

"only issue that i would like to fix is,..."

This is a separate question but the quick answer is: set ReadVariableNames to true.

See documentation

https://www.mathworks.com/help/matlab/ref/readtable.html

[isamh]

if you can help me, would be greatly appreciated. if code is without read & preserve variable names its just vars for the headers

this is the code:

T_preserve = readtable('Tesla_Steady_State_027.csv', "ReadVariableName", true, 'PreserveVariableNames', true);


% ERROR MESSAGE
Warning: Column headers from the file were
modified to make them valid MATLAB
identifiers before creating variable names
for the table. The original column headers
are saved in the VariableDescriptions
property.
Set 'PreserveVariableNames' to true to use
the original column headers as table
variable names. 

[Adam Danz]

What does 'Tesla_Steady_State_027.csv' look like?

Are the var names at the top row? I can't troubleshoot without knowing what the file looks like.

[isamh]

yes, var names are at top but those arent the actually header names

[Adam Danz]

I don't understand. Please attach the file. If the file is too large, delete most of the rows.

Answer 2

[Walter Roberson]

The below code accounts for the possibility of either table having fewer rows than the other one.

Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];
T_preserve = readtable('Tesla_Steady_State_027.csv','Delimiter',',');
T_preserve{end+1:height(Table1),:} = missing;
Table1{end+1:height(T_preserve), :} = missing;
T_new = [T_preserve, PD];
writetable(T_new, 'Tesla_Steady_State_027.csv');

[isamh]

hello, thanks for the help.

i ran it and received this error:

looking at the code again, shouldnt PD be PD2?

The following error occurred converting from
missing to cell:
Conversion to cell from missing is not
possible.
 

[Adam Danz]

Unfortunately missing only fills missing values of some data types. For example, if the table contains cells or graphics handles, the missing value is not defined and will throw an error.

Example:

T = table(num2cell((1:3)'), gobjects(3,1), (1:3)')

T = 3x3 table
    Var1                       Var2                       Var3
    _____    _________________________________________    ____

    {[1]}    [1x1 matlab.graphics.GraphicsPlaceholder]     1  
    {[2]}    [1x1 matlab.graphics.GraphicsPlaceholder]     2  
    {[3]}    [1x1 matlab.graphics.GraphicsPlaceholder]     3  

try
    T{6:7,:} = missing;
catch ME
    fprintf(2, [ME.message,newline])
end

The following error occurred converting from missing to cell:
Conversion to cell from missing is not possible.

T(:,1) = [];  % Remove col 1
try
    T{6:7,:} = missing;
catch ME
    fprintf(2, [ME.message,newline])
end

Conversion to matlab.graphics.GraphicsPlaceholder from missing is not possible.

The outerjoin method gets around the problem but requires that key columns are added or identified.

T1 = table(num2cell((1:3)'), gobjects(3,1), (1:3)')

T1 = 3x3 table
    Var1                       Var2                       Var3
    _____    _________________________________________    ____

    {[1]}    [1x1 matlab.graphics.GraphicsPlaceholder]     1  
    {[2]}    [1x1 matlab.graphics.GraphicsPlaceholder]     2  
    {[3]}    [1x1 matlab.graphics.GraphicsPlaceholder]     3  

T2 = table(num2cell((1:5)'), gobjects(5,1), (1:5)')

T2 = 5x3 table
    Var1                       Var2                       Var3
    _____    _________________________________________    ____

    {[1]}    [1x1 matlab.graphics.GraphicsPlaceholder]     1  
    {[2]}    [1x1 matlab.graphics.GraphicsPlaceholder]     2  
    {[3]}    [1x1 matlab.graphics.GraphicsPlaceholder]     3  
    {[4]}    [1x1 matlab.graphics.GraphicsPlaceholder]     4  
    {[5]}    [1x1 matlab.graphics.GraphicsPlaceholder]     5  

% Add keys
T1.KEY = (1:height(T1))';
T2.KEY = (1:height(T2))';
% Horizontally concatenate
TCAT = outerjoin(T1,T2, 'Keys', 'KEY');
% Remove keys
TCAT(:, cumsum([width(T2), width(T2)])) = []

TCAT = 5x6 table
      Var1_T1                        Var2_T1                     Var3_T1    Var1_T2                     Var2_T2                     Var3_T2
    ____________    _________________________________________    _______    _______    _________________________________________    _______

    {[       1]}    [1x1 matlab.graphics.GraphicsPlaceholder]        1       {[1]}     [1x1 matlab.graphics.GraphicsPlaceholder]       1   
    {[       2]}    [1x1 matlab.graphics.GraphicsPlaceholder]        2       {[2]}     [1x1 matlab.graphics.GraphicsPlaceholder]       2   
    {[       3]}    [1x1 matlab.graphics.GraphicsPlaceholder]        3       {[3]}     [1x1 matlab.graphics.GraphicsPlaceholder]       3   
    {0×0 double}    [1x1 matlab.graphics.GraphicsPlaceholder]      NaN       {[4]}     [1x1 matlab.graphics.GraphicsPlaceholder]       4   
    {0×0 double}    [1x1 matlab.graphics.GraphicsPlaceholder]      NaN       {[5]}     [1x1 matlab.graphics.GraphicsPlaceholder]       5   

[isamh]

I was wondering if there would be a way to add a table that i calculated into a csv file but on a different sheet? also, would it be possible to create a sheet within that file with matlab? or could I create a xlsx file with matlab and add that table in that file?

Table1 = [Speed_50_1, Speed_50_2, Speed_55_1, Speed_55_2, Speed_60_1, Speed_60_2, Speed_65_1, Speed_65_2, Speed_70];
%T_preserve = readtable('Tesla_Steady_State_027.csv','readvariablenames',false,'Delimiter',',');
writetable(Table1,'State_027.csv','Sheet1',1,'Range','A1:AA1')

% I also tried this approach but wont work because of the headers, could matlab create an xlsx automatically?
Table1 = [Speed_50_1, Speed_55_1, Speed_60_1, Speed_65_1, Speed_70];
Table2 = [Speed_50_2, Speed_55_2, Speed_60_2, Speed_65_2];
xlswrite('State_027_1.xlsx',Table1,'A1:O1');
xlswrite('State_027_1.xlsx',Table2,'B1:L1');

[Adam Danz]

writetable with sheet name-value pair.

https://www.mathworks.com/help/matlab/ref/writetable.html#d122e1454612

[Walter Roberson]

you cannot provide sheet range for text output, only for spreadsheet output.

[isamh]

would there be a way for matlab to convert the csv file into a excel file and add that data onto a different sheet?

[Walter Roberson]

csv does not have sheets.

And earlier I discussed why it is literally not possible to add additional columns to a text file without rewriting the entire file.

You cannot use xlsx as a "master" file to make manipulation easier and then export to csv later, because you said that you have 20 rows and 300000 columns, but the maximum supported by xlsx format is 16384 columns.

[isamh]

you have any ideas that could help?

[Walter Roberson]

No, I do not have any ideas that will help. My ideas are along the lines that you should not be trying to store different dimensions of data in a single csv, that csv were never designed for that. Your data organization appears to be confused, and that looks to reflect confusion about what data is to be stored at all.

Why are you using csv?

[isamh]

files are given to me as csv. just looking for other ways that can help or speed things up

[Adam Danz]

You can save them to different pages of an excel spreadsheet by using writetable along with the extension .xls, .xlsm, or .xlsx etc.

[Walter Roberson]

With regards to speeding things up:

.xlsx files are internally a zip'd directory of XML text files. There are some file that give information about what is being stored, and there is one file that is sometimes used to store strings that occur a lot in the data, and there is one file per sheet. The processing of these files involves a lot of text processing to turn the data into numeric form.

Therefore if "speeding things up" is in the sense of making it faster to read data in, then .xlsx files are not the way to go. .xlsx files are convenient and more-or-less portable, but they are not high performance. For pure numeric data, reading csv files using textscan or readmatrix can be faster.

If you have multiple input files, then merging the files together can sometimes be easier for the programmer to process, and faster coding with easier debugging is something real to talk about "help or speed things up". However, if you merge too much information into the same file, then extracting the part you want from the file can take a lot of complicated coding, which slows you right down again.

As a general design principle: data that represents different kinds of things should not go into the same variable -- not unless you are using some kind of structuring that gives information about what the data represents.

Taking the time to read data from csv and store it into a .mat file can be worthwhile if the data is going to be processed multiple times.

Before you decide how to store the variables, you should probably think more about how the data is going to be used, taking into account that it obviously is not all comparable -- not all measurements at a list of pre-determined times for example (otherwise the data you were trying to add on would have the same number of rows or the same number of columns as the data you already had.)

[isamh]

thanks guys, really apprecaite the help and information!

So, I decided to create an xlsx file and add everything onto that. I want to split the data in half(add an empty row or two) so it would be easier to read.

Table11 = [Speed_50_1, Speed_55_1, Speed_60_1, Speed_65_1, Speed_70];
Table12 = [STD_50_1, STD_55_1, STD_60_1, STD_65_1, STD_70];
Table13 = [CV_50_1, CV_55_1, CV_60_1, CV_65_1, CV_70];

Table21 = [Speed_50_2, Speed_55_2, Speed_60_2, Speed_65_2];
Table22 = [STD_50_2, STD_55_2, STD_60_2, STD_65_2];
Table23 = [CV_50_2, CV_55_2, CV_60_2, CV_65_2];

writetable(Table11,'Tesla_Steady_State_027.xlsx','Sheet',1,'Range','A1:O2')
writetable(Table12,'Tesla_Steady_State_027.xlsx','Sheet',1,'Range','A3:O4')
writetable(Table13,'Tesla_Steady_State_027.xlsx','Sheet',1,'Range','A5:O6')

writetable(Table21,'Tesla_Steady_State_027.xlsx','Sheet',1,'Range','A9:O10')
writetable(Table22,'Tesla_Steady_State_027.xlsx','Sheet',1,'Range','A11:O12')
writetable(Table23,'Tesla_Steady_State_027.xlsx','Sheet',1,'Range','A13:O14')

[Adam Danz]

100% agree with WR.

[isamh]

thank you, yeah, I decided to save the data on a xlsx file instead of a csv file. do you know how i can add an empty row underneath Table11-Table13 and right on top of Table21-Table23

[Adam Danz]

Check out the 'range' parameter for writetable().