Matlab dates and time not aligning after combining

14 Nov 2023
1 Réponse
Réponse acceptée
Mise à jour 16 Nov 2023
6 Vues (30 jours)

0 votes

My end goal is to plot discharge over time with a large dataset. The sample interval for discharge is in minutes and changes from every 5 hours to every 5 minutes a couple years into data collection. I have created a table containing date and time combined and discharge. For some reason when I do this minutes will return to 0 randomly. I am not sure how to fix this so my plot will look normal. The file is too large I cannot import it so I attcahed a screenshot of the original data table and of T. Thanks in advance!!
dataTable = readtable(csvFilePath);
Date=datetime(dataTable{:,16})+minutes(dataTable{:,9});
Discharge=dataTable(:,10);
Q =[Discharge]
Hour=dataTable(:,9)
T_cell = table2array(Q);
Hour_cell=table2array(Hour);
times = datenum(Hour_cell);
t = table(Date,times);
dates = datetime(t.Date,'Format','MM/dd/yyyy HH:mm:SS');
times = datetime(t.times,'ConvertFrom','datenum','Format','MM/dd/yyyy HH:mm:SS');
fullt = dates+timeofday(times);
t.DatesNTimes = fullt;
T=table(fullt, T_cell);

3 commentaires
Afficher 1 commentaire plus ancien Masquer 1 commentaire plus ancien

Walter Roberson
Walter Roberson le 14 Nov 2023
times = datenum(Hour_cell);
Why are you doing that? Your Hour_cell is an array of numbers like 900 and 1200. datenum() with numeric inputs expects that the inputs are serial date numbers -- number of full days since 00-Jan-0000 . With single column numeric input, the output would be the same number. What is the point ??
Callie
Callie le 14 Nov 2023
Sorry I am new to Matlab. Instead of using the times part I stuck with Hour_cell and still got the same result.
Les Beckham
Les Beckham le 14 Nov 2023
Can you post a subset of the "too large" dataset that illustrates the issue?

Connectez-vous pour commenter.

Connectez-vous pour répondre à cette question.

 Réponse acceptée

Cris LaPierre
Cris LaPierre le 15 Nov 2023
Modifié(e) : Cris LaPierre le 15 Nov 2023
Ran in:

1 vote

Ran in:
One issue is that your RecHour variable is not minutes. It is time of day in military time format (no colon. Should be 4 numbers, but that's likely lost due to converstion to numeric data type). The point is it is not appropriate to use minute(dataTable{:,9}). This is why your results in row 6 are now on a different day than the original data indicates.
Also, readtable has already converted date to a datetime. No need to do this a second time.
times is created ultimately from RecHours. There is no date information here, so it doesn't make sense to use datetime to create times. It addes a meaningless date to the times. Why do this? You end up ignoring the date anyway.
Finally, you come full circle and create fullt, which appears to just be a recreation of what your 2nd line of code creates, but with a lot of intrigue and mystery behind it.
I don't see any data in 5 minute increments, so can't observe what may be happening there.
Try this updated code and let us know if the issue persists.
% Load csv file (created using Excel's Import from Image feature)
dataTable = readtable('discharge.csv');
% combine date and time to new table variable
dataTable.RecHour = hours(dataTable.RecHour/100);
dataTable.Date=dataTable.date+dataTable.RecHour
dataTable = 24×17 table
Varl DataCod Recl RecYear RecMonth Recday Watershed DayofYear RecHour Discharge Shei Corr H eight Qual date Date ____ ___________ ____ _______ ________ ______ __________ _________ _______ _________ ____ _________ _ _______ _______ ___________ ____________________ 1 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 0 hr 0.003 0 {'5...' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 00:00:00 2 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 3 hr 0.003 0 {'4..' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 03:00:00 3 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 6 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 06:00:00 4 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 9 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 09:00:00 5 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 12 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 12:00:00 6 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 15 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 15:00:00 7 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 18 hr 0.003 0 {'4...' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 18:00:00 8 {''ASD05''} 1 1987 1 1 {''NOIB''} 1 21 hr 0.003 0 {'4..' } 0 {''.''} {''.''} 01-Jan-1987 01-Jan-1987 21:00:00 9 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 0 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 00:00:00 10 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 3 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 03:00:00 11 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 6 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 06:00:00 12 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 9 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 09:00:00 13 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 12 hr 0.003 0 {'4...' } 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 12:00:00 14 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 15 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 15:00:00 15 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 18 hr 0.003 0 {'4....'} 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 18:00:00 16 {''ASD05''} 1 1987 1 2 {''NOIB''} 2 21 hr 0.003 0 {'4..' } 0 {''.''} {''.''} 02-Jan-1987 02-Jan-1987 21:00:00
dataTable.Date.Format = 'MM/dd/yyyy HH:mm:ss';
% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])
T = 24×2 table
Date Discharge ___________________ _________ 01/01/1987 00:00:00 0.003 01/01/1987 03:00:00 0.003 01/01/1987 06:00:00 0.003 01/01/1987 09:00:00 0.003 01/01/1987 12:00:00 0.003 01/01/1987 15:00:00 0.003 01/01/1987 18:00:00 0.003 01/01/1987 21:00:00 0.003 01/02/1987 00:00:00 0.003 01/02/1987 03:00:00 0.003 01/02/1987 06:00:00 0.003 01/02/1987 09:00:00 0.003 01/02/1987 12:00:00 0.003 01/02/1987 15:00:00 0.003 01/02/1987 18:00:00 0.003 01/02/1987 21:00:00 0.003

7 commentaires
Afficher 5 commentaires plus anciens Masquer 5 commentaires plus anciens

Callie
Callie le 15 Nov 2023
Thank you so much for helping. Unfortunately now the time is wrong in the table. I provided a screenshot. I was also able to cut the data.
Cris LaPierre
Cris LaPierre le 15 Nov 2023
Ran in:
Ran in:
Then you have changed something without indicating what. When I run the code I shared on the file you attached, the results look like what I would expect.
% Load csv file (created using Excel's Import from Image feature)
dataTable = readtable('N01BLR copy cut.csv');
% combine date and time to new table variable
dataTable.RecHour = hours(dataTable.RecHour/100);
dataTable.Date=dataTable.date+dataTable.RecHour;
dataTable.Date.Format = 'MM/dd/yyyy HH:mm:ss';
% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])
T = 1259×2 table
Date Discharge ___________________ _________ 01/01/1987 00:00:00 0.003 01/01/1987 03:00:00 0.003 01/01/1987 06:00:00 0.003 01/01/1987 09:00:00 0.003 01/01/1987 12:00:00 0.003 01/01/1987 15:00:00 0.003 01/01/1987 18:00:00 0.003 01/01/1987 21:00:00 0.003 01/02/1987 00:00:00 0.003 01/02/1987 03:00:00 0.003 01/02/1987 06:00:00 0.003 01/02/1987 09:00:00 0.003 01/02/1987 12:00:00 0.003 01/02/1987 15:00:00 0.003 01/02/1987 18:00:00 0.003 01/02/1987 21:00:00 0.003
Callie
Callie le 15 Nov 2023
Sucess! Thank you so much.
Cris LaPierre
Cris LaPierre le 15 Nov 2023
Modifié(e) : Cris LaPierre le 15 Nov 2023
Actually, it's not working when times switch to 5 minute increments. It also seems to switch back and forth initially between 3 hr and 5 min increments, which complicates the issue. Is the CSV your raw data, or is there a precursor file? I suspect in the original data, RecHour is recorded with 4 digits. When you open that file in Excel, it formats it as a number and removes all leading zeros.
See if you can locate a raw data file that you have not yet opened in Excel. When opening it, you should see a popup like this
Click 'Don't Convert', and then inspect the RecHour column. Are there 4 digits?
Cris LaPierre
Cris LaPierre le 15 Nov 2023
Ran in:
Ran in:
I think this code will work.
% Load csv file (created using Excel's Import from Image feature)
dataTable = readtable('N01BLR copy cut.csv');
% combine date and time to new table variable
dataTable.RecHour = duration(floor(dataTable.RecHour/100),rem(dataTable.RecHour,100),0,...
'Format','hh:mm');
dataTable.Date=dataTable.date+dataTable.RecHour;
dataTable.Date.Format = 'MM/dd/yyyy HH:mm:ss'
dataTable = 1259×17 table
Var1 DataCode RecType RecYear RecMonth Recday Watershed DayofYear RecHour Discharge Sheight CorrectedSheight Height LogFlag QualFlag date Date ____ _________ _______ _______ ________ ______ _________ _________ _______ _________ _______ ________________ ______ _______ ________ ___________ ___________________ 1 {'ASD05'} 1 1987 1 1 {'N01B'} 1 00:00 0.003 0 5.01 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 00:00:00 2 {'ASD05'} 1 1987 1 1 {'N01B'} 1 03:00 0.003 0 4.99 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 03:00:00 3 {'ASD05'} 1 1987 1 1 {'N01B'} 1 06:00 0.003 0 4.97 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 06:00:00 4 {'ASD05'} 1 1987 1 1 {'N01B'} 1 09:00 0.003 0 4.97 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 09:00:00 5 {'ASD05'} 1 1987 1 1 {'N01B'} 1 12:00 0.003 0 4.98 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 12:00:00 6 {'ASD05'} 1 1987 1 1 {'N01B'} 1 15:00 0.003 0 4.97 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 15:00:00 7 {'ASD05'} 1 1987 1 1 {'N01B'} 1 18:00 0.003 0 4.94 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 18:00:00 8 {'ASD05'} 1 1987 1 1 {'N01B'} 1 21:00 0.003 0 4.92 0 {'.'} {'.'} 01-Jan-1987 01/01/1987 21:00:00 9 {'ASD05'} 1 1987 1 2 {'N01B'} 2 00:00 0.003 0 4.9 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 00:00:00 10 {'ASD05'} 1 1987 1 2 {'N01B'} 2 03:00 0.003 0 4.9 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 03:00:00 11 {'ASD05'} 1 1987 1 2 {'N01B'} 2 06:00 0.003 0 4.88 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 06:00:00 12 {'ASD05'} 1 1987 1 2 {'N01B'} 2 09:00 0.003 0 4.88 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 09:00:00 13 {'ASD05'} 1 1987 1 2 {'N01B'} 2 12:00 0.003 0 4.87 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 12:00:00 14 {'ASD05'} 1 1987 1 2 {'N01B'} 2 15:00 0.003 0 4.88 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 15:00:00 15 {'ASD05'} 1 1987 1 2 {'N01B'} 2 18:00 0.003 0 4.84 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 18:00:00 16 {'ASD05'} 1 1987 1 2 {'N01B'} 2 21:00 0.003 0 4.81 0 {'.'} {'.'} 02-Jan-1987 01/02/1987 21:00:00
% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])
T = 1259×2 table
Date Discharge ___________________ _________ 01/01/1987 00:00:00 0.003 01/01/1987 03:00:00 0.003 01/01/1987 06:00:00 0.003 01/01/1987 09:00:00 0.003 01/01/1987 12:00:00 0.003 01/01/1987 15:00:00 0.003 01/01/1987 18:00:00 0.003 01/01/1987 21:00:00 0.003 01/02/1987 00:00:00 0.003 01/02/1987 03:00:00 0.003 01/02/1987 06:00:00 0.003 01/02/1987 09:00:00 0.003 01/02/1987 12:00:00 0.003 01/02/1987 15:00:00 0.003 01/02/1987 18:00:00 0.003 01/02/1987 21:00:00 0.003
% View last 10 rows (data is in 5 min increments)
tail(T)
Date Discharge ___________________ _________ 03/23/1987 19:30:00 0.126 03/23/1987 19:35:00 0.127 03/23/1987 19:40:00 0.129 03/23/1987 19:45:00 0.129 03/23/1987 19:50:00 0.13 03/23/1987 19:55:00 0.129 03/23/1987 20:00:00 0.129 03/23/1987 20:05:00 0.127
Callie
Callie le 15 Nov 2023
Yes that works! Before I did not look at the tail and it had the wrong times. This new code works. Thank you
Peter Perkins
Peter Perkins le 16 Nov 2023
Chris has certainly cracked that nut, but I'm gonna suggest that you use a timetable, not just a table.

Connectez-vous pour commenter.

Plus de réponses (0)

Catégories

En savoir plus sur Data Type Identification dans Centre d'aide et File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!

Translated by