Importing csv with dates

Hi all. I am trying to import a csv file which includes numbers and dates using Import data > numeric matrix > Generate Function. All goes well, except for the Dates column. The original format is dd/mm/yyyy, but the imported file replaces all the dates as 1,2,3, etc. Restrictions: -I am not allowed to make any changes to the file before importing. -I tried to import using "column vectors" instead of "Numeric Matrix" but this brings the dates in "datetime" vector instead of "double", which causes me lots of troubles when I try to do other functions and codes, so I am trying to keep everything as "double". Many thanks

[dpb]

Why does datetime cause trouble? It's the logical way to handle dates in Matlab.

Show us where you're having trouble using them and explain what is trying to do and bound to be somebody by shortly to show the way...

[D.J]

For example if I want to merge all the imported "Double" vecotrs in 1 matrix, I get the error :Error using datetime/horzcat (line 1210) All inputs must be datetimes or date/time strings. I have attached a photo that shows successfully combining 2 vectors both "Double", and then combining 1 "double" and 1 "datetime" vectors, which comes up with the error I mentioned. Thank you !

[dpb]

Well, datetimes aren't doubles so, no, you can't do that. But, there's no reason to.

I'd suggest using readtable to bring the data in or convert the data to a table or even a timetable perhaps depending on what it is that you're going to be doing.

It would help to see one of the input files and have some description of what your objective(s) is(are). It's hard to show specific examples that are pertinent without some context...

[D.J]

I have attached a sample. The imported would show dates as just numbers: 1,2,3 and if I want to plot the 3 variables, say assigning X to date, I need the date to show as dd/mm/yyyy in the plot. That is why I am trying to convert the imported column "Date" to show proper date format, but no luck. Many thanks in advance !

[Walter Roberson]

To pick one of the dates from that: for 18/07/2017 what double do you want to result?

[D.J]

I am not sure I understood your question. I basically need to :

1- import the file,which I did using Import data > numeric matrix > Generate Function.

2-Plot the data showing the max and min temperature for each date. I used the following code (which I am sure is not the most reasonable one, but couldn't come up with any other way:

A= (1:31);
A';
Date=A';
x=A';
y=Tmin_all;
z=Tmax_all;
plot3(x,y,z);
xlim([1 31])

Attached is the plot i came up with. I need all dates to show, that is why I am trying to solve the problem from the beginning,which is importing dates with the correct format, rather than working around it.

I hope this clarifies things.

Many thanks

[Walter Roberson]

Importing dates with the correct format contradicts your requirement to keep everything double.

When the date 18/07/2017 is imported and you want to get a double out of that, then what value would you want that double to have? Do you want Julian Date? Do you want Excel Date Number (1900)? Do you want Excel Date Number (1904) ? Do you want MATLAB Serial Date Number? Do you want leap years to be handled, or do you want the approximation of fixed length years?

It would be so much easier if you were willing to use datetime objects.

Answer 1

[D.J]

I tried to do the plot as suggested, I am getting the error Unrecognized variable name 'Date'.

This is what I did:

plot(t.Date,[t.MinimumTemperature__C_ t.MaximumTemperature__C_])

[Walter Roberson]

What shows up for

summary(t)

[dpb]

>> type test-data.csv

Date,Minimum temperature (°C),Maximum temperature (°C)
1/7/2017,2.2,19.7
2/7/2017,2,21
3/7/2017,4.9,21.4
...

is the file you uploaded; if others don't follow the identical names, then readtable will import and use variable names that match what is in the file...

Variable names have to be Matlab-allowable variable names in the end so you see the (°C) on the temperatures was turned into _C__. Fortunately, name-completion is active in the command window so you can see/retrieve actual names simply. Or, you can define shorter, more convenient names either on reading the file or afterwards programmatically to handle something like typos or other anomalies in the actual files themselves.

Did readtable return any error message other than a warning about modifying variable names?

t.Properties.VariableNames
t.Properties.VariableDescriptions

will hold the actual names and the originals.

OH!!! You didn't show us the code prior to plot. Did you actually use

t=readtable('yourfile.csv');

to create a table????

ADDENDUM

Remember we can't see your terminal so we can only debug what you post...the exact code you ran and the input data that goes with it are the key ingredients and you didn't provide either...and the crystal ball is in the shop for repair (yet) again! :)

[dpb]

[Answer moved to comment...please use Comments for diaglogue and reserve Answer for actual answer to Q?. Thnx, dpb] blank figure

Sorry guys. Here are more details I am getting now different errors ! My code mentioned below: and the error I get:

Warning: Variable names were modified to make them valid MATLAB identifiers. 
While setting the 'XTick' property of 'Axes':
Value must be a vector of type single or double whose values increase
Error in plot_Test (line 11)
hAx.XTick=t.Date;

I also attached the figure I got when I run the code. Thank you

Testdata = importfile2('Test_data.csv', 2, 32);
t=readtable('Test_data.csv');

Testdata = importfile2('Test_data.csv', 2, 32);
t=readtable('Test_data.csv');

x=t.Date;
y=[t.MinimumTemperature__C_ t.MaximumTemperature__C_];

hAx=gca;
hAx.XTick=t.Date;
hAx.XAxis.TickLabelFormat='d';
hAx.XAxis.FontSize=8;

plot(t.Date,[t.MinimumTemperature__C_ t.MaximumTemperature__C_])
legend('Tmin','Tmax')
ylabel('T, C')

[dpb]

You modified the example code.

There's no need for importdata at all; ditch it, use readtable, it's much easier as my example showed.

The warning is expected; as I mentioned before it's the fixup for there being space and non-valid characters in the header for variable names.

t=readtable('Test_data.csv');

...
x=t.Date;
y=[t.MinimumTemperature__C_ t.MaximumTemperature__C_];

The above is ok, but not needed altho possibly could be handy later but there's really no reason to make unnecessary duplicate copies of the same data; just use the table reference directly unless there are other array operations to be done.

hAx=gca;
hAx.XTick=t.Date;
hAx.XAxis.TickLabelFormat='d';
hAx.XAxis.FontSize=8;

Here you've tried to address the figure before actually plotting anything...better to plot() first, then fixup the details; otherwise auto scaling may change things around on you a little unexpectedly.

plot(t.Date,[t.MinimumTemperature__C_ t.MaximumTemperature__C_])
legend('Tmin','Tmax')
ylabel('T, C')

The error about

While setting the 'XTick' property of 'Axes':
Value must be a vector of type single or double whose values increase

is owing to the order as well...the default axes that is created by

hAx=gca;

when executed before the call to plot with a datetime X array has "ordinary" doubles for the x- and y- axis values--if you just execute the one statement you'll see a bare axis with range 0-1 on both X and Y. When you try to set datetime tick values on that axis, it errors.

The fix is to FIRST plot, THEN fix up.

Revert to the example code I gave you in the above answer and all will be well. You don't need anything more but you do need to use it in the sequence given for it to work correctly.

Your figure is blank because of the error after creating the axis; that's as far as you got successfully.

[dpb]

BTW, if you don't like the warning, two choices --

1. turn warning off before calling readtable, or
2. don't read the headers and name variables yourself

The latter has the advantage you can shorten the exceedingly long and cumbersome temperature names to something much more handy--that would look like

t=readtable('test-data.csv','ReadVariableNames',false,'HeaderLines',1);
t.Properties.VariableNames={'Date','Tmin','Tmax'}
t =
31×3 table
     Date       Tmin    Tmax
  __________    ____    ____
  01/07/2017     2.2    19.7
  02/07/2017       2      21
  03/07/2017     4.9    21.4
  ...

Then you can write much more simply

plot(t.Date,[t.Tmin t.Tmax])

and carry on from there.

[D.J]

Many thanks. That was very helpful!

[dpb]

No problem, but beware of making changes to somebody's example code unless you understand what you're changing and why -- as this shows, it can be critical about the sequence of actions besides just which actions.

If something works as shown, first go back to that working incarnation and then change only what you must to meet your specific requirements...

[D.J]

No worries. Will remember this next time, but I was changing my own code, not someone else's example. But will remember this in the future. Thank you

[dpb]

Ah...all I saw in the sequence was baically my code rearranged... :)

Anyways, it is a somewhat subtle point that is not afaik, ever spelled out in the documentation anywhere about the difference between an explicit or implied call to axes without an argument to define the base type for the x/y-axes and the specific axis that is created by a call to plot with a datetime argument.

Of course, it's apparent from the specific overloaded plot function documentation for it what the resulting axis will be, but that doesn't exactly address the "behind the scenes" axes creation that occurs in your example (and, at least through R2017b, getting to the specific, overloaded plot functions doc page rather than the generic one isn't all that easy, I've intended to comment to TMW on that previously).

Before the introduction of datetime and the overloaded plot functions, dates were handled with the datenum function which just encodes dates as a specially-scaled double--hence the axes for plots which displayed dates/times on the axis labels were still actually plotted as numeric. It was/is the datetick function which is used to modify tick labels after the plot is drawn, but to modify ranges or tick locations one still must use the equivalent datenum value associated with the desired date.

datetime, however, is a full-blown class and as such has its own methods and functions must be written to deal with the class specifically. Hence, when the axes is created by default, the axis is for default double and there's nothing available to allow the callback function method to call the overloaded function to handle a datetime object since it wasn't the overloaded function that created the axes that knows how to interact with the new feature.

This is really very late in the overall history of Matlab; datetime was introduced in R2014b; Mathworks was founded in 1984 so we're talking barely over a tenth of the life span. Of course, some of us real fogies began our experience with MATLAB-PC even before The Mathworks existed. :)

So, for those who have a longer time frame familiarity it's second nature to know these things; if one has just come to Matlab with a recent release where all these data types are available, there's no way to know anything at all about the path by which arrived at the present situation and that all this is going on behind the scenes.

I don't know if it's feasible to make a "fix" that your sequence would work automagically or not; I'll probably craft an enhancement request for consideration in the documentation, anyway, to point it out. The problem there is that there is just so much documentation that the likelihood of it being found first before the error is essentially zero and whether one could find it to discover the cause after the fact is also questionable.

[D.J]

Many thanks @dpb for the thorough explanation. It is certainly useful to know the history of the function.

[dpb]

It came to me somewhat later that it really is a more subtle error and since my chastisement came out a little more harsh than intended :) that it made some sense to outline the root cause and how it came to be so then seemed a natural extension. I'm sure it will trip up a lot of new users in one form or another with similar not unreasonable expectations that are not met owing to the class difference of the inputs.

Answer 2

[dpb]

t=readtable('test-data.csv');
plot(t.Date,[t.MinimumTemperature__C_ t.MaximumTemperature__C_])
legend('Tmin','Tmax')
ylabel('T, C')

created

datetimes are your friends... :)

And, if you really, really think you must have every day shown on the axis,

hAx=gca;
hAx.XTick=t.Date;
hAx.XAxis.TickLabelFormat='d';
hAx.XAxis.FontSize=8;

after the above produces

Are you still sure you really want to try to convert dates to doubles???

Did I mention datetime is your friend, yet?

Answer 3

[D.J]

You are a gem ! Thank you so much !

[D.J]

Did I mention that I LOVE datetimes ! :-)

[dpb]

No problem, glad to help... :)

There's so much in Matlab it's hard to know where to start to get to end quickest route, granted. But as a general rule, when you start running into issues such as you were, it's generally a good indication you're not using the right approach so stepping back and looking for alternatives is a good idea when that happens instead of just trying to beat Matlab into submission in an arbitrary direction.

The builtin datetime -aware plot routines are a relatively recent introduction to Matlab (prior to that there were datenum and a klunky companion routine to convert the internal representation as doubles to date strings) but it is one thing that has been a real step forward for ease of use for most uses.

The table is another...