Undefined operator '-' for input arguments of type 'cell'.

Hello,

I am trying to subtract two columns (that are in time domain) in spread sheet and create a new column. I attached the .xlsx file for your reference.

T = readtable('T.xlsx');
T.Time=T.BIRTHDT-T.INFODT;

But I am getting an error message: Undefined operator '-' for input arguments of type 'cell'.

I presume either the format of the two columbs might be different or one column is missing the hours/min/seconds information. But not sure how to sort.

Any help would be highly appreciated.

Answer 1

[Star Strider]

Try this:

T = readtable('FindDifference.xlsx');
T.INFODT = datetime(T.INFODT);
T.BIRTHDT = datetime(T.BIRTHDT);
T.Time = years(T.BIRTHDT-T.INFODT);

This puts ‘T.Time’ in units of years. Other units are possible. See the documentation for more information.

[Vijay]

Thank you so much.

As I replied to Walter the ultimate aim is to plot NHY vs the difference (which would be the age of the subject). Also all of the years in T.INFODT are byond 2000.

So your sugestion creates the column with the difference but the code considers then in the years 1900.

[Star Strider]

‘Also all of the years in T.INFODT are byond 2000.’

It would have been quite helpful to have had that information at the outset.

My code then changes the ‘T.INFOD’ assignment to:

T.INFODT = datetime(T.INFODT)+years(2000);

With that change:

First_5_Rows = T(1:5,:)

produces:

First_5_Rows =

  5×5 table

    PATNO      INFODT       SCORE      BIRTHDT       Time  
    _____    ___________    _____    ___________    _______

    3000     01-Feb-2011      0      01-Jan-1941    -70.085
    3000     01-Mar-2012      0      01-Jan-1941    -71.164
    3000     01-Feb-2013      0      01-Jan-1941    -72.086
    3000     01-Mar-2014      0      01-Jan-1941    -73.162
    3000     01-Mar-2015      0      01-Jan-1941    -74.162

I have absolutely no idea where you are going with this. If I did, I might be able to do a bit more to help you.

.

[Vijay]

Not sure why matlab 2019b (windows) in my computer producing INFODT differently

when I used...

T.INFODT = datetime(T.INFODT)+years(2000);

and executed...

datetime(T.INFODT(1:10))

ans =

  10×1 datetime array

   01-Feb-2011 00:00:00
   01-Mar-2012 00:00:00
   01-Feb-2013 00:00:00
   01-Mar-2014 00:00:00
   01-Mar-2015 00:00:00
   01-Apr-2016 00:00:00
   01-Feb-2018 00:00:00
   01-Mar-2019 00:00:00
   01-Feb-2011 00:00:00
   01-Mar-2011 00:00:00   

Where as in 2020a (windows) for datetime(T.INFODT(1:10)) executed...

10×1 datetime array
   Feb-2011
   Mar-2012
   Feb-2013
   Mar-2014
   Mar-2015
   Apr-2016
   Feb-2018
   Mar-2019
   Feb-2011
   Mar-2011   

In both cases the format isn't matching that of your latest post. Because of this, I believe, the computation of the difference with your suggested code is not working to me. Do you have any clue for this?

Regarding the goal of this exercise...

I have relatively a big dataset of this type (but I showed the simplified version to avoid confusion/clutter) where I want to get XYplot for individual ID where the difference on the positive domain taken on x-axis and the score on Y-axis. I may need to loop it for the ID in this regard. But at this time my goal is to get the difference worked.

[Star Strider]

The display format likely does not affect the computations. You can set the Format to be essentially whatever you want (within limmits). I doubt there are any significant differences between R2019b and R2020a, at least with respect to these calculations. (It is always best to have all the latest Updates for each version/release.)

[Vijay]

Thank you so much. Your suggestion worked.

T.INFODT = datetime(T.INFODT,'Format','dd-MMM-yyyy');
T.BIRTHDT = datetime(T.BIRTHDT,'Format','dd-MMM-yyyy');
T.Time = years(T.INFODT-T.BIRTHDT);

Then executing T((1:15),:)...

ans =

  15×5 table

    PATNO      INFODT       NHY      BIRTHDT       Time 
    _____    ___________    ___    ___________    ______

    3000     01-Feb-2011     0     01-Jan-1941    70.085
    3000     01-Mar-2012     0     01-Jan-1941    71.164
    3000     01-Feb-2013     0     01-Jan-1941    72.086
    3000     01-Mar-2014     0     01-Jan-1941    73.162
    3000     01-Mar-2015     0     01-Jan-1941    74.162
    3000     01-Apr-2016     0     01-Jan-1941    75.249
    3000     01-Feb-2018     0     01-Jan-1941    77.086
    3000     01-Mar-2019     0     01-Jan-1941    78.162
    3001     01-Feb-2011     2     01-Jan-1946    65.086
    3001     01-Mar-2011     1     01-Jan-1946    65.162
    3001     01-May-2011     2     01-Jan-1946    65.329
    3001     01-Aug-2011     2     01-Jan-1946    65.581
    3001     01-Nov-2011     2     01-Jan-1946    65.833
    3001     01-Mar-2012     2     01-Jan-1946    66.164
    3001     01-Sep-2012     2     01-Jan-1946    66.668

Now as I tried plotting Time vs NHY PATNO as group variable

i.e...

plot(T.(5),T.(3), 'Group',T.(1))    

It gives error message...

There is no Group property on the Line class.

Should I post this as another question?

[Star Strider]

As always, my pleasure!

I am not entirely certain what you are doing.

Try this:

[G,ID] = findgroups(T.PATNO);

figure
hold on
for k = 1:numel(ID)
    plot(T{G==k,5}, T{G==k,3}, 'LineWidth',2)
end
grid
legend(string(ID))

That is in addition to my previous code, and should be added to the end of it.

[Vijay]

I know this plotting is weird. But I have a lot of road blocks in the analysis of my research project. I am doing this plotting exercise with a hope to see if I can get some hint.

Your code worked very well! Now I need to see if this can give some intuition.

Thank you so much for your kindness and help. I highly appreciate your time and efforts to help me to sort this.

[Star Strider]

As always, my pleasure!

The plotting is not weird if it does what you want it to.

Thank you!

[Walter Roberson]

Note: years(2000) is 2000 fixed-length years. You should be adding calyears(2000) for calendar years.

[Vijay]

Thank you Walter!

Answer 2

[Walter Roberson]

filename = 'FindDifference.xlsx';
opt = detectImportOptions(filename);
opt = setvaropts(opt, {'BIRTHDT', 'INFODT'}, 'Type', 'datetime');

T.Time=T.BIRTHDT-T.INFODT;

T.Format = 'y';

However, I think you will be rather startled at the results.

The INFODT column has hard-coded into it years such as 0011 . This is not just a mistake of interpretation of numeric values: the column is a text column, not a date column or a numeric column (I checked in Excel.) If someone deliberately wanted to code in year 11 CE then that is probably how they would code it.

I would suggest that probably before doing the subtraction, you should have

T.INFODT = T.INFODT + calyears(1900);

[Vijay]

Hi Walter,

Thank you so much for your time.

Regarding the INFODT, such format is just made by matlab read an excel sheet using readtable function. The recoded dates are in years beyond 2000. I am attaching two individual date files joined together as shown below.

T1 = readtable('INFODT.xlsx') ;
T2 = readtable('BIRTHDT.xlsx');
T = join(T1,T2);
writetable(T,'T.xlsx')

I am computing the difference (i.e. T.Time = T.INFODT - T.BIRTHDT ;) to plot NHY vs the age (i.e. T.Time). But the format issues are not allowing to compute the age.

Coming to your suggested code, it's some how not working to me. Do you think there is an alternative in this case?

[Walter Roberson]

Which MATLAB release are you using? When I try in R2020a (on Mac), the dates associated with INFODT did not get written in the format of the original file you posted. Instead, the INFODT column in T got written as an Excel date with custom format . The original file you posted has, for example, 01-Feb-0011 00:00:00 as a pure text column.

[Vijay]

I have been using 2019b (Windows) which resulted in the format below.

3000 '01-Apr-0016 00:00:00' 0 '01-Jan-1959'

3000 '01-Feb-0011 00:00:00' 0 '01-Jan-1945'

3000 '01-Feb-0013 00:00:00' 0 '01-Jan-1948'

3000 '01-Feb-0018 00:00:00' 0 '01-Jan-1954'

Now, when I tried with 2020a the format became...

3000 'Feb-0011' 0 01-Jan-1941

3000 'Mar-0012' 0 01-Jan-1941

3000 'Feb-0013' 0 01-Jan-1941

3000 'Mar-0014' 0 01-Jan-1941

Not sure why the year is taken as 0011 instead of 2011.

In any case, still I can't subtract the column values.