计算数据框中特定值的出现次数，其中所有可能的值均由列表定义 [英] Count occurences of specific values in a data frame, where all possible values are defined by a list

问题描述

我有两个类别 A 和 B ，它们可以采用列表中定义的 5种不同状态(值，名称或类别) abcde .计算每种状态的发生并将其存储在数据帧中非常容易.但是，我也希望结果数据框为类别 A 或 B 中未出现的可能值包括零.

I have two categories A and B that can take on 5 different states (values, names or categories) defined by the list abcde. Counting the occurence of each state and storing it in a data frame is fairly easy. However, I would also like the resulting data frame to include zeros for the possible values that have not occured in Category A or B.

首先，这是一个与描述相匹配的数据框:

First, here's a dataframe that matches the description:

在[1]中:

import pandas as pd
possibleValues = list('abcde')
df = pd.DataFrame({'Category A':list('abbc'), 'Category B':list('abcc')})
print(df)

出[1]:

        Category A      Category B
0       a               a
1       b               b
2       b               c
3       c               c

我用df.groupby(...).size()和.count()尝试了不同的方法，并结合了可能的值列表和列表中的类别名称，但没有成功.

I've tried different approaches with df.groupby(...).size() and .count() , combined with the list of possible values and the names of the categories in a list, but with no success.

这是所需的输出:

        Category A      Category B
a       1               1
b       2               1
c       1               2
d       0               0
e       0               0

为了更进一步，我还想添加一列，其中包含所有类别中每种可能状态的总计:

To go one step further, I'd also like to include a column with the totals for each possible state across all categories:

        Category A      Category B      Total
a       1               1               2
b       2               1               3
c       1               2               3
d       0               0               0
e       0               0               0

SO已经获得了许多相关的问题和答案，但是据我所知，没有一个问题可以提出解决此特定问题的方法.谢谢您的任何建议！

SO has got many related questions and answers, but to my knowledge none that suggest a solution to this particular problem. Thank you for any suggestions!

PS

我想使解决方案适应类别数，可能的值和行数.

I'd like to make the solution adjustable to the number of categories, possible values and number of rows.

推荐答案

需要 apply + + sum :

Need apply + value_counts + reindex + sum:

cols = ['Category A','Category B']
df1 = df[cols].apply(pd.value_counts).reindex(possibleValues, fill_value=0)
df1['total'] = df1.sum(axis=1)
print (df1)
   Category A  Category B  total
a           1           1      2
b           2           1      3
c           1           2      3
d           0           0      0
e           0           0      0

另一种解决方案是将列转换为分类，然后转换为添加的值不带reindex:

Another solution is convert columns to categorical and then 0 values are added without reindex:

cols = ['Category A','Category B']
df1 = df[cols].apply(lambda x: pd.Series.value_counts(x.astype('category', 
                                                                categories=possibleValues)))
df1['total'] = df1.sum(axis=1)
print (df1)
   Category A  Category B  total
a           1           1      2
b           2           1      3
c           1           2      3
d           0           0      0
e           0           0      0

这篇关于计算数据框中特定值的出现次数，其中所有可能的值均由列表定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！