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$

2 Answers 2

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, 2016 at 9:48
  • 1
    $\begingroup$ @Nain df = df.groupby(['Hospital_ID', 'District_ID']).mean() $\endgroup$
    – Rohan
    Jul 29, 2016 at 16:46
  • $\begingroup$ Rohan's method is the only way; you can not aggregate in place. $\endgroup$
    – Emre
    Jul 29, 2016 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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