使用原始索引获取Pandas重复行计数

Question

使用原始索引获取Pandas重复行计数

pythonpandasgroup-byaggregatemultiple-columns

3

我需要在Pandas数据框中查找重复行，然后添加一个额外的列以计数。假设我们有一个数据框：

>>print(df)

+----+-----+-----+-----+-----+-----+-----+-----+-----+
|    |   2 |   3 |   4 |   5 |   6 |   7 |   8 |   9 |
|----+-----+-----+-----+-----+-----+-----+-----+-----|
|  0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|  1 |   2 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|  2 |   2 |   4 |   3 |   4 |   1 |   1 |   4 |   4 |
|  3 |   4 |   3 |   4 |   0 |   0 |   0 |   0 |   0 |
|  4 |   2 |   3 |   4 |   3 |   4 |   0 |   0 |   0 |
|  5 |   5 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|  6 |   4 |   5 |   0 |   0 |   0 |   0 |   0 |   0 |
|  7 |   1 |   1 |   4 |   0 |   0 |   0 |   0 |   0 |
|  8 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
|  9 |   4 |   3 |   4 |   0 |   0 |   0 |   0 |   0 |
| 10 |   3 |   3 |   4 |   3 |   5 |   5 |   5 |   0 |
| 11 |   5 |   4 |   0 |   0 |   0 |   0 |   0 |   0 |
| 12 |   5 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
| 13 |   0 |   4 |   0 |   0 |   0 |   0 |   0 |   0 |
| 14 |   2 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
| 15 |   1 |   3 |   5 |   0 |   0 |   0 |   0 |   0 |
| 16 |   4 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
| 17 |   3 |   3 |   4 |   4 |   0 |   0 |   0 |   0 |
| 18 |   5 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |
+----+-----+-----+-----+-----+-----+-----+-----+-----+

上面的框架将变成下面的框架，并增加一列计数。您可以看到我们仍在保留索引列。

+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
|    |   2 |   3 |   4 |   5 |   6 |   7 |   8 |   9 |  10 |
|----+-----+-----+-----+-----+-----+-----+-----+-----|-----|
|  0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   2 |
|  1 |   2 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   2 |
|  2 |   2 |   4 |   3 |   4 |   1 |   1 |   4 |   4 |   1 |
|  3 |   4 |   3 |   4 |   0 |   0 |   0 |   0 |   0 |   2 |
|  4 |   2 |   3 |   4 |   3 |   4 |   0 |   0 |   0 |   1 |
|  5 |   5 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   3 |
|  6 |   4 |   5 |   0 |   0 |   0 |   0 |   0 |   0 |   1 |
|  7 |   1 |   1 |   4 |   0 |   0 |   0 |   0 |   0 |   1 |
| 10 |   3 |   3 |   4 |   3 |   5 |   5 |   5 |   0 |   1 |
| 11 |   5 |   4 |   0 |   0 |   0 |   0 |   0 |   0 |   1 |
| 13 |   0 |   4 |   0 |   0 |   0 |   0 |   0 |   0 |   1 |
| 15 |   1 |   3 |   5 |   0 |   0 |   0 |   0 |   0 |   1 |
| 16 |   4 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   1 |
| 17 |   3 |   3 |   4 |   4 |   0 |   0 |   0 |   0 |   1 |
+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+

我看到其他解决方案，如下所示：

 df.groupby(list(df.columns.values)).size()

但是这会返回一个带有间隙且没有初始索引的矩阵。

- kPow989

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jezrael · Accepted Answer

您可以先使用reset_index将index转换为列，然后再通过first和len进行aggregate操作：

另外，如果需要按所有列进行分组，必须通过difference删除index列：

print (df.columns.difference(['index']))
Index(['2', '3', '4', '5', '6', '7', '8', '9'], dtype='object')

print (df.reset_index()
         .groupby(df.columns.difference(['index']).tolist())['index']
         .agg(['first', 'size'])
         .reset_index()
         .set_index(['first'])
         .sort_index()
         .rename_axis(None))

    2  3  4  5  6  7  8  9  size
0   0  0  0  0  0  0  0  0     2
1   2  0  0  0  0  0  0  0     2
2   2  4  3  4  1  1  4  4     1
3   4  3  4  0  0  0  0  0     2
4   2  3  4  3  4  0  0  0     1
5   5  0  0  0  0  0  0  0     3
6   4  5  0  0  0  0  0  0     1
7   1  1  4  0  0  0  0  0     1
10  3  3  4  3  5  5  5  0     1
11  5  4  0  0  0  0  0  0     1
13  0  4  0  0  0  0  0  0     1
15  1  3  5  0  0  0  0  0     1
16  4  0  0  0  0  0  0  0     1
17  3  3  4  4  0  0  0  0     1

如果需要添加下一列 10，需要重命名：rename。

#if necessary convert to str
last_col = str(df.columns.astype(int).max() + 1)
print (last_col)
10

print (df.reset_index()
        .groupby(df.columns.difference(['index']).tolist())['index']
        .agg(['first', 'size'])
        .reset_index()
        .set_index(['first'])
        .sort_index()
        .rename_axis(None)
        .rename(columns={'size':last_col}))

    2  3  4  5  6  7  8  9  10
0   0  0  0  0  0  0  0  0   2
1   2  0  0  0  0  0  0  0   2
2   2  4  3  4  1  1  4  4   1
3   4  3  4  0  0  0  0  0   2
4   2  3  4  3  4  0  0  0   1
5   5  0  0  0  0  0  0  0   3
6   4  5  0  0  0  0  0  0   1
7   1  1  4  0  0  0  0  0   1
10  3  3  4  3  5  5  5  0   1
11  5  4  0  0  0  0  0  0   1
13  0  4  0  0  0  0  0  0   1
15  1  3  5  0  0  0  0  0   1
16  4  0  0  0  0  0  0  0   1
17  3  3  4  4  0  0  0  0   1