Counting the transition in a dataframe overtime
For a small dataset try this:
# change type of date column so that it is possible to find min and max correctly
df['date'] = pd.to_datetime(df['date'])

# put value of the oldest resord and newest record side by side
df_min_max_grouped = pd.merge(
    df.loc[df.groupby('Id')['date'].idxmin()],
    df.loc[df.groupby('Id')['date'].idxmax()],
    on='Id',
    suffixes=('_old', '_new')
)

# group by old value and new value and find counts
results = df_min_max_grouped.groupby(['value_old', 'value_new']).size()

results

Output:

value_old  value_new  
Active     Notactive      1
           Superactive    1
Notactive  Superactive    1
dtype: int64

The approach I chose for this answer:

Convert date values to be of type datetime, Correct ordering will not work with string dates.
First find the oldest and the newest records for any Id,
This happens in : df.loc[df.groupby(&quot;Id&quot;)['date'].idxmin()] for oldest and df.loc[df.groupby(&quot;Id&quot;)['date'].idxmax()] and newest records.
Join the oldest and newest records on the basis of Id, and then
Group by old value and new value combination to get a count in each group.

I found that resetting index of the resulting Series produces nice output:
results.reset_index()






value_old
value_new
0




0
Active
Notactive
1


1
Active
Superactive
1


2
Notactive
Superactive
1




The combinations not listed here have 0 records against them. I hope you find this helpful!