You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
Trying to isolate rows in a specific time
2 views (last 30 days)
Show older comments
So I am trying to isolate specific variables in a certain time frame, I have different columns for time and what I need but the data is in a table. Is there a way for me to create a new variable with only the rows of these specific times only. I have attached screenshots of the workshop tables, tahnk you.
2 Comments
Guillaume
on 25 Jun 2018
Screenshots are useless. We can't test code on them. Attach demo mat or text files instead.
However, before that you need to explain a lot better what it is you want. An example of desired output would help greatly.
Cameron Power
on 25 Jun 2018
All I want is to take the data file I have with time intervals of 3 or hour sampling for an entire year, and extract only the data within a certain timeframe, say from 1500Hr to 1700Hr. The time in hours is separate from the other information I need so I want to pull out the entire row. I have attached a csv, import it into Matlab to see the format.
Accepted Answer
Ameer Hamza
on 25 Jun 2018
First of all, your csv file is not properly formatted. So it is difficult to use csvread() or readtable() directly. The following code use textscan() to read the file and then extract rows based on the specified hour range
f = fopen('Shearwater.Surface.Wind_Direction_and_Wind_Speed.2006.3-hourly.csv');
data = textscan(f, '"%f,%f,%f,%f,%f,%f"', 'HeaderLines', 1);
fclose(f);
data = cell2mat(data);
dates = datetime([data(:,1:4) zeros(size(data,1), 2)]);
dataTable = table(dates, data(:,5), data(:,6), 'VariableNames', {'datetime', 'wind_direction', 'wind_speed'});
Hours = hour(dataTable.datetime);
index = Hours >= 15 & Hours <= 17; % all rows where house is between 15 and 17
requiredTable = dataTable(index, :);
21 Comments
Guillaume
on 25 Jun 2018
Yes, that csv extension is misleading, the file is not what is commonly known as a comma separated values file. The quote around each row is a real pain.
If it were me, I'd rewrite the files in a proper format:
fid = fopen('rewritten_file.csv', 'w'); %or whatever you want to call the rewritten file
fwrite(regexprep(fileread('Shearwater.Surface.Wind_Direction_and_Wind_Speed.2006.3-hourly.csv'), '"(.*)", '$1', 'dotexceptnewline'));
fclose(fid);
Then the import is trivial:
dataTable = readtable('rewrittenfile.csv');
Cameron Power
on 26 Jun 2018
I know the format of the csv is clunky but I already have it separated by year, month, day, hour, wind speed, wind direction. I just cannot isolate specific rows depending on the hour
Guillaume
on 26 Jun 2018
Edited: Guillaume
on 26 Jun 2018
Without the quotes surrounding each line, loading the data would be one line of code, and isolate specific rows depending on the hour would be just another line.
As it is, Ameer has shown you how to do it.
Note that if what you want to do is calculate statistica for each group of 3 hours for example, then a different approach would be better, one which calculates the stats for all groups at once (again, with the right format, it's just one line of code)
Ameer Hamza
on 26 Jun 2018
Edited: Ameer Hamza
on 26 Jun 2018
@Cameron, as @Guillaume has pointed has out, you can first use the code to properly format your csv file and then use readtable() to directly read it. You can also run the code in answer to get the table from file data. Once you have the table, use the last 3 lines from my code to extract the desired time range.
Cameron Power
on 27 Jun 2018
Almost perfect, it worked for the one file attached, but failed with the one attached to this comment, is there any reason why?
Ameer Hamza
on 27 Jun 2018
Which code you used to process this file. My code in the answer will not work since the number of columns are different. You must be doing csvread() or readtable(). This file also have several headerLines. Can you share the code which fails for this file?
Cameron Power
on 27 Jun 2018
I tried to use the code you provided but as you said, it manages to complete but with no real result (a 0x3 table with no information).
Ameer Hamza
on 27 Jun 2018
Yes, as I mentioned, that code was just for that specific file. Since this csv file has correct formatting, you can use
dataTable = readtable('merged copy.csv');
After reading, the remaining step to extract specific data are same.
Cameron Power
on 27 Jun 2018
I replaced the dataTable section as such;
f = fopen('Bedford_Basin.Surface.Wind_Direction_and_Wind_Speed.2006.3-hourly copy.csv'); data = textscan(f, '"%f,%f,%f,%f,%f,%f"', 'HeaderLines', 1); fclose(f); data = cell2mat(data); dates = datetime([data(:,1:4) zeros(size(data,1), 2)]); dataTable = readtable('Merged copy.csv') Hours = hour(dataTable.datetime); index = Hours >= 16 & Hours <= 20; % all rows where house is between 15 and 17 requiredTable = dataTable(index, :);
But the variable 'datetime' does not exist
Ameer Hamza
on 27 Jun 2018
When using readtable(), MATLAB takes the variable name from the CSV file. In your Merged copy.csv file, the column name is Date/time, therefore MATLAB converts it into dataTable.Date_Time. Therefore you need to use dataTable.Date_Time instead of dataTable.datetime. You can check the column name using
dataTable.Properties.VariableNames
or by double-clicking the variable name in Workspace pane.
Cameron Power
on 27 Jun 2018
That makes sense and got as far as the Hours line where reads 'Undefined function 'hour' for input arguments of type 'cell'.' I tried replacing it with Time but the same error prompt appeared but with Time as apposed to hour. Below is the code that received this error
f = fopen('Merged copy.csv'); data = textscan(f, '"%f,%f,%f,%f,%f,%f"', 'HeaderLines', 1); fclose(f); data = cell2mat(data); dates = datetime([data(:,1:4) zeros(size(data,1), 2)]); dataTable = readtable('Merged copy.csv') Hours = hour(dataTable.Date_Time); index = Hours >= 16 & Hours <= 20; % all rows where house is between 15 and 17 requiredTable = dataTable(index, :);
Ameer Hamza
on 27 Jun 2018
First, you don't need fopen(), textscan(), fclose() stuff sicne you are using readtable() so just remove those lines. Also I ran the following lines and it worked fine for me.
dataTable = readtable('Merged copy.csv');
Hours = hour(dataTable.Date_Time);
index = Hours >= 16 & Hours <= 20; % all rows where house is between 15 and 17
requiredTable = dataTable(index, :);
You just need these line of codes.
Cameron Power
on 27 Jun 2018
I removed the frivilous code but I was still met with this error "Warning: Variable names were modified to make them valid MATLAB identifiers. The original names are saved in the VariableDescriptions property. Undefined function 'hour' for input arguments of type 'cell'."
Ameer Hamza
on 27 Jun 2018
Which MATLAB version are you using. I am using R2018a and dataTable.Date_Time returns a datetime array rather then a cell. You can try the following line
Hours = hour([dataTable.Date_Time{:}]);
Cameron Power
on 27 Jun 2018
I am using R2017a, and I got a similar error upon replacing with
"Warning: Variable names were modified to make them valid MATLAB identifiers. The original names are saved in the VariableDescriptions property. Undefined function 'hour' for input arguments of type 'char'."
Ameer Hamza
on 27 Jun 2018
The first part is a warning, so don't bother about that. It is just saying the variable name might not match with the column name is CSV file. For the second problem, I don't have the R2017a installed, so cannot test what is the problem. Can you share the output of the following lines
t = dataTable.Date_Time;
class(t)
size(t)
class(t{1})
Cameron Power
on 27 Jun 2018
The outputs read as
ans =
'cell'
ans =
8938 1
ans =
'char'
Ameer Hamza
on 27 Jun 2018
It is strange that the readtable() is reading the first column as a char rather than datetime. Hopefully, the following will work
Hours = hour(datetime(vertcat(dataTable.Date_Time{:})))
If it still doesn't work, then share the image of the portion of the table by double-clicking the dataTable in workspace pan.
Cameron Power
on 27 Jun 2018
Still nothing sadly, this error returned; "Error using vertcat Dimensions of matrices being concatenated are not consistent." and this is a screenshot of the table
Ameer Hamza
on 27 Jun 2018
The screenshot shows that it is reading date time as char array but the above statement should still work. I have no idea what might be causing the issue.
Cameron Power
on 28 Jun 2018
I do not either, thank you for the help thus far and I`ll keep at it
More Answers (0)
See Also
Categories
Find more on Data Type Conversion 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!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)