How do I import a text file and average data in a column from said file?

I have a text file full of data which looks like this:

 'AHMOD.txt'
01-_04,14,09PM     $VNINS,317667.094785,-118.07304581
01-_04,14,09PM     $VNINS,317667.294783,-118.07304572
01-_04,14,09PM     $VNINS,317667.494783,-118.07304562
01-_04,14,09PM     $VNINS,317667.694783,-118.07304613
01-_04,14,09PM     $VNINS,317667.894783,-118.07304591
01-_04,14,10PM     $VNINS,317668.094783,-118.07304579
01-_04,14,10PM     $VNINS,317668.294784,-118.07304591
01-_04,14,10PM     $VNINS,317668.494784,-118.07304591
01-_04,14,10PM     $VNINS,317668.694784,-118.07304563
01-_04,14,10PM     $VNINS,317668.894784,-118.07304520
01-_04,14,11PM     $VNINS,317669.094784,-118.07304532
01-_04,14,11PM     $VNINS,317669.294785,-118.07304514
01-_04,14,11PM     $VNINS,317669.494785,-118.07304524
01-_04,14,11PM     $VNINS,317669.694785,-118.07304491
01-_04,14,11PM     $VNINS,317669.894785,-118.07304461
01-_04,14,12PM     $VNINS,317670.094785,-118.07304486
01-_04,14,12PM     $VNINS,317670.294786,-118.07304442
01-_04,14,12PM     $VNINS,317670.494786,-118.07304424
01-_04,14,12PM     $VNINS,317670.694786,-118.07304395
01-_04,14,12PM     $VNINS,317670.894786,-118.07304388

The text file holds 20 instances of where data was collected, 5 instances per second. Each row holds a time stamp (such as: 01-_04,14,09PM) and two collected data values (such as:317667.094785,-118.07304581).

I need to average the numeric values of data that were collected in each second. Can someone help me with the importing and averaging of the data?

Thank you

[Image Analyst]

Make it easy for us and attach the file with the paper clip icon. Have you tried readtable()?

[Max Mason]

I have attached the text file, thank you!

[Guillaume]

Your text file doesn't look like what you've shown and is a mess to parse. Can you configure whatever is creating the file to output something more sensible?

Answer 1

[Guillaume]

filecontent = fileread('AHMOD.txt');
usefulcontent = regexp(filecontent, '([^ ]+) +\$[^,]+,([^,])+,(.*)$', 'tokens', 'lineanchors', 'dotexceptnewline');
usefulcontent = vertcat(usefulcontent{:});
t = table2timetable(table(...
         datetime(usefulcontent(:, 1), 'InputFormat', 'dd-MM-yy_hh,mm,ssa', 'Format', 'default'), ...
         str2double(usefulcontent(:, 2)), ...
         str2double(usefulcontent(:, 3)), ...
         'VariableNames', {'datetime', 'something', 'somethingelse'} ... change to whatever you want
        ));
tsecond = retime(t, 'secondly', 'mean')  %average by second

[Max Mason]

This is perfect! Is there a way to skip to the 6th line and begin from there rather than the first line? Thank you!

[Guillaume]

Sure, instead of

usefulcontent = vertcat(usefulcontent{:});

use

usefulcontent = vertcat(usefulcontent{6:end});

Alternatively, remove the first 6 rows of t:

t(1:6, :) = [];