matrix manipulation to convert data into useful matrix

I have data table as below:

2022-06-06 23:44:41.658,  TPV 
2022-06-06 23:44:41.684, NAV-S 
G4,    18 
G9,    42 
G17,  42 
G20,  38 
G28,  42 
G32,  43 
G4,   18 
G9,   42 
G17,  42 
G20,  38 
G28,  42 
G32,  43 
2022-06-06 23:44:42.668,  TPV 
2022-06-06 23:44:42.690  NAV-S 
G4,    18 
G9,    39 
G17,   42 
G20,   43 
G28,   42 
G32,   46 
G4,    18 
G9,    39 
G17,   42 
G20,   43 
G28,   42 

I want to convert it to as below three rows as below:

NAV-S                      G4     G9    G17    G20    G28      G32  
2022-06-06 23:44:41.684    18     42      42   38     42         43 
2022-06-06 23:44:42.690    18     39      42   43     42         46 

Are there any functions which can help in this?

[Dyuman Joshi]

Can you upload the file which contains the data table? It's difficult to understand in this format.

[Jeffrey Clark]

@Anjali Mishra, perhaps use textscan to bring in as two columns of strings then using attached (I assumed you forgot the comma in line 16 and I have fixed in attached) ...

fileContent = textscan(fopen('dataTable.txt'),'%s%s','Delimiter',',');
isNum = ~[cellfun(@(x) isempty(str2num(x)),fileContent{1}) ...
         ,cellfun(@(x) isempty(str2num(x)),fileContent{2})];
fileStrings = [string(fileContent{1}) string(fileContent{2})];
colName = unique(fileStrings(isNum(:,2),1));
colChar = char(colName);
[~,colOrder] = sort(str2num(colChar(:,2:end)));
colName = [fileStrings(2,2);colName(colOrder)];
dataTable = table('Size',[sum(isNum(:,1))/2 length(colName)] ...
                 ,'VariableNames',colName ...
                 ,'VariableTypes',["datetime" repmat("doubleNaN",1,length(colName)-1)]);

% to be completed - load table with data

Answer 1

[Anjali Mishra]

It works when there are consistent number of rows. But the row size for each time stamp is changing. That i sresulting into following error using horcat. Is there a solution for that ?

error using horzcat

Dimensions of arrays being concatenated are not consistent.

Error in satellite_data_matrix_creator (line 5)

isNum = ~[cellfun(@(x) isempty(str2num(x)),fileContent{1}) ...

[Jeffrey Clark]

@Anjali Mishra, are you sure you mean rows? As I indicated, your original text seemed to be missing a comma in line 16, which I assumed was a copy/paste problem. If the comma separator between columns is being used by the textscan and wouldn't correctly parse lines without it. If spaces or commas are to be the column separators you should change textscan name,value parameters to:

'Delimiter',{' ',','},'MultipleDelimsAsOne',true

And you still need to do the data row assignments code!

[Jeffrey Clark]

@Anjali Mishra, I'm curious to see your code to load the data into the table, if you want to share.