将计数器对象映射到DataFrame以创建新列

Question

我有一个踢足球比赛的数据集，我正试图更仔细地观察每个球队在家里踢的比赛。因此，我试图为每支球队创建一个专栏，其中包含跟踪连续获胜的计数器。应适用以下逻辑：

• 如果获胜：增加计数器=1

df[['Date', 'Home', 'Home result']].head()

|      | Date     | Home           | Home result |
| ---- | -------- | -------------- | ----------- |
| 0    | 1/1/2018 | Stoke City     | Defeat      |
| 1    | 1/1/2018 | Brighton       | Tie         |
| 2    | 1/1/2018 | Burnley        | Defeat      |
| 3    | 1/1/2018 | Leicester City | Victory     |

我创建了一个函数，该函数输出一个可以映射到数据帧（按日期排序）的字典，以创建新的计数器列。

    def result_counter(df):
        d = {}
        for index, row in df.iterrows():
            counter = 0
            for second_index, second_row in df.iloc[:index+1].iterrows(): 
                if second_row['Home result'] == 'Victory':
                    counter += 1
                elif second_row['Home result'] == 'Tie':
                    counter += 0
                else:
                    counter -= 1
            if second_row['Home'] not in d:
                d[second_row['Home']] = [counter]
            else:
                d[second_row['Home']].append(counter)
                
        return d

应创建一个新列，指示主队的连续得分。子集的预期结果应如下所示：

|      | Date       | Home      | Home result | Counter |
| ---- | ---------- | --------- | ----------- | ------- |
| 0    | 2017-08-19 | Liverpool | Victory     | 1       |
| 1    | 2017-08-27 | Liverpool | Victory     | 2       |
| 2    | 2017-09-16 | Liverpool | Tie         | 2       |
| 3    | 2017-10-14 | Liverpool | Defeat      | 1       |

我试图将dict映射到新的数据帧。在执行此操作时，我会显示一个map对象或与字典中的键对应的整个列表。

# with subset and map 
sorted_liverpool['Counter'] = map(sorted_liverpool, d)
sorted_liverpool[['Date', 'Home', 'Home result', 'Counter']].head()

|      | Date       | Home      | Home result | Counter                        |
| ---- | ---------- | --------- | ----------- | ------------------------------ |
| 0    | 2017-08-19 | Liverpool | Victory     | <map object at 0x7fc0650fe550> |
| 1    | 2017-08-27 | Liverpool | Victory     | <map object at 0x7fc0650fe550> |
| 2    | 2017-09-16 | Liverpool | Tie         | <map object at 0x7fc0650fe550> |
| 3    | 2017-10-14 | Liverpool | Defeat      | <map object at 0x7fc0650fe550> |

# with subset and map directly to "home" column
sorted_liverpool['Counter'] = sorted_liverpool['Home'].map(d)
sorted_liverpool[['Date', 'Home', 'Home result', 'Counter']].head()

|      | Date       | Home      | Home result | Counter                                           |
| ---- | ---------- | --------- | ----------- | ------------------------------------------------- |
| 0    | 2017-08-19 | Liverpool | Victory     | [1, 2, 2, 2, 3, 4, 4, 4, 4, 5, 6, 7, 7, 8, 9, ... |
| 1    | 2017-08-27 | Liverpool | Victory     | [1, 2, 2, 2, 3, 4, 4, 4, 4, 5, 6, 7, 7, 8, 9, ... |
| 2    | 2017-09-16 | Liverpool | Tie         | [1, 2, 2, 2, 3, 4, 4, 4, 4, 5, 6, 7, 7, 8, 9, ... |
| 3    | 2017-10-14 | Liverpool | Defeat      | [1, 2, 2, 2, 3, 4, 4, 4, 4, 5, 6, 7, 7, 8, 9, ... |

如何将创建的字典映射到DataFrame，使其仅显示列表中的相应值，而不是整个列表？

Answer 1

识别块（重置一些计数器）的常用技术是进行累加和。因此：

undefeat_strikes = df['Home result'].eq('Defeat').cumsum()

然后，您可以在此基础上分组，并在Home resultbeingVictory上进行另一次累积，以计算胜利数：

df['Counter'] = df['Counter'].eq('Victory').groupby([df['Home'], df['Home result']]).cumsum()

Answer 2

IIUC，你需要地图，然后计数，

df['Result Value'] = df['Home result'].map({'Victory': 1, 'Tie': 0, 'Defeat': -1})
df.groupby(['Home', 'Home result']).sum()

输出

                       Result Value
Home      Home result              
Liverpool Defeat                 -1
          Tie                     0
          Victory                 2