Matlab dates and time not aligning after combining
古いコメントを表示
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 件のコメント
Walter Roberson
2023 年 11 月 14 日
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
2023 年 11 月 14 日
Les Beckham
2023 年 11 月 14 日
Can you post a subset of the "too large" dataset that illustrates the issue?
採用された回答
Cris LaPierre
2023 年 11 月 15 日
編集済み: Cris LaPierre
2023 年 11 月 15 日
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 件のコメント
Callie
2023 年 11 月 15 日
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
2023 年 11 月 15 日
Cris LaPierre
2023 年 11 月 15 日
編集済み: Cris LaPierre
2023 年 11 月 15 日
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
2023 年 11 月 15 日
Peter Perkins
2023 年 11 月 16 日
Chris has certainly cracked that nut, but I'm gonna suggest that you use a timetable, not just a table.
その他の回答 (0 件)
カテゴリ
ヘルプ センター および File Exchange で Data Type Identification についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
