Trouble with date conversion
5 views (last 30 days)
Show older comments
I have a csv file in which the date is in the format YYYYMMDD for example 19910101 the nextr row has the hour. I cant manage to convert this tot three columns containing Year, month , day. Now I have imported the column als tekst How can I convert it to three Columns showing year, month day?
I dont have much experience with datasets yet.
the file is from the KNMI uurgeg_310_1991-2000.txt
%% Set up the Import Options and import the data
opts = delimitedTextImportOptions("NumVariables", 25);
% Specify range and delimiter
opts.DataLines = [34, Inf];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["Var1", "YYYYMMDD", "HH", "DD", "Var5", "FF", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "Var14", "P", "Var16", "Var17", "Var18", "Var19", "Var20", "Var21", "Var22", "Var23", "Var24", "Var25"];
opts.SelectedVariableNames = ["YYYYMMDD", "HH", "DD", "FF", "P"];
opts.VariableTypes = ["char", "char", "double", "double", "char", "double", "char", "char", "char", "char", "char", "char", "char", "char", "double", "char", "char", "char", "char", "char", "char", "char", "char", "char", "char"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Specify variable properties
opts = setvaropts(opts, ["Var1", "YYYYMMDD", "Var5", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "Var14", "Var16", "Var17", "Var18", "Var19", "Var20", "Var21", "Var22", "Var23", "Var24", "Var25"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["Var1", "YYYYMMDD", "Var5", "Var7", "Var8", "Var9", "Var10", "Var11", "Var12", "Var13", "Var14", "Var16", "Var17", "Var18", "Var19", "Var20", "Var21", "Var22", "Var23", "Var24", "Var25"], "EmptyFieldRule", "auto");
opts = setvaropts(opts, ["HH", "DD", "FF", "P"], "ThousandsSeparator", ",");
% Import the data
tbl = readtable("D:\01 werk in uitvoering\uurgeg_310_1991-2000.txt", opts);
%% Convert to output type
YYYYMMDD = tbl.YYYYMMDD;
HH = tbl.HH;
DD = tbl.DD;
FF = tbl.FF;
P = tbl.P;
%% Clear temporary variables
clear opts tbl
5 Comments
Peter Perkins
on 10 Nov 2023
"I cant manage to convert this tot three columns containing Year, month , day"
Hard to say without context, but you may nt want to do that. Once you have a datetime (as Les shows), likely you can do what you need to without separate date components.
Accepted Answer
Les Beckham
on 7 Nov 2023
See below.
opts = delimitedTextImportOptions;
opts.DataLines = 34;
opts.VariableNames = {'STN', 'YYYYMMDD', 'HH', 'DD', 'FH', 'FF', 'FX', 'T', 'T10N', 'TD', 'SQ', 'Q', 'DR', 'RH', 'P', 'VV', 'N', 'U', 'WW', 'IX', 'M', 'R', 'S', 'O', 'Y'};
opts.VariableTypes = {'double', 'char', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double'};
% numel({'double', 'datetime', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double', 'double'})
T = readtable('uurgeg_310_2021-2030.txt', opts)
DateTime = table(datetime(T.YYYYMMDD, 'InputFormat', 'yyyyMMdd') + hours(T.HH));
T = [DateTime T];
T = renamevars(T, 'Var1', 'DateTime')
1 Comment
Peter Perkins
on 10 Nov 2023
Probably best to then create TT as
TT = timetable(T,RowTimes=DateTime)
(Also best to not name variable quite so similarly to classes.)
More Answers (2)
Stephen23
on 7 Nov 2023
Rather than fiddling around with text or numerics, just import the 2nd column as DATETIME right from the start:
fnm = 'uurgeg_310_2021-2030.txt';
opt = detectImportOptions(fnm, 'Delimiter',',', 'Range',32, 'VariableNamingRule','preserve');
opt = setvartype(opt, 'YYYYMMDD','datetime');
opt = setvaropts(opt, 'YYYYMMDD','InputFormat','uuuuMMdd');
tbl = readtable(fnm,opt)
"I cant manage to convert this tot three columns containing Year, month , day"
[tbl.Year,tbl.Month,tbl.Day] = ymd(tbl.YYYYMMDD);
tbl % scroll to the right
Peter Perkins
on 10 Nov 2023
Worth saying that if the file was a spreadsheet and contained things like 20230101 as numeric values, there's a conversion for that too:
datetime(20230102,ConvertFrom="yyyymmdd")
0 Comments
See Also
Categories
Find more on Time Series Objects in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!