How to conditionally choose column value per key from long format pandas data frame with multiple values per key? group-by and then if-then?

user_12400925 0
Viewed 33

I have a data frame that has a person's name and the positions they have played per year. It is in long format with multiple entries per person. I would like to make 1 data frame for all years with just one entry per person.

I am thinking about using groupby for this. However, I don't know how to handle the position titles. A person can have either forward, offence, or both. What I would like to do is if a person has entries for forward AND offence, to put their position as "both forward and offence" OR if a person has forward, offence and both, to pick "both forward and offence", OR if a person has just forward, or just offence, to take what they have.

I have NO idea where to start though. I have tried googling this but I think I don't know the right terms because nothing useful is coming up. I am thinking of using group-by with an if-then statement after but I am not sure. Any advice or even a suggestion of what terms to google for this would be much much appreciated!

Input dataset:

Name Position
Tom Forward
Tom Offence
Aiden​ Forward
Aiden​ Offence
Aiden​ Both Forward and Offence
Kristy Forward
Kristy Forward 
data = {'Name': ['Tom', 'Tom', 'Aiden', 'Aiden', 'Aiden', 'Kristy', 'Kristy'], 
        'Position': ['Forward', 'Offence', 'Forward', 'Offence', 
                     'Both Forward and Offence', 'Forward', 'Forward']}
df = pd.DataFrame(data)

Ideal output dataset:

Name Position
Tom Both Forward and Offence
Aiden​ Both Forward and Offence
Kristy Forward
pandas-groupby pandas python
1 Answers

You were on the right idea with groupby and if-else. You can see your problem a bit more simple as: if the number of unique position (nunique) per name is 1, you want this one, else 'Both Forward and Offence' so a simple way is.

res = (
    df.groupby('Name', sort=False)
      ['Position'].apply(lambda x: x.min() if x.nunique()==1 
                                   else 'Both Forward and Offence')
      .reset_index()
)
print(res)
#      Name                  Position
# 0     Tom  Both Forward and Offence
# 1   Aiden  Both Forward and Offence
# 2  Kristy                   Forward

the use of x.min() is to select one value in case like Kristy you have several rows with the same position, but could be x.max(), x.iloc[0], ...

user_92747320
Related