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

2 次查看（过去 30 天）
Steph
Steph 2018-12-13
编辑： Peter Perkins 2018-12-19
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 个评论
显示 2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

回答（3 个）

Steph
Steph 2018-12-13
编辑：Steph 2018-12-13
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.
Guillaume
Guillaume 2018-12-13
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).
  1 个评论
Steph
Steph 2018-12-18
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.

请先登录，再进行评论。

Peter Perkins
Peter Perkins 2018-12-18
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 个评论
Steph
Steph 2018-12-18
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
Peter Perkins 2018-12-18
编辑：Peter Perkins 2018-12-19
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).

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by