Trying to isolate rows in a specific time

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.

[Guillaume]

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]

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.

Answer 1

[Ameer Hamza]

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, :);

[Guillaume]

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]

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]

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]

@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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

The outputs read as

ans =

    'cell'

ans =

        8938           1

ans =

    'char'

[Ameer Hamza]

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]

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]

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]

I do not either, thank you for the help thus far and I`ll keep at it