GPS Fields: GPS Analysis within Smartabase using the Time Series Processor

This lesson will describe the steps required to build an event form which will analyse time series files. Typical examples of time series data include GPS tracking units, HR monitors, force plates, power meters, linear tranducers and accelerometers. The following examples will use GPS data, specifically the raw csv file which can be exported from the supplier’s software.

 

Ensure you have an example of the raw data that is exported from the device

Ensure you have an example of the raw data that is exported from the device

Firstly you will need to have an example of the raw data export file which you would like to analyse. You need this because you will need to know the names of the column headings. If the file does not have column headings then you will need to find a way to ensure that every file has the correct headings added to it. The example here shows the raw data WITHOUT headings. You will NOT be able to process this data.

Now that you have the complete raw data file you can start building your form.

Now that you have the complete raw data file you can start building your form.

Here is a file which has had the column headings added.

NOTE: it is vitally important to make sure that the column headings match every file that will be added to the event page.

Whilst the exports of some devices will already do this, others might not. Some organisations will be able to ensure that all raw csv files will have the correct headings in place, but if you cannot guarantee that all users will be able to follow the steps required to manually perform this, there is a solution within Smartabase that will allow for overriding of the headers in the first row. Now that you have the complete raw data file you can start building your form.

You will need to use the Time Series Upload question type

You will need to use the Time Series Upload question type

Name the Time Series Upload Field

Name the Time Series Upload Field

make the name of this question very simple and easy to reference. Try not to use words which exist as variables in the raw data (e.g. in the raw data spreadsheet) or as calculations you will later perform. In this example we have called it ‘GPS’

There are some Advanced Properties which you can apply to the file which you will be attaching

There are some Advanced Properties which you can apply to the file which you will be attaching

Format – if you have any columns in the csv file which are formatted as times, then you need to match the time format with the time options in the dropdown.

Make sure you choose the correct format based on the original spreadsheet and the actual output of the .csv file

Make sure you choose the correct format based on the original spreadsheet and the actual output of the .csv file

Skip Rows – if you want to ignore a specific number of rows at the start of the csv file you can specify a default value here which will exclude the specific number of rows from any calculations you wish to perform.

Skip Rows – if you want to ignore a specific number of rows at the start of the csv file you can specify a default value here which will exclude the specific number of rows from any calculations you wish to perform.

The Skip Rows will apply to all files for all athletes so this is only recommended if you are using devices which have a known period of warming up, or trial runs. For GPS units and HR files, the units can be switched on and start collecting data before the session begins and this will be different for every unit/athlete. Carefully consider whether you need this option.

1: You can write in the number of rows to skip

2: If left blank, then all rows in the file will be available for use in calculations (the standard option for most files)

Take Every Nth Row will take every x rows as actual data. Use this to ignore some data at high recording frequencies.

Take Every Nth Row will take every x rows as actual data. Use this to ignore some data at high recording frequencies.

In the CSV file uploaded you can specify that only every Nth row, e.g. 4th or 5th row, of data is used for analysis. Ensure you know your exact requirements before you set this as a number as this will apply to every file attached for every athlete.

Data Format: Select what format the data is provided in

Data Format: Select what format the data is provided in

Select the Data format of the csv file. Smartabase is compatible with the format of the export files from Catapult GPS devices.  If you are using another technology then leave blank or select Generic.

Override the Headers on the csv spreadsheet, or add them in using the Header Override

Override the Headers on the csv spreadsheet, or add them in using the Header Override

Header Override - If you would like to replace the header in the time series file, supply one here. Headers must be comma separated and contain no special characters. If you would like to replace the names of the column headings or if the file you are adding does not have a header row use this property to define your desired headers.

Now save the Event Form.

N.B. What happens in the actual Event Form when you upload a GPS file

When you have uploaded the file it will be stored on the server. All of the csv columns will not be visible within the event form ( e.g. a table of data will not show). The file will be available for download from the Event Form.

Once on the server, Smartabase will create a number of additional “columns” for the file. For every column in the file you uploaded, Smartabase will create a new column with the delta value for that variable. For example you had a file with 3 columns: Time, Distance, Speed. Once uploaded you will have 6 columns: Time, Time.delta, Distance, Distance .delta, Speed, Speed.delta. Each delta column will calculate the current row- previous row. These delta columns are important as they will be references in calculations later in the form.

You may need to Pull in other important values into you Time Series Analysis Event Form

You may need to Pull in other important values into you Time Series Analysis Event Form

The next section is optional and might not be relevant for other technologies.

When it comes to GPS and HR analysis it is common practice to use previous testing results for specific performance variables for the athlete as comparative or normative values to which the current session can be analysed / compared. In this example we will use Max HR, Max Sprint Speed and Max Acceleration, as these variables are available and provided in the GPS csv export, we can use data from elsewhere in the system to aid in the analysis. In this example we are going to use the most recent maximal HR known for the athlete, and this value can come from anywhere in the system (e.g. a VO2 max tests or shuttle tests). Sprint Speed and acceleration can come from the sprint test event form.

You can find a separate lesson on linked values in the Help Documentation.

Create the Analysis Table in the Event Form that will be used to analyse the Time Series File

Create the Analysis Table in the Event Form that will be used to analyse the Time Series File

Now we are ready to start creating the table that will analyse our file. Before you start this you need to know what calculations you wish to run and how you are going to break the file into known sections. For example:

-In a match, you might want to know Warm Up, First half and Second Half.

-For sports with interchange, each interval that a player is on the field for. In training, you might want to know warm up, drill 1, 2, 3, 4, etc.

-If you want to analyse a training file so that you can learn what loads the players are experiencing for different drills, you will need a way to define those drills in the file.

In this example we are building an Event Form to analyse training sessions. We need to include our database of training drills. Use the Database question type to reference your Drills Database (there is a lesson on Databases if you need additional information). Set the field to Table format. The image here shows drills already selected, allowing user to add rows as required. The drills allow us to label specific parts of the session, and hence specific parts of the file we are analysing.

Next is the Start and End times of each of the drills

Next is the Start and End times of each of the drills

NOTE: for this you will likely need a member of staff to keep a record of the exact times each drill started and finished.

These fields are Duration question Types. NOTE: make sure you select the exact format which matches the file, and matches the Advanced Property setting for the Time Series Upload question (e.g. hh:mm:ss.SSS)

When you select the start and finish times of the various drills or sessions of interest, the calculations will be limited by those times. The rest of the file will be ignored. It is quite common in some sports that the GPS unit may be on for 2 to 3 hours, but a player might only have 40 to 60 minutes worth of actual training/match data that needs to be analysed. In this case a large portion of the raw csv file will need to be ignored. The time columns allow you to do this.

The rest of the table in the form will now be made up of calculations to provide you with the variables you require. Here is an example of calculations typically used when analysing GPS data.

The rest of the table in the form will now be made up of calculations to provide you with the variables you require. Here is an example of calculations typically used when analysing GPS data.

In summary we want to know, time and distance for each drill.

-For each drill we want to the time and distance spent travelling within set speed zones and also relative speed zones. The speed zones can be in either m/s or km/hr you will just need to ensure uniformity across the board, with your export and the numbers you use in your calculations.

To reference any column in the uploaded Time Series file you will need to use the following naming structure. Use the name that you gave the question, in this case GPS, with the column heading in the file you want to reference.

GPS.time

GPS.distance

GPS.speed

GPS.heart rate

Etc.

What you will need to know to build the table correctly...

To understand the following table and how the calculations work, please revise earlier lessons on Table Calculations.

Once you are comfortable with Table calculations, then make sure you understand how to write nested IF equations.  http://profilersports.screenstepslive.com/s/builder/m/557/l/46671-calculations-some-useful-calculations

Once you are comfortable with nested IF statements read up on the greater /lesser than and greater/lesser than or equal to calculations and how they work in the system (outlined in the step below)

How to set up the greater than and lesser than calcs.

Note: All of these will return "Answer" as I've set them up. The first number in the brackets is the query field and the second number is the limit we are interested in

if(gt(5,2), "Answer", "")

if(gt(5,5), "", "Answer")

if(gt(2,5), "", "Answer")

if(gteq(5,2), "Answer", "")

if(gteq(5,5), "Answer", "")

if(gteq(2,5), "", "Answer")

if(lt(2,5), "Answer", "")

if(lt(5,5), "", "Answer")

if(lt(5,2), "", "Answer")

if(lteq(2,5), "Answer", "")

if(lteq(5,5), "Answer", "")

if(lteq(5,2), "", "Answer")

Abbreviations

gt = greater than

gteq = greater than or equal to

lt = less than

lteq = less than or equal to

Examples of different equations used to calculate Time and Distance parameters

Examples of different equations used to calculate Time and Distance parameters

All of the Equations are listed here to ensure you can quickly copy and use them in your own Time Series Forms.

FIELD NAME              FIELD TYPE            CALCULATION                                                            -OUTCOME/REASON

Drill time                   Calculation                (End time  Start time)/60/1000                                -To give you time of each drill in minutes

Start Distance       Table Calculation ,        Aggregate: minifpositive if(gteq(GPS.time,Start time), GPS.distance,-1)

-Distance at start of drill

End Distance             Table Calculation,         Aggregate: maxifpositive if(lteq(GPS.time,End time), GPS.distance,-1) -Distance at end of drill

Drill Distance                Calculation           safe( End Distance -  Start Distance)                         -Distance for the drill

Drill Workrate                 Calculation           safe( Drill Distance/Drill time)                                    -Workrate for the drill

Max Speed (Drill)     Table Calculation       Aggregate: maxifpositive if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), GPS.speed,-1),-1)      -Max speed reached in this drill

Distance < X            Table Calculation,           Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(lteq(GPS.speed,X),GPS.distance.delta,-1),-1),-1))

-Distance covered at speeds lower than X km/h

Time <X           Table Calculation             Aggregate: sumifpositive (safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(lteq(GPS.speed,X),GPS.time.delta,-1),-1),-1)))/1000/60  

-Time spent at speeds lower than X km/h

Distance X to Y  Table Calculation             Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,X), if(lteq(GPS.speed,Y),GPS.distance.delta,-1),-1),-1),-1))

-Distance covered between speeds of X and Y km/h. use this formula for all other speed zones

Time X to Y       Table Calculation,            Aggregate: sumifpositive (safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,X), if(lteq(GPS.speed,Y),GPS.time.delta,-1),-1),-1),-1)))/1000/60

-Time spent between speeds of X and Y km/hr. use this formula for all other speed zones

Distance < 50% MRS Table Calculation     Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(lteq(GPS.speed,(0.5 * Max Sprint Speed)),GPS.distance.delta,-1),-1),-1))

-Distance covered at speeds less than 50% of the athletes individual Maximal Sprint Speed. Use the same formula for TIME  just replace GPS.distance.delta with GPS.time.delta.

Distance 50 to 75 %MRS Table Calculation,     Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,(0.5*Max Sprint Speed)), if(lteq(GPS.speed,(0.75*Max Sprint Speed)),GPS.distance.delta,-1),-1),-1),-1))

-Distance covered at speeds between 50 and 75% of MRS. Use this formula for any other relative speed zones. Use the same formula for TIME  just replace GPS.distance.delta with GPS.time.delta.

Distance > 75%MRS     Table Calculation,           Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,(0.75* Max Sprint Speed)),GPS.distance.delta,-1),-1),-1))

-Distance covered at speeds greater than 75% of MRS.

TIME use the same formula for TIME  just replace GPS.distance.delta with GPS.time.delta.

Translating one of the nested IF statements. What they mean.

AGG: sumifpositive

Formula:  safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,X), if(lteq(GPS.speed,Y),GPS.distance.delta,-1),-1),-1),-1))

What this means

If the time in this row in the raw upload file is greater than or equal to the start time of this drill, AND less than or equal to the end time of this drill, AND if the speed is greater than x km/h, AND the speed is less than Y km/h, THEN give me the distance delta value. If any rows in the file don’t meet all four IF statements then give them a -1 value. This is a table calculation, so go to the next row and do it all again. And keep doing it until you have looked at every row in the file. You will end up with some rows which met all four criteria and some that didn’t. Because I selected the aggregate to be SUMIFPOSITIVE I want you to take all those values that meet the IF statements and add them together to give me the total distance for this drill. SAFE means if all of this ends up giving me a number show me that number, if not, show me a zero, but just don’t give me a blank (because blanks cannot be used in calculations).

The above table of formulas is only concerned with time, distance and speed variables. The same formulas can be used (slightly modify them to reference the columns you want) to perform HR calculations such as average HR in this drill, Max HR in this Drill, etc. if your GPS units export accelerometer data you can also use the same logic to create calculations to provide summary values of acceleration data.

NOTE: the more fields you want the longer this table will be and it will not display nicely in the application.

Now add in your Summary Calculation for the whole session (e.g. total distance and total time)

Now add in your Summary Calculation for the whole session (e.g. total distance and total time)

Now that you have filtered and processed the raw file you can now add in summary calculations for the session excluding times of little relevance.

FIELD NAME                    FIELD TYPE                CALCULATION

Total Distance               Table Calculation        Aggregate: SUM Drill Distance

Total Time                         Table Calculation         Aggregate: SUM Drill Time

Max Speed                    Table Calculation         Aggregate: MAX Max Speed (Drill)

Total Distance < X           Table Calculation         Aggregate: SUM Distance <X

Total Distance X to Y      Table Calculation        Aggregate: SUM Distance X to Y

Total Time <X                Table Calculation         Aggregate: SUM Time <X

Total Time X to Y           Table Calculation       Aggregate: SUM Time X to Y

Total Distance 50 to 75%MRS   Table Calculation        Aggregate: SUM Distance 50 to 75%MRS

Total Time 50 to 75% MRS         Table Calculation        Aggregate: SUM Time 50 to 75% MRS

Repeat as necessary for all absolute and relative speed zones.

If you are collecting HR, data as well you can include additional fields for the HR analysis.

The same page layout and calculations structure can be used for other technologies such as force plate data and accelerometers.  If you need help with any of the calculations contact your local Distributor or Fusion Sport.  

0 Comments

Add your comment

E-Mail me when someone replies to this comment