How find and return rows of pandas dataframe with unique values?

Question

Say I have the following dataframe:

import pandas as pd

series = [('Stranger Things', 3, 'Millie'),
          ('Game of Thrones', 8, 'Emilia'), 
          ('La Casa De Papel', 4, 'Sergio'),
          ('Westworld', 3, 'Evan Rachel'), 
          ('Stranger Things', 3, 'Todd'),
          ('La Casa De Papel', 4, 'Sergio')]

# Create a DataFrame object
df = pd.DataFrame(series, columns=['Name', 'Seasons', 'Actor'])

I am looking for a way to create a new dataframe, or even a list, that tells me the non-unique combinations of values between 'Name' and 'Actor'.

In this example, I would like to get as a result:

Stranger Things, 3, Millie
Stranger Things, 3, Todd

I have tried the sort(), unique(), and distinct() methods without success. Unique always seems to drop the column that I am not querying on (in this case, season).

Any help is appreciated!

Would you explain a bit more what you mean by "non-unique combinations of values between 'Name' and 'Actor'"? — zabop, Oct 01 '21 at 21:48
If Movie is Stranger Things and Actor is Millie in one row, I would like to know if Stranger Things comes up again with Todd as the actor for example. I don't need to know if there is a duplicate of that row (Stranger Things + Millie) in the dataframe. Is that helpful? — SquatLicense, Oct 01 '21 at 21:55
So you want rows where the movie name is not unique, but the actor name belonging to one movie ie unique? — zabop, Oct 01 '21 at 21:56
Why wouldn't it? You first group by Name and then by actor. For example, ` df.groupby(['Name', 'Actor']).agg('count')`. — navneethc, Oct 01 '21 at 22:16
The problem with that is season gets replaced with the count — SquatLicense, Oct 01 '21 at 22:21

score 2 · Answer 1 · answered Oct 01 '21 at 23:22

2

Do you need groupby with nunique?

df[df.groupby('Name')['Actor'].transform('nunique').gt(1)]

              Name  Seasons   Actor
0  Stranger Things        3  Millie
4  Stranger Things        3    Todd

answered Oct 01 '21 at 23:22

cs95

379,657
97
704
746

We wrote the (almost) same code but yours is more "pandas" than mine :) Deleting my answer and upvote. – Akshay Sehgal Oct 01 '21 at 23:25
@AkshaySehgal sorry, didn't see your answer. Just saw the accepted one and thought, hmm, I think this can be done more concisely :-) – cs95 Oct 01 '21 at 23:56
1

totally agree on that. great answer. – Akshay Sehgal Oct 01 '21 at 23:58

score 1 · Accepted Answer · answered Oct 01 '21 at 23:12

This will return a dataframe with those two rows that you show in your post:

actor_cts = df.drop_duplicates(subset=['Name','Actor']).groupby("Name")['Actor'].count()
df[df.Name.isin(actor_cts[actor_cts > 1].index)].reset_index(drop=True)

#              Name Seasons  Actor
# 0 Stranger Things       3 Millie
# 1 Stranger Things       3   Todd

score 0 · Answer 3 · answered Oct 01 '21 at 22:29

this is what I found based on your code! you could change the check_if_similar function to go through all of the data frames Keys too but unfortunately, I Don't have the time to put all of that together. If you have any questions feel free to ask me.

import pandas as pd


def check_if_similar(things):
    similar = []
    num1 = 0
    for _ in things:
        num2 = 0
        for _ in things:
            if num1 != num2:
                if things[num1] == things[num2] and num1 > num2:
                    similar.append([num1, num2])
            num2 += 1
        num1 += 1
    return similar


series = [('Stranger Things', 3, 'Millie'),
          ('Game of Thrones', 8, 'Emilia'), ('La Casa De Papel', 4, 'Sergio'),
          ('Westworld', 3, 'Evan Rachel'), ('Stranger Things', 3, 'Todd'),
          ('La Casa De Papel', 4, 'Sergio')]

# Create a DataFrame object
df = pd.DataFrame(series, columns=['Name', 'Seasons', 'Actor'])

info = check_if_similar(df['Name'].to_list())

for coords in info:
    if str(df.values[coords[0]]) != str(df.values[coords[1]]):
        print(str(df.values[coords[0]]) + "\n" + str(df.values[coords[1]]))
    else:
        print(str(df.values[coords[0]]) + " == " + str(df.values[coords[1]]))

How find and return rows of pandas dataframe with unique values?

3 Answers3