sorting unique date by time.
显示 更早的评论
hello,
I have a n*3 data.(entire US stock data) each cloume date are: stock id, date, price.
the date in in the second cloume is repeating for each stock id.
A T1 P1a
A T2 P2a
A T3 P3a
A T4 P4a
B T1 P1a
B T2 P2b
B T3 P3b
B T4 P4b
.... .... ....
i try to arrange data, let first cloume be the date, and firt row be the stock id.
ID =unique (Data(:,1))';
Date = unique(Data(:,2))
and then i have no ideal how to coding .
the data need to be
A B C ...............
T1 P1a P1b P1c ...............
T2 P2a P2b P2c
T3 P3a P3b P3c
T4 P4a P4b P4c
i have no coding experiences before, and to try the learn the matlab.
thanks for your time
2 个评论
Jan
2019-6-26
You forgot to mention, in which format the input data are stored. Is this a cell array or a table object? The wanted output format is not clear also.
HAOLUN TIAN
2019-6-26
采纳的回答
Shameer Parmar
2019-6-26
编辑：Shameer Parmar
2019-6-27
Hello Haolun, here is the logic..
% Assumming input Data be like this..
% first column is 'ID', second Column is 'date', third column is 'price'
inputData =[
11 21062019 1000
11 22062019 1500
11 23062019 2000
11 24062019 2500
22 21062019 3000
22 22062019 3500
22 23062019 4000
22 24062019 4500
33 21062019 5000
33 22062019 5500
33 23062019 6000
33 24062019 6500];
IDs =unique(inputData(:,1));
Dates = unique(inputData(:,2));
totalIDs = length(IDs);
totalDates = length(Dates);
% logic to create output data
% First item of output will be empty
outputData{1,1} = [];
% This FOR loop is for first row (row of IDs)
for count1 = 1:totalIDs
outputData{1,count1+1} = IDs(count1);
end
% This FOR loop is for first column (column of Dates)
for count2 = 1:totalDates
outputData{count2+1,1} = Dates(count2);
end
% This For loop is for intermediate values
for count1 = 1:totalIDs
for count2 = 1:totalDates
ID_val = IDs(count1);
Date_val = Dates(count2);
ID_Column_Indices = find(ismember(inputData(:,1),ID_val));
Date_Column_Indices = find(ismember(inputData(:,2),Date_val));
require_price_val = [];
for i=1:length(ID_Column_Indices)
for j=1:length(Date_Column_Indices)
if ID_Column_Indices(i)==Date_Column_Indices(j)
index = ID_Column_Indices(i);
require_price_val = inputData(index,3);
end
end
end
outputData{count2+1,count1+1} = require_price_val;
end
end
inputData % to display input data
outputData % to display output data
% Output data will be display as..
outputData =
[] [ 11] [ 22] [ 33]
[21062019] [1000] [3000] [5000]
[22062019] [1500] [3500] [5500]
[23062019] [2000] [4000] [6000]
[24062019] [2500] [4500] [6500]
Please see attached script and run it and see the result.. Thanksss... :)
4 个评论
HAOLUN TIAN
2019-6-26
Shameer Parmar
2019-6-27
编辑：Shameer Parmar
2019-6-27
Hello Haolun,
Can you provide me that .csv file, then I can run my code on it and try to resolve the error of 'ismember'..
The error is might be because your data is in alphanumeric format and I wrote the code to work for numeric data..
You can hide your confidential data and provide me the modified .csv file.. I just want the input data/ file so that I can help you.. Good Luck..
HAOLUN TIAN
2019-6-27
HAOLUN TIAN
2019-6-27
更多回答（2 个）
Steven Lord
2019-6-26
0 个投票
If you were to store your data in a table or timetable array, the operation you're describing is unstack.
The first example on that documentation page looks somewhat similar to your application and includes the step of creating a table from the data, so you may be able to adapt it to suit your needs.
Once you've converted your data into a table or timetable there are lots of other functions that take advantage of the tabular nature of the data. You could name the variables in your table as A, B, or C for example, and index into the table using those variable names rather than using ID code like 11, 22, or 33 as Shameer Parmar did
Campion Loong
2019-6-27
编辑：Campion Loong
2019-6-27
As Steve suggested above, you should import the CSV into a timetable and unstack to get what you want. I'd also mention the detectImportOptions function -- it gives you many knobs to tweak how exactly you'd like to import (without spending time to actually read the whole file):
Assuming Shameer's data format, your CSV would look like this:
data.csv:
STOCK1 21062019 1000
STOCK1 22062019 1500
STOCK1 23062019 2000
STOCK1 24062019 2500
STOCK1 21062019 3000
STOCK2 22062019 3500
STOCK2 23062019 4000
STOCK2 24062019 4500
STOCK3 21062019 5000
STOCK3 22062019 5500
STOCK3 23062019 6000
STOCK3 24062019 6500
Here are the steps to get your data into MATLAB, in your desired layout:
>> opt = detectImportOptions('data.csv');
% Not necessary (automatically detected) if your timestamps are in a more common time format
% e.g. '21-06-2019' instead of '21062019'
>> opt.VariableTypes{2} = 'datetime';
>> opt.VariableOptions(2).InputFormat = 'ddMMyyyy';
% Give your timestamps more friendly formats and variables meaningful names
>> opt.VariableOptions(2).DatetimeFormat = 'dd-MMM-yyyy';
>> opt.VariableNames = ["Ticker" "Time" "data"];
% Now read-in your data
>> tt = readtimetable('data.csv',opt)
tt =
12×2 timetable
Time Ticker data
___________ ________ ____
21-Jun-2019 'STOCK1' 1000
22-Jun-2019 'STOCK1' 1500
23-Jun-2019 'STOCK1' 2000
24-Jun-2019 'STOCK1' 2500
21-Jun-2019 'STOCK2' 3000
22-Jun-2019 'STOCK2' 3500
23-Jun-2019 'STOCK2' 4000
24-Jun-2019 'STOCK2' 4500
21-Jun-2019 'STOCK3' 5000
22-Jun-2019 'STOCK3' 5500
23-Jun-2019 'STOCK3' 6000
24-Jun-2019 'STOCK3' 6500
% Unstack to your desired layout
>> tt = unstack(tt,'data','Ticker')
tt =
4×3 timetable
Time STOCK1 STOCK2 STOCK3
___________ ______ ______ ______
21-Jun-2019 1000 3000 5000
22-Jun-2019 1500 3500 5500
23-Jun-2019 2000 4000 6000
24-Jun-2019 2500 4500 6500
类别
在 帮助中心 和 File Exchange 中查找有关 Financial Toolbox 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
