How to convert class double to class table ?

Hello,

I have imported a delimited file in the form of a table. Some columns in the data are dates and I want to replace those dates to numbers so that I can do calculations with them and then reinsert the calculated data back into the matrix. I am using the function number=datenum(inputDate) which works fine but when I try to insert them I get the error:

Error using mainScript (line 21) All input arguments must be tables. It looks like the calculated data are of class "double" and it has to be converted to class "table", can anyone be of help?

I really appreciate, this is holding my whole project back.

[Walter Roberson]

What is your code on line 21 -- the code that tries to reinsert the calculated data back into the matrix?

Some columns in the data are dates

Do you mean character vectors, or do you mean datetime() objects ? It is recommended to stop using datenum() and only use datetime() and duration() related functions.

[George Papamichael]

Yes, these are datetime objects and line 21 is the code that should reasemble the table. Thanks, I will look into those functions you proposed.

[Walter Roberson]

For example you can

dt = datetime('now');
dt12 = dateshift(dt, 'start', 'hour') - hours(12);

I was hoping you would post the code for your line 21 so we could have a look at what you were doing.

[George Papamichael]

clc

n=100;

sal=importSalaries(1:n,:)

a2=sal{:,2};
sex=grp2idx(a2)-1;

dateOfBirth=datenum(sal{:,3})
class(dateOfBirth)

col6=sal(:,6);
salary=sal(:,8);

a6=sal{:,6};
oldNew=grp2idx(a6);

datesFixed=fixdatenum(sal{:,4},sal{:,5});

(line 21:) matrix=[dateOfBirth,table(datesFixed),table(oldNew),salary]

newColumnNumbers=datenum(datesFixed);
filter=~isnan(newColumnNumbers);
filteredMatrix=matrix(filter,:)

The "dateOfBirth" is of class double, the others are ok.

[Walter Roberson]

matrix = table(dateOfBirth, datesFixed, oldNew, salary);

[George Papamichael]

Great! it works like a charm! But I have another issue now with the dateOfBirth column, it appears as floating point number in the table (something like "7.2471e+05" instead of 724710), how to format those numbers so they look like integers in the table?

[Walter Roberson]

datesFixed=fixdatenum(sal{:,4},sal{:,5});

That hints to me that you are putting together a date and time field. If so then the result is not going to be an integer.

Anyhow... your current format affects how tables display.

[George Papamichael]

The fixdatenum function is as follows:

function out = fixdatenum(date1,date2)
num1=datenum(date1);
num2=datenum(date2);

if isnan(num1)
    if isnan(num2)
        out=NaN;
    else
        out=num2;
    end
else
    if isnan(num2)
        out=num11;
    else
        out=max(num1,num2);
    end
end

end

So basically it compares two dates: If they are both NaN it returns NaN. If exactly one of them is NaN it returns the non-NaN value and if both are not NaN it returns the maximum of the two. Should I have done it differently?

[George Papamichael]

Everything ok so far, I have succeded in forming this matrix X out of numbers. But now I need X'. Anybody can tell me how to transpose it? I get the error:

Error using ' (line 192)

Undefined function 'ctranspose' for input arguments of type 'table'.

[Image Analyst]

@George Papamichael, you keep forgetting to attach your data. Please attach the file and your code for reading it in. That way others (who don't have the Crystal Ball Toolbox like Walter) could try to help you.

[George Papamichael]

I paste my code:

clc

n=100;

sal=importSalaries(1:n,:);

a2=sal{:,2};
sex=grp2idx(a2)-1;

dateOfBirth=int32(datenum(sal{:,3}));

a6=sal{:,6};
oldNew=grp2idx(a6);

salary=sal{:,8};

datesFixed=fixdatenum(sal{:,4},sal{:,5});

matrix=table(sex,dateOfBirth,datesFixed,oldNew,salary);

newColumnNumbers=datenum(datesFixed);
filter=(newColumnNumbers~=0);
filteredMatrix=matrix(filter,:);

and I attach the final matrix:

[Walter Roberson]

I do not see where you have created a matrix X at all?

You created a table() object that only has numeric values inside it, but is still a table() object.

You could extract the numbers and transpose that

fm{:,:}.'

but it would not be a table() object

[George Papamichael]

I used the letter X for simplicity. It is the "fm" variable I am referring to.

[Walter Roberson]

Okay then: you should use

rows2vars(fm)

to transpose the fm table() object.

[George Papamichael]

Thanks! This worked. Now I need the transpose of fm multiplied with fm itself. I used the commands

fmt=rows2vars(fm) as you suggested and then

X=fmt*fm

This last statement gives me the error:

Undefined operator '*' for input arguments of type 'table'.

What to do here?

[Walter Roberson]

What you should do at this point is give up. You will never be able to do mathematical operations on a table.

If only you had a numeric array instead of a table...

https://www.mathworks.com/matlabcentral/answers/828595-how-to-convert-class-double-to-class-table#comment_1523163

[George Papamichael]

I tried to select the option "numeric matrix" at the import process but that destroys the dates??? There must be a way...

[Walter Roberson]

A = datetime('now')-days([2 1 0].');
B = [4 7 9].'

B = 3×1

4 7 9

FM = table(A,B)

FM = 3×2 table
             A              B
    ____________________    _

    15-May-2021 19:13:16    4
    16-May-2021 19:13:16    7
    17-May-2021 19:13:16    9

What result would you expect from taking FM' * FM ? What does it mean to multiply a date by 7 ?

[George Papamichael]

I don't need year, month, hours minutes and seconds, just number of days. First I have to define an origin for the dates (other than the meaningless 01-Jan-0000). In this example I'd choose 16-May-2021 so the first column will be [-1,0,1]'. Then FM' * FM is a matrix that comes up in multiple linear regression. Take a look at Linear Regression at the last equation of the section "Least-squares estimation and related techniques" where the parameters β are calculated.

[Walter Roberson]

Establish an common origin such as jan 1 2021, datetime(), subtract from the datetime of in the table, days() the result. round() if you want. Use that as one numeric column. Use {} indexing to extract the other columns of the table and create an overall numeric array.

Answer 1

[George Papamichael]

I finally found a thing that works and that is to select "Column Vectors" at the import data wizard. Then I worked on each column (I have 8 columns in total, not too bad) and for the time origin of time i used the now() function. This is my code:

clc

n=1000;
origin=floor(now);

var1=VarName1(1:n);
var2=VarName2(1:n);
var3=VarName3(1:n);
var4=VarName4(1:n);
var5=VarName5(1:n);
var6=VarName6(1:n);
var7=VarName7(1:n);

salary=VarName8(1:n);

gender=grp2idx(var2)-1;

dateOfBirth=origin-datenum(var3);

newDates=fixdatenum(var4,var5);

oldNew=grp2idx(var6);

matrix=[ones(n,1) gender dateOfBirth newDates oldNew];

filter=~isnan(newDates);

X=matrix(filter,:);
salaryFiltered=salary(filter);

M = X' * X

observations=length(salaryFiltered)

alpha = X' * salaryFiltered;

beta = M\alpha