从Pandas dataFrame中删除重复项，并保留原始条件

Assuming I have the following DataFrame:

假设我有以下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.

我想删除关于A列的重复行，并且我想保留B列中值为'PhD'的行作为原始行，如果我没有找到'PhD'，我想保留行B栏中的“Bs”。

I am trying to use

我正在尝试使用

 df.drop_duplicates('A')

with a condition

有条件的

3 个解决方案

#1

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

cmp = lambda x：2如果'PhD'在x中则为1，如果'Bs'在x中则为0

In action:

在行动：

sort_df(df,'B',cmp).drop_duplicates('A', take_last=True)

sort_df（df，'B'，cmp）。drop_duplicates（'A'，take_last = True）

    A   B
4   1   PhD
8   2   PhD

#2

Assuming uniqueness of B value given A value, and that each A value has a row with Bs in the B column:

假设给定A值的B值的唯一性，并且每个A值在B列中具有Bs的行：

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[df['B']=="Bs"]

Then concatenate df and df2:

然后连接df和df2：

df3 = concat([df2, df])

Then you can use drop_duplicates like you wanted:

然后你可以像你想要的那样使用drop_duplicates：

df3.drop_duplicates('A', inplace=True)

#3

Consider using Categoricals. They're a nice was to group / order text non-alphabetically (among other things.)

考虑使用Categoricals。他们很不错的是按字母顺序对文本进行分组/排序（除其他外）。

import pandas as pd
df = pd.DataFrame([(1,'Ms'), (1, 'PhD'), (2, 'Ms'), (2, 'Bs'), (3, 'PhD'), (3, 'Bs'), (4, 'Ms'), (4, 'PhD'), (4, 'Bs')], columns=['A', 'B'])
df['B']=df['B'].astype('category')
# after setting the column's type to 'category', you can set the order
df['B']=df['B'].cat.set_categories(['PhD', 'Bs', 'Ms'], ordered=True)
df.sort(['A', 'B'], inplace=True)
df_unique = df.drop_duplicates('A')

#1

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