Undefined operator '-' for input arguments of type 'cell'.
Show older comments
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.
Accepted Answer
Star Strider
on 15 Sep 2020
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.
10 Comments
Vijay
on 15 Sep 2020
Star Strider
on 15 Sep 2020
‘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.
.
Star Strider
on 15 Sep 2020
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
on 15 Sep 2020
Star Strider
on 15 Sep 2020
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
on 15 Sep 2020
Star Strider
on 15 Sep 2020
As always, my pleasure!
The plotting is not weird if it does what you want it to.
Thank you!
Walter Roberson
on 15 Sep 2020
Note: years(2000) is 2000 fixed-length years. You should be adding calyears(2000) for calendar years.
Vijay
on 17 Sep 2020
More Answers (1)
Walter Roberson
on 15 Sep 2020
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);
3 Comments
Vijay
on 15 Sep 2020
Walter Roberson
on 15 Sep 2020
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.
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
