Re-arrange CRSP Data
2 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
JohnB
le 27 Déc 2018
Modifié(e) : Cris LaPierre
le 28 Déc 2018
How can I rearrange CRSP Data into matrix since both stocks and dates are ranged into columns.
How can I place dates in a single row and only Stocks tickers in a column ?
![2018-12-27_18-26-44.jpg](https://www.mathworks.com/matlabcentral/answers/uploaded_files/199825/2018-12-27_18-26-44.jpeg)
2 commentaires
Réponse acceptée
Cris LaPierre
le 27 Déc 2018
Modifié(e) : Cris LaPierre
le 28 Déc 2018
I would elect to do this using a table. However, column headers in a table are variable names, and MATLAB variables cannot be numbers. That means a little modification will be needed. You can set the codes as the rownames and the modified dates as the variableNames (column headers).
This is a bit of a hack, and can likely be improved. Still, this should get you started
file = 'US_500_Book_SRT.xlsx';
opts = detectImportOptions(file);
% Your dates are in a numeric 'yyyymmdd' format that can't be recognized by readtable.
% I import them as numbers here and convert them later.
opts = setvartype(opts,1,'categorical');
% load data
data = readtable(file,opts);
% convert dates from numeric 'yyyymmdd' to datetime 'yyyyMM'
datetimeYY = @(D)datetime(D,'ConvertFrom','yyyymmdd','Format','yyyyMM');
data = convertvars(data,2,datetimeYY);
% I want to use dates as a grouping mechanism, so convert to categorical as well.
data = convertvars(data,2,'categorical')
% extract the unique stock codes and yyyyMM dates
stocks = categories(data.Var1);
dates = categories(data.Var2);
% Find groups, using indeces to assign to row, column of matrix
Gs = findgroups(data.Var1);
Gd = findgroups(data.Var2);
mat2 = zeros(length(stocks),length(dates));
% Didn't find a more elegant way to do this. Loop through each row of data
% Assign to table using stocks group for the row number and dates group for the column
for loop = 1:length(Gs)
mat2(Gs(loop),Gd(loop)) = data.Var3(loop);
end
% Convert mat2 to a table
tbl2 = table(mat2);
tbl2 = splitvars(tbl2);
% assign stock codes as rownames
tbl2.Properties.RowNames = stocks;
% make column names dates (prepend with 'd_' so it is a valid MATLAB variable name)
tbl2.Properties.VariableNames = 'd_' + string(dates);
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/199829/image.png)
Plus de réponses (0)
Voir également
Catégories
En savoir plus sur Data Type Conversion dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!