Extract a specific range of rows from an excel file using opts = detectImpo​rtOptions(​filename)

I try to extract rows 191-222 from an excel file, and I use opts = detectImportOptions(filename)

However, by inspecting the weblink for this on MATLAB, I cannot find how to narrow down to a range, 191-222.

I tried

opts = detectImportOptions('tempDataTrollhFlygpl.xlsx',  'NumHeaderLines', 191-222, 'VariableNamingRule','preserve');
opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx',  opts)
M = D(:,[3 4])

But that won't work. Any ideas how to limit opts = detectImportOptions to extract only within rows 191-222?

Thanks!

Answer 1

[Dyuman Joshi]

Specify the data range to be read using the 'Range' option -

opts = detectImportOptions('tempDataTrollhFlygpl.xlsx', ...
                        'Range', '191:222', 'VariableNamingRule','preserve');

opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx',  opts);
M = D(:,[3 4])

[Dyuman Joshi]

Any updates, @Sergio?

[Sergio]

Sorry for late answer @Dyuman Joshi . Thanks, answer well accepted!

I tried to plot this, and I got a blank plot, and no errors.

opts = detectImportOptions('tempDataTrollhFlygpl.xlsx', ...
    'Range', '191:222', 'VariableNamingRule','preserve');
opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx',  opts);
M = D(:,[3 4])
plot(M.Var3,str2double(M.Var4))
xlabel('Date');
ylabel('Temperature');

Do you know what is wrong?

Thanks

[Dyuman Joshi]

A possibility is that the data has NaN values.

Please attach the excel so I can run and reproduce the issue and provide a concrete suggestion/solution.

[Sergio]

@Dyuman Joshi

[Sergio]

@Dyuman Joshi I got it by removing str2double , the plot shows . But I tried to do an average estimation of that colum 4, by

A=mean(M,4)

But that gave nothing. However, if I do

A=mean(M)

I get an average of BOTH the date and the temperature. Strange?

[Dyuman Joshi]

The data is (already being) converted to double data format (using setvartype), so there is no need to use str2double -

opts = detectImportOptions('tempDataTrollhFlygpl.xlsx', ...
    'Range', '191:222', 'VariableNamingRule','preserve');
opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx',  opts);
M = D(:,[3 4])

M = 32x2 table
       Var3        Var4
    ___________    ____

    30-Jun-1961    16.2
    01-Jul-1961    18.2
    02-Jul-1961    19.5
    03-Jul-1961    17.3
    04-Jul-1961      13
    05-Jul-1961    13.1
    06-Jul-1961    13.7
    07-Jul-1961      14
    08-Jul-1961    12.9
    09-Jul-1961      14
    10-Jul-1961    13.8
    11-Jul-1961    13.7
    12-Jul-1961    14.3
    13-Jul-1961    16.1
    14-Jul-1961    15.6
    15-Jul-1961    12.9

plot(M.Var3,M.Var4)
xlabel('Date');
ylabel('Temperature');

[Dyuman Joshi]

No, that is an expected behaviour. As M is a table, so using mean() directly on it calculates the mean of each variable.

If you want to calculate the mean of a particular variable in a table, use curly brackets to access the data in it or use the dot indexing as you did in plotting the data

mean(D{:,4})
%or
mean(D.Var4)

See mean and Access Data in Tables for reference.

[Sergio]

Thanks @Dyuman Joshi!