Value counts for two columns inside the same table-CodePudding

I'm trying to count values in two columns and then put the results in the same table.

dict = { "before": list("ABCDEFABDCFEFF"),
         "after" : list("FABFCFFEEDEBFF") }

df = pd.DataFrame(dict)

Output

   before after
0       A     F
1       B     A
2       C     B
3       D     F
4       E     C
5       F     F
6       A     F
7       B     E
8       D     E
9       C     D
10      F     E
11      E     B
12      F     F
13      F     F

I've achieved something close to what I want, but this looks messy, and I'm hoping for a "smoother" solution.

df.melt().groupby("variable")["value"].value_counts().to_frame().unstack()

Output:

         value               
value        A  B  C  D  E  F
variable                     
after        1  2  1  1  3  6
before       2  2  2  2  2  4

CodePudding user response：

df.apply(lambda x: x.value_counts())

CodePudding user response：

If you want to have before and after as the row indexes as shown in your current output, you should use the following.

df.apply(lambda x: x.value_counts()).transpose()

CodePudding user response：

A different way with melt using pivot_table:

>>> df.melt().assign(count=1).pivot_table('count', 'variable', 'value', aggfunc='count')

value     A  B  C  D  E  F
variable                  
after     1  2  1  1  3  6
before    2  2  2  2  2  4