python drop duplicates_Python Pandas Drop Duplicates保持倒数第二

What's the most efficient way to select the second to last of each duplicated set in a pandas dataframe?

For instance I basically want to do this operation:

df = df.drop_duplicates(['Person','Question'],take_last=True)

But this:

df = df.drop_duplicates(['Person','Question'],take_second_last=True)

Abstracted question: how to choose which duplicate to keep if duplicate is neither the max nor the min?

解决方案

With groupby.apply:

df = pd.DataFrame({'A': [1, 1, 1, 1, 2, 2, 2, 3, 3, 4],

'B': np.arange(10), 'C': np.arange(10)})

df

Out:

A B C

0 1 0 0

1 1 1 1

2 1 2 2

3 1 3 3

4 2 4 4

5 2 5 5

6 2 6 6

7 3 7 7

8 3 8 8

9 4 9 9

(df.groupby('A', as_index=False).apply(lambda x: x if len(x)==1 else x.iloc[[-2]])

.reset_index(level=0, drop=True))

Out:

A B C

2 1 2 2

5 2 5 5

7 3 7 7

9 4 9 9

With a different DataFrame, subset two columns:

df = pd.DataFrame({'A': [1, 1, 1, 1, 2, 2, 2, 3, 3, 4],

'B': [1, 1, 2, 1, 2, 2, 2, 3, 3, 4], 'C': np.arange(10)})

df

Out:

A B C

0 1 1 0

1 1 1 1

2 1 2 2

3 1 1 3

4 2 2 4

5 2 2 5

6 2 2 6

7 3 3 7

8 3 3 8

9 4 4 9

(df.groupby(['A', 'B'], as_index=False).apply(lambda x: x if len(x)==1 else x.iloc[[-2]])

.reset_index(level=0, drop=True))

Out:

A B C

1 1 1 1

2 1 2 2

5 2 2 5

7 3 3 7

9 4 4 9