replace values based on Number of duplicate rows are occured
I would do this manually. First, let us create the index set of entries whose state must remain active. To do this, I iterate over all rows and record active instances. Note that the later occurrence overrides earlier ones, so we keep only the last one occurrence of active event.
last_active = dict()
for i, row in df.iterrows():
    if row['Active'] == 'Active':
        last_active[row['site']] = i
keep_active = last_active.values()

Now I assign the state 'Active' to those entries whose index is in keep_active and InActive otherwise.
df['refined_active'] = df.apply(lambda x: 'Active' if x.name in keep_active else 'InActive', axis=1)