How to change a cell in Pandas dataframe with respective frequency of the cell in respective column

Question

I have a pandas dataframe with binary value columns. I would like to replace values in each cell with its frequency in rspective column in place. My question is how to keep track of the current column while using apply on the subset of columns like here: (to be applied from 8th columns to the end) :

train_data.ix[:,8:] = train_data.ix[:,8:].apply(x: what should come here?)

I know that train_data.ix[:,col_number].value_counts()[0] will return number of zeros in col_number but how can I use it inside apply function?

Answer 1

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randint(2,size=(10, 4)), columns=list('ABCD'))
df


    A   B   C   D
0   0   1   1   1
1   1   0   1   0
2   1   0   0   1
3   0   1   1   1
4   0   0   1   1
5   0   1   1   1
6   0   0   0   0
7   1   1   1   0
8   1   1   1   0
9   1   0   1   0

values = df.apply(pd.value_counts)
values

    A   B   C   D
0   5   5   2   5
1   5   5   8   5

new_df = pd.DataFrame()
for x in df.columns:
    new_df[x] = df[x].apply(lambda row: values[x][row])
new_df


    A   B   C   D
0   5   5   8   5
1   5   5   8   5
2   5   5   2   5
3   5   5   8   5
4   5   5   8   5
5   5   5   8   5
6   5   5   2   5
7   5   5   8   5
8   5   5   8   5
9   5   5   8   5

I created a df with random integers 0 and 1.

Then count them by column in values df.

Then loop through each column and replace each cell with their respective count. Being random it is close to 5/5 split, but you can see the C column has 8/2 split.