Python Pandas Countif

Question

I like to build the counting tool.

I used to use the COUNTIF function in excel =COUNTIF($L$2:$L$3850,"AAA"). but, I am not sure there is similar function in python pandas.

This is my dataframe

#        2015  2016  2017
#  0      AAA   AA    AA
#  1      AA    AA    A
#  2      AA    A     A

I want to count this dataframe like this:

#        2015  2016  2017
#  AAA    1     0     0
#  AA     2     2     1
#  A      0     1     2 

How can I start the code? any tips?

Thanks in advance.

Answer 1

Apply value_counts

df.apply(pd.value_counts).fillna(0).astype(int)
Out[203]: 
     2015  2016  2017
A       0     1     2
AA      2     2     1
AAA     1     0     0

Answer 2

Using cross_tab

df.stack().pipe(lambda s: pd.crosstab(s, s.index.get_level_values(1)))

col_0  2015  2016  2017
row_0                  
A         0     1     2
AA        2     2     1
AAA       1     0     0

---

With get_dummies

pd.get_dummies(df.values.ravel()).T.dot(
    pd.get_dummies(df.columns.repeat(len(df)))
)

     2015  2016  2017
A       0     1     2
AA      2     2     1
AAA     1     0     0