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

1 view (last 30 days)
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
new_VWAPdata.symbol (for ACB)
Symbol Most_Recent_Trade_Time VWAP
AAPL 14:24 123.8655
AAPL 14:25 123.88

Answers (2)

Harry Laing
Harry Laing on 7 Dec 2020
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);
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)}];
error('Unknown Symbol')
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 on 7 Dec 2020
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.


Find more on Data Type Conversion in Help Center and File Exchange




Community Treasure Hunt

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

Start Hunting!

Translated by