Matlab Stacking problem - turning 8 files into one

Question

Angelina Linke 2023-9-27

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2026227-matlab-stacking-problem-turning-8-files-into-one

回答： SAI SRUJAN 2023-10-11

hello everybody.

I have an assignment for uni to do and our prof wants us to do everything in matlab. It's a Fixed-Effects-Regression and I collected a handful of data on datastream that I will need. We are also told to use chatgpt which is currently not helping me as I get the same error message over and over again.

I have 8 files with data (7 files contain the data of 1612 companies and 121 months which will have to be reduced at a later step and one file has all the companies and another column I want to use, maybe I don't need to use it or maybe that file is my problem?)

** edit: I downloaded the data as yearly data to get around the reducing part. Not sure if it will be easier now

What I need to do it to stack these files into one (reduce the months to only decembers of each year which would leave me with 10 or 11 months) having the companies in one column and the remaining data in the next columns if that makes sense.

what I basically need is a new file with the companies in one column and then the year-end data of each file (december but our prof told us to get the date for all months of a year) + tobins q for which I have data prepared in those 7 files

If anyone wants to have a look, I can share the onedrive link to the files, I believe it's only one point I'm missing out but I can't seem to find it. Mind you, it's in German, though, the code should be understandable for other languages as well.

New Code is as follows:

clear all; clc; 
% Pfad zur Excel-Datei
filePath = 'BoardSize.xlsx';
% Lesen Sie die Daten aus der Excel-Datei
data = xlsread(filePath);
% Ermitteln Sie die Größe des Daten-Arrays
[numRows, numCols] = size(data);
% Geben Sie die Range aus
fprintf('Die Range der Excel-Datei %s ist %d Zeilen x %d Spalten.\n', filePath, numRows, numCols);
% Pfad zu den Excel-Dateien
filePaths = {'BoardGenderDiversityPercent.xlsx', 'BoardSize.xlsx', 'CommonEquity.xlsx', 'MarketCapitalization.xlsx', 'NetSalesOrRevenue.xlsx', 'ROA.xlsx', 'TotalLiabilities.xlsx'};
% Initialisieren Sie eine leere Zelle für die kombinierten Daten
combinedData = {};
% Schleife durch die Dateien und lesen Sie die Daten ein
for i = 1:numel(filePaths)
    % Lesen Sie die Daten aus der aktuellen Datei 
    data = xlsread(filePaths{i}, 'A2:BJA1421'); % Passen Sie den Bereich an Ihre Dateien an
    
    % Fügen Sie die Daten zur kombinierten Zelle hinzu
    combinedData = [combinedData, data];
end
% Erstellen Sie eine neue Excel-Datei
outputFile = 'KombinierteDatei.xlsx';
% Erstellen Sie eine Matrix für die Ausgabe, in der die Unternehmen in Spalte A und die Jahre in Spalte B stehen
outputMatrix = combinedData; % Include all columns
% Schreiben Sie die Daten in die Excel-Datei
xlswrite(outputFile, outputMatrix, 'Sheet1');
% Hinweis: Wenn xlswrite nicht funktioniert, können Sie stattdessen writematrix verwenden (ab MATLAB R2019b)
% writematrix(outputMatrix, outputFile);

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2023-9-27

编辑：dpb 2023-9-27

在 MATLAB Online 中打开

You'll get more direct help if attach an example file -- you can zip up if needed for size - one or two of the datasets plus the "odd-man-out" data of other format.

You don't tell us what error you're getting or even where -- that leaves us needing the Crystal Ball Toolbox which has yet to be released by MathWorks -- "any day now".

Some general comments -- xlsread and friends have been deprecated for quite a long time in favor of either readtable or readmatrix.

combinedData = [combinedData, data];

is going to put 1612 companies' data side by side for all 7 datasets; that's not going to be an easy way to use the data besides the dynamic reallocation likely is pretty slow with this size (although isn't huge, it is sizable).

It would seem a "more better" organization would be to read each company characteristic and create a table of the date, company, and then the 7/8(?) characteristic measures by company and date. Then you have a table of the measures in each column for each company; there will be 121 times for each so the overall table height would end up as 1612*121 rows X 8 columns instead.

Whether the dates are stored in one of the files or you generate them from known sampling time, I strongly recommend storing them as datetime class; makes doing things by date grouping or selection by date much simpler.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

SAI SRUJAN 2023-10-11

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2026227-matlab-stacking-problem-turning-8-files-into-one#answer_1330764

在 MATLAB Online 中打开

Hi Angelina Linke,

I understand that you have a issue related to stacking of data from different files into one file.

You can follow the given steps to resolve the issue,

Read data from the files using "readtable" MATLAB function.
In MATLAB, you have the ability to combine data from multiple tables using various functions such as "join", "outerjoin", and "innerjoin".
You can use the above mentioned MATLAB functions to stack the data into one table.
Filter the stacked data in the table by following the given example.

companies=['A' ;'B' ;"C"];
earnings=[100 ;200 ;300];
T=table(companies,earnings);
%filter condition
idx=T.earnings>=200;
%new table aftering applying the condition.
newT=T(idx,:);