Assuming I have the following DataFrame:
A | B
1 | Ms
1 | PhD
2 | Ms
2 | Bs
I want to remove the duplicate rows with respect to column A, and I want to retain the row with value 'PhD' in column B as the original, if I don't find a 'PhD', I want to retain the row with 'Bs' in column B.
I am trying to use
df.drop_duplicates('A')
with a condition
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
>>> 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
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)
Sort your columns to put the one you want to keep on the top, then drop_duplicates does the right thing.
import pandas as pd
df = pd.DataFrame([(1, '2022-01-25'),
(1, '2022-05-25'),
(2, '2021-12-20'),
(2, '2021-11-20'),
(3, '2020-03-03'),
(3, '2020-03-04'),
(4, '2019-07-06'),
(4, '2019-07-07'),
(4, '2019-07-05')], columns=['A', 'B'])
print("Original data")
print(df.to_string(index=False) )
#Sort your dataframe so that the one you want is on the top:
df.sort_values(['A', 'B'], inplace=True, ascending=True)
print("custom sort")
print(df.to_string(index=False) )
# dropping duplicates this way keeps first
df_unique = df.drop_duplicates('A')
print("Keep first")
print(df_unique.to_string(index=False) )
Prints:
Original data
A B
1 2022-01-25
1 2022-05-25
2 2021-12-20
2 2021-11-20
3 2020-03-03
3 2020-03-04
4 2019-07-06
4 2019-07-07
4 2019-07-05
custom sort
A B
1 2022-01-25
1 2022-05-25
2 2021-11-20
2 2021-12-20
3 2020-03-03
3 2020-03-04
4 2019-07-05
4 2019-07-06
4 2019-07-07
Keep first
A B
1 2022-01-25
2 2021-11-20
3 2020-03-03
4 2019-07-05