In Pandas DataFrame when each cell is an array sort each subarray

Question

To illustrate my question, consider the following Pandas DataFrame:

df = pd.DataFrame({'player': ['Bob', 'Jane', 'Alice'], 
                   'hand': [['two','ace'], ['queen','king'], ['three','five']]})

I would like to sort each hand array. I've tried using lamdas orlooping through df using iterrow, but I couldn't get either to work.

BONUS: The reason I want it sorted is so I could do a groupby on that column to identify all players having same hand. Perhaps, there is a more direct way of doing it.

Your data is not tabular. I doubt that a dataframe is the appropriate abstraction for your data if you wish to keep it in its current shape. — orlp
– orlp, Commented Dec 2, 2021 at 1:37
@orlp what other abstraction would allow to easily do a groupby on the subarray? — user2759838
– user2759838, Commented Dec 2, 2021 at 12:40

mozway · Accepted Answer · 2021-12-02 01:50:02Z

2

You can apply(sorted):

df['hand'] = df['hand'].apply(sorted)

Output:

  player           hand
0    Bob     [ace, two]
1   Jane  [king, queen]
2  Alice  [five, three]

This won't allow you to group as lists are not hashable.

If your goal is to group or compare, and the cards are unique, you could also use a frozenset:

df['hand'] = df['hand'].apply(frozenset)

Or, if you want to consider duplicated cards (e.g, ace+ace), sort and convert to tuple:

df['hand'] = df['hand'].apply(lambda x: tuple(sorted(x)))

Output:

  player           hand
0    Bob     (two, ace)
1   Jane  (king, queen)
2  Alice  (three, five)

Then you can groupby hand to list the players with the same hand:

df.groupby('hand')['player'].apply(list)

Output:

hand
(ace, two)         [Bob]
(five, three)    [Alice]
(king, queen)     [Jane]
Name: player, dtype: object

edited Dec 2, 2021 at 1:50

answered Dec 2, 2021 at 1:38

mozway

267k13 gold badges56 silver badges106 bronze badges

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

Comments

BENY · Accepted Answer · 2021-12-02 01:41:19Z

2

I will do explode ,for your next step , you can just groupby the hand agg the player

df.explode('hand').groupby('hand').player.agg(list)
hand
ace        [Bob]
five     [Alice]
king      [Jane]
queen     [Jane]
three    [Alice]
two        [Bob]
Name: player, dtype: object

answered Dec 2, 2021 at 1:41

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

user2759838 Over a year ago

This will get us who has each card, but how would you tell who all have the same hand?

user11717481 · Accepted Answer · 2021-12-02 02:05:31Z

0

I think that using sorted is one of the best options, and in this question it is also raised.

>>> df['hand'] = [tuple(sorted(x)) for x in df['hand']]
>>> df
  player           hand
0    Bob     (ace, two)
1   Jane  (king, queen)
2  Alice  (five, three)

answered Dec 2, 2021 at 2:05

user11717481

1,5919 gold badges18 silver badges30 bronze badges

Comments

Loc Quan · Accepted Answer · 2024-08-24 05:31:55Z

0

Since np.sort() is so much faster than sorted(), you can use:

df['hand'] = df['hand'].apply(np.sort)

answered Aug 24, 2024 at 5:31

Loc Quan

1231 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

In Pandas DataFrame when each cell is an array sort each subarray

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related