Replace all values in Dataframe with their count per column

user_12543122 0
Viewed 119
import pandas as pd

data = ([1, 1], [2, 3], [4, 5], [1, 2], [3, 4], [3, 2])
df = pd.DataFrame(data)

print(df)

   0  1
0  1  1
1  2  3
2  4  5
3  1  2
4  3  4
5  3  2

The desired output would be a count of all occurrences for each value in df. Desired output below:

   0  1
0  2  1
1  1  1
2  1  1
3  2  2
4  2  1
5  2  2

There are 2 "1"'s in the first column, 1 "2", 1 "4", 2 "3"'s. ETC for each column.

pandas python
3 Answers

You can map each column (Series s) to its value count:

>>> df.apply(lambda s: s.map(s.value_counts()))                                                                                        
   0  1
0  2  1
1  1  1
2  1  1
3  2  2
4  2  1
5  2  2
user_36200030

One way is using transform on each columns:

df.apply(lambda x: x.groupby(x).transform('count'))

Or using Counter with replace:

from collections import Counter
df.replace(df.apply(Counter))

   0  1
0  2  1
1  1  1
2  1  1
3  2  2
4  2  1
5  2  2
user_98406370

Alternatively you can leverage numpy.unique:

import numpy as np

def count_values(s):
    _, idx, cnt = np.unique(s, return_inverse=True, return_counts=True)
    return cnt[idx]

df.apply(count_values)
   0  1
0  2  1
1  1  1
2  1  1
3  2  2
4  2  1
5  2  2
user_49834500
Related