Get a frequency count based on multiple dataframe columns

Question

I have the following dataframe.

Group	Size
Short	Small
Short	Small
Moderate	Medium
Moderate	Small
Tall	Large

I want to count the frequency of how many times the same row appears in the dataframe.

Group           Size      Time
Short          Small        2
Moderate       Medium       1 
Moderate       Small        1
Tall           Large        1

Note on performance, including alternatives: [Pandas groupby.size vs series.value_counts vs collections.Counter with multiple series](https://stackoverflow.com/questions/50328246/pandas-groupby-size-vs-series-value-counts-vs-collections-counter-with-multiple) — jpp, Jun 25 '18 at 14:02

score 204 · Accepted Answer · edited Aug 25 '23 at 17:51

You can use groupby's size

import pandas as pd

# read the sample data from the OP
df =  pd.read_html('https://stackoverflow.com/q/33271098/7758804')[0]

Option 1:

dfg = df.groupby(by=["Group", "Size"]).size()

# which results in a pandas.core.series.Series
Group     Size
Moderate  Medium    1
          Small     1
Short     Small     2
Tall      Large     1
dtype: int64

Option 2:

dfg = df.groupby(by=["Group", "Size"]).size().reset_index(name="Time")

# which results in a pandas.core.frame.DataFrame
      Group    Size  Time
0  Moderate  Medium     1
1  Moderate   Small     1
2     Short   Small     2
3      Tall   Large     1

Option 3:

dfg = df.groupby(by=["Group", "Size"], as_index=False).size()

# which results in a pandas.core.frame.DataFrame
      Group    Size  Time
0  Moderate  Medium     1
1  Moderate   Small     1
2     Short   Small     2
3      Tall   Large     1

BENY · Answer 2 · 2020-10-14T13:15:10.827

93

Update after pandas 1.1 value_counts now accept multiple columns

df.value_counts(["Group", "Size"])

You can also try pd.crosstab()

Group           Size

Short          Small
Short          Small
Moderate       Medium
Moderate       Small
Tall           Large

pd.crosstab(df.Group,df.Size)


Size      Large  Medium  Small
Group                         
Moderate      0       1      1
Short         0       0      2
Tall          1       0      0

EDIT: In order to get your out put

pd.crosstab(df.Group,df.Size).replace(0,np.nan).\
     stack().reset_index().rename(columns={0:'Time'})
Out[591]: 
      Group    Size  Time
0  Moderate  Medium   1.0
1  Moderate   Small   1.0
2     Short   Small   2.0
3      Tall   Large   1.0

edited Oct 14 '20 at 13:15

answered May 05 '17 at 21:39

BENY

317,841
20
164
234

7

nice. you can even add `margins=True` to get the marginal counts! – Matt Hancock Jun 27 '17 at 19:43
Also df.value_counts(["Group", "Size"]).reset_index() will turn it into a dataframe – Joe Rivera Mar 01 '22 at 20:23
As you count all columns, you can use `df.value_counts()`. – Mykola Zotko May 26 '22 at 18:05

score 5 · Answer 3 · answered Aug 06 '20 at 17:03

5

Other posibbility is using .pivot_table() and aggfunc='size'

df_solution = df.pivot_table(index=['Group','Size'], aggfunc='size')

answered Aug 06 '20 at 17:03

asantz96

611
5
15

Get a frequency count based on multiple dataframe columns

3 Answers3

Linked

Related