Transform a matrix datetime in a one collumn datetime

Hello, I have a water level data in a matrix format, which each line represent the month and each collumn (from 1 to 47) the day of that month. I'd like to transform that matrix in one single collumn, in order to plot a time series of that data. However I have two kinds of gaps: the first one is because the months has different lengths (28,29,30,31); and the other class of gaps is a real gap, because there was problem with the sensor. So, I'm trying to concatenate all the lines data in one collumn, but without considering the first class of gap. Otherwise, the time of that data would be wrong. Does anybody could help?
obs: I'm using just the data with MediaDiaria ==1 1 (DailyMean) and NivelConsistencia == 1 (Consistency Level)
Kindest regards,
Felipe
This is the code that I've done up to now:
filename = ['C:\Users\felip\OneDrive\Desktop\Estacoes_ANA\Todas estacoes cotas 2\']
arquivo = [filename,num2str(2),'.csv'];
tabela = readtable(arquivo);
data = tabela.Data;
data_ini_all = find(data == '01/01/2022');
data_ini_a = data_ini_all(1);
data_fim_all = find(data == '01/01/2002');
if length(data_fim_all) > 1
data_fim_a = data_fim_all(end-1,1);
end
if length(data_fim_all) == 1
data_fim_a = data_fim_all(1,1);
end
tabela_sel = tabela(data_ini_a:data_fim_a,:);
ind_media = tabela_sel.MediaDiaria;
ind_consist = tabela_sel.NivelConsistencia;
index = find(ind_media == 1 & ind_consist == 1 );
nivel = tabela_sel(index,17:47);
teste = tabela_sel(index,:);
nivel_num = table2array(nivel)';
nivel_collumn = nivel_num(:);
nivel_certo = flip(nivel_collumn);
tempo = linspace(2002,2022,length(nivel_collumn))';
plot(tempo,nivel_certo,'k')
xlim([tempo(1) tempo(end)])
ylim([0 700])
grid on

1 Comment

The 'InputFormat' would appear to be 'dd/MM/yyyy' however parsing this file is going to be a definite challenge —
arquivo = 'https://www.mathworks.com/matlabcentral/answers/uploaded_files/1326615/2.csv';
C = readcell(arquivo)
C = 1990×17 cell array
Columns 1 through 10 {'Sistema' } {'de' } {'Informações' } {'Hidrológicas'} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'Versão' } {'Web' } {[ 3]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'©' } {[ 2018]} {'Agência' } {'Nacional' } {'de' } {'Águas' } {'(ANA)' } {[<missing>]} {[<missing>]} {[<missing>]} {'NivelConsistenci…'} {[ 1]} {'=' } {'Bruto,' } {[ 2]} {'=' } {'Consistido'} {[<missing>]} {[<missing>]} {[<missing>]} {'MediaDiaria:' } {[ 0]} {'=' } {'Não,' } {[ 1]} {'=' } {'Sim' } {[<missing>]} {[<missing>]} {[<missing>]} {'TipoMedicaoCotas:'} {[ 1]} {'=' } {'Escala,' } {[ 2]} {'=' } {'Linígrafo,'} {[ 3]} {'=' } {'Data' } {'Status:' } {[ 0]} {'=' } {'Branco,' } {[ 1]} {'=' } {'Real,' } {[ 2]} {'=' } {'Estimado,'} {'Restrições' } {'da' } {'consulta:' } {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'Código' } {'da' } {'Estação:66090000'} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'EstacaoCodigo;Ni…'} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'66090000;1;01/06…'} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'66090000;1;01/06…'} {'07:00:00;0;1;380…'} {[<missing> ]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'66090000;1;01/06…'} {'17:00:00;0;1;380…'} {[<missing> ]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'66090000;1;01/05…'} {'07:00:00;0;1;430…'} {[<missing> ]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'66090000;1;01/05…'} {'17:00:00;0;1;430…'} {[<missing> ]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} {'66090000;1;01/05…'} {[<missing> ]} {[<missing> ]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing> ]} {[<missing>]} {[<missing>]} {[<missing>]} Columns 11 through 17 {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {'Logger' } {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[ 3]} {'=' } {'Duvidoso,'} {[ 4]} {'=' } {'Régua' } {'Seca' } {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]} {[<missing>]}
C{1:20,1}
ans = 'Sistema'
ans = 'Versão'
ans = '©'
ans = 'NivelConsistencia:'
ans = 'MediaDiaria:'
ans = 'TipoMedicaoCotas:'
ans = 'Status:'
ans = 'Restrições'
ans = 'Código'
ans = 'EstacaoCodigo;NivelConsistencia;Data;Hora;MediaDiaria;TipoMedicaoCotas;Maxima;Minima;Media;DiaMaxima;DiaMinima;MaximaStatus;MinimaStatus;MediaStatus;MediaAnual;MediaAnualStatus;Cota01;Cota02;Cota03;Cota04;Cota05;Cota06;Cota07;Cota08;Cota09;Cota10;Cota11;Cota12;Cota13;Cota14;Cota15;Cota16;Cota17;Cota18;Cota19;Cota20;Cota21;Cota22;Cota23;Cota24;Cota25;Cota26;Cota27;Cota28;Cota29;Cota30;Cota31;Cota01Status;Cota02Status;Cota03Status;Cota04Status;Cota05Status;Cota06Status;Cota07Status;Cota08Status;Cota09Status;Cota10Status;Cota11Status;Cota12Status;Cota13Status;Cota14Status;Cota15Status;Cota16Status;Cota17Status;Cota18Status;Cota19Status;Cota20Status;Cota21Status;Cota22Status;Cota23Status;Cota24Status;Cota25Status;Cota26Status;Cota27Status;Cota28Status;Cota29Status;Cota30Status;Cota31Status'
ans = '66090000;1;01/06/2022;;1;1;380;334;352;18;6;0;0;0;;0;340;339;337;336;335;334;334;337;340;341;340;343;352;360;367;372;376;380;379;375;369;364;360;359;355;352;351;347;343;339;;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;'
ans = '66090000;1;01/06/2022;01/01/1900'
ans = '66090000;1;01/06/2022;01/01/1900'
ans = '66090000;1;01/05/2022;01/01/1900'
ans = '66090000;1;01/05/2022;01/01/1900'
ans = '66090000;1;01/05/2022;;1;1;430;340;369;1;31;0;0;0;;0;429;422;413;404;400;396;381;385;387;386;386;387;380;377;366;359;357;355;353;351;349;347;345;344;343;342;342;341;341;341;340;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
ans = '66090000;1;01/04/2022;;1;1;506;435;490;2;30;0;0;0;;0;506;506;506;505;505;505;505;503;503;502;502;500;500;500;499;498;496;495;492;491;489;486;484;481;477;472;465;456;447;437;;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;'
ans = '66090000;1;01/04/2022;01/01/1900'
ans = '66090000;1;01/04/2022;01/01/1900'
ans = '66090000;1;01/03/2022;01/01/1900'
... etc.
.

Sign in to comment.

Answers (1)

Thank you Star Stride for the answer,
Yes, it is a challenge. I've used "readtable" instead of "readcell". I think it is easier to visualize the data.
To simplify the table, I've deleted the collumn that I'm not using. Using it, the only change in the code would be:
arquivo = [filename,'table2_simplified','.csv'];
instead of
arquivo = [filename,num2str(5),'.csv'];
and
nivel = tabela_sel(index,4:34);
instead of
nivel = tabela_sel(index,17:47);
It might help, but I still don't know the final solution

Products

Release

R2023a

Asked:

on 16 Mar 2023

Answered:

on 16 Mar 2023

Community Treasure Hunt

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

Start Hunting!