matrix manipulation to convert data into useful matrix
2 次查看(过去 30 天)
显示 更早的评论
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?
2 个评论
Dyuman Joshi
2022-6-11
Can you upload the file which contains the data table? It's difficult to understand in this format.
Jeffrey Clark
2022-6-12
编辑:Jeffrey Clark
2022-6-12
@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
回答(1 个)
Anjali Mishra
2022-6-13
2 个评论
Jeffrey Clark
2022-6-13
@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
2022-6-18
@Anjali Mishra, I'm curious to see your code to load the data into the table, if you want to share.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Text Files 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!