Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

406
Visualizações
Remove consecutive duplicates while keeping the max value

I am trying to remove consecutive duplicates from column X while keeping the entry with the max value based on column Y, unfortunately with no success. The data frame is as follow:

idx X Y
0 A 3
1 B 2
2 A 7
3 A 10
4 B 1
5 C 4
6 A 3
7 A 3

What I want to achieve is:

idx X Y
0 A 3
1 B 2
3 A 10
4 B 1
5 C 4
7 A 3

Most of the solutions I found just remove the duplicates tout court without accounting for any repeating pattern.

Please note that the duplicates might have the same value.

over 4 years ago · Santiago Trujillo
3 Respostas
Responde à pergunta

0

You need to apply an itertools-style-groupby and then keep the rows where Y is maximal.

>>> df 
   idx  X   Y
0    0  A   3
1    1  B   2
2    2  A   7
3    3  A  10
4    4  B   1
5    5  C   4
6    6  A   3
7    7  A   5
>>> y_max = df.groupby(df['X'].ne(df['X'].shift()).cumsum())['Y'].transform('max')
>>> df[df['Y'] == y_max] 
   idx  X   Y
0    0  A   3
1    1  B   2
3    3  A  10
4    4  B   1
5    5  C   4
7    7  A   5

edit:

Initial solution had a bug and only produced the correct idx column by accident.

edit 2:

If you only want to keep one row per group, you can use

>>> y_idxmax = df.groupby(df['X'].ne(df['X'].shift()).cumsum())['Y'].idxmax()
>>> df.loc[y_idxmax] 
   idx  X   Y
0    0  A   3
1    1  B   2
3    3  A  10
4    4  B   1
5    5  C   4
7    7  A   5

Credit goes to Ch3steR for this one.

over 4 years ago · Santiago Trujillo Relatório

0

Or I'd prefer just simply only specify the groups in the groupby parameters:

df.groupby(df['X'].ne(df['X'].shift()).cumsum(), as_index=False).max()

Or:

df.groupby(df['X'].ne(df['X'].shift()).cumsum()).max().reset_index(drop=True)

Both output:

   idx  X   Y
0    0  A   3
1    1  B   2
2    3  A  10
3    4  B   1
4    5  C   4
5    7  A   5
over 4 years ago · Santiago Trujillo Relatório

0

Create a column which heaps consecutives into one group

   df['temp']=(~(df['X']==df['X'].shift())|(df['X'].shift(-1)==df['X'])).cumsum()

groupby the group of consecutives and filter out wherevalues of Y are equal to maximum in each group. Drop the temp column

df[df.groupby('temp')['Y'].transform(lambda x:(x==x.max()))].drop(columns=['temp'])

A neater way is not to create column but save the groups of consecutives into a variable and groupby the variable as follows

s=(~(df['X']==df['X'].shift())|(df['X'].shift(-1)==df['X'])).cumsum()
print(df[df.groupby(s)['Y'].transform(lambda x:(x==x.max()))])

    idx  X   Y
0    0  A   3
1    1  B   2
3    3  A  10
4    4  B   1
5    5  C   4
7    7  A   5
over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda