How to keep only the first and last item of a pandas groupby join

user_13132728 0
Viewed 153

Let's say I have the following df:

pd.DataFrame({'x':['bob','bob','bob','amy','amy','amy'],'y':['keep this','not this','keep this','keep this','not this','keep this']})


     x  y
0   bob keep this
1   bob not this
2   bob keep this
3   amy keep this
4   amy not this
5   amy keep this

And I want to group by x, and join y. However, I only want the first and last items of the join.

Doing a basic group by join, this is the result:

df.groupby('x').agg({'y':', '.join})

          y
x   
amy keep this, not this, keep this
bob keep this, not this, keep this

However, my desired output is below:

          y
x   
amy keep this, keep this
bob keep this, keep this

Is there a way I can do this programmatically? I tried using some indexing and slicing tricks as part of the join but got the following error:

TypeError: 'builtin_function_or_method' object is not subscriptable

It would be really nice if there were a way to do this. Thanks!

pandas python
4 Answers

You can groupby twice, once to keep first/last, and once for your aggregation:

(df.groupby('x', as_index=False)
   .apply(lambda d: d.iloc[[0,-1]])
   .groupby('x')
   .agg({'y':', '.join})
)

output:

                        y
x                        
amy  keep this, keep this
bob  keep this, keep this
user_163434640

Just use the first and last appearences in the groupby:

df.drop_duplicates('x', keep='first').append(df.drop_duplicates('x', keep='last')).groupby('x').agg({'y':', '.join})
user_119450020

You can use groupby.first and groupby.last, concatenate them into a dataframe and apply groupby again with the join.

df = pd.DataFrame({'x':['bob','bob','bob','amy','amy','amy'],'y':['keep this','not this','keep this','keep this','not this','keep this']})
df

# Define groups of x values.
g = df.groupby('x')

g.first().append(g.last()).groupby('x').agg({'y': ', '.join})
user_163274760

You can use:

  1. GroupBy.first() + GroupBy.last().
  2. Join them by +
  3. convert resulting series to dataframe by .to_frame()

as follows:

(df.groupby('x')['y'].first() + ', ' + df.groupby('x')['y'].last()
).to_frame()

Result:

                        y
x                        
amy  keep this, keep this
bob  keep this, keep this
user_150706970
Related