Matlab dates and time not aligning after combining
Show older comments
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 Comments
Walter Roberson
on 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
on 14 Nov 2023
Les Beckham
on 14 Nov 2023
Can you post a subset of the "too large" dataset that illustrates the issue?
Accepted Answer
Cris LaPierre
on 15 Nov 2023
Edited: Cris LaPierre
on 15 Nov 2023
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.Date.Format = 'MM/dd/yyyy HH:mm:ss';
% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])
7 Comments
Callie
on 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.
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"])
Callie
on 15 Nov 2023
Cris LaPierre
on 15 Nov 2023
Edited: Cris LaPierre
on 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?
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'
% Extract the desired variables to a new table
T = dataTable(:,["Date","Discharge"])
% View last 10 rows (data is in 5 min increments)
tail(T)
Callie
on 15 Nov 2023
Peter Perkins
on 16 Nov 2023
Chris has certainly cracked that nut, but I'm gonna suggest that you use a timetable, not just a table.
More Answers (0)
Categories
Find more on Data Type Identification in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
