The Tracab .dat and .xml file combination allow us to really delve in to player tracking data more then ever before. Whether it's to analysis players' physical output at a very granular level or to analyse movement patterns of individuals and teams the Tracab data is fantastic.
Using your .dat and .xml files from Tracab and the Alteryx workflow below you can parse out the data to make it meaningful to yourself. This then allows you to go one step beyond the general summary data provided and actually analyse what you want at whatever level of detail that you wish.
You will see that I create a distance calculation as my final step in this workflow, but we can go way beyond this. Because we have the XY co-ordinates for every player for every 0.04 seconds of the game we can use the vast array of alteryx tools to answer pretty much any question we may have. For examples of what can be done with the Tracab dat and by joining it with Opta data please visit here: https://public.tableau.com/profile/brian.prestidge#!/vizhome/WhatCanTracabDataProvideAnalysts/Tracab
Any queries please email me: brian.prestidge@theinformationlab.co.uk
Parsing
Select Tool to rename the fields and also to change the data types of some fields from V_String (text) to Double (number)
Formula Tool with 2 different formulas, one to give the ball owning team a value of home or away (or null) depending on the value of "H"/"A"/Null. The 2nd formula gives each player's team or referee a name based upon the number they have been allocated (-1/0/1).
Text To Columns Tool: This tool looks at Field_3 which contains details about the ball position and possession information. This tool splits out each "chunk" of information which are seperated by a , (comma).
Each Chunk represents (in order): X Position, Y Position, Speed, Ball Owning Team, Ball Status, Ball Contact Info.
Text To Columns Tool: This tool looks at Field_2 and splits out the data at each ;(semi-colon). Each split represents a different player on the pitch and is placed on to a new row so that we have one row per frame per player.
Multi-Row Formula Tools that looks at the seconds value in the previous row and adds 0.04 seconds (the time between each frame in the Tracab tracking data).
There are 5 Multi-Row Formula Tools; one that looks at the number of seconds so far in the match (inclduing stoppage time), and the other four look at the number of seconds in each half.
Filter Tool that removes all records (rows of data) that dont belong to a half. Ie. all data prior to the start of the match or after the end of the match.
Formula Tool that gives the start starting frame of each half a time (in seconds). For example, the start of the first half has Half1Seconds of 0.00, the start of the second half has Half2Seconds of 2700.00 (45 mins) etc...
Formula Tool that gives provides a HalfID using an IF formula based upon the FrameID and the Start and End Frame IDs of each half.
The ToNumber function used in this calculation converts the FrameIDs from a string (text) to a number so it can be used in this calculation.
Filter Tool: We take the True output of this tool which only passes through data where the Team Type is not "Unassigned" or "Referee" (ie. just the teams).
Chunk 3-5=IF [Chunk 3-5]="A" THEN "Away" ELSEIF [Chunk 3-5]="...
Chunk 2-1=IF [Chunk 2-1]="1" THEN "Home" ELSEIF [Chunk 2-1]="...
Chunk 2-3=IF [Chunk 2-1]="Referee" THEN "Referee"
ELSEIF [Chu...
Chunk 3-6
Replace
Chunk 3-6
Warn
Chunk 3-6_Matched
Field_3
Error
Chunk 3-
MatchSeconds
NewField
Float
4
Empty
IF [MatchSeconds]=0 THEN 0
ELSE[Row-1:MatchSeconds]+0.04
ENDIF
IF [MatchSeconds]=0 THEN 0
ELSE[Row-1:MatchSeconds]+0.04
ENDIF
FileName
Distance (cm)
Int32
254
Empty
SQRT(([Row+1:X_Player]-[X_Player])*([Row+1:X_Player]-[X_Player])
+(([Row+1:Y_Player]-[Y_Player])*([Row+1:Y_Player]-[Y_Player])))
SQRT(([Row+1:X_Player]-[X_Player])*([Row+1:X_Player]-[X_Playe...
HalfID=IF ToNumber([FrameID])>=ToNumber([Half1StartFrameID]) ...
[TeamType] != "Unassigned" AND [TeamType] != "Referee"
Custom
[TeamType] != "Unassigned" AND [TeamType] != "Referee"
Distance (m)=[Distance (cm)]/100
Simple
HalfID
IsNotNull
!IsNull([HalfID])
Half4Seconds
NewField
Float
4
Empty
IF [Half4Seconds]=6300.00 THEN 6300.00
ELSEIF [HalfID]="4" THEN [Row-1:Half4Seconds]+0.04
ELSE Null()
ENDIF
IF [Half4Seconds]=6300.00 THEN 6300.00
ELSEIF [HalfID]="4" TH...
Configuration for the Input Tool above:
File Format: Comma-Delimited Text File (.csv)
Output Filename as Field: Filename
Delimiters: : (colon)
Field Length: 2564
Select the Input Tool below and in the configuration pane choose the location of your file Tracab .dat file (ie. 756526_Leicester-Newcastle.dat))
This Regex Tool removes any non-word character (ie. colon or semi-colon) from the last chunk (Chunk 3-6) by replacing any of this characters with "" (nothing).
Output Tool: change the configuration settings to alter the file type and save location of your output file (currently set to .tde (Tableau Data Extract)).
Formula Tool that gives Chunk 2-3 (Home/Away/Referee) the values of Home/Away/Referee or "Unassigned" where there is additional data (used for internal purposes by Tracab).
Half2Seconds
NewField
Float
4
Empty
IF [Half2Seconds]=2700.00 THEN 2700.00
ELSEIF [HalfID]="2" THEN [Row-1:Half2Seconds]+0.04
ELSE Null()
ENDIF
IF [Half2Seconds]=2700.00 THEN 2700.00
ELSEIF [HalfID]="2" TH...
Field_2
Half1Seconds
NewField
Float
4
Empty
IF [Half1Seconds]=0.00 THEN 0.00
ELSEIF [HalfID]="1" THEN [Row-1:Half1Seconds]+0.04
ELSE Null()
ENDIF
IF [Half1Seconds]=0.00 THEN 0.00
ELSEIF [HalfID]="1" THEN [Ro...
755526_Hull-Newcastle.dat
28591
:
False
2564
False
False
DoubleQuotes
1
755526_Hull-Newcastle.dat
MatchSeconds=IF [FrameID]=[Half1StartFrameID] THEN 0.00 ELSE ...
Half1Seconds=IF [FrameID]=[Half1StartFrameID] THEN 0.00 ELSE ...
Half2Seconds=IF [FrameID]=[Half2StartFrameID] THEN 2700.00 EL...
...
Field_2
Warn
Chunk 2-
TracabOutput.tde
Overwrite
Suffix
FileName
TracabOutput.tde
Multi-Row Formula Tool: Uses Pythagoras Theorum to calculate the distance the player travels in that frame. Divided by 100 to convert from cm to metres.
Text To Columns Tool: This tool looks at Field_2 and splits out each piece of player data into chunks. These "chunks" of information are seperated by a , (comma).
Each Chunk represents (in order): Team, Tracking ID, Shirt Number, X Position, Y Position, Speed.
Half3Seconds
NewField
Float
4
Empty
IF [Half3Seconds]=5400.00 THEN 5400.00
ELSEIF [HalfID]="3" THEN [Row-1:Half3Seconds]+0.04
ELSE Null()
ENDIF
IF [Half3Seconds]=5400.00 THEN 5400.00
ELSEIF [HalfID]="3" TH...
XML Parse (Match Details)
755526_Hull-Newcastle_metadata.xml
True
True
False
True
755526_Hull-Newcastle_metadata.xml
TracabMetaData_OuterXML
period
,
,
Configuration for the Input Tool above:
File Format: XML Files
Output Filename as Field: Filename
Return Root Element: Ticked
Return Child Elements: Ticked
Return Outer XML: Ticked
Filename=Replace([FileName],"_metadata", "")
Select the Input Tool below and in the configuration pane choose the location of your file Tracab .xml file (ie. 756526_Leicester-Newcastle_metadata.xml)
Horizontal
Tracab DAT Parse Example