How to select specific data and create a table

Hi everyone,

I have been looking for some information in the answers and I have an idea of how to perform my code but I'm still lacking some knowledge and I get lost.

I have an Excel file (find it attached) which has in the first column the name of the runners of the 5000 m in the olympic games with the heats (runs) that everyone of them did and next to it I have the time they did.

I want to:

• Read the name of the first runner (Hagos Gebrhiwet) and then the time from heat 1 to heat 5 but the code needs to check what is the last heat and then decide that after heat 5 the next input is a new runner.
• Read the name of runner 2 (Albert Kibichii Rop) and then the time of his 2 heats and decide that after that there is a new runner.
• When it finishes reading all the data I want to output it in a table like the second picture I have attached.

You will find either the input and ouputs in the attached Excel file.

The main purpose of my question is to know the best way to import the data (xlsread works fine) and then manage to put the data as I would like the simplest possible way.

Many thanks in advance

Answer 1

[Guillaume]

One approach:

[~, ~, data] = xlsread('Example_Runners.xlsx');  %read the whole lot
data = data(2:end, :);  %remove header
isrunner = ~startsWith(data(:, 1), 'Heat');  %identify the rows that are not heat
runners = repelem(data(isrunner, 1), diff(find([isrunner;true]))-1);  %replicate runner for each heat
t = [table(runners), cell2table(data(~isrunner, :), 'VariableNames', {'heat', 'time'})];
unstack(t, 'time', 'heat')

[David Rojas Blanco]

Hi Guillaume,

This absolutely worked! Thank you very much. I was able to understand a bit better how to do this kind of operations.

Many thanks