tabliea Prepare挑战 2020/w18, 足球队员上/下场次数表

Prep挑战 2020/w18,--足球队员被替换次数表

https://preppindata.blogspot.com/2020/04/2020-week-18.html

本次数据规整挑战，使用了利物浦队在2019年的参赛表。

包括3大块数据：

1. Match Details, 比赛日期，对手，主客场，比分结果，双方的队形。
2. 队员名单：Starting XI，开始上场的队员名单。Substitutes，替补队员名单。
3. 替换表：三次替换的情况。换下队员号，换上队员号，替换时间。

问题，对每个队员的上场情况分析：

• 每名队员出场的次数。
• 平均下来，每名队员每场比赛的在场时间。只计算他首次出场的情况。

需要计算：

判断本场比赛，这个队员是否被替换，什么时间被替换。

规整

第一步： 清理输入数据结构。

所有数据在一张大表中。需要删除空列。并修改表头。使用Prep数据解释器，一键清理即可。

1.自动生成的列名带前缀：如Match Details Opposition，需要改为Opposition。

• 最简单的就是直接改。✅
• 批量改，需要线列转行，然后使用replace, 然后再行转列。我觉得数据量非常大的情况下不适用。

2.把替换表的sub1, sub1 1, sub1 2改成便于理解的sub1 on, sub1 off, sub1 time。其他6个列也是如此。

3. 队员名单：转置Starting XI和Substitutes两类的列，列转行，因为目标就是分析球员的上/下场次。

• Starting XI重命名为Player Names
• 转置1名称 重命名为 Player Number
• Player Number, 移除字母和空格，只保留数字。1-11即开始上场的队员，12-18是替补队员。

第二步： 回答问题。

第一步得到了我们需要的表格结构，第二步，就是使用这个表格来得到我们想要的答案。

 

2个问题：

•    每名队员出场的次数。
•    平均下来，每名队员每场比赛的在场时间。

这2个问题的答案隐藏在替换列中, 即sub* on , sub* off, sub* time。

 

第一个问题：每名队员出场的次数。

经过第一步清理的表，一条记录代表一个队员一场比赛的情况。因此判断这个队员是否出场，然后进行聚合函数统计count即可知道。

1. 是主力， 确认出场。"Player Number <=1 "

2.是替补，再判断, 需要替换表的列进行判断。从sub* on的列是否等于替补的队员编号。

• 是，替换上场了，确认出场。
• 否，未替换上场，未出场。

 

第2个问题：  平均下来，每名队员每场比赛的在场时间。

首先，判断每名队员每场比赛的在场时间。

1.是主力，并没有下场90分

2.替补，没有上场0分。

3.是主力，被替换下场。sub * time，为其在场时间

4.是替补，被替换上场。90 - sub * time，为其在场时间。

然后，聚合函数统计，每名队员的所有在场时间，和上场次数。两者相除即平均在场时间。

---

以下是官方给定的方法，大同小异。

我们要知道这个队员是否被替换上/下场，替换发生的时间，替换属于第几次替换（每场比赛最多有3次替换机会）

首先，判断球员，一场比赛是否被替换上场/下场。代码类似：

IF [Player Number] = [sub1 off] 
OR [Player Number] = [sub2 off]
OR [Player Number] = [sub3 off] 
THEN TRUE
ELSE FALSE 
END

增加两个字段sub on?和sub off?

然后，利用这两个字段，再创建字段Sub Number, 即第几次替换。并创建Sub Time, 替换发生的时间。

最后，移除sub1 on, sub1 off之类的无用字段。

最终问题：每名队员平均每场比赛的在场时间？

首先得计算出这名队员所有场次的在场时间，然后求平均值。

分4种情况：

• 如果有替换情况，替换上场或替换下场，分辨计算时间，
• 没替换情况则是整场都上场90分钟或整场都坐冷板凳0分钟。

IF [Subbed Off?] = TRUE 
 THEN [Subbed On/Off Time]

ELSEIF [Subbed On?] = TRUE 
 THEN 90-[Subbed On/Off Time]

ELSEIF [Player Number]>=12
 AND 
    ISNULL([Sub Number]) 
    THEN 0

ELSE 90
END

然后，聚合操作，求平均值。

最终问题：每名队员出场的次数？

根据队员编号Player Numbers <= 11 则出场了，如果是替补，被替换上场isnull(Sub Number)是False则也代表出场：

IF [Player Number] <= 11 OR not ISNULL([Sub Number])
    THEN 1
ELSE 0
END

然后，这一步和上一步一起聚合。

平均值相除即可求出。