1
$\begingroup$

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?

$\endgroup$
  • 1
    $\begingroup$ With these sorts of questions, it is very beneficial if you provide some sample data and expected outputs for said data. $\endgroup$ – Stephen Rauch Jun 12 '17 at 16:45
  • $\begingroup$ df.ix[:,8:] = df.ix[:,8:].mean() * df.ix[:,8:] if there are 1s and 0s only $\endgroup$ – oW_ Jun 12 '17 at 23:32
  • 1
    $\begingroup$ btw value_counts() is sorted, so value_counts()[0] would return the most frequent element and not necessarily the number of 0s $\endgroup$ – oW_ Jun 12 '17 at 23:33
  • $\begingroup$ Thx for suggestions $\endgroup$ – Kaggle Jun 15 '17 at 9:00
1
$\begingroup$
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.

$\endgroup$
  • $\begingroup$ Thx for your solution! $\endgroup$ – Kaggle Jun 15 '17 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.