Pandas - filter dataframe with sorted index

Question

I have this df_player_means, with 5 items:

Pierre-Emerick Aubameyang    0.629630
Sergio Aguero                0.592593
Danny Ings                   0.555556
Mohamed Salah                0.538462
Sadio Mane                   0.500000

And this df_player_colors, where all players and their respective colors appear multiple times, with 15k items:

Andrew Robertson      #CE1317
Dejan Lovren          #CE1317
Joel Matip            #CE1317
Joseph Gomez          #CE1317
Nathaniel Phillips    #CE1317
                       ...   
Michail Antonio       #7C2C3B
Nathan Holland        #7C2C3B
Pablo Fornals         #7C2C3B
Robert Snodgrass      #7C2C3B
Tomas Soucek          #7C2C3B

How do I filter df_player_colors, (or map df_player_means to df_player_colors), ending up with df_player_unique_colors, with players from df_player_means, in that exact sorted order, alongside their respective colors?

I have tried:

 players = df_player_means.index

 df_player_unique_colors = df_player_colors[df_player_colors.index.isin(players)]

But colors being mapped are wrong..

Ben.T · Accepted Answer · 2020-05-17 18:25:00Z

2

IIUC, try with join and drop_duplicates maybe if you have duplicated colors for same player

df_player_means.to_frame(name='mean')\
               .join(df_player_colors.to_frame(name='color'), how='left')\
               .drop_duplicates()

or maybe with loc like:

df_player_colors.loc[df_player_means.index]

edited May 17, 2020 at 18:25

answered May 17, 2020 at 18:20

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

8-Bit Borges Over a year ago

oh, I get 'Series' object has no attribute 'join'

Ben.T Over a year ago

@8-BitBorges if they are Series, then use to_frame to be able to use join, see the edit

Collectives™ on Stack Overflow

Pandas - filter dataframe with sorted index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related