Convert cell in wideformat into longformat

Hello,

we have a Sample in Cell-Format and it is in wide format and we want to convert it into long format.

Our Sample looks like this, with 3377 companies from 2011-2022:

| Year | Company A   | Company B   | Company C   |...
|------|-------------|-------------|-------------|
| 2011 | Data A12    | Data B12    | Data C12    |
| 2012 | Data A13    | Data B13    | Data C13    |
| ...  | ...         | ...         | ...         |
| 2022 | Data A22    | Data B22    | Data C22    |

And we want to turn our Sample into this, so that we can match our Data to the company names and years:

| Year | Company     | Data     |
|------|-------------|----------|
| 2011 | Company A   | Data A12 |
| 2012 | Company A   | Data A13 |
| ...  | ...         | ...      |
| 2022 | Company A   | Data A22 |
| 2012 | Company B   | Data B12 |
| 2013 | Company B   | Data B13 |
| ...  | ...         | ...      |
| 2022 | Company B   | Data B22 |
| ...  | ...         | ...      |

We tried to use the 'stack' command and coudn't get an output.

Thanks in advance.

[Stephen23]

"We tried to use the 'stack' command and coudn't get an output."

As its documentation clearly states, STACK only works with tables and timetables.

"we have a Sample in Cell-Format..."

Your data look perfectly suitable for a table, why are you using a cell array?

Answer 1

[Hendrik Kiel]

This is my solution:

[Data,~,DataRaw]=xlsread('Data.xlsx');

% Create cell with company names:
CompanyNames=DataRaw(1,[2:end]);
% Repeat the company name twelve times (for each year in the sample):
CompanyNames=repelem(CompanyNames,1,12);
% Wide to long format
CompanyNames=CompanyNames(:);
% CompanyID
[~, ~, CompanyID] = unique(CompanyNames, 'stable');

% Year
Year=Data(:,1);
Year=repmat(Year,1,3377);
Year=Year(:);

% Data
% Remove first Column 'Years'
Data(:,1)=[];
Data=Data(:);

% NewData
NewData=[CompanyID Year Data];