Trouble with date conversion
2 次查看(过去 30 天)
显示 更早的评论
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 个评论
Peter Perkins
2023-11-10
"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.
采纳的回答
Les Beckham
2023-11-7
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 个评论
Peter Perkins
2023-11-10
Probably best to then create TT as
TT = timetable(T,RowTimes=DateTime)
(Also best to not name variable quite so similarly to classes.)
更多回答(2 个)
Stephen23
2023-11-7
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
2023-11-10
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 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Text Files 的更多信息
产品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!