Taking A large spreadsheet and making it into smaller spreadsheets

What I need to do is make a multiband raster image from text files. I have matching software that only works with rasters, I want to apply that software to my hand samples' spectral signatures. So I want to create a raster where each pixel represents a hand sample. To do that each band needs its own text file with the extension .xyz, I will be using QGIS to convert this text file into a raster image. Then I can layer stack all these rasters into a single scene. Then I can run it through my matching software. I'm having difficulty getting started and would greatly appreciate the help.

I have spectral data for 100 hand samples already compiled into an excel spreadsheet like so:

sample names | sample 1 | sample 2 | sample 3 | ...

X | 1 | 1 | 1 | ....

Y | 1 | 2 | 3 | ...

wavelength (nm) | reflectance % | reflectance % | reflectance % | ...

400 | 20 | 35 | 10 | ...

401 | 23 | 33 | 12 | ...

... | ... | ... | ... | ...

I want to creat a new text file for every row with a wavelenth number. Ex 400nm

( X | Y | reflectance %) *I actually do not want this row in the new spreadsheet, its only here to help explain.*

1 | 1 | 20

1 | 2 | 35

1 | 3 | 10

... | ... | ...

This is a basic text file. Only 3 columns and n number of rows. There needs to be no headings for the columns, just the values.

Thank you!

Answer 1

[Mathieu NOE]

hello Curtis

try this , nothing fancy :

%% load xlsx (param data file)
data = readtable('params.xlsx','VariableNamingRule','preserve');
[m,n] = size(data);

samples = n-1; % NB : nb of samples must match nb of wavelength

%% create txt files , one per wavelength / sample
for ci = 1:samples
    X_vector = table2array(data(1,2:samples+1));
    Y_vector = table2array(data(2,2:samples+1));
    reflectance = table2array(data(3+ci,2:samples+1));
    out = [X_vector(:) Y_vector(:) reflectance(:)];
    % write in txt file
    filename = ['wavelength' num2str(table2array(data(3+ci,1))) 'nm.xyz'];
    writematrix(out, filename, "FileType","text","Delimiter","|");
end

[Curtis Bernard]

This doesn't work out exactly the way I need it to. My sample number does not equal the number of wavelengths. The wavelength range goes from 350-2500 (2150 wavelengths). And I have I think around 800 samples.

Is there away to adjust this so that the number of wavelengths and samples do not need to match?

Thank you!!

[Curtis Bernard]

I was able to figure out a work around. Since the script is set up where the number of samples must equal the number of wavelengths I just added in blank samples with reflectance values of -1 (might use 0 instead). Once I convert them to rasters it will be easy enough to clip the extent.

Thank you for your help!!

[Mathieu NOE]

you're welcome !

[Mathieu NOE]

hello again

this is a sllightly modified code, maybe of some interest for you :

%% load xlsx (param data file)
data = readtable('params.xlsx','VariableNamingRule','preserve');
[m,n] = size(data);

samples = n-1; % nb of samples 
nb_wavelength = m-3; % nb of wavelength

%% create txt files , one per wavelength 
for ci = 1:nb_wavelength
    X_vector = table2array(data(1,2:samples+1));
    Y_vector = table2array(data(2,2:samples+1));
    reflectance = table2array(data(3+ci,2:samples+1));
    out = [X_vector(:) Y_vector(:) reflectance(:)];
    % write in txt file
    filename = ['wavelength_' num2str(table2array(data(3+ci,1))) '_nm.xyz'];
    writematrix(out, filename, "FileType","text","Delimiter","|");
end