How to convert text to time and merge two seperate date and time columns in readtable

Hi,

Before I ask my question I have to state that I have searched forums a lot and can not compile all the information I need.

I have attached a sample of my file which has date and hour as the first and second columns. I will be needing these timestamps combined for my analysis.

I need to first convert that time data which reads as Hour with date information. but I think matlab does not see it in a date format as I attached how data shows when I use timetable, as u can see it reads hour with extra '' . I was not successful converting that column to a time information..

Si first I need to make sure readtable reads that second column in time format.

Then I need to merge these two date and time information in seperate colums so I can have something in the following format:

dd/mm/yyyy hh:mm

After that I need to add 7 hours to each of the timestamp data (Maybe this is another question)

I need to have a table2timetable in the end so I can aggregate data.

Below is my code.

I have multiple files like this and each file has around 300K rows and 10 columns so import data is out of question for me as well

I am using matlab 2017b.

filename='AUDUSD.csv';
aud = readtable(filename);
newaud=datetime (aud{:,2},'InputFormat','yyyy/MM/dd/HH:mm');
 
audtry =  table2timetable(newaud);
 

Error using datetime (line 616)

Unable to parse date/time text using the format 'yyyy/MM/dd/HH:mm'.

Error in Untitled8 (line 4)

newaud=datetime (aud{:,2},'InputFormat','yyyy/MM/dd/HH:mm');

[Guillaume]

Can you supply the original AUDUSD.csv file?

[Steph]

So I changed the code to the following:

and it gives me merged date and time bbut starting with 2018, today, not with the original data starting day which is 2014/12.

filename='AUDUSD.csv';

aud = readtable(filename);

newaud=datetime (aud{:,2},'InputFormat','HH:mm:ss');

audtry = table2timetable(newaud);

[Steph]

Dear Guillaume,

Original file is of size 47gb,

mathworks wouldnt let me upload it to here.

[Guillaume]

Just trim it. We only need the first 10 lines.

Answer 1

[Steph]

I added the trimmed file,

I playe around a bit, now I have a datetime with the incorrect time stamp for years which has the correct hour. So it is 2018 year with today's date repeated for all rows with the correct hours.

and also a table that has the correct year but not reading hour data correctly.

I am working on trying to merge these two but failing so far.

Answer 2

[Guillaume]

Assuming you're using R2018a or later, the 1st column will be read as a datetime array and the 2nd one as a duration array. In which case:

aud = readtable('AUDUSD.csv');
aud = [table(aud.DATE + aud.HOUR, 'VariableNames', {'DateTime'}), aud(:, 3:end)];
aud = table2timetable(aud);

On earlier versions, the HOUR column should be read as text (I assume), in which case:

aud = readtable('AUDUSD.csv');
aud[table(aud.DATE + duration(aud.HOUR), 'VariableNames', {'DateTime'}), aud(:, 3:end)];
aud = table2timetable(aud);

should work (untested since I don't have an older version installed anymore).

[Steph]

Thank you very much for your effort, I am using 2017b and unfortunately the second code you wrote did not work. I am getting an error of unbalanced or unexpected paranthesis error.

I added the equall sign after aud on the second line, then it said the input data must be a numeric matrix with three columns, or three seperate numeric arrays.

Answer 3

[Peter Perkins]

Steph, in R2018b, you can read the file:

>> type tmp1.csv
2014-12-14,"17:00:00",1,2
2014-12-14,"17:05:00",3,4
2014-12-14,"17:10:00",5,6

and convert the times of day to durations. You have to explicitly remove the single quotes - the 'QuotesRule' option used by detectImportOptions and friends only deals with double quotes. I think single quotes in a CSV file is unusual. Anyway:

>> t = readtable("tmp1.csv");
>> t.Properties.VariableNames = ["Date" "Time" "X" "Y"];
>> t.Time = erase(t.Time,"'");
>> t.Time = duration(t.Time)
t =
  3×4 table
       Date         Time      X    Y
    __________    ________    _    _
    2014-12-14    17:00:00    1    2
    2014-12-14    17:05:00    3    4
    2014-12-14    17:10:00    5    6

If you are on an earlier version, try text2duration from the file exchange. In even earlier versions, readtable may read in the date stamps as text, you can convert those to datetimes after reading.

[Steph]

Hi Peter,

Thank you very much for your effort. The reason I have the single quotes at the beginning and ending is that the data was converted to cvs after some processing in a seperate programme(java). I couldnt make the code u wrote work in 2017b. (the version I am using)

I am gettting the following error:(I included original column names in the code line

The variablenames property must be a cell array, with each element containing one nonempty character vector.

[Peter Perkins]

In 17b, replace double quotes with single. In particular, {'Date' 'Time' 'X' 'Y'} (braces, not []) and erase(t.Time,'''') (yes, that's four single quotes in a row).