How do I import data with date and time information into Mathematica?

Questions
, ,
1

This has got to be a question asked by every new user, and as a new user, I am asking it after having struggled through a lot of Mathematica documentation, uTube videos, Google searches, and trial and error mucking around in Mathematica.

I want to import *.csv files that have date and time and data in the (almost) universal format of:

{mm/dd/yyyy, hh:mm:ss, element1, element2, etc.}

Being an Excel-minded person, I think of the mm/dd/yyyy as the integer part and the hh:mm:ss as the fractional part of a single number that can be used as the x-axis of a plot. I have figured out how to separately extract the date and time fields from an imported data file, but it seems to me that there must be a SIMPLE way for Mathematica to combine these two pieces of information into the preferred Mathematica format: {yyyy, mm, dd, hh, mm, ss}.

THERE IS NO FINDABLE DOCUMENTATION FOR THIS SIMPLE TASK.

Just for the fun of it, I have attached a typical *.csv file of the type I’m trying to import.

Any suggestions?

2

Try this:

data = Rest@Import["/Users/dreiss/Desktop/TestDataNov2013.csv"];

data = data /. {d_String, t_String, z__} :> {DateList[d <> ":" <> t],  z};

The important point in this example is that, indeed, Mathematica does have powerful facilities to handle the date time format that is embedded in your example (i.e., DateList). However, in this particular example the day and time are in separate columns, so you need to let Mathematica know that they need to be combined, otherwise they are distinct data sources.

3

David,
Thanks for getting back to me on this. Here is what I have run and gotten in return:

In[25]:= path = “C:\Users\User\Desktop\TestDataNov2013.csv”

Out[25]= “C:\Users\User\Desktop\TestDataNov2013.csv”

In[32]:= data = Rest@Import[path]; data =
data /. {d_string, t_string, z_} :> {DateList[d <> “:” <> t], z}

Out[32]=
{{“11/4/2013”, “9:05:45”, 80.75, 9.4},
{“11/4/2013”, “11:06:21”, 78.09, 9.4},
{“11/4/2013”, “12:40:52”, 76.9, 9.7},
{“11/4/2013”, “15:02:47”, 74.27, 9.6},
{“11/4/2013”, “15:43:46”, 75.16, 10.}, etc.

This looks a lot like what I get when I simply import the *.csv:

In[38]:= myDate = Import[path]

Out[38]= {{“?Date?”, “?Time?”, “?Temperature?”, “?Flow Rate?”},
{“11/4/2013”, “9:05:45”, 80.75, 9.4},
{“11/4/2013”, “11:06:21”, 78.09, 9.4},
{“11/4/2013”, “12:40:52”, 76.9, 9.7},
{“11/4/2013”, “15:02:47”, 74.27, 9.6},
{“11/4/2013”, “15:43:46”, 75.16, 10.}, etc.

What I think I’m trying to get to is:

{{y, m, d, h, m, s}, temperature, flowrate}

where the date and time are combined into a format that Mathematica can use for making plots of data.

I’m still missing something.

4

You have only executed the first line of the code that I posted. You need to execute both lines. The first line imports the data, and the second line causes the date to be generated in the form that you indicate.

5

Sorry, I have error (WM 9.0.1, Win7,x64):
DateList::ambig: Warning: the interpretation of the string 11/4/2013:9:05:45 as a date is ambiguous. >>

6

Yes that is normal. It is not an error, it is a warning. It is there because the way when interprets a date like 2/3/2014 is ambiguous. In North America it is interpreted one way and in Europe it is interpreted differently. The function DateList can be specified to force one specific interpretation and this is discussed in its documentation.
If you look at the output of your running the code, you will see that everything has turned out the way you wanted it to.

7

Thank you for explanation.

8

Happy to help. :slight_smile:

9

I executed your suggestion again from scratch with the same result (with the difference that I truncated the data file to make things easier to handle):

 In[1]:= path = "C:\\Users\\User\\Desktop\\ShortTestDataNov2013.csv"
    
    Out[1]= "C:\\Users\\User\\Desktop\\ShortTestDataNov2013.csv"
    
    In[2]:= data = Rest@Import[path]
    
    Out[2]= {{"11/4/2013", "9:05:45", 80.75, 9.4}, {"11/4/2013", 
      "11:06:21", 78.09, 9.4}, {"11/4/2013", "12:40:52", 76.9, 
      9.7}, {"11/4/2013", "15:02:47", 74.27, 9.6}, {"11/4/2013", 
      "15:43:46", 75.16, 10}, {"11/4/2013", "17:24:29", 73.66, 11.4}, {""}}

In[3]:= data = 
 data /. {d_String, t_String, z_} :> {DateList[d <> ":" t], z}

Out[3]= {{"11/4/2013", "9:05:45", 80.75, 9.4}, {"11/4/2013", 
  "11:06:21", 78.09, 9.4}, {"11/4/2013", "12:40:52", 76.9, 
  9.7}, {"11/4/2013", "15:02:47", 74.27, 9.6}, {"11/4/2013", 
  "15:43:46", 75.16, 10}, {"11/4/2013", "17:24:29", 73.66, 
  11.4}, {""}}

I don’t see what I’m doing wrong.

10

You copied the code incorrectly it is

data = 
  data /. {d_String, t_String, z__} :> {DateList[d <> ":" <> t], z}

rather than

 data = 
 data /. {d_String, t_String, z_} :> {DateList[d <> ":" t], z}

There are two underscores after the z and there is a <> between the “:” and the t.

11

It worked!

Now my question is why something so simple and obvious is not a built-in function in Mathematica.

Thanks so much for your help. I’ll probably be back.

Chuck

12

But it is built in to Mathematica: it’s called DateList :wink:

In your particular case, as I mentioned, there is no way for Mathematica to sensibly make the decision that your two columns in the Excel spreadsheet should be combined into one.

So, remembering that the programming language behind Mathematica is the Wolfram Language, one just writes a one-line piece of code to achieve the desired interpretation of the data that you supply to it.

How many lines in C++?

Enjoy!

13

I know this was a year ago but i had a few beginner questions to help me understand Mathematica. The online help isnt all that helpful.

  1. What does “Rest@Import” do exactly? I see that Rest imports without the first item, but I cant find what “@” does and how it relates to Import. There is a reference to @ being a prefix but really hard to understand form the online help what it does.

  2. Is “d_String, z_String” a command in your code? both d and z are lower case so I dont think they are commands but String is uppercase so not sure if that is a Mathematica built-in function and if so, what does this do? Is this a defined variable or function?

  3. What does the extra underscore for “z__” do? I thought that a single underscore was a function but not sure in this case what exactly is happening with the second underscore

  4. And what does “:>” mean? I searched on it and came up with nothing

  5. I know that <> is to join but still trying to understand what that second line is doing step by step.

  6. Finally I copied the example and it ran perfectly. But I wasn’t able to to a plot using DateListPlot function, I tried and it didnt work. It did work when I took out the second variable and only had a single date with a single variable. So how do I get it to plot when the data is formatted like your example, {{date time }, variable1, variable2}, {date time stamp}, variable 1, variable 2} etc. it didnt seem to recognize that the same date/time stamp applied to two plotting variables (results). I looked and didn’t see how to get it to recognize the format. There should be two different lines plotted using the same date/time stamp

Thanks for any thoughts, I know these are beginner questions.

14

This is an older topic but when I search for this issue, this discussion still features high in the results list. Hence, although I am but a basic user of Mathematica, I thought it would be handy to share how I solved this in this specific discussion.

I had this problem last year as well, except I had “yyyy/mm/dd”. I used the solution with success. However, the 15+ million records took nearly three hours to process(on my 2012 MacBook pro). Once done I saved it as an .mx file and used that ever since.

Recently, I needed to reload the data so I decided to try a different approach (I only have one data element besides the time stamp):

result = {Flatten[{ToExpression[StringSplit[#[[1]], "/"]],ToExpression[StringSplit[#[[2]],":"]]}], #[[3]]} & /@ data

Which works with “yyyy/mm/dd”,“hh:mm:ss” and as a bonus only took 10 min to run (about 17.5 times faster). For “mm/dd/yyyy” a quick re-ordering of the first StringSplit is probably needed.

I have no idea if using some sort of date object would make selecting specific ranges (such as “all records between 9am and 10 am for the month of March”, but having the datetime as a list of numbers means I can do it programatically. But this is a problem for another discussion.