How to convert a column in matrix from string to numeric

4 vues (au cours des 30 derniers jours)
Stepan Subik
Stepan Subik le 13 Mar 2020
Commenté : Stepan Subik le 15 Mar 2020
I am having a trouble of converting a string column in a table to a numeric type. I have a table imported from .csv file containing 6 columns with headers and 4 of them should be numeric and one is date in format yyyyMMddhhmm, delimitator is ','.
When I load it using readtable, it assumes that everything is a string and I can't convert the 4 columns to a numeric type. I have tried to convert it through many something2somethingelse converters, did not work.
I tried even:
data = readtable(['data' cell2mat(indexVars(i))], 'Format','%s%f%{yyyyMMddhhmm}D%f%f%f');
It returned:
Error using readtable (line 216)
Unable to read the entire file. You may need to specify a different format, delimiter, or number of header
lines.
Note: readtable detected the following parameters:
'Delimiter', ',', 'HeaderLines', 0, 'ReadVariableNames', true
Any ideas?
  3 commentaires
Stepan Subik
Stepan Subik le 13 Mar 2020
Here is a part of the file
dpb
dpb le 13 Mar 2020
Modifié(e) : dpb le 14 Mar 2020
As suspectected, the hours are 0-23 hours; the last two records of the file time field are
194905011800
194905011900
which are 18 and 19 hours in 24-hr time, not 12-hr time so must use 'HH' instead of 'hh'
>> datetime('194905011800','InputFormat','yyyyMMddhhmm')
Error using datetime (line 636)
Unable to convert '194905011800' to datetime using the format 'yyyyMMddhhmm'.
>>
>> datetime('194905011800','InputFormat','yyyyMMddHHmm')
ans =
datetime
01-May-1949 18:00:00
>>
See the doc for datetime format strings for all the details.

Connectez-vous pour commenter.

Réponse acceptée

dpb
dpb le 13 Mar 2020
Modifié(e) : dpb le 13 Mar 2020
data = readtable(['data' cell2mat(indexVars(i))], 'Format','%s%f%{yyyyMMddhhmm}D%f%f%f');
'hh' is for 12-hr day; 'HH' is for 24-hr day. If is 12-hour, need the AM/PM suffix is probably the problem I'd guess.
But, you didn't show us a sample of the input time string to be able to confirm just what is the actual input format being used; but from the verbal description would seem to have to have used 24-hr days. So, use 'HH' instead.
BTW, if you have R2016(b?) or later, detectImportOptions often will do better as does more thorough probing of the file than does readtable on its own.
ADDENDUM:
As suspected, use 'HH' instead of 'hh' to match the input file date format (24hr clock)
>> doc datetime
>> datetime('194905011800','InputFormat','yyyyMMddhhmm')
Error using datetime (line 636)
Unable to convert '194905011800' to datetime using the format 'yyyyMMddhhmm'.
>> datetime('194905011800','InputFormat','yyyyMMddHHmm')
ans =
datetime
01-May-1949 18:00:00
>>
I'll grant seems like the error message could be far more informative than is and in this case tell you precisely what was wrong. In more general case at least could identify which field it failed on.
  3 commentaires
dpb
dpb le 13 Mar 2020
Did you just fix the format string as I suggested and retry?
If by "it" you mean detectImportOptions as described it probes the file more deeply than does readtable and has better chance thereby of determing the data/time format automagically. You pass the returned object.
See the doc on both...
Stepan Subik
Stepan Subik le 15 Mar 2020
Thank you, the detectImportOptions did the job ;-)

Connectez-vous pour commenter.

Plus de réponses (0)

Catégories

En savoir plus sur Logical dans Help Center et File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by