# Replacing column values in pandas

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


## 2 Answers

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


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()

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