2020: Week 19

This week’s challenge is a follow-up from last week (Week 18) as we want to do some further analysis!

Last week we found out how many minutes each player had played. Now we want to do some analysis about what positions they were playing in, and how many goals were scored.

We are again going to use the lineup data source from last week, then also add some new data sources:

1. Player List. A list of all players at Liverpool, and their preferred position group. The numbers at the start of the string is their squad number, and not the position number from the lineup table.

2. Position List.  This provides us with data about the formations that Liverpool have used, and how these player numbers mapped to positions and position types.

Requirements

• Input the following: 
• Your Week 18 Workflow
• Player List 
• Position List

All of these can be downloaded here.

We require two outputs this week; they have been broken down here:

Output 1

• Calculate how many goals Liverpool and their Opposition scored in each game. 

Answer the following question:

• For each Liverpool and Opposition formation, what were the average Goals Scored/Conceded for each combination?

The final output: 

Output 2

• Clean the Player List, so we are left with Player Name & Preferred Position Type

Position Types are:

• G = Goalkeeper
• D = Defender
• M = Midfielder
• A = Attacker

• Join the Line-up, Formation Positions, and Player List tables
• Work out what the substitutes position is when he enters the field. We will assume that if a sub is made, the formation doesn’t change, and the player is a direct replacement in the same position. Therefore, the sub off position will equal the sub on position. 

Answer the following questions:

• How many times did each player play in each position? 
• How many minutes did they play in each position? 
• A player has a preferred type of position, how many times did they play not in that position type (Out of Position)?

A sample out of the final output:

The full outputs can be found here for comparison. Don’t forget to fill the participation tracker and share your solutions using #PreppinData on Twitter.