[Python] Count unique values on how many instances they are checked or not in python

I'm trying to count how many unique issue numbers on my dataframe and what is their latest Perf Status. I have a table below on what my dataframe looks like, its a bunch of Issues with logs of dates on when the Perf is checked and unchecked.

What I would need are:

• Count of How many unique issue number that are Perf Status went back and forth
• Count of How many unique issue number that are Perf Status that are latest status is True
• Count of How many unique issue number that are Perf Status that are latest status is False
• Count of How many unique issue number that are Perf Status that got at least checked True

Issue Number Date Field New Value Old Value
001 2024-06-10 10:26:00 Perf T F
001 2024-06-06 11:05:00 Perf F T
001 2024-06-01 21:37:00 Perf T F
001 2024-06-01 11:48:00 Perf F

What I have so far:

# Filter data where Field is 'ERC Report as Perf Team'
filtered_data = dfmain[dfmain['Field'] == filter]

# Create a new column to indicate status change
filtered_data['Status_Change'] = filtered_data['Perf Time'].shift(-1) != filtered_data['Perf Time']

# Convert Perf_Status to boolean (F to False, T to True)
filtered_data['Perf Time'] = filtered_data['Perf Time'].map({'F': False, 'T': True})

# Group by Issue_Number and count status changes
status_change_counts = filtered_data.groupby(['Number','Perf Time'])['Status_Change'].sum().reset_index()

# Filter and count F to T and T to F changes
ft_count = status_change_counts[(status_change_counts['Status_Change'] == 1) & (status_change_counts['Perf Time'] == True)].shape[0]
tf_count = status_change_counts[(status_change_counts['Status_Change'] == 1) & (status_change_counts['Perf Time'] == False)].shape[0]

# Print the counts
print(f"Number of iterations from F to T: {ft_count}")
print(f"Number of iterations from T to F: {tf_count}")

Continue reading...