How do I transform unique row combinations into sorted tuples

user_13781425 0
Viewed 46

I have a dataframe like this:

>>> df = pd.DataFrame([['bar',0],['restaurant',0],
...                    ['bar',0],
...                    ['movie',1],['bar',1],['restaurant',1],
...                    ['bar',2],['restaurant',2],['movie',2]]
...                    ,columns=['place','ID'])
>>> df
        place  ID
0         bar   0
1  restaurant   0
2         bar   0
3       movie   1
4         bar   1
5  restaurant   1
6         bar   2
7  restaurant   2
8       movie   2

I would like to group by the ID and get the unique combinations of places, in order, with repeats allowed. If I do this:

>>> df.groupby('ID')['place'].unique().apply(lambda x: tuple(sorted(x))).reset_index()
   ID                     place
0   0         (bar, restaurant)
1   1  (bar, movie, restaurant)
2   2  (bar, movie, restaurant)

It is almost correct, but I want repeats allowed:

                      place  ID
0    (bar, bar, restaurant)   0
1  (bar, movie, restaurant)   1
2  (bar, movie, restaurant)   2
pandas-groupby pandas dataframe python
1 Answers

I would use groupby with as_index=False without unique. It is also faster if we order first instead of ordering each group.

new_df = (df.sort_values('place')
            .groupby('ID',as_index=False)[['place']]
            .agg(tuple))
print(new_df)

   ID                     place
0   0    (bar, bar, restaurant)
1   1  (bar, movie, restaurant)
2   2  (bar, movie, restaurant)

Or :

(df.sort_values('place')
   .groupby('ID')['place']
   .apply(tuple)
   .reset_index()
)
user_118842370
Related