Removing duplicates from Pandas dataFrame with condition for retaining original
>>> df
A B
0 1 Ms
1 1 Ms
2 1 Ms
3 1 Ms
4 1 PhD
5 2 Ms
6 2 Ms
7 2 Bs
8 2 PhD
Sorting a dataframe with a custom function:
def sort_df(df, column_idx, key):
'''Takes a dataframe, a column index and a custom function for sorting,
returns a dataframe sorted by that column using that function'''
col = df.ix[:,column_idx]
df = df.ix[[i[1] for i in sorted(zip(col,range(len(col))), key=key)]]
return df
Our function for sorting:
cmp = lambda x:2 if 'PhD' in x else 1 if 'Bs' in x else 0
In action:
sort_df(df,'B',cmp).drop_duplicates('A', take_last=True)
P.S. in modern pandas versions there is no option take_last
, use keep
instead - see the doc.
A B
4 1 PhD
8 2 PhD
Consider using Categoricals
. They're a nice was to group / order text non-alphabetically (among other things.)
import pandas as pd
#create a pandas dataframe for testing with two columns A integer and B string
df = pd.DataFrame([(1, 'Ms'), (1, 'PhD'),
(2, 'Ms'), (2, 'Bs'),
(3, 'PhD'), (3, 'Bs'),
(4, 'Ms'), (4, 'PhD'), (4, 'Bs')],
columns=['A', 'B'])
print("Original data")
print(df)
# force the column's string column B to type 'category'
df['B'] = df['B'].astype('category')
# define the valid categories:
df['B'] = df['B'].cat.set_categories(['PhD', 'Bs', 'Ms'], ordered=True)
#pandas dataframe sort_values to inflicts order on your categories
df.sort_values(['A', 'B'], inplace=True, ascending=True)
print("Now sorted by custom categories (PhD > Bs > Ms)")
print(df)
# dropping duplicates keeps first
df_unique = df.drop_duplicates('A')
print("Keep the highest value category given duplicate integer group")
print(df_unique)
Prints:
Original data
A B
0 1 Ms
1 1 PhD
2 2 Ms
3 2 Bs
4 3 PhD
5 3 Bs
6 4 Ms
7 4 PhD
8 4 Bs
Now sorted by custom categories (PhD > Bs > Ms)
A B
1 1 PhD
0 1 Ms
3 2 Bs
2 2 Ms
4 3 PhD
5 3 Bs
7 4 PhD
8 4 Bs
6 4 Ms
Keep the highest value category given duplicate integer group
A B
1 1 PhD
3 2 Bs
4 3 PhD
7 4 PhD
Assuming uniqueness of B value given A value, and that each A value has a row with Bs in the B column:
df2 = df[df['B']=="PhD"]
will give you a dataframe with the PhD rows you want.
Then remove all the PhD and Ms from df:
df = df[df['B']=="Bs"]
Then concatenate df and df2:
df3 = concat([df2, df])
Then you can use drop_duplicates like you wanted:
df3.drop_duplicates('A', inplace=True)