1
$\begingroup$

I have a data frame which has three columns as shown below. There are about 10,000 entries in the data frame and there are duplicates as well.

Hospital_ID   District_ID  Employee
Hospital 1    District 19   5 
Hospital 1    District 19   10
Hospital 1    District 19   6
Hospital 2    District 10   50
Hospital 2    District 10   51

Now I want to remove the duplicates but I want to replace the values in my original data frame by their mean so that it should look like this:

Hospital 1    District 19   7.0000
Hospital 2    District 10   50.5000
$\endgroup$
6
$\begingroup$

As Emre already mentioned, you may use the groupby function. After that, you should apply reset_index to move the MultiIndex to the columns:

import pandas as pd

df = pd.DataFrame( [ ['Hospital 1', 'District 19', 5],
                     ['Hospital 1', 'District 19', 10],
                     ['Hospital 1', 'District 19', 6],
                     ['Hospital 2', 'District 10', 50],
                     ['Hospital 2', 'District 10', 51]], columns = ['Hospital_ID', 'District_ID', 'Employee'] )

df = df.groupby( ['Hospital_ID', 'District_ID'] ).mean()

which gives you:

  Hospital_ID  District_ID  Employee
0  Hospital 1  District 19       7.0
1  Hospital 2  District 10      50.5
$\endgroup$
2
$\begingroup$

What you want to do is called aggregation; deduplication or duplicate removal is something else. I think the code self-explanatory:

df.groupby(['Hospital_ID', 'District_ID']).mean()

$\endgroup$
3
  • $\begingroup$ But this will not replace the values in original dataframe and I want to replace the original values by the mean values in the original dataframe $\endgroup$
    – enterML
    Jul 29 '16 at 9:48
  • 1
    $\begingroup$ @Nain df = df.groupby(['Hospital_ID', 'District_ID']).mean() $\endgroup$
    – Rohan
    Jul 29 '16 at 16:46
  • $\begingroup$ Rohan's method is the only way; you can not aggregate in place. $\endgroup$
    – Emre
    Jul 29 '16 at 17:14

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.