How do I remove duplicates where one has a null value in Python?

Question

Problem

Sorry to all who have helped, but I have had to rephrase the question. I have a dataframe with duplicates for most of the columns, except the last column. Where I have duplicates, I want to apply the following rule:

1. If both have valid entries in the last column, then keep both.
2. If both have null entries in the last column, then keep one.
3. If one has a valid entry and the other a null entry, then keep the valid entry.

I then want to take the duplicate values out and create a separate dataframe with them. At the moment, my approach is laborious and deletes both duplicates where they are both null.

Reprex

Starting Dataframe

import pandas as pd
import numpy as np

data_input = {'Student':     ['A', 'A',          'B', 'B',            'C',      'D',      'E',      'F', 'F',         'G',     "H",     "H", "I", "I"], 
              "Subject": ["Law", "Law",      "Maths", "Maths",    "Maths", "Law",    "Maths",  "Music", "Music", "Music",      "Art", "Art", "Dance", "Dance"], 
              "Checked":  ["Bob", "James",    np.nan,  "Jack",     "Laura", "Laura",  np.nan,    np.nan, "Tim",   "Tim",       "Tim", np.nan, np.nan, np.nan]}

# Create DataFrame
df1 = pd.DataFrame(data_input)

Desired Output

First Attempt

attempt1 = df1.sort_values(['Student', 'Checked'], ascending=False).drop_duplicates(["Student", "Subject"]).sort_index()

I took this from another Q&A on Stack, but it does not give me the outcome I want and I don't understand it.

Attempt 2

#Create Duplicate column
df1["Duplicates"] = df1.duplicated(subset=["Student", "Subject"], keep=False)

#Create list of rows with no duplicates
df_new1 = df1[df1["Duplicates"]==False]

#Create list of rows with duplicates & remove all those with null values
#HERE IS WHERE I GET STUCK. IF BOTH DUPLICATES ARE NULLS, I WANT TO KEEP ONE OF THEM
df_new2 = df1[df1["Duplicates"]==True]
df_new3 = df_new2[~df_new2["Checked"].isnull()]

#Combine unique rows, and duplicates without null values
#Keep duplicates without null values
df_new = df_new1.append(df_new3)

#Tidy up
df_new = df_new[["Student", "Subject", "Checked"]].sort_values(by="Student")

df_new

I can then create a list of the duplicates that both appear valid

#Create separate list of duplicates with valid "Checked" values
df_new["Duplicates"] = df_new.duplicated(subset="Student", keep=False)
conflicting_duplicates = df_new[df_new["Duplicates"]==True]
conflicting_duplicates

Help

Thank you to everyone! Your answers helped, but I hadn't realised that I also want to keep one of the entries where both are null.

Is there a better way of doing this?

Answer 1

Use boolean indexing:

# is the group containing more than one row?
m1 = df1.duplicated(['Student', 'Subject'], keep=False)
# is the row a NaN in "Checked"?
m2 = df1['Checked'].isna()
# both conditions True
m = m1&m2

# keep if either condition is False 
df1[~m]

# to get dropped duplicates
# keep if both are True
df1[m]

Output:

   Student Subject Checked
0        A     Law     Bob
1        A     Law   James
3        B   Maths    Jack
4        C   Maths   Laura
5        D     Law   Laura
6        E   Maths     NaN
8        F   Music     Tim
9        G   Music     Tim
10       H     Art     Tim