0

This isn't a straightforward pivot question. I don't want to create new named columns (or numbered ones).

What I am looking for is to find a way to search for actors that satisfy the asked question, such as:

Any ACTOR that has UP and DOWN

in the table below.

Actors Events
A Up
B Up
C Left
A Down
D Left
C Down
C Up

The expected answer should be

[A,C]

So, using just event column to search for more than one event and returning the actor(s) that fulfill this requirement.

I have a set like this:

Actors Events
A Up
B Up
C Left
A Down
D Left
C Down
C Up

I want to find all actors that has events intersection. Like:

Any ACTOR that has UP and DOWN

So this should return A , C and any other that has both.

One solution was to "explode" into new data frames by events, merge all by actors, creating a new data frame like:

Actors Event01 Event02 Event03
A Up Down
B Up
C Up Down Left
D Left

or even a dictionary with lists, like:

{'A':['Up', 'Down'], 'B': ... }

But these solutions don't look very smart, thus consuming time, memory and process to explode and rebuild it.

Plus, the big new DataFrame should have new columns with new names for events (not only one named events) which could be a little stressful to managing.

Does anyone have a better solution?

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
TheBasalt
  • 21
  • 3
  • `df.assign(col=df.groupby('Actors').cumcount()).pivot(index='Actors', columns='col', values='Events')` – mozway Apr 22 '22 at 12:47
  • Hi, @mozway. Thanks for the reply. But, actually, I don't wanna pivot the table, 'cause this should give me named columns that will be hard to manage. I want to find a way to search into only one column and find all actors that satisfies the query. – TheBasalt Apr 22 '22 at 17:33
  • Then your question is a bit too vague or off-topic. You should know which effect result you expect to ask a specific question. What's wrong with the original long format? – mozway Apr 22 '22 at 17:43
  • Thanks for the observation. I rewrote the question. What I am seeking is to return actors that have more than one queried events. Eg.: actor(s) that have UP and DOWN event(s) Following the presented table, the answer must be A and C actors. – TheBasalt Apr 22 '22 at 18:04
  • Maybe you want something like `df.groupby('Actors')['Events'].nunique()`? – mozway Apr 22 '22 at 19:19
  • Hello, @mozway. I believe the solution is something with 'groupby' but checking if the actors is subset of [Up, Down]. Some like a subset, not pivot. The pivot code u passed gave mixed values columns (For the first column, the value is the first to appears - so if one actor has Up and the other Down, the first column should show UP and DOWN - mixed values). Still trying to find the solution for this Series. Thanks again, my friend. The pivot – TheBasalt Apr 24 '22 at 15:18

0 Answers0