How to create extra data based on a string cell value?

3 次查看(过去 30 天)
hi,
How do I extract the data from a cell based on a string value? I'm trying to create a new array with all the data for each unique 'symbol' sorted by TIME -oldest to newest.
thanks Allan
The new arrays would be:
new_VWAPdata.symbol (for ACB)
Symbol Most_Recent_Trade_Time VWAP
ACB 14:22:11.863324 10.2616
ACB 14:21:11.999557 10.2616
etc
new_VWAPdata.symbol (for ACB)
Symbol Most_Recent_Trade_Time VWAP
AAPL 14:24 123.8655
AAPL 14:25 123.88
etc.

回答(2 个)

Harry Laing
Harry Laing 2020-12-7
I'm going to assume that the actual date (day) of the time column doesn't matter, or happens to be todays date. Because MATLAB's datetime function/format can be used here, but you need to specify a day too.
I'd write a mini-function to convert the time value you have to a datetime variable with todays date and that time, then you can write a for loop to filter through your 'Symbol' column and place the data in an associated array, then sort by time afterwards.
% Create Function that takes your recent trade time value and convert it to date+time format
function most_recent_trade_time_DATETIME = Time_to_Datetime(most_recent_trade_time)
% your time will now be in the form: 07-Dec-2020 14:22:11.863324
datetime_string = [date,' ',most_recent_trade_time]; % 'date' gets todays date
% datetime_string is still a string, must convert to 'datetime' format
most_recent_trade_time_DATETIME = datetime(datetime_string);
end
Now that you have a function to convert from a string to a datetime format, you can write a loop. There's many ways to do this, and what I suggest below probably isn't the most efficient, but as an example:
% Initialise new 'struct' of data, each having its own table within the structure for each symbol
SortedData.ACP = table();
SortedData.AAPL = table();
for i = 1:length(new_VWAPdata.Symbol) % loop for number of rows in new_VWAPdata
% convert time value to datetime using our function above
most_recent_trade_time_DATETIME = Time_to_Datetime(new_VWAPdata.Most_Recent_Trade_Time(i))
% Start an if statement based on Symbol
if new_VWAPdata.Symbol(i) == 'ACP'
SortedData.ACP = [SortedData.ACP; % Update the table for ACP by adding the time and VWAP data
{most_recent_trade_time_DATETIME, new_VWAPdata.VWAP(i)}];
elseif new_VWAPdata.Symbol(i) == 'AAPL'
SortedData.AAPL = [SortedData.AAPL; % Update the table for AAPL by adding the time and VWAP data
{most_recent_trade_time_DATETIME, new_VWAPdata.VWAP(i)}];
else
error('Unknown Symbol')
end
end
SortedData.ACP = sortrows(SortedData.ACP, 1) % sort the rows based on the first column, in ascending order
SortedData.AAPL = sortrows(SortedData.AAPL,1) % sort the rows based on the first column, in ascending order
The resulting structure should have two tables, that look something like the below image. Obviously you can rename the columns in the tables.
NOTE: The function should be at the bottom/end of your script, or in it's own file in your current MATLAB directory.

dpb
dpb 2020-12-7
Read the data into a table instead is probably quite a lot easier.
Then use grouping variables to operate over data by whatever you choose.

类别

Help CenterFile Exchange 中查找有关 Data Type Identification 的更多信息

标签

产品


版本

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by