How to Merge 500 tables with occsaional different dates in their date column

1 次查看（过去 30 天）
AR
AR 2016-11-9
评论： AR 2016-11-22
Dear Expert Users, I'd like to merge my 500 tables, each table is a stock's daily ~6 to 12-yr history. Each table has 7-columns with the first column as yyyyMMdd, 4 columns in $ for hi/low/close/open prices, 1 column for amt of volume traded on that day, and last column is just one I added that is the "ticker" stringvar for that stock (duplicated on every line for eveery day of historical date).
I looked at using merge but each table is a different length. I tried to use join but some dates are missing. I tried to use Financial Tool, however, I have a single file with 500-tables so it is not reading varibles in app.
Do I have any options?
Thank you. --AR

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

采纳的回答

Peter Perkins
Peter Perkins 2016-11-9
Is outerjoin what you are looking for?
>> t1 = table(datetime(2016,1,[1;2;3;5]),[1;2;3;4],[5;6;7;8]);
>> t2 = table(datetime(2016,1,[2;4;5;6]),[9;10;11;12],[13;14;15;16]);
>> outerjoin(t1,t2,'Keys','Var1','MergeKeys',true)
ans =
Var1 Var2_t1 Var3_t1 Var2_t2 Var3_t2
___________ _______ _______ _______ _______
01-Jan-2016 1 5 NaN NaN
02-Jan-2016 2 6 9 13
03-Jan-2016 3 7 NaN NaN
04-Jan-2016 NaN NaN 10 14
05-Jan-2016 4 8 11 15
06-Jan-2016 NaN NaN 12 16
If you have access to R2016b, you should look at using timetables.
  1 个评论
AR
AR 2016-11-22
Pete, Thanks. This worked w/adding 'Type' 'left' to outerjoin and looping across all 500 tables. Within loop I horzcat my table-X with my table-X Variable of interest (e.g. Opening Prices) to create one large matrix with Opening Price as columns and every day as row to create a 6429 (# of total days) x 500 (# of stocks) matrix. Once matrix created some stocks had NaN for that day. My code.
X=allDates; % before loop create vector of all date ranges across all 500 tables
i=1:length(files)
XTable=horzcat(XTabletimes(:,1), XTableVar(:,2)); % column 1 in table is datenum value and column 2 is Opening Price
[A,~,~] = outerjoin(X,XTable,'key','Date','Type','left','MergeKeys',true); %first joins XTable to allDates then subsequently joins new XTable (XTable2) to (allDates + XTable1) then continues through all 500 tables (e.g. files)
X=A; % creates new X (allDates + XTable1) for next iteration to join
end

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Data Preprocessing 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by