How do I point at certain columns in a .csv file and then run calculations on it?

Hello all, I currently have the code below that runs calculations and exports a matrix for my .csv files. Right now this is assuming my data is a clean 3Xwhatever matrix in my .csv files. Within my .csv files I only want to look at data in columns starting at B10, C10, and D10 and then running ot the end of those columns. Can matlab point to certain data within a .csv? I am assumning I need to tweak the M = readmatrix(fullfile(fn(ii).folder,fn(ii).name)) line or M = readmatrix(fullfile(fn(ii).folder,fn(ii).name)) line.

Any help is much appreciated! Thank you.

% appropriate dir() call that returns info 
% about the files you want to process:
fn = dir('*.csv'); % this call returns info about .csv files in the current directory;
                   % you may need to modify it to work for your file locations
                   % (see dir documentation)
% number of files:
N_files = numel(fn);
% pre-allocate results matrix (one row per file, 3 columns):
results = zeros(N_files,3);
% read and process each file:
for ii = 1:N
    % read the file:
    M = readmatrix(fullfile(fn(ii).folder,fn(ii).name));
    % process the file's data:
    S = sum(abs(diff(M,1,1)),1);
    % store the result:
    results(ii,:) = S;
end
% write the results file (can be located anywhere):
writematrix(results,'results.csv')

[Caleb]

Here’s how you can modify your code to achieve this:

% appropriate dir() call that returns info  about the files you want to process:
fn = dir('*.csv'); % this call returns info about .csv files in the current directory; you may need to modify it to work for your file locations                  
% (see dir documentation)

% number of files:
N_files = numel(fn); 

% pre-allocate results matrix (one row per file, 3 columns):
results = zeros(N_files,3); 

% read and process each file:
for ii = 1:N_files 
    % read the file starting from B10 to the end of columns B, C, and D:    
    M = readmatrix(fullfile(fn(ii).folder,fn(ii).name), 'Range', 'B10:D10'); % !! specify the range that MATLAB needs to look at !! 
    
    % process the file's data:    
    S = sum(abs(diff(M,1,1)),1); 
    
    % store the result:    
    results(ii,:) = S; 
end

% write the results file (can be located anywhere):
writematrix(results,'results.csv') 

[Voss]

@Caleb: Using

'Range', 'B10:D10'

will read from row 10 only, but I believe @Lucas wants to read from row 10 to the last row of data.

@Lucas: Please upload a sample csv file (using the paperclip button) so that someone can determine how to modify the code for your files.

[Lucas]

@Voss @Caleb thank you for the help. I attache the file here, Voss is correct, I would like to read columns B10, C10, and D10 within this .csv all the way to the end of them (they are all the same length). Thanks!

Answer 1

[Voss]

You can modify the code as follows (specifying 9 header lines, and using only columns 2-4 of the matrix read):

% appropriate dir() call that returns info 
% about the files you want to process:
fn = dir('*.csv'); % this call returns info about .csv files in the current directory;
                   % you may need to modify it to work for your file locations
                   % (see dir documentation)
% number of files:
N_files = numel(fn);
% pre-allocate results matrix (one row per file, 3 columns):
results = zeros(N_files,3);
% read and process each file:
for ii = 1:N_files
    % read the file starting from line 10:
    M = readmatrix(fullfile(fn(ii).folder,fn(ii).name),'NumHeaderLines',9);
    % process columns 2-4 of the file's data:    
    S = sum(abs(diff(M(:,2:4),1,1)),1); 
    % store the result:
    results(ii,:) = S;
end
% write the results file (can be located anywhere):
writematrix(results,'results.csv')

Also note that N had to be changed to N_files in the for line (that was a mistake in my original code, sorry about that).

[Lucas]

I was in class for a while. Thank you though. This is working now!

Lucas

[Voss]

You're welcome!

[Lucas]

I appreciate it @Voss! I have had the chance to look at some of this distance data and it looks great. I want to tweak my main calculation slightly. Where I have S = sum(abs(diff(M(:,2:4),1,1)),1).

I would like to continue this method of subtracting the second line from the first and moving down. But I need an intermediate step (or 2) where the values are subtracted values are squared. The that row of values would be added up and the sqrt of that would be taken.That end value would be added to the end values of that operation perfromed down the rows. This is starting to hurt my head. I am going to try and implement it as well.

I have attached an image that explains this clearer (I hope). Its a way of doing the distance formula. I just need to loop it for 500 x y and z coordinates.

I appreciate you.

Answer 2

[dpb]

" only want to look at data in columns starting at B10, C10, and D10 and then running ot the end of those columns. Can matlab point to certain data within a .csv? "

... 
nHdr=9;             % header lines to skip
C=["B";"D"];        % start, ending column (adjacent) --> "B:D" here...change to suit
range=join(C,":");  % build a range expression
for ii=1:numel(fn)
  M = readmatrix(fullfile(fn(ii).folder,fn(ii).name), ...
                'NumHeaderLines',nHdr,'Range',range);
...

See the doc for readmatrix and friends; also look into detectImportOptions to fine tune import even further such as returning noncontiguous ranges.

One could make a couple other minor adjustments to the calculational code; @Voss left yours untouched other than to select the specific columns to operate over from the whole array whereas the above only returns the desired columns.

...
nFiles=numel(fn);                       % number files to read
nCols=numel(char(C(1)):char(C(2)));     % number of columns to read (try it, you'll like it!!! :))
S=zeros(nFiles,nCols);                  % only have on total sum by nCols wide
for ii=1:nF
  M = readmatrix(fullfile(fn(ii).folder,fn(ii).name), ...
                'NumHeaderLines',nHdr,'Range',range);
  S(ii,:)=sum(abs(diff(M,1,1)));       
end
writematrix(S, ...)                     % save results as desired...

@Voss is correct if there were only one row; was not a case I figured would occur, but code should account for. agreed...I initially was thinking it was only the overall summation that was wanted, not the individual sums...looking back at the original I see I had misread it...

[Voss]

What happens if M has only one row?

e.g.

M = [8 -4 10];
sum(abs(diff(M)))

ans = 26

sum(abs(diff(M,1,1)),1)

ans = 1×3

0 0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

[dpb]

I mixed old and new code and the Submit button quit working so I went away for awhile, @Voss.

I thought originall the overall total was all that was wanted and was going to compute it that needs only a single row for output but rereading I see I missed there.

Agreed; I didn't consider the possibility of there being only a single row but the code should handle just in case.

It's interesting that

M = [8 -4 10];
abs(diff(M,1,1))

ans =

  0x3 empty double matrix

is empty but

sum(abs(diff(M,1,1)))

ans = 1×3

0 0 0

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

gets converted to the zeros vector. I don't know that I had ever noticed that behavior before.

It did let me correct now. I've been having lots of trouble with the forum with latest release of Firefox over last couple of weeks or so -- it brings up the window but have to manually refresh multiple times or close and try again before can get access to do anything....don't know if TMW changed something or Firefox introduced a bug...

[Lucas]

Thank you @dpb. I appreciate your help on this as well!

take care.

Lucas