Transform column data to m x n matrix

5 次查看(过去 30 天)
Hi, I have 3 column data and want to transform it into an m x n matrix. Please see the attached file for an example.

采纳的回答

Andrei Bobrov
Andrei Bobrov 2018-10-18
OriginalData = readtable('Path_to_your_file\DataTransformation.xlsx',...
'Range','A3:C26',...
'ReadVariableNames',true);
[gr,G] = findgroups(OriginalData.Key2);
A = accumarray([gr,(1:numel(gr))'],OriginalData.values);
TransformedData = [[{nan};G],num2cell([OriginalData.Key1';A])];

更多回答(3 个)

Kevin Chng
Kevin Chng 2018-10-17
编辑:Kevin Chng 2018-10-17
Hi
The best solution is :
Drag your excel and drop it in MATLAB command window.
It will pop out a app for you to configure your excel input.
Since there are char in 2nd column, therefore I stored them in table data type instead of matrix.
opts = spreadsheetImportOptions("NumVariables", 3);
opts.Sheet = "Sheet1";
opts.DataRange = "A4:C29";
opts.VariableNames = ["Key1", "Key2", "values"];
opts.VariableTypes = ["double", "categorical", "double"];
opts = setvaropts(opts, 2, "EmptyFieldRule", "auto");
DataTransformation = readtable("DataTransformation.xlsx", opts, "UseExcel", false);
clear opts
Code above is generated through the application.
However, I saw there are other data or table in your excel sheet, if you want to import them in other table, just repeat the method what i say : Drag the excel sheet and drop it in MATLAB command window, select the range and data type you wanted to export them.
  2 个评论
Maneesh
Maneesh 2018-10-18
Thanks for your response. But this does not solve my problem. I have the data as shown in the cells titled Original Data and want Matlab to transform it to the shape titled Transformed Data
Kevin Chng
Kevin Chng 2018-10-18
Read table from Original Data, then write it to Transformed Data according to its pattern?

请先登录,再进行评论。


Image Analyst
Image Analyst 2018-10-17
Have you tried reshape()?
arraymxn = reshape(originalMatrix, m, n);
  1 个评论
Maneesh
Maneesh 2018-10-18
Thanks for your response. The reshape will not distribute the data the way I want.

请先登录,再进行评论。


madhan ravi
madhan ravi 2018-10-18
编辑:madhan ravi 2018-10-18
[num,txt,raw]=xlsread('DataTransformation.xlsx')
[m,n]=size(num)
num(isnan(num))=[]
data=reshape(num,m,n) %m and n can be your choice but make sure m times n == number(num)
  3 个评论
madhan ravi
madhan ravi 2018-10-18
yes I don't understand what the OP wants
Maneesh
Maneesh 2018-10-18
Ok, let me explain how I have done it in excel.
In original data I have 3 columns titled Key1, Key2 and values
I Join Key1 and Key2 to give me a unique_ID
I create another table with Key1 Transposed which in the sample data set gives me 26 columns.
Then I take unique values of Key2 in new column.
Then I use the following formula:
IFERROR(INDEX(OriginalData.values,MATCH(TransformedData.Key1&TransformedData.Key2,OriginalData.Unique_ID,0)),0)
Result of the above is the Data set as shown in my Transformed Data table.
I have updated the excel file to contain the Excel operation.
This works fine for a small set of data, but I have a very large size of data and I need to create a time series of this data for each month for the past 11 years. So doing this in Excel is very time consuming and of course Excel really has big issues dealing with such large amounts of data. Thanks

请先登录,再进行评论。

类别

Help CenterFile Exchange 中查找有关 Tables 的更多信息

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by