Transform column data to m x n matrix

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.

Answer 1

[Andrei Bobrov]

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])];

[Maneesh]

Hi Andrei,

Could you please help me with aonther similar problem.

I have logged it at "https://uk.mathworks.com/matlabcentral/answers/485165-group-transpose-and-plot"

Thanks

Maneesh

Answer 2

[Kevin Chng]

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.

[Maneesh]

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]

Read table from Original Data, then write it to Transformed Data according to its pattern?

Answer 3

[Image Analyst]

Have you tried reshape()?

arraymxn = reshape(originalMatrix, m, n);

[Maneesh]

Thanks for your response. The reshape will not distribute the data the way I want.

Answer 4

[madhan ravi]

[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)

[Kevin Chng]

Hi madhan,

you open his file, you will notice that two tables are totally different format, some logic have to apply for matching them. Title is bit misleading.

[madhan ravi]

yes I don't understand what the OP wants

[Maneesh]

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