1
$\begingroup$

I have a dataframe which has three columns as shown below. There are about 10,000 entries in the dataframe 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 dataframe by their mean so that it should look like this:

Hospital 1    District 19   7.0000
Hospital 2    District 10   50.5000

Thanks

$\endgroup$
5
$\begingroup$

As Emre already said 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
| improve this answer | |
$\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()

| improve this answer | |
$\endgroup$
  • $\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 Sadale 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.