How to convert text to time and merge two seperate date and time columns in readtable
2 views (last 30 days)
Show older comments
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');
4 Comments
Answers (3)
Guillaume
on 13 Dec 2018
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).
Peter Perkins
on 18 Dec 2018
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.
2 Comments
Peter Perkins
on 18 Dec 2018
Edited: Peter Perkins
on 19 Dec 2018
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).
See Also
Categories
Find more on Data Type Conversion 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!