How do I extract numbers from mixed data to create a table?

I have a bunch of data in the following format.

190208:124540.835217 Tx:MOV x pos: 1637941µm prec: 1000 acc: 0 mode: 00 id: 1691

190208:124541.140924 Rx:MOV x status: 0 pos: 1637130µm id: 1691

190208:124541.517632 Tx:MOV x pos: 979337µm prec: 0 acc: 0 mode: 00 id: 1719

190208:124541.657078 Rx:MOV x status: 0 pos: 979341µm id: 1719

190208:124541.797484 Tx:MOV x pos: 1637941µm prec: 1000 acc: 0 mode: 00 id: 1741

190208:124542.104695 Rx:MOV x status: 0 pos: 1637113µm id: 1741

What I would like to do is to extract the time (in bold) and the position (underlined) and organize them in a table. Each row should contain the position responding to a specific time. Is there any way to code so that I don't have to do this manually?

[madhan ravi]

Please attach your data as .mat file and give an example of your desired output so a precise solution is given.

[Amanda Hillström]

This is what my data looks like, and I do not know how to convert it to a MAT-file.

190208:123346.128737 Tx:MOV x pos: 1668224µm prec: 2 acc: 0 mode: 00 id: 7056

190208:123346.577784 Rx:MOV x status: 0 pos: 1668224µm id: 7056

190208:123347.191182 Tx:MOV x pos: 1668232µm prec: 2 acc: 0 mode: 00 id: 7066

190208:123347.233088 Rx:MOV x status: 0 pos: 1668233µm id: 7066

190208:123347.414860 Tx:MOV x pos: 1668231µm prec: 2 acc: 0 mode: 00 id: 7076

190208:123347.453385 Rx:MOV x status: 0 pos: 1668231µm id: 7076

190208:123347.512106 Tx:MOV x pos: 1552037µm prec: 2 acc: 0 mode: 00 id: 7111

190208:123347.691557 Rx:MOV x status: 0 pos: 1552036µm id: 7111

190208:123348.056873 Tx:MOV x pos: 1552114µm prec: 2 acc: 0 mode: 00 id: 7121

190208:123348.121175 Rx:MOV x status: 0 pos: 1552114µm id: 7121

190208:123348.288643 Tx:MOV x pos: 1552114µm prec: 2 acc: 0 mode: 00 id: 7131

190208:123348.328085 Rx:MOV x status: 0 pos: 1552114µm id: 7131

190208:123348.370362 Tx:MOV x pos: 1668316µm prec: 2 acc: 0 mode: 00 id: 7166

190208:123348.550054 Rx:MOV x status: 0 pos: 1668318µm id: 7166

190208:123348.698950 Tx:MOV x pos: 1668399µm prec: 2 acc: 0 mode: 00 id: 7176

I have attached a short example of my desired output. Ideally, I would like to create two separate charts - one for the rows where it says Tx and another one for Rx.

[KSSV]

Where is your data present? In excel? Attach the data file.

[Amanda Hillström]

I'm sorry, I think I misinterpreted the word data. It's more like a log, which appears as text and numbers on a webpage. I would like to extract the time and position from a log.

[Guillaume]

How do you get your data in the first place? Copy/paste from the website? Save the page as html? Save as text? Does this website offers an automatic way of getting at this data (e.g. json export, which would make everything simpler and more importantly more robust).

Whichever way you've exported the data to put into your question, I can already see a minor issue. You're using the wrong character set which is mangling some characters. That  is probably supposed to be a non-breaking space, Non breaking spaces are incorrectly interpreted as ' ' if you incorrectly look at UTF8 data with an ISO8859-1 character set.

While we can certainly solve your question without worrying about any that, if you want something reliable it would be better to focus on getting the data correctly in the first place.

[Amanda Hillström]

Thank you for your answer, I see your point. I copy/paste it from the website. The only other way to do it is to save it as a .txt file as the website does not offer an automatic way of getting at the data. I will research if there is any other way to reach it. If not, is there any way to convert the data from .txt to a more suitable format?

[Guillaume]

"I copy/paste it from the website."

You copy it from the website and paste it into what? (Hopefully not notepad). With that process, the preservation of the correct encoding depends both on the source application and the destination application. If either fails to take into account the encoding, it will get lost.

It may be safer to save the page as text or html (assuming it's html to start with). Can you attach a page saved as text? Or if the website is publicly accesible, can you put a link to an example page?

[Amanda Hillström]

I have attached a page saved as text.

[madhan ravi]

Tx has eof in some places instead of pos: so what do you want to extract there?

Answer 1

[madhan ravi]

s={'190208:123346.128737 Tx:MOV x pos: 1668224µm prec: 2 acc: 0 mode: 00 id: 7056'
'190208:123346.577784 Rx:MOV x status: 0 pos: 1668224µm id: 7056'
'190208:123347.191182 Tx:MOV x pos: 1668232µm prec: 2 acc: 0 mode: 00 id: 7066'
'190208:123347.233088 Rx:MOV x status: 0 pos: 1668233µm id: 7066'
'190208:123347.414860 Tx:MOV x pos: 1668231µm prec: 2 acc: 0 mode: 00 id: 7076'
'190208:123347.453385 Rx:MOV x status: 0 pos: 1668231µm id: 7076'
'190208:123347.512106 Tx:MOV x pos: 1552037µm prec: 2 acc: 0 mode: 00 id: 7111'
'190208:123347.691557 Rx:MOV x status: 0 pos: 1552036µm id: 7111'
'190208:123348.056873 Tx:MOV x pos: 1552114µm prec: 2 acc: 0 mode: 00 id: 7121'
'190208:123348.121175 Rx:MOV x status: 0 pos: 1552114µm id: 7121'
'190208:123348.288643 Tx:MOV x pos: 1552114µm prec: 2 acc: 0 mode: 00 id: 7131'
'190208:123348.328085 Rx:MOV x status: 0 pos: 1552114µm id: 7131'
'190208:123348.370362 Tx:MOV x pos: 1668316µm prec: 2 acc: 0 mode: 00 id: 7166'
'190208:123348.550054 Rx:MOV x status: 0 pos: 1668318µm id: 7166'
'190208:123348.698950 Tx:MOV x pos: 1668399µm prec: 2 acc: 0 mode: 00 id: 7176'};
% code starts here
Tx=table;
tx=s(contains(s,'Tx'));
Tx.TIME=str2double(regexp(tx,'\d+.?\d*(?=.Tx)','match','once'));
Tx.POSITION=str2double(regexp(tx,'(?<=pos:.*)\d*','match','once'));
Rx=table;
rx=s(contains(s,'Rx'));
Rx.TIME=str2double(regexp(rx,'\d+.?\d*(?=.Rx)','match','once'));
Rx.POSITION=str2double(regexp(rx,'(?<=pos:.*)\d*','match','once'));

Gives:

>> Rx

Rx =

  7×2 table

        TIME         POSITION
    _____________    ________

    123346.577784    1668224 
    123347.233088    1668233 
    123347.453385    1668231 
    123347.691557    1552036 
    123348.121175    1552114 
    123348.328085    1552114 
    123348.550054    1668318 

>> Tx

Tx =

  8×2 table

        TIME         POSITION
    _____________    ________

    123346.128737    1668224 
    123347.191182    1668232 
     123347.41486    1668231 
    123347.512106    1552037 
    123348.056873    1552114 
    123348.288643    1552114 
    123348.370362    1668316 
     123348.69895    1668399 

>> 

[madhan ravi]

To save it to .mat file:

save('Tx_Rx.mat','Tx','Rx')
   %  ^^^^^^^^^------your filename

[Guillaume]

As I've commented in the question, the textual data as is shown has been acquired with the wrong character set. The result of that is that some characters are not what they should be. (The  is a clear example, note that this  is probably followed by character 160 which does not render but is still present and will affect regular expressions).

While it's unlikely that the character set mismatch will affect the numbers, it's still a possibility. There is nothing worse than code that appears to do the right thing when you test it on some particular data but fails silently on some other and simply gives you incorrect results.

I would strongly recommend sorting out the import first, to make sure it is correct.

[madhan ravi]

@Guillaume: Completely agree with you.

[Amanda Hillström]

Thank you so much. It does not work for me though, this message pops up

"Error using validateHandleToPrint
No figure to print.

Error in validate (line 17)
pj = validateHandleToPrint(pj);

Error in print (line 67)
pj = validate( pj );"

What have I done wrong?

[Guillaume]

That error doesn't look like it can come from the code in Madhan answer. It looks like an error that occurs later when you try to print something.

[madhan ravi]

opts=detectImportOptions('Log1.txt');
T=readtable('Log1.txt',opts);
s=table2cell(T);
Tx=table;
tx=s(contains(s,'Tx'));
Tx.TIME=str2double(regexp(tx,'\d+.?\d*(?=.Tx)','match','once'));
Tx.POSITION=str2double(regexp(tx,'(?<=(pos:.*)|(eof:.*))\d*','match','once'));
               %                      ^^^^^^^^^^^^^^^^^--- extracts after pos: or eof: , see my comment above
Rx=table;
rx=s(contains(s,'Rx'));
Rx.TIME=str2double(regexp(rx,'\d+.?\d*(?=.Rx)','match','once'));
Rx.POSITION=str2double(regexp(rx,'(?<=pos:.*)\d*','match','once'));

[Amanda Hillström]

Thank you, it is correct that I would like to import both eof and pos. This error keeps popping up, where should I place my file so that matlab can reach it? I have tried importing it and it automatically organizes itself in a table.

Error using matlab.io.internal.validateFileName (line 53)

Unable to find or open 'Log1.txt'. Check the path and filename or file permissions.

Error in detectImportOptions (line 235)

[filename,ext] = matlab.io.internal.validateFileName(filename);

[madhan ravi]

Make sure the file is in the working path.

[Amanda Hillström]

Thank you so much for your help. It saved my day!

[Guillaume]

One thing to be aware of with this solution, is that table decode the file using your OS locale which is unlikely to be UTF8 (It certainly won't be on Windows). It will therefore mangle some characters at the file should be read as UTF8.

Answer 2

[Guillaume]

Your log1.txt file is encoded in UTF8 and starts with a BOM (a bit unusual). Reading as text without telling matlab it's utf8 may mangle some text.

The following should decode your file correctly:

fid = fopen('log1.txt', 'rt', 'n', 'UTF8'); %tells matlab it's encoded in utf8
text = fread(fid, [1 Inf], '*char');
timepos = regexp(text, '(\d+:\d+\.\d+).*?pos: (\d+)', 'tokens');
timepos = vertcat(timepos{:});
time = datetime(timepos(:, 1), 'InputFormat', 'ddMMyy:HHmmss.SSSSSS');  %I'm assuming that's the correct format 
position = str2double(timepos(:, 2));

result = table(time, position)

I'm assuming I've got the format for the date\time correct. As the year is encoded on two digits only, you may also want to specify the 'PivotYear' for the conversion. Otherwise, when decoding in 2019, it is assumed that any year above 69 is in the 19xx, and anything below is 20xx.

[Amanda Hillström]

Thank you!!

[Guillaume]

If your query has been resolved, please accept one of the answer.

[Amanda Hillström]

I made plotted the result from both of your solutions and they look the same which implies that they both work, right? Your code did not separate Tx from Rx though (not sure if I even mentioned that it would be a preferable way to do it). The date shows up as 19th Feb 2008 when it should be 8th Feb 2019.

[Guillaume]

The date shows up as 19th Feb 2008 when it should be 8th Feb 2019.

At least, I had a stab at guessing the date format. This is trivially fixed by changing the 'InputFormat' to 'yyMMdd:HHmmss.SSSSSS'.

As for the TX/RX (no, it wasn't mentioned before), it's also an easy change:

fid = fopen('log1.txt', 'rt', 'n', 'UTF8'); %tells matlab it's encoded in utf8
text = fread(fid, [1 Inf], '*char');
fclose(fid);                                %I forgot to close the file in my original answer!
timedirpos = regexp(text, '(\d+:\d+\.\d+)\s+([RT]x).*?pos: (\d+)', 'tokens');
timedirpos = vertcat(timedirpos{:});
time = datetime(timedirpos(:, 1), 'InputFormat', 'yyMMdd:HHmmss.SSSSSS');  %Should be correct now
direction = categorical(timedirpos(:, 2))
position = str2double(timedirpos(:, 3));
result = table(time, direction, position)

[Amanda Hillström]

Thanks. Could you help me organize it so that the three colums on the first row are: time, position (Tx), position (Rx)? Also, is there any way to include the decimals after seconds that were visible in my log?

yyMMmm:HHmmss.SSSSSS <- currently they do not appear in my table

[Guillaume]

You can specify the display format of the datetime during the import to whatever you want, e.g.:

time = datetime(timedirpos(:, 1), 'InputFormat', 'yyMMdd:HHmmss.SSSSSS', 'Format', 'dd-MMM-yyyy HH:mm:ss.SSSSSS');

or afterward change it to whatever you want, eg:

result.time.Format = 'dd-MMM-yyyy HH:mm:ss.SSSSSS';

With regards to "so that the three colums on the first row are: time, position (Tx), position (Rx)", I'm not really clear how that would work. No Tx and Rx have the same timing. I suppose you could round them up (to the nearest what?) but still, you don't always have a TX followed by a RX, so what should happen in that case? E.g. looking at the end of the table:

>> tail(result)
ans =
  8×3 table
               time                direction     position 
    ___________________________    _________    __________
    08-Feb-2019 12:34:33.111048       Tx        1.5161e+06
    08-Feb-2019 12:34:33.122383       Rx        1.5161e+06
    08-Feb-2019 12:34:33.375990       Tx        1.5161e+06
    08-Feb-2019 12:34:33.381392       Rx        1.5161e+06
    08-Feb-2019 12:34:33.563973       Tx        1.1615e+06
    08-Feb-2019 12:34:33.822953       Tx        1.8105e+06
    08-Feb-2019 12:34:33.822953       Tx         1.456e+06
    08-Feb-2019 12:34:33.826743       Rx        7.7424e+05

You've got three consecutive TX, two of them at the same time but with different position. What would the corresponding RX for each?