Search code examples
pythonpandascountfrequency

Count frequency of value in pandas column where values in another column are similar


Given a pandas dataframe that looks as below column_a and column_b. How can I construct 2 additional columns, one that counts the frequency of each value from column_a for all columns, and another that counts the unique number of values from where values in column_a are the same:

column_a | column_b | col_a_count | count_unique_b_where_a
  0           1           4         3
  0           1           4         3
  0           2           4         3
  0           3           4         3
  2           0           3         1
  2           0           3         1
  2           0           3         1 
  5           3           1         1
  9           5           6         5 
  9           5           6         5
  9           3           6         5
  9           4           6         5
  9           2           6         5
  9           1           6         5

Solution

  • Using groupby and agg:

    s = (df.groupby('column_a').agg(
            {'column_a': 'count', 'column_b': 'nunique'}).reindex(df.column_a))
    

              column_a  column_b   
    column_a                       
    0                4         3   
    0                4         3   
    0                4         3   
    0                4         3   
    2                3         1   
    2                3         1   
    2                3         1   
    5                1         1   
    9                6         5   
    9                6         5   
    9                6         5   
    9                6         5   
    9                6         5   
    9                6         5